动态SQL在Oracle的应用-优快云博客

  DECLARE
   sql_stmt VARCHAR2(100);
   plsql_block VARCHAR2(300);
   v_zip VARCHAR2(5) := '11106';
   v_total_students NUMBER;
   v_new_zip VARCHAR2(5);
   v_student_id NUMBER := 151;
BEGIN
   -- Create table MY_STUDENT
   sql_stmt := 'CREATE TABLE my_student '||
               'AS SELECT * FROM student WHERE zip = '||v_zip;
   EXECUTE IMMEDIATE sql_stmt;
   -- Select total number of records from MY_STUDENT table
   -- and display results on the screen
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM my_student'
   INTO v_total_students;
   DBMS_OUTPUT.PUT_LINE ('Students added: '||v_total_students);
    -- Select current date and display it on the screen
   plsql_block := 'DECLARE '                                 ||
                  '   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;
   -- Update record in MY_STUDENT table
   sql_stmt := 'UPDATE my_student SET zip = 11105 WHERE student_id =
                :1 '||
               'RETURNING zip INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING v_student_id RETURNING INTO
      v_new_zip;
   DBMS_OUTPUT.PUT_LINE ('New zip code: '||v_new_zip);
END;
/
Students added: 4
08-MAR-2016
New zip code: 11105
PL/SQL procedure successfully completed.

上述脚本包含多个动态SQL的范例。

首先创建表my_student，并且使用邮政编码的指定值记录来填充它。要注意变量v_zip与create语句一起使用，而不是作为绑定参数进行传递。

为啥不能绑定变量呢？

  DECLARE
   sql_stmt VARCHAR2(100);
   v_zip VARCHAR2(5) := '11106';
BEGIN
   sql_stmt := 'CREATE TABLE my_student '||
               'AS SELECT * FROM student WHERE zip = :1';
   EXECUTE IMMEDIATE sql_stmt using v_zip;
end;
/
DECLARE
*
ERROR at line 1:
ORA-01027: bind variables not allowed for data definition operations
ORA-06512: at line 7

通过报错可以看出DDL语句是不允许绑定变量的。

其次，获取my_student表中学生的总数量，并在屏幕上显示。在EXECUTE IMMEDIATE语句中使用into选项，因为select语句会返回单行数据。

第三，创建一个简单的PL/SQL语句块，用于获取当前日期，并在屏幕上显示。由于这个PL/SQL语句块不包含绑定参数，按照最简单的形式使用EXECUTE IMMEDIATE语句

最后，更新my_student表中指定学生ID的数据记录，并使用returning语句返回邮政编码的更新值。这样的话，EXECUTE IMMEDIATE命令会包含using和returning into选项。借助于using选项，可以在运行时向update语句传递学生ID的值;借助于returning into选项，可以把update语句中邮政编码的新值传入自己的程序。

还需要注意的一点是，使用动态SQL语句时，不可以把模式对象的名称作为绑定参数传递给动态SQL语句(pass names of schema objects to dynamic SQL)

  DECLARE
   sql_stmt VARCHAR2(100);
   v_zip VARCHAR2(5) := '11106';
   v_total_students number;
BEGIN
   sql_stmt := 'CREATE TABLE my_student '||
               'AS SELECT * FROM student WHERE zip = '||v_zip;
   EXECUTE IMMEDIATE sql_stmt;
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :my_student'
   INTO v_total_students using 'my_student';
   DBMS_OUTPUT.PUT_LINE ('Students added: '||v_total_students);
end;
/
DECLARE
*
ERROR at line 1:
ORA-00903: invalid table name
ORA-06512: at line 10

为了运行时提供表的名称，需要把这个范例与SELECT语句连起来。

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||my_table into v_total_students;

还需要注意的是，动态SQL语句结尾不应该是分号';'。类似的，动态PL/SQL语句块的结尾不能使右斜线'/'

传入NULL值

在一些情况下，需要给动态SQL语句传递NULL值，作为绑定参数的值。

  DECLARE
	sql_stmt varchar2(100);
BEGIN
	sql_stmt := 'update course set prerequisite = :some_value';
	execute immediate sql_stmt using NULL;
end;
/
execute immediate sql_stmt using NULL;
                                 *
ERROR at line 5:
ORA-06550: line 5, column 34:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 5, column 1:
PL/SQL: Statement ignored

之所以产生这个错误，原因在于USING子句中字面值NULL没有被识别为一种SQL类型。为把NULL值传递给动态SQL语句，应该按照如下样子修改这个范例

  DECLARE
	sql_stmt varchar2(100);
	v_null varchar2(1);
BEGIN
	sql_stmt := 'update course set prerequisite = :some_value';
	execute immediate sql_stmt using v_null;
end;
/