|SQL*Plus User's Guide and Reference
Part Number A88827-02
Manipulating Commands, 4 of 6
The following features of SQL*Plus make it possible for you to set up command files that allow end-user input:
You can define variables, called user variables, for repeated use in a single command file by using the SQL*Plus DEFINE command. Note that you can also define user variables to use in titles and to save your keystrokes (by defining a long string as the value for a variable with a short name).
To define a user variable L_NAME and give it the value "SMITH", enter the following command:
To confirm the variable definition, enter DEFINE followed by the variable name:
To list all user variable definitions, enter DEFINE by itself at the command prompt. Note that any user variable you define explicitly through DEFINE takes only CHAR values (that is, the value you assign to the variable is always treated as a CHAR datatype). You can define a user variable of datatype NUMBER implicitly through the ACCEPT command. You will learn more about the ACCEPT command later in this chapter.
To delete a user variable, use the SQL*Plus command UNDEFINE followed by the variable name.
Suppose you want to write a query like the one in SALES (see Example 3-7) to list the employees with various jobs, not just those whose job is SA_MAN. You could do that by editing a different CHAR value into the WHERE clause each time you run the command, but there is an easier way.
By using a substitution variable in place of the value SA_MAN in the WHERE clause, you can get the same results you would get if you had written the values into the command itself.
A substitution variable is a user variable name preceded by one or two ampersands (&). When SQL*Plus encounters a substitution variable in a command, SQL*Plus executes the command as though it contained the value of the substitution variable, rather than the variable itself.
For example, if the variable SORTCOL has the value JOB_ID and the variable MYTABLE has the value EMP_DETAILS_VIEW, SQL*Plus executes the commands
as if they were
You can use substitution variables anywhere in SQL and SQL*Plus commands, except as the first word entered at the command prompt. When SQL*Plus encounters an undefined substitution variable in a command, SQL*Plus prompts you for the value.
You can enter any string at the prompt, even one containing blanks and punctuation. If the SQL command containing the reference should have quote marks around the variable and you do not include them there, the user must include the quotes when prompted.
SQL*Plus reads your response from the keyboard, even if you have redirected terminal input or output to a file. If a terminal is not available (if, for example, you run the command file in batch mode), SQL*Plus uses the redirected file.
After you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. You can suppress this listing by setting the SET command variable VERIFY to OFF.
You should avoid creating substitution variables with names that may be identical to values that you will pass to them, as unexpected results can occur. If a value supplied for a substitution variable matches a variable name, then the contents of the matching variable are used instead of the supplied value.
Create a command file named STATS, to be used to calculate a subgroup statistic (the maximum value) on a numeric column:
CLEAR BUFFER INPUT SAVE STATS Created file STATS
Now run the command file STATS:
And respond to the prompts for values as shown:
Enter value for group_col: JOB_ID old 1: SELECT &GROUP_COL, new 1: SELECT JOB_ID, Enter value for number_col: SALARY old 2: MAX(&NUMBER_COL) MAXIMUM new 2: MAX(SALARY) MAXIMUM Enter value for table: EMP_DETAILS_VIEW old 3: FROM &TABLE new 3: FROM EMP_DETAILS_VIEW Enter value for group_col: JOB_ID old 4: GROUP BY &GROUP_COL new 4: GROUP BY JOB_ID
SQL*Plus displays the following output:
JOB_ID MAXIMUM ---------- ---------- AC_ACCOUNT 8300 AC_MGR 12000 AD_ASST 4400 AD_PRES 24000 AD_VP 17000 FI_ACCOUNT 9000 FI_MGR 12000 HR_REP 6500 IT_PROG 9000 MK_MAN 13000 MK_REP 6000 JOB_ID MAXIMUM ---------- ---------- PR_REP 10000 PU_CLERK 3100 PU_MAN 11000 SA_MAN 14000 SA_REP 11500 SH_CLERK 4200 ST_CLERK 3600 ST_MAN 8200 19 rows selected.
If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example:
is interpreted as
Suppose you wanted to expand the file STATS to include the minimum, sum, and average of the "number" column. You may have noticed that SQL*Plus prompted you twice for the value of GROUP_COL and once for the value of NUMBER_COL in Example 3-12, and that each GROUP_COL or NUMBER_COL had a single ampersand in front of it. If you were to add three more functions--using a single ampersand before each--to the command file, SQL*Plus would prompt you a total of four times for the value of the number column.
You can avoid being re-prompted for the group and number columns by adding a second ampersand in front of each GROUP_COL and NUMBER_COL in STATS. SQL*Plus automatically DEFINEs any substitution variable preceded by two ampersands, but does not DEFINE those preceded by only one ampersand. When you have DEFINEd a variable, SQL*Plus substitutes the value of variable for each substitution variable referencing variable (in the form &variable or &&variable). SQL*Plus will not prompt you for the value of variable in this session until you UNDEFINE variable.
To expand the command file STATS using double ampersands and then run the file, first suppress the display of each line before and after substitution:
Now retrieve and edit STATS by entering the following commands:
GET STATS SELECT &GROUP_COL, MAX(&NUMBER_COL) MAXIMUM FROM &TABLE GROUP BY &GROUP_COL 2 2* MAX(&NUMBER_COL) MAXIMUM APPEND , 2* MAX(&NUMBER_COL) MAXIMUM, CHANGE/&/&& 2* MAX(&&NUMBER_COL) MAXIMUM, I 3i MIN (&&NUMBER_COL) MINIMUM, 4i SUM(&&NUMBER_COL) TOTAL, 5i AVG(&&NUMBER_COL) AVERAGE 6i 1* SELECT &GROUP_COL, CHANGE/&/&& 1* SELECT &&GROUP_COL, 7 7* GROUP BY &GROUP_COL CHANGE/&/&&/ 7* GROUP BY &&GROUP_COL SAVE STATS2 Created file STATS2
Finally, run the command file STATS2 and respond to the prompts as follows:
START STATS2 Enter value for group_col: JOB_ID Enter value for number_col: SALARY Enter value for table: EMP_DETAILS_VIEW
SQL*Plus displays the following output:
JOB_ID MAXIMUM MINIMUM TOTAL AVERAGE ---------- ---------- ---------- ---------- ---------- AC_ACCOUNT 8300 8300 8300 8300 AC_MGR 12000 12000 12000 12000 AD_ASST 4400 4400 4400 4400 AD_PRES 24000 24000 24000 24000 AD_VP 17000 17000 34000 17000 FI_ACCOUNT 9000 6900 39600 7920 FI_MGR 12000 12000 12000 12000 HR_REP 6500 6500 6500 6500 IT_PROG 9000 4200 28800 5760 MK_MAN 13000 13000 13000 13000 MK_REP 6000 6000 6000 6000 JOB_ID MAXIMUM MINIMUM TOTAL AVERAGE ---------- ---------- ---------- ---------- ---------- PR_REP 10000 10000 10000 10000 PU_CLERK 3100 2500 13900 2780 PU_MAN 11000 11000 11000 11000 SA_MAN 14000 10500 61000 12200 SA_REP 11500 6100 250500 8350 SH_CLERK 4200 2500 64300 3215 ST_CLERK 3600 2100 55700 2785 ST_MAN 8200 5800 36400 7280 19 rows selected.
Note that you were prompted for the values of NUMBER_COL and GROUP_COL only once. If you were to run STATS2 again during the current session, you would be prompted for TABLE (because its name has a single ampersand and the variable is therefore not DEFINEd) but not for GROUP_COL or NUMBER_COL (because their names have double ampersands and the variables are therefore DEFINEd).
Before continuing, set the system variable VERIFY back to ON:
You cannot use substitution variables in the buffer editing commands, APPEND, CHANGE, DEL, and INPUT, nor in other commands where substitution would be meaningless, such as in SQL*Plus comments (REMARK, /*... */ or --). The buffer editing commands, APPEND, CHANGE, and INPUT, treat text beginning with "&" or "&&" literally, as any other text string.
The following system variables, specified with the SQL*Plus SET command, affect substitution variables:
Defines the substitution character (by default the ampersand "&") and turns substitution on and off.
Defines an escape character you can use before the substitution character. The escape character instructs SQL*Plus to treat the substitution character as an ordinary character rather than as a request for variable substitution. The default escape character is a backslash (\).
Lists each line of the command file before and after substitution.
Defines the character that separates the name of a substitution variable or parameter from characters that immediately follow the variable or parameter--by default the period (.).
You can bypass the prompts for values associated with substitution variables by passing values to parameters in a command file through the START command.
You do this by placing an ampersand (&) followed by a numeral in the command file in place of a substitution variable. Each time you run this command file, START replaces each &1 in the file with the first value (called an argument) after START filename, then replaces each &2 with the second value, and so forth.
For example, you could include the following commands in a command file called MYFILE:
In the following START command, SQL*Plus would substitute CLERK for &1 and 7900 for &2 in the command file MYFILE:
When you use arguments with the START command, SQL*Plus DEFINEs each parameter in the command file with the value of the appropriate argument.
To create a new command file based on SALES that takes a parameter specifying the job to be displayed, enter
GET SALES 1 COLUMN LAST_NAME HEADING 'LAST NAME' 2 COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999 3 COLUMN COMMISSION_PCT HEADING 'COMMISSION %' FORMAT 90.90 4 SELECT LAST_NAME, SALARY, COMMISSION_PCT 5 FROM EMP_DETAILS_VIEW 6* WHERE JOB_ID='SA_MAN' 6 6* WHERE JOB_ID='SA_MAN' CHANGE /SA_MAN/&1 6* WHERE JOB_ID='&1' SAVE ONEJOB Created file ONEJOB
Now run the command with the parameter CLERK:
SQL*Plus lists the line of the SQL command that contains the parameter, before and after replacing the parameter with its value, and then displays the output:
old 3: WHERE JOB_ID='&1' new 3: WHERE JOB_ID='SA_MAN' LAST NAME MONTHLY SALARY COMMISSION % ------------------------- -------------- ------------ Russell $14,000 0.40 Partners $13,500 0.30 Errazuriz $12,000 0.30 Cambrault $11,000 0.30 Zlotkey $10,500 0.20
You can use any number of parameters in a command file. Within a command file, you can refer to each parameter any number of times, and can include the parameters in any order.
Before continuing, return the columns to their original heading by entering the following command:
Three SQL*Plus commands--PROMPT, ACCEPT, and PAUSE--help you communicate with the end user. These commands enable you to send messages to the screen and receive input from the user, including a simple Return. You can also use PROMPT and ACCEPT to customize the prompts for values SQL*Plus automatically generates for substitution variables.
Through PROMPT and ACCEPT, you can send messages to the end user and accept values as end-user input. PROMPT displays a message you specify on-screen; use it to give directions or information to the user. ACCEPT prompts the user for a value and stores it in the user variable you specify. Use PROMPT in conjunction with ACCEPT when your prompt for the value spans more than one line.
To direct the user to supply a report title and to store the input in the variable MYTITLE for use in a subsequent query, first clear the buffer:
Next, set up a command file as shown and save this file as PROMPT1:
INPUT 4 PROMPT Enter a title of up to 30 characters 5 ACCEPT MYTITLE PROMPT 'Title: ' 6 TTITLE LEFT MYTITLE SKIP 2 7 SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY 8 FROM EMP_DETAILS_VIEW 9 WHERE JOB_ID='SA_MAN' 10 SAVE PROMPT1 Created file PROMPT1.sql
Finally, run the command file, responding to the prompt for the title as shown:
START PROMPT1 Enter a title of up to 30 characters Title: Department Report Department Report EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY ----------- -------------------- ------------------------- ---------- 145 John Russell 14000 146 Karen Partners 13500 147 Alberto Errazuriz 12000 148 Gerald Cambrault 11000 149 Eleni Zlotkey 10500
Before continuing, turn the TTITLE command off:
If you want to customize the prompt for a substitution variable value, use PROMPT and ACCEPT in conjunction with the substitution variable, as shown in the following example.
As you have seen in Example 3-15, SQL*Plus automatically generates a prompt for a value when you use a substitution variable. You can replace this prompt by including PROMPT and ACCEPT in the command file with the query that references the substitution variable. First clear the buffer with:
To create such a file, enter the following:
Save this file as PROMPT2. Next, run this command file. SQL*Plus prompts for the value of ENUMBER using the text you specified with PROMPT and ACCEPT:
SQL*Plus prompts you to enter an Employee ID:
Enter a valid employee ID For Example 145, 206 Employee ID. :205 old 3: WHERE EMPLOYEE_ID=&ENUMBER new 3: WHERE EMPLOYEE_ID= 205 Department Report FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Shelley Higgins 12000
What would happen if you typed characters instead of numbers? Since you specified NUMBER after the variable name in the ACCEPT command, SQL*Plus will not accept a non-numeric value:
Try entering characters instead of numbers to the prompt for "Employee. ID.", SQL*Plus will respond with an error message and prompt you again to re-enter the correct number:
When SQL*Plus prompts you to enter an Employee ID, enter the word "one" instead of a number:
Enter a valid employee ID For Example 145, 206 Employee ID. :one SP2-0425: "one" is not a valid number
If you want to display a message on the user's screen and then have the user enter Return after reading the message, use the SQL*Plus command PAUSE. For example, you might include the following lines in a command file:
If you want to clear the screen before displaying a report (or at any other time), include the SQL*Plus CLEAR command with its SCREEN clause at the appropriate point in your command file, using the following format:
Before continuing to the next section, reset all columns to their original formats and headings by entering the following command: