Example 3-11 Defining a User Variable
To define a user variable EMPLOYEE and give it the value "SMITH", enter the following command:
SQL> DEFINE EMPLOYEE = SMITH
To confirm the definition of the variable, enter DEFINE followed by the variable name:
SQL> DEFINE EMPLOYEE
SQL*Plus lists the definition:
DEFINE EMPLOYEE = "SMITH" (CHAR)
To delete a user variable, use the SQL*Plus command UNDEFINE followed by the variable name.
By using a substitution variable in place of the value SALESMAN 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 and the variable MYTABLE has the value EMP, SQL*Plus executes the commands
SQL> BREAK ON &SORTCOL SQL> SELECT &SORTCOL, SAL 2 FROM &MYTABLE 3 ORDER BY &SORTCOL;
as if they were
SQL> BREAK ON JOB SQL> SELECT JOB, SAL 2 FROM EMP 3 ORDER BY JOB;
(The BREAK command suppresses duplicate values of the column named in SORTCOL; BREAK is discussed![[*]](jump.gif)
.)
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.
Example 3-12 Using Substitution Variables
Create a command filenamed STATS, to be used to calculate a subgroup statistic (the maximum value) on a numeric column:
SQL> CLEAR BUFFER SQL> INPUT 1 SELECT &GROUP_COL, 2 MAX(&NUMBER_COL) MAXIMUM 3 FROM &TABLE 4 GROUP BY &GROUP_COL 5 SQL> SAVE STATS Created file STATS
Now run the command file STATS and respond as shown below to the prompts for values:
SQL> @STATS Enter value for group_col: JOB old 1: SELECT &GROUP_COL, new 1: SELECT JOB, Enter value for number_col: SAL old 2: MAX(&NUMBER_COL) MAXIMUM new 2: MAX(SAL) MAXIMUM Enter value for table: EMP old 3: FROM &TABLE new 3: FROM EMP Enter value for group_col: JOB old 4: GROUP BY &GROUP_COL new 4: GROUP BY JOB
SQL*Plus displays the following output:
JOB MAXIMUM ---------- ---------- ANALYST 3000 CLERK 1300 MANAGER 2975 PRESIDENT 5000 SALESMAN 1600
If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example:
SQL> SELECT * FROM EMP WHERE EMPNO='&X.01'; Enter value for X: 123
will be interpreted as
SQL> SELECT * FROM EMP WHERE EMPNO='12301';
You can avoid being reprompted 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.
Example 3-13 Using Double Ampersands
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:
SQL> SET VERIFY OFF
Now retrieve and edit STATS by entering the following commands:
SQL> GET STATS 1 SELECT &GROUP_COL, 2 MAX(&NUMBER_COL) MAXIMUM 3 FROM &TABLE 4 GROUP BY &GROUP_COL SQL> 2 2* MAX(&NUMBER_COL) MAXIMUM SQL> APPEND , 2* MAX(&NUMBER_COL) MAXIMUM, SQL> C /&/&& 2* MAX(&&NUMBER_COL) MAXIMUM, SQL> I 3i MIN(&&NUMBER_COL) MINIMUM, 4i SUM(&&NUMBER_COL) TOTAL, 5i AVG(&&NUMBER_COL) AVERAGE 6i SQL> 1 1* SELECT &GROUP_COL, SQL> C /&/&& 1* SELECT &&GROUP_COL, SQL> 7 7* GROUP BY &GROUP_COL SQL> C /&/&& 7* GROUP BY &&GROUP_COL SQL> SAVE STATS2 created file STATS2
Finally, run the command file STATS2 and respond to the prompts for values as follows:
SQL> START STATS2 Enter value for group_col: JOB Enter value for number_col: SAL Enter value for table: EMP
SQL*Plus displays the following output:
JOB MAXIMUM MINIMUM TOTAL AVERAGE ---------- ---------- ---------- ---------- --------- ANALYST 3000 3000 6000 3000 CLERK 1300 800 4150 1037.5 MANAGER 2975 2450 8275 2758.33333 PRESIDENT 5000 5000 5000 5000 SALESMAN 1600 1250 5600 1400
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:
SQL> SET VERIFY ON
for more information on these system variables.
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:
SELECT * FROM EMP WHERE JOB='&1' AND SAL=&2
In the following START command, SQL*Plus would substitute CLERK for &1 and 7900 for &2 in the command file MYFILE:
SQL> START MYFILE CLERK 7900
When you use arguments with the START command, SQL*Plus DEFINEs each parameter in the command file with the value of the appropriate argument.
Example 3-14 Passing Parameters through START
To create a new command file based on SALES that takes a parameter specifying the job to be displayed, enter
SQL> GET SALES 1 COLUMN ENAME HEADING SALESMAN 2 COLUMN SAL HEADING SALARY FORMAT $99,999 3 COLUMN COMM HEADING COMMISSION FORMAT $99,990 4 SELECT EMPNO, ENAME, SAL, COMM 5 FROM EMP 6* WHERE JOB = 'SALESMAN' SQL> CHANGE /SALESMAN/&1 6* WHERE JOB = '&1' SQL> 1 1* COLUMN ENAME HEADING SALESMAN SQL> CHANGE /SALESMAN/&1 1* COLUMN ENAME HEADING &1 SQL> SAVE ONEJOB Created file ONEJOB
Now run the command with the parameter CLERK:
SQL> START ONEJOB 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 = '&1'
new 3: WHERE JOB = 'CLERK'
EMPNO CLERK SALARY COMMISSION
--------- ---------- ---------- ----------
7369 SMITH $800
7876 ADAMS $1,100
7900 JAMES $950
7934 MILLER $1,300You 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.
Note: You cannot use parameters when you run a command with RUN or slash (/). You must store the command in a command file and run it with START or @.
Before continuing, return the column ENAME to its original heading by entering the following command:
SQL> COLUMN ENAME CLEAR
Example 3-15 Prompting for and Accepting Input
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:
SQL> CLEAR BUFFER
Next, set up a command file as shown below:
SQL> INPUT 1 PROMPT Enter a title up to 30 characters long. 2 ACCEPT MYTITLE PROMPT 'Title: ' 3 TTITLE LEFT MYTITLE SKIP 2 4 SELECT * FROM DEPT 5 SQL> SAVE PROMPT1 Created file PROMPT1
The TTITLE command sets the top title for your report. This command is covered in detail.
Finally, run the command file, responding to the prompt for the title as shown:
SQL> START PROMPT1 Enter a title up to 30 characters long. Title: Department Report as of 1/1/95
SQL*Plus displays the following output:
Department Report as of 1/1/95
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONBefore continuing, turn the TTITLE command you entered in the command file off as shown below:
SQL> TTITLE OFF
Example 3-16 Using PROMPT and ACCEPT in Conjunction with Substitution Variables
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. To create such a file, enter the commands shown:
SQL> CLEAR BUFFER buffer cleared SQL> INPUT 1 PROMPT Enter a valid employee number 2 PROMPT For example: 7123, 7456, 7890 3 ACCEPT ENUMBER NUMBER PROMPT 'Emp. no.: ' 4 SELECT ENAME, MGR, JOB, SAL 5 FROM EMP 6 WHERE EMPNO = &ENUMBER 7 SQL> SAVE PROMPT2 Created file PROMPT2
Next, run the command file. SQL*Plus prompts for the value of ENUMBER using the text you specified with PROMPT and ACCEPT:
SQL> START PROMPT2 Enter a valid employee number For example: 7123, 7456, 7890 Emp. No.:
Try entering characters instead of numbers to the prompt for "Emp. No.":
Emp. No.: ONE "ONE" is not a valid number Emp. No.:
Because you specified NUMBER after the variable name in the ACCEPT command, SQL*Plus will not accept a non-numeric value. Now enter a number:
Emp. No.: 7521 old 3: WHERE EMPNO = &ENUMBER new 3: WHERE EMPNO = 7521
SQL*Plus displays the following output:
ENAME MGR JOB SALARY ---------- ---------- --------- ---------- WARD 7698 SALESMAN $1,250
PROMPT Before continuing, make sure you have your account card. PAUSE Press RETURN to continue.
CLEAR SCREEN
Before continuing to the next chapter, reset all columns to their original formats and headings by entering the following command:
SQL> CLEAR COLUMNS