Substitution variables appear in SQL or SQL*Plus commands. SQL*Plus prompts for a value when you
execute those commands. We have used substitution variables in earlier examples in this book (Listing
5-14, for example, to test certain commands multiple times with different literal values.
Substitution variable values are volatile; that is, SQL*Plus doesn’t remember them and doesn’t store
them anywhere. This is what distinguishes substitution variables from the other two types. If you
execute the same SQL or SQL*Plus command again, SQL*Plus prompts for a value again. The default
character that makes SQL*Plus prompt for a substitution variable value is the ampersand (&), also known
as the DEFINE character. Check out what happens in Listing 11-1.
Listing 11-1. Using the DEFINE Character (&)
SQL> select * from departments
2 where dname like upper('%&letter%');
Enter value for letter: a
old 2: where dname like upper('%&letter%')
new 2: where dname like upper('%a%')
DEPTNO DNAME LOCATION MGR
-------- ---------- -------- --------
10 ACCOUNTING NEW YORK 7782
20 TRAINING DALLAS 7566
30 SALES CHICAGO 7698
SQL>
288 CHAPTER 11 „ WRITING AND AUTOMATING SQL*PLUS SCRIPTS
Actually, if a substitution variable occurs twice within a single command, SQL*Plus also prompts
twice for a value, as demonstrated in Listing 11-2.
Listing 11-2. Prompting Twice for the Same Variable
SQL> select ename from employees
2 where empno between &x and &x+100;
Enter value for x: 7500
Enter value for x: 7500
old 2: where empno between &x and &x+100
new 2: where empno between 7500 and 7500+100
ENAME
--------
WARD
JONES
SQL>
You can use the period character (.) to mark the end of the name of a substitution variable, as
shown in Listing 11-3. The period (.) is also known as the CONCAT character in SQL*Plus.
Normally, you don’t need the CONCAT character very often, because white space is good enough to
delimit variable names; however, white space in strings can sometimes be undesirable. See Listing 11-3
for an example.
Listing 11-3. Using the DEFINE and CONCAT Characters
SQL> select '&drink.glass' as result from dual;
Enter value for drink: beer
old 1: select '&drink.glass' as result from dual
new 1: select 'beerglass' as result from dual
RESULT
---------
beerglass
SQL>
Note that you can display the current settings of the DEFINE and CONCAT characters with the SQL*Plus
SHOW command, and you can change these settings with the SQL*Plus SET command, as shown in Listing
11-4.
289 CHAPTER 11 „ WRITING AND AUTOMATING SQL*PLUS SCRIPTS
Listing 11-4. Displaying the DEFINE and CONCAT Character Settings
SQL> show define
define "&" (hex 26)
SQL> show concat
concat "." (hex 2e)
SQL>
If you don’t want SQL*Plus to display the explicit replacement of substitution variables by the values
you entered (as in Listings 11-1, 11-2, and 11-3), you can suppress that with the SQL*Plus VERIFY setting,
as shown in Listing 11-5.
Listing 11-5. Switching the VERIFY Setting ON and OFF
SQL> set verify on
SQL> set verify off
SQL> show verify
verify OFF
SQL>
If you change the VERIFY setting to OFF, as shown in Listing 11-5, and you execute the SQL command
(still in the SQL buffer) with the SQL*Plus RUN command, you don’t see the “old: ...” and “new: ...”
lines anymore, as shown in Listing 11-6.
Listing 11-6. The Effect of VERIFY OFF
SQL> select ename from employees
2 where empno between &x and &x+100;
Enter value for x: 7500
Enter value for x: 7500
ENAME
--------
WARD
JONES
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-735493/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27042095/viewspace-735493/