动态sql vs dbms_sql

       动态SQL接受一个包含SQL语句的输入字符串并在数据库中执行。它们通过两种技术实施，一种是原来的DBMS_SQL，另一种是可以使用EXECUTE IMMEDIATE PL/SQL语句。

使用动态SQL的原因

1．非DML语句

       首先PL/SQL过程中不能直接执行DDL，如TRUNCATE TABLE, CREATE INDEX和ALTER SESSION，但是可以使用如下进行：

       EXECUTE IMMEDIATE 'TRUNCATE TABLE table_name';

       虽然这些也可以通过DBMS_SQL，但是复杂得多，所以使用不多。

2．未知的标识符

       有可能在直到运行时才能知道dml语句中相关的模式名，表名和列名，如下：

       如下：

CREATE OR REPLACE PROCEDURE p_purge is
  cursor c_purge is
    select table_name, days_to_retain from purge_control;
begin
  for c_rec in c_purge loop
    execute immediate 'delete from ' || c_rec.table_name ||
                      ' where insert_date < sysdate - :b1 '
      using c_rec.days_to_retain;
    dbms_output.put_line(sql%rowcount);
  end loop;
end;

/

通常情况下，EXECUTE IMMEDIATE最适合于这种情况，但是某些情况下，DBMS_SQL会更好。因为EXECUTE IMMEDIATE每次都要求解析一次，而DBMS_SQL可以使用绑定变量，而无需每次解析。

3．绑定和返回列未知

       在这种情况下，必须使用DBMS_SQL，因为EXECUTE IMMEDIATE要求在USING列表中的变量的数量是固定的。

       不过也有一种技术可以使用应用程序上下文代替绑定变量，可以使动态sql技术在这种情况下使用。

 

DBMS_SQL的额外好处

       EXECUTE IMMEDIATE通常比DBMS_SQL更容易，但是某些情况下必须使用DBMS_SQL。

       EXECUTE IMMEDIATE要求只有一个字符串，因此不得超过32000个。DBMS_SQL.PARSE允许多个字符串拼接。

       EXECUTE IMMEDIATE允许通过dblink执行，但是远程CLOB不允许在本地拆分，而如果调用远程的DBMS_SQL，就可以将clob在远程拆分好到varchar后传输回来。