替代变量的使用

现在老板改主意了，他要您打印一份工资在1600元（包括1600元）或高于1600元的员工的清单。您没有办法重用以上的SQL语句。就因为相差了一个数字您就不得不重新输入与上面已经调试好的SQL语句几乎完全相同的语句。如果您的老板一天改几次主意，您又该怎么办呢？

以&开始的替代变量

请不用慌，Oracle已经高瞻远瞩，早就想到了这一点。为此Oracle引入了替代变量，

替代变量就可以帮助您解决所遇到的难题。现在您可以使用例11-2的查询语句。

例11-2

SQL> SELECT empno, ename, sal

2 FROM emp

3 WHERE sal >= &V_salary;

例11-2中的&v_salary就是替代变量。变量的名字是您起的，只要您认为有意义就行，但别忘了在变量的名字之前加上一个&号。

当运行例11-2的SQL语句时，Oracle系统会给出："输入v_salary的值："的提示，此时可以输入任何您感兴趣的工资值，如1600，按回车键之后，您就会得到所需要的结果。

例11-2系统提示和输入

现在您可以使用例11-3的SOL*PLUS命令将例11-2的SQL语句存在一个叫 sal的脚本文件中。之后您就可以反复地使用这个脚本文件了。

例11-3

SQL> save d:\sql\sal.sql

如果您想知道sal的原值和新值，您可以先输入如例11-5的SQL*PLUS命令。例11-5

SQL> SET VERIFY ON

之后再使用例11-6的SQL*PLUS命令运行脚本文件sal。例11-6 sQL> @d:\sql\sal

例11-6系统提示和输入输入v_salary的值：1700

原值 3：WHERE sal>=&v_salary

新值 3：WHERE sal>=1700

一般只是在调试阶段才可能将VERIFY设置成ON。当SOL语句是嵌在程序或脚本文件中时，而该程序或脚本文件又是为用户开发的，在它们交付使用之前一定要将VERIFY 置成OFF。因为用户看到那些莫名其妙的显示时会感到困惑。

字符型和日期型替代变量

在解释字符型和日期型替代变量之前，我们先看一个例子，如例11-7。例11-7

SQL> SELECT empno, ename, sal, job, deptno

2 FROM emp

3 WHERE job = &v_job;

例11-7系统提示和输入输入v_job的值： CLERK

当运行例11-7的SQL语句时，在系统提示输入时您输入了CLERK，但系统却返回了出错信息。这是为什么呢？

这是因为Oracle系统在处理SQL*PLUS替代变量时，只是把替代变量（&v job）简单地替换成我们在系统提示下输入的值（CLERK）。因为job这一列的数据类型为字符型，所以CLERK必须用单引号扩起来。为了以后的演示方便，您可以先将例11-7的SQL语句存入一个叫JOB的脚本文件，如例11-8。

例11-8

SQL> SAVE D:\SQL\JOB

例11-9

SQL> set verify off

现在使用例11-10的SQL*PLUS命令运行脚本文件job。

例11-10 sQL> @D:\soL\JOB

之后在系统提示输入时输入'CLERK'。

例11-10系统提示和输入输入v_job的值：'CLERK'

我们使用例11-11的SQL*PLUS命令重新运行脚本文件job来演示一种难以理解但可能出现的输入错误。

例11-11

SQL> @D:\sQL\JOB

之后在系统提示输入时输入‘CLERK’

例11-11系统提示和输入输入v_job的值：'CLERK'；

由于我们习惯地在输入的结尾加上了分号（；），Oracle系统返回了出错信息。一般只有一个SOL语句需要反复执行时才使用替代变量。这样的SOL语句多数都是嵌在程序或脚本文件中的，而这样的程序或脚本文件又是为用户开发的。您很难知道用户对SQL语句和SQL*PLUS命令的掌握程度。因此作为软件的开发者，您应该保证使提示信息一目了然，而且用户要输入的信息越简单越少越好。所以最好的办法是，作为软件的开发者，应该把SQL语句中的字符型和日期型替代变量用单引号扩起来。现在我们利用例11-12的SQL*PLUS命令来编辑脚本文件job。例11-12

SQL> EDIT D:\SQL\JOB

SELECT empno,ename,sal,job,deptno FROM emp

