动态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的一些其他好处所抵消。

16.7 Oracle11g新特性

16.7.1 DBMS_SQL.TO_REFCURSOR函数

16.7.2 DBMS_SQL.TO_CURSOR


对于标红的那句话在itpub中有实际的例子可以进行解释plsql块和普通sql语句中的绑定变量
posted @ 2014-05-05 00:29  明月镇魂  阅读(190)  评论(0编辑  收藏  举报