SQL*Plus中替换变量与定义变量

2022-10-05 08:19:02

替换变量

SQL*Plus中的替换变量又叫替代变量，它一般用来临时存储相关数据；在SQL语句之间传递值。一般使用&或&&前缀来指定替换变量. 关于使用替换变量，一般是利用其创建通用的脚本或达到和用户交换目的。如下所示：

例如，我需要查看表的相关信息，如果不使用替换变量，每次查询我都要修改脚本。非常不便，如果使用替换变量，我们可以将下面脚本存放在tab.sql脚本中,每次运行时，只需要输入替换变量的值就可以了。

COL LOGGING FOR A7;
 
COL OWNER FOR A12;
 
COL TABLE_NAME FOR A30
 
COL TABLESPACE_NAME FOR A30
 
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS, COMPRESSION, 
       LOGGING, PARTITIONED, NUM_ROWS,
       TO_CHAR(LAST_ANALYZED,‘YY-MM-DD HH24:MI:SS‘) LAST_ANALYZED
 
FROM DBA_TABLES 
 
    WHERE TABLE_NAME LIKE ‘&table_name_like%‘
 
    ORDER BY TABLE_NAME;

epps> @tab.sql
Enter value for table_name_like: EMP
old   4:  WHERE  TABLE_NAME LIKE ‘&table_name_like%‘
new   4:  WHERE  TABLE_NAME LIKE ‘EMP%‘
 
OWNER     TABLE_NAME       TABLESPACE_NAME      STATUS   COMPRESS LOGGING PAR   NUM_ROWS LAST_ANALYZED
------- --------------- ---------------------- -------- -------- ------- --- ---------- -----------------
SCOTT        EMP               USERS             VALID    DISABLED YES     NO          14 09-07-20 22:00:12
WIPOWNER     EMPLOYEE_MASTER   WIPOWNER_DATA     VALID    DISABLED YES     NO       12084 14-09-07 14:08:22
 
epps> @tab.sql
Enter value for table_name_like: DEPT
old   4:  WHERE  TABLE_NAME LIKE ‘&table_name_like%‘
new   4:  WHERE  TABLE_NAME LIKE ‘DEPT%‘
 
OWNER     TABLE_NAME    TABLESPACE_NAME    STATUS   COMPRESS LOGGING PAR   NUM_ROWS LAST_ANALYZED
------- -------------- ------------------ -------- -------- ------- --- ---------- -----------------
SCOTT       DEPT                USERS       VALID    DISABLED YES     NO           4 09-07-20 22:00:12
 
epps> 

如果替换变量为数字类型，那么可以直接是&+变量名称，如下所示&DEPTNO，

SQL> SELECT deptno, dname, loc 
  2  FROM SCOTT.DEPT
  3  WHERE DEPTNO= &DEPTNO;
Enter value for deptno: 10
old   3: WHERE DEPTNO= &DEPTNO
new   3: WHERE DEPTNO= 10
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

如果是字符串类型或日期类型，最好使用单引号将其包裹起来，否则在输入变量值时则必须加上单引号，不那样做就会报错，如下所示。

SQL> SELECT deptno, dname, loc 
  2  FROM SCOTT.DEPT
  3  WHERE DNAME= &DNAME;
Enter value for dname: SALES
old   3: WHERE DNAME= &DNAME
new   3: WHERE DNAME= SALES
WHERE DNAME= SALES
             *
ERROR at line 3:
ORA-00904: "SALES": invalid identifier
 
SQL> SELECT deptno, dname, loc 
  2  FROM SCOTT.DEPT
  3  WHERE DNAME= &DNAME;
Enter value for dname: ‘SALES‘
old   3: WHERE DNAME= &DNAME
new   3: WHERE DNAME= ‘SALES‘
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

关于替换变量中&与&&的区别,两者区别在于作用范围不一样，&引用的替换变量只在当前SQL有效，而&&引用的替换变量则在当前会话有效。下面以人们经常引用的例子来说明

SQL> SELECT 2+&NUM FROM DUAL;
Enter value for num: 2
old   1: SELECT 2+&NUM FROM DUAL
new   1: SELECT 2+2 FROM DUAL
 
       2+2
----------
         4
 
SQL> SELECT 3+&NUM FROM DUAL;
Enter value for num: 3
old   1: SELECT 3+&NUM FROM DUAL
new   1: SELECT 3+3 FROM DUAL
 
       3+3
----------
         6
 
SQL> SELECT 2+&&NUM FROM DUAL;
Enter value for num: 2
old   1: SELECT 2+&&NUM FROM DUAL
new   1: SELECT 2+2 FROM DUAL
 
       2+2
----------
         4
 
SQL> SELECT 3+&&NUM FROM DUAL;
old   1: SELECT 3+&&NUM FROM DUAL
new   1: SELECT 3+2 FROM DUAL
 
       3+2
----------
         5

小知识1：在存储过程或包体里面，经常有在字符串中使用&的情况，执行脚本时，经常会将这些字符串视为替换变量，要求输入值，这样烦不甚烦，其实只需要设置一下SQL*PLUS的环境变量即可避免这种情况。通常通过SET DEFINE OFF

小知识2：如果要显示SQL*Plus使用替换值替换后的脚本文件，可以使用SET VERIFY ON/OFF 命令

SQL> SET VERIFY OFF
SQL> SELECT 2+&NUM FROM DUAL;
 
       2+2
----------
         4
 
SQL> SET VERIFY ON
SQL> SELECT 2+&NUM FROM DUAL;
old   1: SELECT 2+&NUM FROM DUAL
new   1: SELECT 2+2 FROM DUAL
 
       2+2
----------
         4
 

用户定义变量

使用DEFINE定义了的变量（或许我们可以叫其定义常量，和C/C++中的DEFINE非常类似），可以使用&引用声明的变量。其作用范围或生命周期通常是整个会话。如果定义了变量后，需要清除变量，则可以使用UNDEFINE清除变量，使用DEFINE VARIABLE来查看变量

SQL> DEFINE NUM=2;            --定义变量
SQL> DEFINE NUM                    --查看变量
DEFINE NUM             = "2" (CHAR)
SQL> SELECT 2+&NUM FROM DUAL;      --引用变量
old   1: SELECT 2+&NUM FROM DUAL
new   1: SELECT 2+2 FROM DUAL
 
       2+2
----------
         4
 
SQL> UNDEFINE NUM;               --清除变量
SQL> SELECT 2+&NUM FROM DUAL;    --清除变量后，&NUM变成替换变量了。需要输入值
Enter value for num: 