WHERE job='&v_job'

再使用例11-13的SQL*PLUS命令来运行脚本文件job。

例11-13 SQL>@D:\soL\JOB

之后在系统提示输入时输入CLERK.

例11-13系统提示和输入输入v_job的值：CLERK

例11-14

SQL> SELECT ename,job, &&v_col

2 FROM emp 3 ORDER BY &v_col;

之后在系统提示输入时输入sal。例11-14系统提示和输入输入v_col的值：sal

从例11.14可以看出:尽管在该例的sQL语句中替代变量v_col出现了两次,但Oracl系统只提示输入一次。

如果您想重用某个替代变量而不想让Oracle系统每次提示输入的话，可以使用以&&开始的替代变量。这样Oracle系统只在第一次使用这个替代变量时提示您输入它，以后再使用该替代变量时Oracle系统就不再提示输入而是使用您第一次输入的值。

那么如果重新输入例11-14的SQL语句会得到什么样的结果呢？您可以试一下。其实Oracle系统将没有任何提示而是直接产生和例11-14显示的结果完全一样的输出。这是因为Oracle系统已经记住了您第一次输入的值。

如果想改变这一替代变量的值，例如这回您想知道的不是工资（sal）而是佣金（comm），可以使用SQL*PLUS的UNDEFINE命令来取消这个变量。如例11-15。

例11-15

SQL> undefine v_col

现在您可以输入与例11-14完全相同的SQL语句，如例11-16。例11-16

SQL> SELECT ename,job,&&v_col

2 FROM emp

3 ORDER BY &V_col;

之后在系统提示输入时输入comm。

例11-16系统提示和输入输入v_col的值：comm

11.5 替代变量可以出现的地方

替代变量几乎可以出现在SQL语句中的任何地方。替代变量可以出现在：

■ SELECT子句中。

■ ORDER BY子句中。

■ WHERE子句中（替代变量甚至可以替代整个的条件表达式）。

■ 任何可以使用列或表达式的地方。

■ 表名出现的地方。

■ 整个的查询语句。

以下我们用一个比较全面的例子，如例11-17来演示上面所说的替代变量的使用。例11-17

SQL> SELECT scolumn1, scolumn2

2 FROM &table_name

3 WHERE &condition

4 ORDER BY &sorting;

运行以上的SQL语句之后，在系统提示输入时输入相关的替代变量的值。

例11-17系统提示和输入输入column1 的值：dname

输入column2的值：loc

输入table_name的值：dept

输入condition 的值：deptno <>10

输入sorting 的值：dname

使用DEFINE定义替代变量

除了以上介绍的使用替代变量的两种方法之外，还可以使用SQL*PLUS命令DEFINE 来定义替代变量。DEFINE 命令只能用来创建字符型的替代变量。下面的例11-19就是一个创建字符型替代变量的例子。

例11-19

SQL> DEFINE v_job= CLERK

现在可以输入例11-20的SQL语句。

例11-20

SQL> SELECT ename,sal,job

2 FROM emp

3 WHERE job='&v_job'

4 ORDER BY sal;

由于我们已经使用例11-19的SQL语句将替代变量v.job赋为CLERK，所以Oracle 系统已经记住了这次的输入值并不再提示输入而是直接把CLERK作为变量v_job的值来使用。因此当输入例11-20的SQL语句后，Oracle系统将没有任何提示而直接产生例11-20 的输出结果。

可以使用以下的SQL*PLUS命令来创建一个字符型的替代变量，并为它赋值：DEFINE 替代变量名=变量值（如例11-19）。

可以使用以下的SQL*PLUS命令显示一个替代变量、替代变量的值及其数据类型：DEFINE 替代变量名（如例11-21）。

例11-21 SQL> DEFINE v_job

例11-21结果

"CLERK" (CHAR)DEFINE V_JOB

也可以使用以下的SQL*PLUS命令显示所有的替代变量、替代变量值及其数据类型：DEFINE（如例11-22）。

例11-22 SQL> DEFINE

虽然您已经学习了几种定义替代变量的方法，但所有这些方法都有一个共同的缺陷，就是系统产生的提示信息非常不清楚。用这样的方法开发的软件用户是很难满意的。不过，Oracle又一次高瞻远瞩地想到了这一点。为此Oracle引入了ACCEPT命令，用该命令来定义替代变量就可以帮助您解决所遇到的难题。

