--============================
-- PL/SQL --> 动态 SQL 的常见错误
--============================
动态 SQL 在使用时，有很多需要注意的地方，如动态 SQL 语句结尾处不能使用分号 (;) ，而动态 PL / SQL 结尾处需要使用分号 (;) ，但不能使用正
斜杠结尾 (/) ，以及 shcema 对象不能直接作为变量绑定。本文介绍了动态 SQL 的常见问题。
一、演示动态 SQL 的使用
下面的示例中，首先使用动态 SQL 基于 scott . emp 创建表 tb2 ，然后里直接使用动态 SQL 从新表中获取记录数并输出。再接下来是定义了一个动
态 PL / SQL 代码并执行以获取当前的系统时间，最后使用动态 SQL 对新表进行更新。
DECLARE -- 定义变量以及给变量设定初始值
sql_stmt VARCHAR2 ( 100 );
plsql_block VARCHAR2 ( 300 );
v_deptno NUMBER := 30 ;
v_count NUMBER ;
v_new_sal VARCHAR2 ( 5 );
v_empno NUMBER := 7900 ;
BEGIN
sql_stmt := 'CREATE TABLE tb_emp ' || -- 为变量赋值，生成动态 SQL 语句
'AS SELECT * FROM scott.emp WHERE deptno = ' || v_deptno ;
EXECUTE IMMEDIATE sql_stmt ; -- 执行动态 SQL 语句
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tb_emp' -- 直接使用 EXECUTE IMMEDIATE 后跟动态 SQL 串获得新表的记录数
INTO v_count ;
DBMS_OUTPUT . PUT_LINE ( 'The employee count is : ' || v_count );
plsql_block := 'DECLARE ' || -- 声明一个 PL/SQL 块，存放到变量 plsql_block 中
' v_date DATE; ' ||
'BEGIN ' ||
' SELECT SYSDATE INTO v_date FROM DUAL; ' ||
' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,''DD-MON-YYYY''));' ||
'END;' ;
EXECUTE IMMEDIATE plsql_block ; -- 执行动态的 PL/SQL 块
sql_stmt := 'UPDATE tb_emp SET sal = sal + 100 WHERE empno =:eno ' || -- 更新新表的一条记录
'RETURNING sal INTO :sal' ; -- 动态 SQL 语句中包含 RETURNING 子句返回更新后的结果
EXECUTE IMMEDIATE sql_stmt -- 执行动态 SQL 块
USING v_empno
RETURNING INTO v_new_sal ; -- 使用 RETURNING 子句将结果存放到变量 v_new_sal 中
DBMS_OUTPUT . PUT_LINE ( 'New salary is: ' || v_new_sal );
END ;
The employee count is : 6
04 - JAN - 2011
New salary is: 1050
二、动态 SQL 的常见错误
1. 使用动态 DDL 时，不能使用绑定变量
下面的示例中，在创建表示，使用了绑定变量 : dno ，在执行的时候收到了错误信息。
DECLARE
sql_stmt VARCHAR2 ( 100 );
v_deptno VARCHAR2 ( 5 ) := '30' ;
v_count NUMBER ;
BEGIN
sql_stmt := 'CREATE TABLE tb_tmp ' || 'AS SELECT * FROM scott.emp ' ||
'WHERE deptno = :dno' ;
EXECUTE IMMEDIATE sql_stmt
USING v_deptno ;
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tb_tmp'
INTO v_count ;
DBMS_OUTPUT . PUT_LINE ( 'The temp table count is ' || v_count );
END ;
DECLARE
*
ERROR at line 1 :
ORA - 01027 : bind variables not allowed for data definition operations
ORA - 06512 : at line 8
解决办法，将绑定变量直接拼接，如下：
sql_stmt := 'CREATE TABLE tb_tmp ' || 'AS SELECT * FROM scott.emp ' || 'WHERE deptno = ' || v_deptno ;
2. 不能使用 schema 对象作为绑定参数
下面的示例中，动态 SQL 语句查询需要传递表名，因此收到了错误提示。
DECLARE
sql_stmt VARCHAR2 ( 100 );
v_count NUMBER ;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :tb_name'
INTO v_count ;
DBMS_OUTPUT . PUT_LINE ( 'The table record is ' || v_count );
END ;
DECLARE
*
ERROR at line 1 :
ORA - 00903 : invalid table name
ORA - 06512 : at line 5
处理办法
DECLARE
sql_stmt VARCHAR2 ( 100 );
v_tablename VARCHAR2 ( 30 ) := 'scott.emp' ; -- 增加一个变量并赋值
v_count NUMBER ;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || v_tablename -- 使用 || 连接变量
INTO v_count ;
DBMS_OUTPUT . PUT_LINE ( 'The table record is ' || v_count );
END ;
The temp table count is 14
3. 动态 SQL 块不能使用分号结束 (;)
下面的示例中，动态 SQL 语句使用了分号来结束，收到错误提示。
DECLARE
sql_stmt VARCHAR2 ( 100 );
--v_tablename VARCHAR2(30) :='scott.emp';
v_count NUMBER ;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM tb_emp;' -- 此处多出了分号，应该去掉
INTO v_count ;
DBMS_OUTPUT . PUT_LINE ( 'The temp table count is ' || v_count );
END ;
DECLARE
*
ERROR at line 1 :
ORA - 00911 : invalid character
ORA - 06512 : at line 6
处理办法
去掉动态 SQL 语句末尾的分号
4. 动态 PL / SQL 块不能使用正斜杠来结束块，但是块结尾处必须要使用分号 (;)
DECLARE
plsql_block VARCHAR2 ( 300 );
BEGIN
plsql_block := 'DECLARE ' ||
' v_date DATE; ' ||
' BEGIN ' ||
' SELECT SYSDATE INTO v_date FROM DUAL; ' ||
' DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_date,''YYYY-MM-DD''));' ||
'END;
/' ; -- 此处多出了 / ，应该将其去掉
EXECUTE IMMEDIATE plsql_block ;
END ;
DECLARE
*
ERROR at line 1 :
ORA - 06550 : line 3 , column 2 :
PLS - 00103 : Encountered the symbol "/" The symbol "/" was ignored .
ORA - 06512 : at line 13
处理办法
去掉动态 PL / SQL 块尾部的斜杠
5. 空值传递的问题
下面的示例中对表 tb_emp 更新，并将空值更新到 sal 列，直接使用 USING NULL 收到错误提示。
DECLARE
sql_stmt VARCHAR2 ( 100 );
v_empno NUMBER := 7900 ;
BEGIN
sql_stmt := 'UPDATE tb_emp SET sal = :new_sal WHERE empno=:eno' ;
EXECUTE IMMEDIATE sql_stmt
USING NULL, v_empno ; -- 此处不能直接使用 NULL
END ;
USING NULL, v_empno ;
*
ERROR at line 7 :
ORA - 06550 : line 7 , column 11 :
PLS - 00457 : expressions have to be of SQL types
ORA - 06550 : line 6 , column 3 :
PL / SQL : Statement ignored
正确的处理办法
DECLARE
sql_stmt VARCHAR2 ( 100 );
v_empno NUMBER := 7900 ;
v_sal NUMBER ; -- 声明一个新变量，但不赋值
BEGIN
sql_stmt := 'UPDATE tb_emp SET sal = :new_sal WHERE empno=:eno' ;
EXECUTE IMMEDIATE sql_stmt
USING v_sal , v_empno ;
COMMIT ;
DBMS_OUTPUT . PUT_LINE ( 'The new sal is NULL' );
END ;
6. 传递参数时顺序不正确的问题
使用 USING 传递参数到动态 SQL 或使用 INTO 子句传递结果集到变量应注意按正确的顺序排列处理
下面的示例中由于 v_ename 与 v_sal 为不同的数据类型，在使用 INTO 时不小心将顺序颠倒，导致错误产生。当然，如果数据类型相同，
且不会存在溢出的情况下将没有错误提示。
DECLARE
TYPE emp_cur_type IS REF CURSOR ;
emp_cv emp_cur_type ;
sql_stat VARCHAR2 ( 100 );
v_dno NUMBER := & dno ;
v_ename VARCHAR2 ( 25 );
v_sal NUMBER ;
BEGIN
sql_stat := 'SELECT ename,sal FROM scott.emp WHERE deptno = :dno' ;
OPEN emp_cv FOR sql_stat -- 使用游标来处理动态 SQL
USING v_dno ;
LOOP
FETCH emp_cv
INTO v_sal , v_ename ; -- 从结果集中提取记录时，顺序发生颠倒
EXIT WHEN emp_cv % NOTFOUND ;
dbms_output . put_line ( 'Employee name is ： ' || v_ename ||
', The sal is ： ' || v_sal );
END LOOP ;
CLOSE emp_cv ;
END ;
Enter value for dno : 20
old 5 : v_dno NUMBER := & dno ;
new 5 : v_dno NUMBER := 20 ;
DECLARE
*
ERROR at line 1 :
ORA - 01722 : invalid number
ORA - 06512 : at line 14
处理办法
更正参数变量的顺序
7. 日期和字符型必须要使用引号来处理
下面的示例中，使用了日期型变量，未使用引号标注，且使用了变量绑定，但直接输入日期型数据，而不加引号，则收到错误提示。
DECLARE
sql_stat VARCHAR2 ( 100 );
v_date DATE :=& dt ; -- 定义日期型变量，未使用引号
v_empno NUMBER := 7900 ;
v_ename tb_emp . ename % TYPE ;
v_sal tb_emp . sal % TYPE ;
BEGIN
sql_stat := 'SELECT ename,sal FROM tb_emp WHERE hiredate=:v_date' ; -- 使用了占位符 :v_date 进行变量绑定
EXECUTE IMMEDIATE sql_stat
INTO v_ename , v_sal
USING v_date ;
DBMS_OUTPUT . PUT_LINE ( 'Employee Name ' || v_ename|| ', sal is ' || v_sal );
END ;
Enter value for dt : 1981 - 05 - 01 -- 执行时，输入的字串中也未使用引号，此时收到错误提示
old 3 : v_date DATE :=& dt ;
new 3 : v_date DATE := 1981 - 05 - 01 ;
v_date DATE := 1981 - 05 - 01 ;
*
ERROR at line 3 :
ORA - 06550 : line 3 , column 20 :
PLS - 00382 : expression is of wrong type
ORA - 06550 : line 3 , column 13 :
PL / SQL : Item ignored
ORA - 06550 : line 13 , column 9 :
PLS - 00320 : the declaration of the type of this expression is incomplete or malformed
ORA - 06550 : line 11 , column 3 :
PL / SQL : Statement ignored
处理办法一
执行时输入带引号的字串
flasher@ORCL > /
Enter value for dt : '1981-05-01'
old 3 : v_date DATE :=& dt ;
new 3 : v_date DATE := '1981-05-01' ;
Employee Name BLAKE , sal is 2850
PL / SQL procedure successfully completed .
处理办法二
在声明变量时赋值用引号，如下
v_date DATE := '&dt' ;
如存在字符格式转换，可以直接使用转换函数，如
v_date DATE := TO_DATE ( '&dt' , 'DD-MON-RR' );
如果上面的例子中，动态 SQL 语句不使用绑定日期变量，而是将其连接成字符串，则可以使用下面的方式来实现
DECLARE
sql_stat VARCHAR2 ( 100 );
v_date DATE := '&dt' ;
v_empno NUMBER := 7900 ;
v_ename tb_emp . ename % TYPE ;
v_sal tb_emp . sal % TYPE ;
BEGIN
sql_stat := 'SELECT ename,sal FROM tb_emp WHERE hiredate=' || chr ( 39 ) || v_date|| chr ( 39 ); --chr(39) 代表单引号
EXECUTE IMMEDIATE sql_stat
INTO v_ename , v_sal ;
DBMS_OUTPUT . PUT_LINE ( 'Employee Name ' || v_ename|| ', sal is ' || v_sal );
END ;
Enter value for dt : 1981 - 05 - 01
old 3 : v_date DATE := '&dt' ;
new 3 : v_date DATE := '1981-05-01' ;
SELECT ename , sal FROM tb_emp WHERE hiredate = '1981-05-01'
Employee Name BLAKE , sal is 2850
PL / SQL procedure successfully completed .
8. 单行 SELECT 查询不能使用 RETURNING INTO 返回
下面的示例中，使用了动态的单行 SELECT 查询，并且使用了 RETURNING 子句来返回值。事实上， RETURNING coloumn_name INTO 子句仅
仅支持对 DML 结果集的返回，因此，收到了错误提示。
DECLARE
sql_stat VARCHAR2 ( 200 );
v_empno tb2 . empno % TYPE := & eno ;
v_ename tb2 . ename % TYPE ;
BEGIN
sql_stat := 'SELECT ename FROM tb2 WHERE empno =:eno
RETURNING ename INTO :v_ename ' ;
EXECUTE IMMEDIATE sql_stat
USING v_empno
RETURNING INTO v_ename ;
DBMS_OUTPUT . PUT_LINE ( 'The employee name is ' || v_ename );
END ;
处理办法
去掉动态 SQL 语句中的 RETURNING coloumn_name INTO 子句，在执行 EXECUTE IMMEDIATE 时，直接使用 INTO 子句来传递值。
DECLARE
sql_stat VARCHAR2 ( 200 );
v_empno tb2 . empno % TYPE := & eno ;
v_ename tb2 . ename % TYPE ;
BEGIN
sql_stat := 'SELECT ename FROM tb2 WHERE empno =:eno' ;
-- RETURNING ename INTO :v_ename '; -- 去掉 RETURNING 子句
EXECUTE IMMEDIATE sql_stat
INTO v_ename -- 增加 INTO 子句来返回变量值
USING v_empno ;
--RETURNING INTO v_ename; -- 去掉 RETURNING 子句
DBMS_OUTPUT . PUT_LINE ( 'The employee name is ' || v_ename );
END ;
三、总结
1. 使用动态 DDL 时，不能使用绑定变量。应该将绑定变量与原动态 SQL 使用连接符进行连接。
2. 不能使用 schema 对象作为绑定参数，将 schema 对象与原动态 SQL 使用连接符进行连接。
3. 动态 SQL 块不能使用分号结束 (;) 。
4. 动态 PL/SQL 块不能使用正斜杠来结束块，但是块结尾处必须要使用分号 (;) 。
5. 空值传递的时候，不能直接使用 USING NULL 子句，应当声明变量，使用变量传递，当未给变量赋值时，即为空值。
6. 参数的传入传出应保证顺序的正确，以及防止数据溢出的问题。
7. 日期型或字符型在动态 SQL 中处理时，需要注意单引号个数的问题，特殊情况下可以使用 chr(39) 作为单引号使用。
8. 动态 SQL 中 RETURNING INTO 返回 DML 操作的结果，对于 SELECT 查询返回的结果，在执行 EXECUTE IMMEDIATE 时，直接使用 INTO 子句来传递。
四、更多参考
有关 SQL 请参考
SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总
SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)
有关 PL/SQL 请参考
