PL/SQL语言可以实现对数据库对象的存储和访问。但是标准的PL/SQL要求查询的对象和查询的结果等条件必须是在编译时候就确定的,而在很多情况下却无法保证这一点。这时,就需要使用动态SQL语句。
在PL/SQL中使用动态SQL语句的原因:
为了开发通用的程序;
为了在运行时动态调用其他PLSQL过程;
在运行时输入一个判断条件;
执行DDL语句
PL/SQL中使用动态SQL语句有两种方法,一个是利用DBMS_SQL包,另一个是本地动态SQL语法——EXECUTE IMMEDIATE SQL。一般情况下,EXECUTE IMMEDIATE方式比DBMS_SQL包方式要编码简单,运行速度也要快一些。但是,DBMS_SQL包提供了更为强大的功能,在下列情况下只能使用DBMS_SQL包:
不知道SELECT语句中列的个数和类型;
不知道绑定变量的个数和类型;
使用数组提取大批量数据时;
在同一个会话中多次执行同一个语句。
在下列情况时应该使用本地动态SQL:
知道列的个数和类型;
知道绑定变量的个数和类型;
使用DDL语句;
执行语句的次数很少。
Tom通过大量例子说明了DBMS_SQL和EXECUTE IMMEDIATE的用法。本地动态SQL中OPEN CURSOR FOR的用法,以及利用SYS_CONTEXT解决绑定变量个数不可知的问题,使得本地动态SQL的使用范围进一步扩大。最后Tom利用例子说明了本地动态SQL、DBMS_SQL、本地动态SQL数组方式和DBMS_SQL数组方式的性能差别。
动态SQL也存在着很多弱点:
动态SQL中断了依赖链:当一张表的表结构发生变化或者被删除时,所有访问这张表的存储过程的状态变为INVALID。但是在动态SQL中访问这张表的存储过程不会受影响,这会给系统带来潜在的问题,而且在错误发生后,问题的定位也会变得更加复杂。
代码更脆弱:普通存储过程在编译之后就不会出现语句结构等错误,但是动态SQL却无法验证这一点,它只能在执行的时候才知道SQL语句是否正确。
更难以优化:由动态SQL建立的查询语句比静态SQL构建的查询语句更难以优化,由于查询语句选择的列、查询条件甚至表都会发生变化,因此系统中可能出现各种版本的SQL语句,想要事先为各种查询建立好相应的索引变得几乎不可能,这将大大的增加优化的难度。