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可以设置自动打印,执行代码后会直接打印程序段中使用到的绑定变量。
替代变量与绑定变量的学习内容暂时到此为止,往后的学习过程中,相信会频繁使用到两者,如果学习到了新内容,会在此基础上进行扩充!