动态sql，绑定变量和调优经验分析

为什么在PL/SQL里用动态SQL ?

有时SQL语句在编译的时候不能全部确定，动态SQL使你能够在运行时动态地构建SQL语句，从而创建更通用、灵活的应用程序。

何时用动态SQL?

1. 你想执行SQL数据定义语句(如CREATE)，一个数据控制语句(如GRANT)，或一个会话控制语句(如ALTER SESSION)，但它们和INSERT、UPDATE和DELETE语句不同，是不能被直接包括在PL/SQL程序里的。这时需要动态SQL。

2.你想要更多的灵活性。例如，你可能想要给一个SELECT语句创建不同搜索条件的WHERE字句。再比如，你事先并不知道需要查询哪些列，甚至连这些列叫什么都不清楚。

怎样用动态SQL?

使用EXECUTE IMMEDIATE语句去解析和立即运行动态SQL语句或者一个匿名PL/SQL语块。

EXECUTE IMMEDIATE的主要参数是一个包含SQL语句的字符串。字符串中可以包含占位符，就是在任意名称前面加一个冒号，叫做绑定变量。对应绑定变量，在INTO, USING, 和RETURNING INTO子句中可以用定义好的变量去替代这些绑定变量。

关于绑定变量，可以看下面的例3.

例1:

DECLARE
  v_sSQL VARCHAR2(1024);
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE BONUS (ID NUMBER, AMT NUMBER)';
  EXECUTE IMMEDIATE 'alter session set sql_trace = TRUE';
  EXECUTE IMMEDIATE 'alter session set tracefile_identifier = ''Demo''';
  EXECUTE IMMEDIATE 'INSERT INTO BONUS VALUES(1, 12345.67890)';
  EXECUTE IMMEDIATE 'INSERT INTO BONUS VALUES(2, 12345.67890)';
  EXECUTE IMMEDIATE 'UPDATE BONUS SET AMT = NULL WHERE ID = 1';
  EXECUTE IMMEDIATE 'UPDATE BONUS SET AMT = NULL WHERE ID = 2';
  EXECUTE IMMEDIATE 'alter session set sql_trace =FALSE';
  EXECUTE IMMEDIATE 'DROP TABLE BONUS';

例2：

BEGIN
  EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''semicolons''); END;';
END;

注意如果是匿名块，在句尾要加分号。

例3： 

DECLARE
  v_sSQL     VARCHAR2(1024);
  v_nNull    NUMBER;
  v_sTabName VARCHAR2(1024);
  v_nNewAmt  NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'alter session set tracefile_identifier = ''Demo2''';
  EXECUTE IMMEDIATE 'alter session set sql_trace = TRUE';
  EXECUTE IMMEDIATE 'CREATE TABLE BONUS (ID NUMBER, AMT NUMBER)';
  v_sSQL := 'INSERT INTO BONUS VALUES(:id, :amt)';
  EXECUTE IMMEDIATE v_sSQL
    USING 1, 12345.67890;
  EXECUTE IMMEDIATE v_sSQL
    USING 2, 98765.43210;
  FOR v_nID IN 1 .. 2 LOOP
    EXECUTE IMMEDIATE 'UPDATE BONUS SET AMT = :amt WHERE ID = :id RETURNING AMT INTO :new_amt'
      USING v_nNull, v_nID
      RETURNING INTO v_nNewAmt;
    dbms_output.put_line(nvl(v_nNewAmt, 0));
  END LOOP;
  EXECUTE IMMEDIATE 'DROP TABLE BONUS';
  EXECUTE IMMEDIATE 'alter session set sql_trace =FALSE';
END;

使用绑定变量注意点：

1. 你可以把所有参数模式是IN的绑定参数放在USING子句上。

2. 在DML语句里使用RETURNING放OUT参数。用RETURNING INTO则没有指定参数模式

3. 占位符只能用在可以设置变量的地方，比如WHERE子句中，不能是数据库对象，比如表名

4. 在USING子句中，不能是NULL。绕开的方式是使用未初始化的变量

在动态SQL中使用Bulk：

1. Bulk可以绑定参数和一组集合类型的值。

2. 集合类型可以是任何PL/SQL的集合类型，比如索引表、嵌套表、变长数组

3. 有三个子句后面支持Bulk关键字，EXECUTE IMMEDIATE、FETCH和FORALL

例4：

DECLARE

  TYPE EmpCurTyp IS REF CURSOR; 

  TYPE NumListIS TABLE OF NUMBER; 

  TYPE NameListIS TABLE OF VARCHAR2(25); 

  emp_cv EmpCurTyp;

  empids NumList;

  enames NameList;

  sals NumList;

BEGIN

  OPEN emp_cv FOR 'SELECT employee_id,last_name FROM employees'; 

  FETCH emp_cv BULK COLLECT INTO empids, enames;

  CLOSE emp_cv;

  EXECUTE IMMEDIATE 'SELECT salary FROM employees' BULK COLLECT INTO sals;

END; 

例5：

DECLARE

  TYPE NameList IS TABLE OF VARCHAR2(15); 

  enames NameList;

  bonus_amt NUMBER := 50; 

  sql_stmt VARCHAR(200); 

BEGIN

  sql_stmt:= 'UPDATE employees SET salary = salary + :1 RETURNING last_name INTO :2'; 

  EXECUTE IMMEDIATE sql_stmt USING bonus_amt RETURNING BULK COLLECT INTO enames;

END;

例6：

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  TYPE NameList IS TABLE OF VARCHAR2(15);
  empids NumList;
  enames NameList;
BEGIN
  empids := NumList(101, 102, 103, 104, 105);
  FORALL i IN 1 .. 5 
    EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * 1.04 WHERE employee_id= :1 
                      RETURNING last_name INTO :2'
      USING empids(i)
      RETURNING BULK COLLECT
      INTO enames;
END;

动态SQL调优经验分享

调优的时候可以使用tkprof。因为调优前后SQL功能是一致的，所以主要关注游标解析耗费的时间，所以在用tkprof时，加上sort=prsela，使输出文件中SQL的顺序按照游标解析耗费的时间排序。

下面是8个调优案例，包括调优前和调优后的SQL

案例一：

案例二：

案例三：

案例四：

案例五：

案例六：

案例七：

案例八：