2.3　如何在SQL*Plus中使用变量

为了使数据处理更加灵活，在SQLPlus中可以使用变量。SQLPlus中的变量在SQL*Plus中的整个启动期间一直有效，这些变量可以用在SQL语句、PL/SQL块以及文本文件中。在执行这些代码时，先将变量替换为变量的值，然后再执行。

2.3.1　用户自定义的变量

用户可以根据需要，自己定义变量。有两种类型的自定义变量，第一类变量不需要定义，可以直接使用，在执行代码时SQL*Plus将提示用户输入变量的值。第二类变量需要事先定义，并且需要赋初值。

第一类变量不需要事先定义，在SQL语句、PL/SQL块以及脚本文件中可以直接使用。这类变量的特点是在变量名前面有一个“＆”符号。当执行代码时，如果发现有这样的变量，SQL*Plus将提示用户逐个输入变量的值，当用变量值代替变量后，才执行代码。例如，假设用户构造了一条SELECT语句，在语句中使用了两个变量：

SELECT ename, sal FROM＆table_name WHERE ename='＆name'；

这条语句的执行过程为：

输入table_name的值：emp

输入name的值：SMITH

原值　1：SELECT ename, sal FROM＆table_name WHERE ename='＆name'

新值　1：SELECT ename, sal FROM emp WHERE ename='SMITH'

ENAME SAL

SMITH 800

其中字符串“emp”和“SMITH”是用户输入的变量值。在SQL*Plus中首先用变量值代替变量，生成一个标准的SQL语句，然后再执行这条语句。当为所有的变量都提供了变量值后，这条语句才能执行。在构造这样的SQL语句时要注意，使用变量和不使用变量的语句在形式上是一致的。例如，ename列的值为字符型，应该用一对单引号限定，使用了变量以后，仍然要用一对单引号限定。

上述语句如果需要再次执行，系统将提示用户再次逐个输入变量的值。为了使用户在每次执行代码时不需要多次输入变量的值，可以在变量名前加上“＆＆”符号。使用这种形式的变量，只需要在第一次遇到这个变量时输入变量的值，变量值将保存下来，以后就不需要不断输入了。例如，假设把上述SELECT语句该为以下形式：

SELECT ename, sal FROM＆＆table_name WHERE ename='＆＆name'

那么在第一次执行时，像以前一样需要输入变量的值，而再次执行时，就不再需要输入变量的值了，直接使用以前提供的变量值。以下是第二次以后的执行情况：

SQL＞/

原值　1：SELECT ename, sal FROM＆＆table_name WHERE ename='＆＆name'

新值　1：SELECT ename, sal FROM emp WHERE ename='SMITH'

……

在SQL*Plus中可以使用的第二类自定义变量需要事先定义，而且需要提供初值。定义变量的命令是DEFINE。定义变量的格式是：

define变量名=变量值

变量经定义后，就可以直接使用了。实际上，用DEFINE命令定义的变量和使用“＆”的变量在本质上是一样的。用DEFINE命令定义变量以后，由于变量已经有值，所以在使用变量时不再提示用户输入变量的值。

如果执行不带参数的DEFINE命令，系统将列出所有已经定义的变量，包括系统定义的变量和用“＆”定义的变量，以及即将提到的参数变量。例如：

SQL＞define

DEFINE_CONNECT_IDENTIFIER="ORCL"（CHAR）

DEFINE_SQLPLUS_RELEASE="1001000200"（CHAR）

DEFINE_EDITOR="NOTEPAD"（CHAR）

DEFINE_O_VERSION="Oracle10g Enterprise Edition Release 10.1.0.2.0-Production

With the OLAP and Oracle Data Mining options

JServer Release 10.1.0.2.0-Production"（CHAR）

DEFINE_O_RELEASE="1001000200"（CHAR）

DEFINE_RC="1"（CHAR）

DEFINE TABLE_NAME="emp"（CHAR）

DEFINE NAME="SMITH"（CHAR）

其中最后两个变量就是刚才我们用“＆”定义的变量。

现在，让我们看一个用DEFINE命令定义变量的例子。

SQL＞define col_name=ename

SQL＞define salary=3000

在这里定义了两个变量，然后在SQL语句中就可以直接使用这两个变量了。在使用变量时，仍然用“＆变量名”的形式来引用变量的值。例如：

SQL＞SELECT＆col_name FROM emp WHERE sal＞＆salary；

在执行这条语句时，用ename代替变量col_name，用3000代替变量salary，生成一条标准的SQL语句。这条语句的执行结果为：

原值　1：SELECT＆col_name FROM emp WHERE sal＞＆salary

新值　1：SELECT ename FROM emp WHERE sal＞3000

ENAME

KING

当一个变量不再使用时，可以将其删除。undefine命令用于取消一个变量的定义。删除一个变量的命令格式为：

undefine变量名