Oracle PL/SQL自学（五）：替代变量与绑定变量

1.替代变量与绑定变量

之前在学习变量声明与使用这一章节时，发现替代变量与绑定变量的内容比较多，可以写的内容远远不止书本上的那些，遂决定单独写一篇文，对这两种变量的使用做一个大概的总结。

前文链接: Oracle PL/SQL自学（四）：PL/SQL变量的声明与使用.

2.替代变量

2.1基本用法

在学习到替代变量前，我们发现当每次要查找不同的数据时，我们都要对代码段进行修改，在实际工作中，这种操作方法会极大地影响工作效率，那么这时就需要引入替代变量的概念来解决这个问题。
简单来说，当使用了替代变量后，每一次进行查询或更新数据的操作，都在我们输入目标值之后再执行，这个目标值用&来标记，也就是替代变量。

改写上一章3.3中的例程：

DECLARE
num1 NUMBER := 10;
num2 NUMBER := #
BEGIN
IF num1<num2 THEN
DBMS_OUTPUT.PUT_LINE('num1<num2 num2=' || num2 );
else
DBMS_OUTPUT.PUT_LINE('num1>num2 num2=' || num2 );
END IF;
END;
/

运行结果：

SQL> /
输入 num 的值:  30
原值    3: num2 NUMBER := #
新值    3: num2 NUMBER := 30;
num1<num2 num2=30

PL/SQL 过程已成功完成。

SQL> 

注意：
1.替代变量可以出现在sql语句的任何位置；
2.在一个程序块中可以有多个替代变量；
3.替代变量可以是声明的变量，也可以是表名、列名；
4.若替代变量是字符型或日期型，最好将替代变量连同&使用单引号括起，否则每次输入替代变量都需要输入单引号，否则程序会报错；
5.使用set define off命令可以关闭替代变量的功能，主要是为了避免字符串中带&给程序执行带来麻烦；
6.使用set verify off命令可以避免显示替代变量被新值替代的情况；

2.2 Accept命令

在替代变量的基本用法中，提示信息不够明确，使用accept命令可以自定义提示信息。

语法：accept 替代变量名称 [数据类型] [format 格式] [PROMPT ‘提示信息’] [hide]

下面使用一个例子对accept的用法做一个详解。
例程1：

/*G:\Oracle\sqltest\test.sql*/
set verify off
accept num prompt'输入自定义值num:'
DECLARE 
num1 NUMBER := #
BEGIN
DBMS_OUTPUT.PUT_LINE('num1 = ' || num1 );
END;
/

运行结果：

SQL> @G:\Oracle\sqltest\test.sql
输入自定义值num:1
num1 = 1

PL/SQL 过程已成功完成。

如果我们输入的替代变量类型与PL/SQL程序块内声明替代变量的变量类型不一致，则程序执行过程会报变量类型错误的bug，对于一个学习PL/SQL的新手来说，如果在以后的编程过程中遇见了这种问题，与其花时间排查bug，不如直接在accept语句中限定替代变量的数据类型。

例程2：

/*G:\Oracle\sqltest\test.sql*/
set verify off
accept num NUMBER prompt'输入自定义值num:'
DECLARE 
num1 NUMBER := #
BEGIN
DBMS_OUTPUT.PUT_LINE('num1 = ' || num1 );
END;
/

运行结果：

SQL> @G:\Oracle\sqltest\test.sql
输入自定义值num:ASD
SP2-0425: "ASD" 不是有效的 NUMBER
输入自定义值num:1
num1 = 1

PL/SQL 过程已成功完成。

使用HIDE可以用***替代输入的内容。

例程3：

accept num NUMBER prompt'输入自定义值num:' HIDE

运行结果：

SQL> @G:\Oracle\sqltest\test.sql
输入自定义值num:***
num1 = 123

PL/SQL 过程已成功完成。

使用FORMAT限定输入字串长度。

例程4：

set verify off
accept inputstr char prompt'输入自定义值str:' FORMAT A5
DECLARE 
strs char(12):= '&inputstr';
BEGIN
DBMS_OUTPUT.PUT_LINE('str = ' || strs );
END;
/

运行结果：

SQL> @G:\Oracle\sqltest\test.sql
输入自定义值str:abcdefg
SP2-0598: "abcdefg" 与输入格式 "A5" 不匹配
输入自定义值str:abc
str = abc

PL/SQL 过程已成功完成。

使用FORMAT限定输入字串格式。

例程4：（来自Oracle12C–替代变量（六））

set verify off
ACCEPT inputDate DATE FORMAT 'YYYY-MM-DD' PROMPT '请输入要查询的雇佣日期：' 
SELECT empno , ename , job , hiredate
FROM emp 
WHERE hiredate=TO_DATE('&inputDate','YYYY-MM-DD') ;

运行结果：

SQL> @G:\Oracle\sqltest\test.sql
请输入要查询的雇佣日期：1981-11-17

     EMPNO ENAME      JOB       HIREDATE
---------- ---------- --------- --------------
      7839 KING       PRESIDENT 17-11月-81

注意：当一个程序中有多个相同的替代变量时，如果不做任何处理的话，就需要多次输入相同的替代变量。使用&&替换第一次出现的替代变量的&，可以使Oracle服务器不再重复请求我们输入同一个替代变量的值。

2.3 用户定义“变量”

除了手动输入替代变量，我们还可以直接通过DEFINE来定义变量，其实也就是定义了一个常量，这和C/C++中相似。定义的常量在整个会话中有效。
使用UNDEFINE 可以清除变量，使用DEFINE+替代变量名 可以查看定义的变量。
例程4：

/*缓存区内容*/
DECLARE
num1 NUMBER := #
BEGIN
DBMS_OUTPUT.PUT_LINE('num1 = ' || num1 );
END;
/

运行结果：

SQL> define num=1
SQL> /
num1 = 1
 
PL/SQL 过程已成功完成。
SQL> define num
DEFINE NUM             = "1" (CHAR)
SQL> undefine num
SQL> define num
SP2-0135: 符号 num 未定义
SQL> 

3.绑定变量

不同于define声明的常量，绑定变量虽然也是在SQL *PLUS会话中直接创建（支持NUMBER和CHAR类型），但是我们可以在PL/SQL程序块中对其进行调用。引用方法是在绑定变量前加冒号（：）。
例程1：

SQL> VARIABLE b_var NUMBER
SQL> L
  1  BEGIN
  2  :B_VAR :=25;
  3* END;
SQL> /

PL/SQL 过程已成功完成。

SQL> PRINT b_var

     B_VAR
----------
        25

结合数据查询的简单程序，还可以把数据查询结果存入绑定变量。
例程2：

SQL> VARIABLE g_var NUMBER
SQL> L
  1  BEGIN
  2  select &obj INTO :g_var
  3  FROM emp WHERE empno = 7902;
  4* END;
SQL> /

PL/SQL 过程已成功完成。

SQL> PRINT g_var

     g_var
----------
      3000

注意：
1.如果同时有多个绑定变量，可直接输入print打印所有绑定变量；
2.命令set autoprint on可以设置自动打印，执行代码后会直接打印程序段中使用到的绑定变量。

替代变量与绑定变量的学习内容暂时到此为止，往后的学习过程中，相信会频繁使用到两者，如果学习到了新内容，会在此基础上进行扩充！