[转]动态SQL的技巧与陷阱

动态SQL的技巧与陷阱
这节会让我们了解如何完全利用动态SQL语句并避免一些常见的缺陷。
1、改善性能
下例中，Oracle为每个不同的emp_id单独打开一个游标。这就造成资源浪费并降低了效率：
CREATE PROCEDURE fire_employee(emp_id NUMBER) AS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = ' || TO_CHAR(emp_id);
END;
我们可以使用绑定变量来改善性能，如下例所示。这就能让Oracle为不同的emp_id值重用同一个游标。
CREATE PROCEDURE fire_employee(emp_id NUMBER) AS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM emp WHERE empno = :num'
              USING emp_id;
END;
2、让过程对任意模式对象起作用
假设我们需要一个过程，让它接受数据表名，然后将指定的表从数据库中删除。我们可能会下面这样编写使用动态SQL的独立过程：
CREATE PROCEDURE drop_table(table_name IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE :tab'
              USING table_name;
END;
但是，在运行的时候，这个过程可能会因为表名错误而无法执行成功。这就是我们为什么不能用参数绑定来为动态SQL传递模式对象的名称。解决方法是直接把参数嵌套到字符串中。我们把上面的EXECUTE IMMEDIATE语句修改一下：
CREATE PROCEDURE drop_table(table_name IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
END;
这样，我们就可以向动态SQL语句传递任意数据表名称了。
3、使用重复占位符
动态SQL语句中的占位符与USING子句中的绑定参数是位置关联的，而不是名称关联。所以，如果在SQL语句中同样的占位符出现两次或多次，那么，它的每次出现都必须与一个USING子句中的绑定参数相关联。例如下面的动态字符串：
sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';
我们可以为动态字符串编写对应的USING子句：
EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;
但是，动态PL/SQL块中只有唯一的占位符才与USING子句中的绑定参数按位置对应。所以，如果一个占位符在PL/SQL块中出现两次或多次，那么所有这样相同的占位符都只与USING语句中的一个绑定参数相对应。比如下面的例子，第一个占位符(x)与第一个绑定参数(a)关联，第二个占位符 (y)与第二个绑定参数(b)关联。
DECLARE
  a   NUMBER := 4;
  b   NUMBER := 7;
BEGIN
  plsql_block    := 'BEGIN calc_stats(:x, :x, :y, :x); END';

  EXECUTE IMMEDIATE plsql_block
              USING a, b;
  ...
END;
4、使用游标属性
每个显式的游标都有四个属性：%FOUND、%ISOPEN、%NOTFOUND和%ROWCOUNT。它们都能返回与静态或动态SQL语句执行结果相关的有用信息。
为处理SQL数据操作语句，Oracle会打开一个名为SQL的隐式游标。它的属性会返回最近一次执行的INSERT、UPDATE、DELETE或单行SELECT的相关信息。例如，下面函数就使用%ROWCOUNT返回从数据表中删除的行数：
CREATE FUNCTION rows_deleted(table_name IN VARCHAR2, condition IN VARCHAR2)
  RETURN INTEGER AS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM ' || table_name || ' WHERE ' || condition;

  RETURN SQL%ROWCOUNT;   -- return number of rows deleted
END;
同样，当我们把游标变量的名字附加进去时，游标的属性也能返回多行查询执行结果的相关信息。
5、传递空值
下面，我们来为动态SQL传递空值，见下面的EXECUTE IMMEDIATE语句：
EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x' USING NULL;
但是，这个语句会因为在USING子句中使用NULL而执行失败，因为USING语句中所传递的参数是不能为空的。所以，要想解决这个问题，直接使用字符串就可以了：
DECLARE
  a_null   CHAR(1);   -- set to NULL automatically at run time
BEGIN
  EXECUTE IMMEDIATE 'UPDATE emp SET comm = :x'
              USING a_null;
END;
6、远程操作
如下例所示，PL/SQL子程序能够执行引用远程数据库对象的动态SQL语句：
PROCEDURE delete_dept(db_link VARCHAR2, dept_id INTEGER) IS
BEGIN
  EXECUTE IMMEDIATE 'DELETE FROM dept@' || db_link || ' WHERE deptno = :num'
              USING dept_id;
END;
同样，远程过程调用(RPC)的目标也包括动态SQL语句。例如，假设下面返回数据表中记录个数的函数存放在Chicago的数据库上：
CREATE FUNCTION row_count(tab_name VARCHAR2)
  RETURN INTEGER AS
  ROWS   INTEGER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name
               INTO ROWS;

  RETURN ROWS;
END;
下面是一个从匿名SQL块调用远程函数的例子：
DECLARE
  emp_count   INTEGER;
BEGIN
  emp_count    := row_count@chicago('emp');
END;
7、使用调用者权限
默认情况下，存储过程是使用定义者权限执行的，而不是调用者权限。这样的过程是绑定在它们所属的模式对象上的。假设下面用于删除数据库对象的过程存放在模式对象scott上：
CREATE PROCEDURE drop_it(kind IN VARCHAR2, NAME IN VARCHAR2) AS
BEGIN
  EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || NAME;
END;
我们把用户jones赋予允许他执行上面的存储过程的EXECUTE权限。当用户jones调用drop_it时，动态SQL就会使用用户scott的权限来执行语句：
SQL> CALL drop_it('TABLE', 'dept');
这样的话，由于数据表dept的前面并没有限定修饰词进行限制，语句执行时删除的就是scott上的数据表，而不是jones上的。
但是，AUTHID子句可以让存储过程按它的调用者权限来执行，这样的存储过程就不会绑定在一个特定的schema对象上。例如下面的新版本drop_it就会按调用者权限执行：
CREATE PROCEDURE drop_it(kind IN VARCHAR2, NAME IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
  EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || NAME;
END;
8、使用RESTRICT_REFERENCES
从SQL语句调用函数时，我们必须要遵守一定的规则来避免负面印象。为了检测冲突，我们可以使用编译指示RESTRICT_REFERENCES。它能确保函数没有读和/或写数据表和/或打包变量。
但是，如果函数体包含了动态INSERT、UPDATE或DELETE语句，那它就总与规则"write no database state" (WNDS)和"read no database state" (RNDS)相冲突。这是因为动态SQL语句是在运行时才被检验，而不是编译期。在一个EXECUTE IMMEDIATE语句中，只有INTO子句才能在编译期检验是否与RNDS冲突。
9、避免死锁
有些情况下，执行SQL数据定义语句会导致死锁。例如，下面的过程就能引起死锁，因为它尝试着删除自身。为了避免死锁，就不要用ALTER或DROP来操作正在使用的子程序或包。
CREATE PROCEDURE calc_bonus (emp_id NUMBER) AS
BEGIN
  ...
  EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus';