7.6 &符号替换

随着SQL语句的发展和完善，可以保存它们以便将来使用。有时，查询只是略有不同，因此，希望有一种通用的查询形式，在运行时可以替换变量或者占位符。SQL*Plus以&符号替换的形式提供这种功能。SELECT语句的每个元素都可以替换，将查询减少为核心元素以方便重用，这样可以节省大量单调的重复性工作。本节将讨论替换变量以及DEFINE和VERIFY命令。

7.6.1 替换变量

可将替换变量看作是占位符。SQL查询由两个或者多个子句组成。每个子句又可划分为子子句，这些子子句由字符文本组成。所有文本、子子句或者子句元素都是替换的对象。

1．单个&符号替换

SQL语句中最基本、最普遍的元素替换形式是单个&符号替换。&符号是用来在语句中指定替换变量的符号，它在变量名之前，并且它们之间没有空格。当执行语句时，SQL*Plus客户程序处理语句，发现替换变量，并尝试用下面两种方法之一来解析变量的值。首先，它检查在用户会话中是否定义了该变量(DEFINE命令将在本章稍后讨论)。如果没有定义变量，用户进程提示需要一个值，这个值会替换变量。提交值之后，语句就完成了，Oracle服务器就会执行它。在执行时解析&符号替换变量，有时称之为运行时绑定( runtime binding)或者运行时替换(runtime substitution)。

给定LAST_NAME或者EMPLOYEE_ID值，要求查找联系信息，例如PHONE_NUMBER数据。通用查询形式如下所示：

        select employee_id, last_name, phone_number from employees
        where last_name = '&LASTNAME' or employee_id = &EMPNO;

当运行该查询时，Oracle服务器提示给名为LASTNAME的变量输入一个值。可以输入员工的姓(如果知道的话)，如“King”。如果不知道员工的姓氏，但知道员工的ID号，也可以输入ID号，然后按Enter键提交。接下来Oracle会提示为EMPNO变量输入一个值。输入值(例如0)之后，按Enter键。之后，Oracle就没有剩余的替换变量需要解析了，因此执行下面的语句：

        select employee_id, last_name, phone_number from employees
        where last_name = 'King' or employee_id = 0;

可以将变量指定为任何字面值，但必须是有效的标识符名称。提示变量时替换的字面值必须是与该上下文相适应的数据类型；否则就会返回ORA-00904: invalid identifier错误。如果变量要替换字符或者日期值，那么需要将字面值包含在单引号内。一种有用方法是，当处理字符和日期值时，将&替换变量包含在单引号内。这样，要求用户提交字面值时，就不必输入引号了。

2．双&符号（&&）替换

有时在相同查询中会多次引用替换变量。在这种情况下，Oracle服务器会提示输入每个单&符号替换变量的值。对于复杂脚本而言，这种工作可能效率低下、令人乏味。下面的语句从EMPLOYEES表中检索FIRST_NAME和LAST_NAME列，查找这两列中包含相同字符串的行：

        select first_name, last_name from employees
        where last_name like '%&SEARCH%' and first_name like '%&SEARCH%';

这两个条件相同，但应用于不同列。执行该语句时，首先提示输入在与LAST_NAME列的比较中使用的SEARCH变量的替换值。之后，提示输入在与FIRST_NAME列的比较中使用的SEARCH变量的替换值。这里有两个问题。第一，输入相同的值两次，这样做效率低下。第二，且更重要的是，查询可能出现拼写错误，因为Oracle没有验证每次都为相同名称的替换变量输入相同的字面值。在本示例中，逻辑假设是替换变量的内容应该相同，但变量有相同名称这一点对于Oracle服务器没有意义，它不做这种假设。图7-11中的第一个示例显示了运行前面的查询并给SEARCH替换变量提交两个不同值之后的结果。在此特例中，结果不正确，因为要求检索包含相同字符串的FIRST_NAME和LAST_NAME对。

如果要在相同查询中多次引用替换变量，而且语句中变量的值必须相同，在这种情况下，最好使用&&替换。这需要在查询中多次出现的替换变量第一次出现时添加前缀，要使用两个&符号而不是一个。当Oracle 服务器遇到&&替换变量时，会为该变量定义会话值，并不再提示输入后续引用中替换该变量的值。

图7-11中的第二个示例显示在有关LAST_NAME列的条件中的SEARCH变量之前添加两个&符号，之后在有关FIRST_NAME列的条件中的SEARCH变量之前添加一个&符号。当执行时，对于LAST_NAME列而言只会提示一次输入一个值取代SEARCH变量。在后续引用中，会自动从该变量的会话值中解析这个值，和在有关LAST_NAME列的条件中一样。要解除SEARCH变量的定义，需要使用本章稍后讨论的UNDEFINE命令。

3．替换列名

到目前为止，WHERE子句的字面元素都是替换讨论的焦点，但实际上SQL语句的任何元素都是可以替换的对象。在下面的语句中，FIRST_NAME和JOB_ID列是静态的，总是会被检索，但选中的第三列是变化的，被指定为名为COL的替换变量。在ORDER BY子句中按该替换变量列排序结果集：

        select first_name, job_id, &&col
        from employees
        where job_id in ('MK_MAN', 'SA_MAN')
        order by &col;

和字符和日期字面值不一样，当显式指定或者通过&替换变量指定时，列名引用都不需要单引号。

4．替换表达式和文本

在运行时几乎可以替换SQL语句的任何元素。条件是Oracle要求至少第一个单词是静态的。在SELECT语句中，至少需要SELECT关键字不变，可以将语句的余下部分替换为如下所示：

        select &rest_of_statement;

