转自乐沙弥的世界。
--============================
-- 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 := 'CREATETABLE tb_emp' || --为变量赋值，生成动态SQL语句
'ASSELECT * FROM scott.emp WHERE deptno =' || v_deptno;
EXECUTE IMMEDIATEsql_stmt; --执行动态SQL语句
EXECUTE IMMEDIATE 'SELECTCOUNT(*) FROM tb_emp' --直接使用EXECUTE IMMEDIATE后跟动态SQL串获得新表的记录数
INTO v_count;
DBMS_OUTPUT.PUT_LINE('Theemployee 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 IMMEDIATEplsql_block; --执行动态的PL/SQL块
sql_stmt := 'UPDATEtb_emp SET sal = sal + 100 WHERE empno =:eno' || --更新新表的一条记录
'RETURNINGsal INTO:sal'; --动态SQL语句中包含RETURNING子句返回更新后的结果
EXECUTE IMMEDIATEsql_stmt --执行动态SQL块
USINGv_empno
RETURNING INTO v_new_sal; --使用RETURNING子句将结果存放到变量v_new_sal中
DBMS_OUTPUT.PUT_LINE('Newsalary is: ' || v_new_sal);
END;
Theemployee count is : 6
04-JAN-2011
Newsalary is: 1050
二、动态SQL的常见错误
1.使用动态DDL时，不能使用绑定变量
下面的示例中，在创建表示，使用了绑定变量:dno，在执行的时候收到了错误信息。
DECLARE
sql_stmt VARCHAR2(100);
v_deptno VARCHAR2(5) := '30';
v_count NUMBER;
BEGIN
sql_stmt := 'CREATETABLE tb_tmp ' || 'AS SELECT *FROM scott.emp ' ||
'WHEREdeptno = :dno';
EXECUTE IMMEDIATEsql_stmt
USINGv_deptno;
EXECUTE IMMEDIATE 'SELECTCOUNT(*) FROM tb_tmp'
INTO v_count;
DBMS_OUTPUT.PUT_LINE('Thetemp table countis ' || v_count);
END;
DECLARE
*
ERRORat line 1:
ORA-01027: bindvariables not allowed for datadefinition operations
ORA-06512: atline 8
解决办法，将绑定变量直接拼接，如下：
sql_stmt := 'CREATETABLE tb_tmp ' || 'AS SELECT *FROM scott.emp ' || 'WHERE deptno= ' || v_deptno;
2.不能使用schema对象作为绑定参数
下面的示例中，动态SQL语句查询需要传递表名，因此收到了错误提示。
DECLARE
sql_stmtVARCHAR2(100);
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECTCOUNT(*) FROM :tb_name'
INTO v_count;
DBMS_OUTPUT.PUT_LINE('Thetablerecord is ' || v_count);
END;
DECLARE
*
ERRORat line 1:
ORA-00903: invalid table name
ORA-06512: atline 5
处理办法
DECLARE
sql_stmtVARCHAR2(100);
v_tablenameVARCHAR2(30) :='scott.emp'; --增加一个变量并赋值
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECTCOUNT(*) FROM' || v_tablename --使用|| 连接变量
INTO v_count;
DBMS_OUTPUT.PUT_LINE('Thetable recordis ' || v_count);
END;
Thetemp table count is 14
3.动态SQL块不能使用分号结束(;)
下面的示例中，动态SQL语句使用了分号来结束，收到错误提示。
DECLARE
sql_stmtVARCHAR2(100);
--v_tablenameVARCHAR2(30) :='scott.emp';
v_count NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECTCOUNT(*) FROMtb_emp;' --此处多出了分号，应该去掉
INTO v_count;
DBMS_OUTPUT.PUT_LINE('Thetemp table countis ' || v_count);
END;
DECLARE
*
ERRORat line 1:
ORA-00911: invalid character
ORA-06512: atline 6
处理办法
去掉动态SQL语句末尾的分号
4.动态PL/SQL块不能使用正斜杠来结束块，但是块结尾处必须要使用分号(;)
DECLARE
plsql_blockVARCHAR2(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 IMMEDIATEplsql_block;
END;
DECLARE
*
ERRORat line 1:
ORA-06550: line3, column 2:
PLS-00103: Encounteredthe symbol "/" The symbol "/" was ignored.
ORA-06512: atline 13
处理办法
去掉动态PL/SQL 块尾部的斜杠
5.空值传递的问题
下面的示例中对表tb_emp更新，并将空值更新到sal列，直接使用USING NULL收到错误提示。
DECLARE
sql_stmtVARCHAR2(100);
v_empno NUMBER := 7900;
BEGIN
sql_stmt := 'UPDATEtb_emp SET sal = :new_sal WHERE empno=:eno';
EXECUTE IMMEDIATEsql_stmt
USING NULL,v_empno; --此处不能直接使用NULL
END;
USING NULL,v_empno;
*
ERRORat line 7:
ORA-06550: line7, column 11:
PLS-00457: expressionshave to be of SQLtypes
ORA-06550: line6, column 3:
PL/SQL: Statementignored
正确的处理办法
DECLARE
sql_stmtVARCHAR2(100);
v_empno NUMBER := 7900;
v_sal NUMBER; --声明一个新变量，但不赋值
BEGIN
sql_stmt := 'UPDATEtb_emp SET sal = :new_sal WHERE empno=:eno';
EXECUTE IMMEDIATEsql_stmt
USINGv_sal, v_empno;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Thenew 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_statVARCHAR2(100);
v_dno NUMBER := &dno;
v_ename VARCHAR2(25);
v_sal NUMBER;
BEGIN
sql_stat := 'SELECTename,sal FROM scott.emp WHERE deptno = :dno';
OPEN emp_cv FOR sql_stat --使用游标来处理动态SQL
USINGv_dno;
LOOP
FETCH emp_cv
INTO v_sal, v_ename; --从结果集中提取记录时，顺序发生颠倒
EXIT WHEN emp_cv%NOTFOUND;
dbms_output.put_line('Employeenameis ：' || v_ename ||
', Thesalis ：' || 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
*
ERRORat line 1:
ORA-01722: invalidnumber
ORA-06512: atline 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 := 'SELECTename,sal FROM tb_emp WHEREhiredate=:v_date'; --使用了占位符:v_date进行变量绑定
EXECUTE IMMEDIATEsql_stat
INTO v_ename,v_sal
USINGv_date;
DBMS_OUTPUT.PUT_LINE('EmployeeName '||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;
*
ERRORat line 3:
ORA-06550: line3, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line3, column 13:
PL/SQL: Itemignored
ORA-06550: line13, column 9:
PLS-00320: thedeclaration of the type of thisexpression is incomplete or malformed
ORA-06550: line11, column 3:
PL/SQL: Statementignored
处理办法一
执行时输入带引号的字串
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 successfullycompleted.
处理办法二
在声明变量时赋值用引号，如下
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 := 'SELECTename,sal FROM tb_emp WHEREhiredate=' || chr(39) ||v_date||chr(39);--chr(39)代表单引号
EXECUTE IMMEDIATEsql_stat
INTO v_ename,v_sal;
DBMS_OUTPUT.PUT_LINE('EmployeeName '||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 successfullycompleted.
8.单行SELECT 查询不能使用RETURNINGINTO返回
下面的示例中，使用了动态的单行SELECT查询，并且使用了RETURNING子句来返回值。事实上，RETURNINGcoloumn_name INTO 子句仅
仅支持对DML结果集的返回，因此，收到了错误提示。
DECLARE
sql_statVARCHAR2(200);
v_empno tb2.empno%TYPE := &eno;
v_ename tb2.ename%TYPE;
BEGIN
sql_stat := 'SELECTename FROM tb2 WHERE empno =:eno
RETURNINGename INTO :v_ename ';
EXECUTE IMMEDIATEsql_stat
USINGv_empno
RETURNING INTO v_ename;
DBMS_OUTPUT.PUT_LINE('Theemployee name is' || v_ename);
END;
处理办法
去掉动态SQL语句中的RETURNINGcoloumn_name INTO子句，在执行EXECUTE IMMEDIATE时，直接使用INTO子句来传递值。
DECLARE
sql_statVARCHAR2(200);
v_empno tb2.empno%TYPE := &eno;
v_ename tb2.ename%TYPE;
BEGIN
sql_stat := 'SELECTename FROM tb2 WHERE empno =:eno';
-- RETURNINGename INTO :v_ename'; --去掉RETURNING子句
EXECUTE IMMEDIATEsql_stat
INTO v_ename --增加INTO子句来返回变量值
USINGv_empno;
--RETURNINGINTOv_ename; --去掉RETURNING子句
DBMS_OUTPUT.PUT_LINE('Theemployee name is' || v_ename);
END;
三、总结
1.使用动态DDL时，不能使用绑定变量。应该将绑定变量与原动态SQL使用连接符进行连接。
2.不能使用schema对象作为绑定参数，将schema对象与原动态SQL使用连接符进行连接。
3.动态SQL块不能使用分号结束(;)。
4.动态PL/SQL块不能使用正斜杠来结束块，但是块结尾处必须要使用分号(;)。
5.空值传递的时候，不能直接使用USINGNULL子句，应当声明变量，使用变量传递，当未给变量赋值时，即为空值。
6.参数的传入传出应保证顺序的正确，以及防止数据溢出的问题。
7.日期型或字符型在动态SQL中处理时，需要注意单引号个数的问题，特殊情况下可以使用chr(39)作为单引号使用。
8.动态SQL中RETURNINGINTO返回DML操作的结果，对于SELECT查询返回的结果，在执行EXECUTEIMMEDIATE时，直接使用INTO子句来传递。
