在sqlplus或其他工具中，开发和执行SQL语句时，经常会遇到以下三种变量：
- 脚本替换变量：& / && / define / undefine
- SQL语句绑定变量：: / var(iable) / exec
- PL/SQL存储过程变量：declare
本文将对Oracle SQL中的变量进行分析探讨。
1. 脚本替换变量
替换变量可以放在SQL或PL/SQL语句中的任意位置，包含select值或字段，from表名，where字段或条件，order by、group by、having等任意位置，以及PL/SQL语句的deaclre、begin中的任意位置。
替换变量在使用时，进行的是字符串替换，没有数据类型之分，全部为字符串，在SQL语句执行之前已由sqlplus等工具完成了SQL语句的字符串替换。需要注意的是，如果SQL语句中，替换变量所在的位置应为字符或日期类型的值，则最好用单引号扩起来。
1.1 使用规则
替换变量的使用，可以分别以下几个基本动作：
- 交互输入：当&或&&引用的变量没有定义时，会提示用户输入替换值
- 变量引用：当&或&&引用的变量已经定义时，会引用变量的值
- 字符替换：通过字符串替换的方式，将该位置出现的替换变量替换为对应变量值或输入值
- 变量定义：通过define命令定义或修改，或变量未定义时&&第一次交互输入的值
替换变量使用过程中，所设计的操作符或命令如下：
- &：变量引用、交互输入和字符替换
- &&：变量引用、交互输入、字符替换和变量定义
- define/undefine：定义与清除变量
1.2 命令帮助
SQL> help define
DEFINE
------
Specifies a substitution variable and assigns a CHAR value to it, or
lists the value and variable type of a single variable or all variables.
DEF[INE] [variable] | [variable = text]
SQL> help undefine
UNDEFINE
--------
Deletes one or more substitution variables that you defined either
explicitly (with the DEFINE command), or implicitly (with a START
command argument).
UNDEF[INE] variable ...1.3 应用详解
- &：如果变量已定义时，则引用变量并替换；如果变量无定义，交互输入替换值，替换，但不定义变量，即如果后面又出现该替换变量，则仍需要交互输入替换值，当然也可以使用define提前定义替换变量值，规避交互输入。
SQL> select '&name','&name' from dual;
Enter value for name: Li
Enter value for name: Qiang
old 1: select '&name','&name' from dual
new 1: select 'Li','Qiang' from dual
'L 'QIAN
-- -----
Li Qiang
SQL> select &col,&col from v$database;
Enter value for col: name
Enter value for col: open_mode
old 1: select &col,&col from v$database
new 1: select name,open_mode from v$database
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
SQL> /
Enter value for col: open_mode
Enter value for col: name
old 1: select &col,&col from v$database
new 1: select open_mode,name from v$database
OPEN_MODE NAME
-------------------- ---------
READ WRITE ORCL
SQL> define col
SP2-0135: symbol col is UNDEFINED- &&：如果变量已定义时，则引用变量并替换；如果变量无定义，交互输入替换值，替换并定义变量。也可以使用define提前定义替换变量值，规避交互输入。
SQL> select '&&name','&&name' from dual;
Enter value for name: Li
old 1: select '&&name','&&name' from dual
new 1: select 'Li','Li' from dual
'L 'L
-- --
Li Li
SQL> select &&col,&&col from v$database;
Enter value for col: name
old 1: select &&col,&&col from v$database
new 1: select name,name from v$database
NAME NAME
--------- ---------
ORCL ORCL
SQL> /
old 1: select &&col,&&col from v$database
new 1: select name,name from v$database
NAME NAME
--------- ---------
ORCL ORCL
SQL> define col
DEFINE COL = "name" (CHAR)- define：用于查看、定义或修改替换变量值。define定义的变量，可以使用&或&&直接引用和替换，无需再交互输入变量值。
- unfine：用于清除变量。
SQL> define col
DEFINE COL = "name" (CHAR)
SQL> undefine col
SQL> define col
SP2-0135: symbol col is UNDEFINED
SQL> define col=name
SQL> select &&col from v$database;
old 1: select &&col from v$database
new 1: select name from v$database
NAME
---------
ORCL
SQL> select &col from v$database;
old 1: select &col from v$database
new 1: select name from v$database
NAME
---------
ORCL- PL/SQL中的使用示例
SQL> declare
2 n number;
3 begin
4 select count(*) into n from &t;
5 dbms_output.put_line(n);
6 end;
7 /
Enter value for t: v$database
old 4: select count(*) into n from &t;
new 4: select count(*) into n from v$database;
1
PL/SQL procedure successfully completed.
SQL> /
Enter value for t: v$datafile
old 4: select count(*) into n from &t;
new 4: select count(*) into n from v$datafile;
4
PL/SQL procedure successfully completed.2. SQL语句绑定变量
绑定变量用于，将一组只有检索值或插入值不同，而其他部分相似的SQL语句，统一为绑定变量替换后的SQL语句形式，从而实现软解析。
绑定变量只能用于SQL语句的where或values部分，形式上会在变量名前面加冒号。在PL/SQL语句中，SQL语句外的变量均为PL/SQL存储过程变量，可以使用using命令，将带绑定变量的SQL语句和存储过程变量的值，一起发送给SQL引擎。
2.1 使用规则
绑定变量在赋值或引用时，需要在变量名前加冒号“:”。这样做对PL/SQL语句块来说，是为了与存储过程变量区分，对SQL语句来说，是为了表示这是个绑定变量，SQL执行前不做变量引用，从而实现SQL软解析，最后SQL执行时，再做变量引用。
与绑定变量有关的命令如下，前3个为sqlplus工具命令，最后1个为PL/SQL语句命令。
- variable：绑定变量定义与赋值
- execute：绑定变量赋值，
- print：打印绑定变量的值
- execute immediate（PL/SQL）：执行动态SQL，并使用using附加绑定变量
绑定变量有数据类型之分，在sqlplus中使用variable命令定义绑定变量时，需要声明数据类型，在PL/SQL中包含绑定变量的SQL中，可以不指定数据类型，PL/SQL会根据using后跟的值类型，来确定绑定变量数据类型。
- NUMBER
- CHAR CHAR (n [CHAR|BYTE])
- NCHAR NCHAR (n)
- VARCHAR2 (n [CHAR|BYTE])
- NVARCHAR2 (n)
- CLOB NCLOB
- REFCURSOR
- BINARY_FLOAT BINARY_DOUBLE
2.2 命令帮助
SQL> help variable
VARIABLE
--------
Declares a bind variable that can be referenced in PL/SQL, or
lists the current display characteristics for a single variable
or all variables.
VAR[IABLE] [<variable> [type][=value]]
where type represents one of the following:
NUMBER CHAR CHAR (n [CHAR|BYTE])
NCHAR NCHAR (n) VARCHAR2 (n [CHAR|BYTE])
NVARCHAR2 (n) CLOB NCLOB
REFCURSOR BINARY_FLOAT BINARY_DOUBLE
One can assign value to a variable for input with the new syntax
Example:
VARIABLE tmp_var VAHRCHAR2(10)=Smith
or
VARIABLE tmp_var VAHRCHAR2(10)
VARIABLE tmp_var=Smith
EXECUTE DBMS_OUTPUT.PUT_LINE(:tmp_var)
SQL> help print
PRINT
-----
Displays the current values of bind variables, or lists all bind
variables.
PRINT [variable ...]
SQL> help exec
EXECUTE
-------
Executes a single PL/SQL statement or runs a stored procedure.
EXEC[UTE] statement2.3 应用详解
- sqlplus中使用绑定变量
sqlplus工具中，通过variable命令创建绑定变量并赋值，再执行包含绑定变量的SQL语句，sqlplus会将SQL语句和绑定变量值，一起发送给SQL引擎。
SQL> variable fileno number = 1
--或
SQL> variable fileno number
SQL> exec :fileno := 1;
PL/SQL procedure successfully completed.
SQL> print fileno
FILENO
----------
1
SQL> select file#,name from v$datafile where file# = :fileno;
FILE# NAME
--------------- ----------------------
1 /data/ORCL/system01.dbf- PL/SQL语句中使用
PL/SQL语句中使用绑定变量时，需要使用execute immediate执行带有绑定变量的SQL语句，使用into接收select语句的输出，使用using附加绑定变量的值。using中的值，可以是实际值，也可以是存储过程变量。
--using实际值
SQL> declare
2 oid varchar2(200);
3 begin
4 execute immediate 'select object_id from dba_objects where owner = :1 and object_name = :2' into oid using 'MDSYS','R_TABLE';
5 dbms_output.put_line(oid);
6 end;
7 /
70560
PL/SQL procedure successfully completed.
--using存储过程变量
SQL> declare
2 oid varchar2(200);
3 own varchar2(10) := 'MDSYS';
4 obn varchar2(10) := 'R_TABLE';
5 begin
6 execute immediate 'select object_id from dba_objects where owner = :1 and object_name = :2' into oid using own,obn;
7 dbms_output.put_line(oid);
8 end;
9 /
70560
PL/SQL procedure successfully completed.3. PL/SQL存储过程变量
PL/SQL存储过程变量，需要在declare字句中进行声明，赋值时使用“:=”冒号等号赋值符。在赋值或引用存储过程变量时，直接写变量名即可，不需要加&或:号。
3.1 使用规则
与PL/SQL存储过程相关的操作如下：
- declare：变量声明，生命变量名、数据类型，并可以赋值
- %TYPE：引用某个表的某一列的数据类型
- %ROWTYPE：引用某个表的表结构
- var := value：变量赋值
- select col into var：将select结果赋值给变量
- execute immeidate 'select ...' into var：将SQL结果赋值给变量
3.2 应用详解
变量使用前必须声明，变量声明必须在declare字句中。
可以在变量声明时，同时赋值，也可以在begin字句使用变量前进行赋值。
直接键入变量名，即可引用变量，无需加任何修饰符。
--声明时直接赋值
SQL> declare
2 out varchar2(20) := 'My Output';
3 begin
4 dbms_output.put_line(out);
5 end;
6 /
My Output
--在begin中赋值
SQL> declare
2 out varchar2(20);
3 begin
4 out := 'My Output';
5 dbms_output.put_line(out);
6 end;
7 /
My Output
--使用select into语句赋值，into在select和from之间
SQL> declare
2 name v$database.name%TYPE;
3 begin
4 select name into name from v$database;
5 dbms_output.put_line(name);
6 end;
7 /
ORCL
--使用execute immediate执行SQL语句后，将结果赋值给变量，into语句后面
SQL> declare
2 name v$database.name%TYPE;
3 begin
4 execute immediate 'select name from v$database' into name;
5 dbms_output.put_line(name);
6 end;
7 /
ORCL
--将表结构作为变量的数据类型，可以通过点号，引号某列的值
SQL> declare
2 out v$database%ROWTYPE;
3 begin
4 select * into out from v$database;
5 dbms_output.put_line(out.name);
6 end;
7 /
ORCL