SQL语言之和符号(&)替换

 

可以使用和符号(&)来替换SQL语句中的元素，包括所有文本、子句、子子句。即通过和符号来指定变量。

1. SQL语句中最基本、最普遍的元素替换形式是单个和符号(&)替换。和字符是用来在语句中指定替换变量的符号，它在变量名之前，并且它们之间没有空格。
2. Oracle使用以下两种方式来确定变量的值：
   • 检查用户会话中是否使用DEFINE定义了该变量。
   • 如果没有定义变量，在SQL进程运行时，由用户指定(即输入一个变量值)。
3. 如果变量要替换字符或者日期值，需要将字面值包含在单引号内。
4. 示例1：

SQL> select employee_id,last_name,salary from employees

  2  where last_name='&LASTNAME';

Enter value for lastname: King

old   2: where last_name='&LASTNAME'

new   2: where last_name='King'

 

EMPLOYEE_ID LAST_NAME                     SALARY

----------- ------------------------- ----------

        156 King                           10000

        100 King                           24000

注：如不在&LASTNAME外添加''，则需要在输入King时添加''，否则会报错。

 

示例2：

SQL> define LASTNAME=King;

SQL> select employee_id,last_name,salary from employees

  2  where last_name='&LASTNAME';

old   2: where last_name='&LASTNAME'

new   2: where last_name='King'

 

EMPLOYEE_ID LAST_NAME                     SALARY

----------- ------------------------- ----------

        156 King                           10000

        100 King                           24000

 

5. 可以使用和字符替换列名，和字符、日期字符字面值不一样，当指定或者通过&替换变量指定时，列名引用都不需要单引号。
6. 和字符可以替换SQL语句的任何元素，但至少第一个单词是静态的，如：SELECT $rest_of_statement。
7. oracle不会检验相同替换变量输入的字面值。
8. 使用双和符号(&&)定义的变量，oracle服务器会将其作为会话变量，在后续引用中不再提示替换该变量值。在会话期间或在显式解除定义前，此变量一直有效。
9. 使用UNDEFINE可以显式解除&&定义的会话变量值。
10. DEFINE命令有两个作用：
    • 可以用来检索SQL会话中当前定义的所有变量列表
    • 可以定义会话期间一个或者多个变量的值
11. 使用SET DEFINE ON/OFF可以打开或关闭和字符(&)的替换功能。当输入的字面值中包括和符号(&)时，可在之前使用set define off来关闭字符(&)的替换功能。
12. VERIFY命令专用于SQL*PLUS，可以控制提交的替换变量是否显示到屏幕上，以便验证替换是否正确。即示例中的：

old   2: where last_name='&LASTNAME'

new   2: where last_name='King'

可以使用SET VERIFY ON/OFF来打开或关闭此功能。