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

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

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

例如，你可能想要给一个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. USING子句一般跟默认是IN的参数

2. RETURNING子句一般放默认OUT参数。用RETURNING INTO则没有指定参数的INOUT

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

4. 在USING子句后不能放NULL。如果绑定变量就是一个NULL值，可以使用未初始化的变量绕开这个限制

在动态SQL中使用Bulk：

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

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

案例一：

第一个是能够使用绑定变量的就多用，不要使用连接符直接拼在字符串中，那样SQL每次运行都需要重新解析。

案例二：

待优化SQL：

BEGIN

 SELECT VALUE
   INTO v_sSymbolCond
   FROM default_values
  WHERE infeed_id = p_sInfeedId
    AND field = NVL2(v_nMsgType, to_char(v_nMsgType) || '/', '') ||
        'SYMBOL_CONDITION';
 v_sSymbolCond  := REPLACE(v_sSymbolCond,
                           'IDENTIFIER',
                           '''' || p_sIdentifier || '''');
 v_sRetrieveSql := 'SELECT ' || v_sSymbolCond || ' FROM dual';
 EXECUTE IMMEDIATE v_sRetrieveSql
   INTO v_sSymbol;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
   v_sSymbol := p_sIdentifier;
END;

优化后SQL：

BEGIN

 SELECT VALUE
   INTO v_sSymbolCond
   FROM default_values
  WHERE infeed_id = p_sInfeedId
    AND field = NVL2(v_nMsgType, to_char(v_nMsgType) || '/', '') ||
        'SYMBOL_CONDITION';
 v_sSymbolCond  := REPLACE(v_sSymbolCond, 'IDENTIFIER', ':p_sIdentifier');
 v_sRetrieveSql := 'BEGIN SELECT ' || v_sSymbolCond ||
                   ' INTO :v_sSymbol FROM dual; END;';
 EXECUTE IMMEDIATE v_sRetrieveSql
   USING p_sIdentifier, OUT v_sSymbol;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
   v_sSymbol := p_sIdentifier;
END;

案例三：

案例四：

案例五：

案例六：

案例七：

案例八：