[用于sqlplus工具环境下的]替换变量(&和&&区别)

9.6.1 替换变量

2011-12-08 16:53 宁洪/吴云洁/李梦汶 等译 清华大学出版社 我要评论(0) 字号：T | T

综合评级：

想读(8)  在读(5)  已读(1)   品书斋鉴(0)   已有14人发表书评

《OCP/OCA认证考试指南全册：Oracle Database 11g(1Z0-051, 1Z0-052, 1Z0-053)》第9章使用SQL检索、限制和排序数据，本章首先介绍了该语句的基本形式，然后不断扩充其核心功能。还讨论了WHERE子句，该子句指定一个或者多个条件，Oracle服务器通过计算这些条件来限制语句返回的行。最后讨论了&符号替换：这是一种通过在运行时替换查询元素，重用相同的语句来执行不同的查询的机制。本节为大家介绍替换变量。

AD：

9.6.1  替换变量

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

1. 单个&符号替换

SQL语句中最基本、最普遍的元素替换形式是单个&符号替换。&符号是用来在语句中指定替换变量的符号，它在变量名之前，并且它们之间没有空格。当执行语句时，Oracle服务器处理语句，发现替换变量，并尝试用下面两种方法之一来解析变量的值。首先，它检查在用户会话中是否定义了该变量(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服务器没有意义，它不做这种假设。图9-8中的第一个示例显示了运行前面的查询并给SEARCH替换变量提交两个不同值之后的结果。在此特例中，结果不正确，因为要求检索包含相同字符串中包含的FIRST_NAME和LAST_NAME对。 图9-8  &&替换

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

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

提示：

不管您是作为开发人员、数据库管理员或者公司终端用户，遇到的SQL查询都可以广义分为即席查询或者重复查询。即席查询通常是在某些数据研究练习过程中写的一次性语句，不可能重用它们。重复查询是经常运行或者定期运行的查询，它们通常保存为脚本文件，当需要时，几乎不需要修改就可以运行。重用节省了大量重复开发时间，并允许这些一致查询受益于Oracle本身自动调整功能，这些功能可以提高查询性能。

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关键字后，它可以是任何合法查询。适合&替换的是那些多次运行只是略有不同的语句。

【责任编辑：云霞 TEL：[phone]】