动态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; |