当执行时，会提示提交名为REST_OF_STATEMENT变量的值，在追加到SELECT关键字后，它可以是任何合法查询。适合&替换的是那些多次运行只是略有不同的语句。

7.6.2 定义和验证

当相同变量在语句中多次出现时，可以使用&&替换来避免重复输入。当出现&&替换时，变量被保存为会话变量。随着语句的执行，会使用保存的会话变量自动解析所有后续出现的变量。相同会话内语句的后续执行自动从保存的会话值中解析替换变量。这不一定是用户想要的，也的确限制了替换变量的有用性。然而，Oracle提供了一种机制来解除定义(UNDEFINE)这些会话变量。VERIFY命令专用于SQL*Plus，它控制在执行使用替换变量的SQL语句之前，是否将替换的元素回显到用户的屏幕上。

1．DEFINE和UNDEFINE命令

在使用&&替换的SQL语句中第一次引用替换变量时，就隐式创建了会话级别的变量。在会话期间或者在显式解除定义它们之前，它们一直存在或者保持可用。当用户退出客户工具(例如SQL*Plus)或者用户进程异常终止时，会话就会结束。

永久会话变量的存在会降低使用&替换变量语句的一般功能。幸运的是，使用UNDEFINE命令可以删除这些会话变量。在脚本内或者在SQL*Plus或SQL Developer的命令行中，解除定义会话变量的语法如下所示：

        UNDEFINE variable;

考察一个简单示例，它从EMPLOYEES表中选择静态和可变列，并依据可变列排序输出。

        select last_name, &&COLNAME
        from employees where department_id=30 order by &COLNAME;

该语句第一次执行时，会提示为名为COLNAME的变量输入一个值。假设输入SALARY。替换变量，执行语句。相同会话内语句的后续执行不会提示任何COLNAME值，因为已经在会话上下文中将它定义为SALARY，只能使用UNDEFINE COLNAME命令解除它的定义。解除变量定义之后，语句下面的执行就会提示用户输入COLNAME变量的值。

DEFINE命令有两个目的：它可用来检索SQL会话中当前定义的所有变量列表；还可用来显式定义会话期间在一个或者多个语句中作为替换变量引用的变量的值。DEFINE命令的这两个变体的语法如下所示：

        DEFINE;
        DEFINE variable=value;

支持永久会话变量的功能可以按要求使用SET DEFINE OFF命令启动或者关闭。SET命令不是SQL语言命令，而是SQL环境控制命令。通过指定SET DEFINE OFF，客户工具(如SQL*Plus)就不保存会话变量或者给&符号添加特殊含义。如有必要，这允许将&用作普通的字面值字符。因此，SET DEFINE ON | OFF命令确定&替换在会话中是否可用。下面的查询将&用作字面值。当执行时，会提示输入绑定变量SID的值。

        select 'Coda & Sid' from dual;

关闭&替换的功能，执行该查询时就没有提示：

        SET DEFINE OFF
        select 'Coda & Sid' from dual;
        SET DEFINE ON

一旦执行语句，SET DEFINE ON命令可用来打开替换功能。如果DEFINE设置为OFF，并且不能从字面上解析在语句中使用了&的上下文，Oracle就会返回错误。

2．VERIFY命令

操作Oracle服务器时可以使用两类命令：SQL语言命令和SQL客户控制命令。SELECT语句是语言命令，而SET命令控制SQL客户环境。有许多不同的语言和控制命令可用，但与替换相关的控制命令是DEFINE和VERIFY。

VERIFY命令控制提交的替换变量是否显示到屏幕上，以便验证替换是否正确。显示的消息由旧子句和包含替换变量的输入值的新子句组成。使用命令SET VERIFY ON|OFF可将VERIFY命令切换为ON或OFF。如果首先将VERIFY切换为OFF，执行使用&替换的查询，系统会提示输入一个值。然后替换值，运行语句，显示结果。然后将VERIFY切换为ON，执行相同的查询，系统会提示输入一个值。一旦输入值，在语句开始执行之前，Oracle会先显示包含替换变量名的旧子句，并且有行号，然后，显示包含替换值的新子句。

练习7-5 使用&替换

要求使用当前税率写一个可重用的查询，将EMPLOYEE_ID号作为输入，返回EMPLOYEE_ID、FIRST_NAME、SALARY、ANNUAL SALARY (SALARY * 12)、TAX_RATE和TAX (TAX_RATE * ANNUAL SALARY)信息，供HR部门的职员使用。

(1) 启动SQL*Plus ，连接HR模式。

(2) 选择的列表必须包含4个指定列以及两个表达式。第一个表达式的别名为ANNUAL SALARY，它的计算很简单，而第二个表达式的别名为TAX，它取决于TAX_RATE。因为TAX RATE可能变化，所以必须在运行时提交这个值。

(3) 可能的解决方案如下所示：

        SELECT &&EMPLOYEE_ID, FIRST_NAME, SALARY, SALARY * 12 AS "ANNUAL SALARY",
        &&TAX_RATE, (&TAX_RATE * (SALARY * 12)) AS "TAX"
        FROM EMPLOYEES WHERE EMPLOYEE_ID = &EMPLOYEE_ID;

(4) SELECT子句中EMPLOYEE_ID和TAX_RATE前面的双&符号规定，Oracle在执行语句时，必须提示用户给每个替换变量分别只提供一个值，在后续的&EMPLOYEE_ID和&TAX_RATE被引用时要使用它们。