Oracle PL/SQL 程序设计读书笔记 - 第16章 动态SQL和动态PL/SQL
Oracle PL/SQL 程序设计读书笔记 - 第16章 动态SQL和动态PL/SQL
Oracle PL/SQL 程序设计读书笔记 - 第16章 动态SQL和动态PL/SQL
16.1 NDS语句
16.1.1 EXECUTE IMMEDIATE语句
EXECUTE IMMEDIATE SQL_string
[INTO {define_variable[, define_variable]...| record}]
[USING [IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument]...];
我们可以把这个语句用于除了多行查询以外的任何SQL语句或者PL/SQL块。如果SQL_string后面带了分号，这就会按照一个PL/SQL块来处理；否则，就会按照DML或者DDL语句来处理。
16.1.2 OPEN FOR语句
OPEN {cursor_variable |:host_cursor_variable} FOR SQL_string
[USING bind_argument [, bind_argument]...];
16.2 绑定变量
16.2.2 重复的占位符
- 当我们执行一个动态SQL字符串，我们必须为每一个占位符都提供一个参数，即使这些占位符是重复的。
- 如果我们执行的是一个动态PL/SQL块，我们必须为每一个唯一占位符提供一个参数。
16.2.3 NULL值的传递
我们不能直接将NULL直接量当做参数来进行传递。
- 把NULL值隐藏在一个变量后面，如果用一个未初始化的的变量做起来会更容易些。* 通过转换函数把NULL值显式的转换成一个有类型的值。
16.4 动态PL/SQL
- 动态字符串必须是在一个有效的PL/SQL块，这个块必须是以DECLARE或者BEGIN关键字开始，用END关键字和分号结束。如果字符串不是用分号结尾的，是不会被识别成PL/SQl块的。
- 动态块中，我们只能访问属于全局作用范围的PL/SQl代码元素。
- 在动态PL/SQL块招聘的错误可以在运行EXECUTE IMMEDIATE语句的局部块中捕获并处理。
16.5 NDS的建议
16.5.1 对于共享程序使用调用者权限
建议对所有的动态SQL程序都和要加上AUTHID CURRENT_USER子句，尤其对那些计划要提供给开发人员使用的程序更是如此。
16.5.2 预估并处理动态的错误
- 在调用EXECUTE IMMEDIATE和OPEN FOR时，总是带上一个异常处理单元。
- 在每一个异常处理句柄里，记录下或者显示错误发生时的错误消息以及SQL语句。
- 也可以考虑在这些语句之前加上一个跟踪机制，这样我们就可以很容易的对动态SQL的构造和执行进行观察了。
16.5.3 使用绑定而不是拼接
- 绑定通常更快速
- 绑定的编写和维护都很容易
- 绑定有助于避免隐式转换
- 绑定避免了发生代码注入的可能性
不过，使用绑定也有一些潜在的缺陷。绑定变量会忽略柱状统计信息，因为绑定变量的值是在语句解析后赋值的。对于CBO来说，可用信息太少了，有可能无法为我们的SQL语句提供最好的执行计划。
16.5.5 把代码注入的风险最小化
- 限制用户权限
- 尽可能使用绑定变量
- 检查动态文本中的危险文本
- 用DBMS_ASSERT校验输入
16.6 使用时候使用DBMS_SQL
16.6.1 解析非常长的字符串
Oracle10g的EXECUTE IMMEDIATE所能执行的VARCHAR2字符串，其内容最大长度限制是32K。如果超过这个长度就要用DBM_SQL了。 不过11g中EXECUTE IMMEDIATE可以执行一个VARCHAR2字符串或者一个CLOB，后者的最大长度可以有4GB。
16.6.2 得到查询的列的信息
DBMS_SQL允许我们对动态游标中的列进行描述，以记录的关联数组形式返回每个列的信息。
16.6.4 把动态游标的解析最小化
EXECUTE IMMEDIATE有一个缺陷，每次执行一个动态字符串时，都需要重新准备，通常包括会解析、优化以及生成执行计划。对于大部分动态SQL的需求而言，这些步骤所带来的开销可以被NDS的一些其他好处所抵消。
