More on variables with Oracle's SQL*Plus
Speaker Notes
Slide 1:
This continues the information on variables with Oracle SQL*Plus. NOTE: SQL*Plus goes far beyond the common specifications for SQL.
Remember from the previous presentation that you can request user input by putting an & or && in front of the variable name. The single & is for the current select while the && allows you to reuse the variable that has been saved without prompting the user each time.
Slide 2:
Remember when using order by, the default is ascending.
In this example, the user is not entering actual data. The user is entering the names of columns/fields that will be used when the SELECT is executed.
Slide 3:
Remember to clear the stored variable you use UNDEFINE col since col is the name I used with the && --- &&col.
Slide 4:
You can use the DEFINE and ACCEPT commans in SQL*Plus to predefine variables.
Slide 5:
DEFINE followed by a variable name can be used to check the definition of a variable.
Note that the user input is in CHAR.
Slide 6:
Note that this time the defined variable is being used for data.
The defined variable colin refers to a column/field.
The defined variable jobci refers to the data literal CI.
Slide 7:
The ACCEPT takes user input and stores it in the variable. Datatype can be specified along with format and prompt.
See notes for more information.
Slide 8:
Variables can be set up to take user input as the SELECT is being run or they can be predefined using the DEFINE or the ACCEPT.
Slide 9:
This uses the datatype, format and prompt features of ACCEPT to take in data as a variable.
Slide 10:
To suppress the display of the old and new, you can set verify off. When you want to see them again you can set verify on.
Set verify is essentially a toggle switch.