新博客http://www.cnblogs.com/zhjh256 欢迎访问
分类: Oracle
2008-04-18 15:04:37
动态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后传输回来。