--============================
-- 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