例11-23

SQL> SELECT ename, sal, job

2 FROM emp

3 WHERE job= UPPER('&v_job')

4 ORDER BY sal;

运行以上的SQL语句之后在系统提示输入时，输入替代变量的值clerk。例11-23系统提示和输入输入v_job的值：clerk 之后系统会产生如下的输出。

例11-25

sQL> edit d:\sql\acc_job

当编辑窗口出现时，要在第一行加入：ACCEPT v_job PROMPT Please Enter the Job Title：'，之后选择文件菜单，再选择保存子菜单，最后退出编辑器，如例11-26。

如果您的操作系统和数据库字的字符集都为中文的话，也可以使用如下的方法将系统提示信息改为中文。首先使用例11-28的SQL*PLUS命令将脚本文件acc_job.sql装入SQL 缓冲区。

例11-28

SQL> get d:\sql\acc_job

现在可以使用例11-29的SQL*PLUS命令将SQL缓冲区中的SQL语句存在名为chacc_job的脚本文件中。

例11-29

sQL> save d:\sql\chacc_job replace 例11-29结果

已写入文件d：\sql\chaccjob.sql

之后可以用例11-30的SQL*PLUS命令来编辑刚创建的脚本文件chacc_job.sql. 例11-30

sQL> edit d:\sql\chaccjob.sql

当编辑窗口出现时，要在第一行加入：ACCEPTv_job PROMPT'

请您输入职位（Job）：'之后选择【文件】菜单，再选择【保存】子菜单，最后退出编辑器，如例11-31。

如果公司中的某些信息只有经理们可以看，可以使用ACCEPT命令的HIDE选项。该选项将使ACCEPT命令不显示用户的输入，就像在操作系统下输入口令似的。

下面我们用一个简单的例子来演示如何开发一个使用该功能的脚本文件。可以输入如例11-33的查询语句。该查询语句只有当用户在系统提示输入MANAGER时，系统才会显示emp表中的雇员名（ename）、工资（sal）和职位（job）。

例11-33

SQL> SELECT ename,sal,job

2 FROM emp

3 WHERE ' &pwd' = 'MANAGER';

运行以上的SQL语句之后和系统提示输入时，要输入替代变量的值MANAGER。例11-33系统提示和输入输入pwd的值：MANAGER

之后系统会输出每个员工的名字（ename）、工资（sal）和职位（job）。

现在可以使用如例11-34的SQL*PLUS命令将刚刚测试好的SQL语句存入D：SQL目录下（文件夹）的PWD脚本文件中。

例11-34

SQL> SAVE D:\SQL\PWD

之后运行如例11-35的SQL*PLUS命令来编辑刚刚存入PWD脚本文件中的SQL语句。例11-35

SQL> EDIT D:\soL\PwD

在编辑器中的SQL语句之前（可以是第一行）输入如下的一行：ACCEPT pwd PROMPT'Please Enter Your Password：'HIDE，如例11-36。

运行以上的PWD脚本文件之后，在系统提示输入时输入替代变量的值MANAGER。例11-37系统提示和输入请输入你的口令∶*******

与例11-33不同的是，当您输入MANAGER 时，系统不显示输入而是显示*******。这样就没人能偷看到口令（Password）。

例11-37的显示输出与例11-33的完全一样

ACCEPT命令的格式和选项

除了上面所介绍的ACCEPT命令的用法之外，还可以使用该命令显式地定义日期型或数字型变量。ACCEPT命令的格式如下：

ACCEPT 变量名 【数据类型】【FORMAT 格式化模式】【PROMPT 正文】【HIDE】其中：

变量名：存储输入值的变量名。如果该变量不存在，SQL*PLUS会自动创建它。数据类型：为数字型、日期型和字符型。字符型的最大长度是240个字节。FORMAT 格式化模式：它的定义与第9章中的一样，例如99，999.00或A28等。PROMPT 正文：在用户可以输入变量的值之前系统的提示信息。HIDE：系统不显示用户的输入值，如口令（PASSWORD）。

另外值得注意的是：在ACCEPT关键字之后的替代变量之前不能加&符号，但该替代变量被引用时在其前面要加&符号。