Release 8.1.5
A66736-01
 Library |
 Product |
 Contents |
 Index |
| SQL*Plus User's Guide and Reference Release 8.1.5 A66736-01 |
|
Manipulating Commands, 4 of 7
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 you keystrokes (by defining a long string as the value for a variable with a short name).
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 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 SALESMAN. 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 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. For more information about the BREAK command, see the section "Clarifying Your Report with Spacing and Summary Lines" in Chapter 4.)
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.
Create a command file named 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
is interpreted as
SQL> SELECT * FROM EMP WHERE EMPNO='12301';
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:
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
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 REMARK. 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:
For more information about system variables, see the SET command in the "Command Reference" in Chapter 8.
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:
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.
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,300
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.
|
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
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 simply 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:
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 in Chapter 4.
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 BOSTON
Before continuing, turn the TTITLE command you entered in the command file off as shown below:
SQL> TTITLE 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. 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.:
Since 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
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:
PROMPT Before continuing, make sure you have your account card.
PAUSE Press RETURN to continue.
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:
CLEAR SCREEN
Before continuing to the next section, reset all columns to their original formats and headings by entering the following command:
SQL> CLEAR COLUMNS
|
 Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|