轉(zhuǎn)載地址:http://blog.csdn.net/li_guang/article/details/3995794

在Oracle中執(zhí)行動態(tài)SQL的幾種方法

在一般的sql操作中，sql語句基本上都是固定的，如：SELECT t.empno,t.ename  FROM scott.emp t WHERE t.deptno = 20;但有的時候，從應(yīng)用的需要或程序的編寫出發(fā)，都可能需要用到動態(tài)SQl，如：當(dāng) from 后的表 不確定時，或者where 后的條件不確定時，都需要用到動態(tài)SQL。

一、使用動態(tài)游標(biāo)實現(xiàn)1、聲明動態(tài)游標(biāo)TYPE i_cursor_type IS REF CURSOR;2、聲明游標(biāo)變量my_cursor i_cursor_type;3、使用游標(biāo)n_deptno:=20;dyn_select := 'select empno,ename from emp where deptno='||n_deptno;OPEN my_cursor FOR dyn_select;LOOP   FETCH my_cursor INTO n_empno,v_ename;  EXIT WHEN my_cursor%NOTFOUND;  --用n_empno,v_ename做其它處理  --....END LOOP;CLOSE dl_cursor;4、小結(jié)：動態(tài)游標(biāo)可以勝任大多數(shù)動態(tài)SQL的需求了，使用簡潔方便居家旅行之必備殺人放火之法寶。

二、使用 EXECUTE IMMEDIATE最早大家都使用DBMS_SQL包，但是太太麻煩了，最終都放棄了。但是自從有了EXECUTE IMMEDIATE之后，但要注意以下幾點：EXECUTE IMMEDIATE代替了以前Oracle8i中DBMS_SQL package包.它解析并馬上執(zhí)行動態(tài)的SQL語句或非運(yùn)行時創(chuàng)建的PL/SQL塊.動態(tài)創(chuàng)建和執(zhí)行SQL語句性能超前，EXECUTE IMMEDIATE的目標(biāo)在于減小企業(yè)費(fèi)用并獲得較高的性能，較之以前它相當(dāng)容易編碼.盡管DBMS_SQL仍然可用，但是推薦使用EXECUTE IMMEDIATE,因為它獲的收益在包之上。  使用技巧

1. EXECUTE IMMEDIATE將不會提交一個DML事務(wù)執(zhí)行，應(yīng)該顯式提交 如果通過EXECUTE IMMEDIATE處理DML命令，那么在完成以前需要顯式提交或者作為EXECUTE IMMEDIATE自己的一部分. 如果通過EXECUTE IMMEDIATE處理DDL命令,它提交所有以前改變的數(shù)據(jù)

2. 不支持返回多行的查詢,這種交互將用臨時表來存儲記錄(參照例子如下)或者用REF cursors.

3. 當(dāng)執(zhí)行SQL語句時，不要用分號，當(dāng)執(zhí)行PL/SQL塊時，在其尾部用分號.

4. 在Oracle手冊中，未詳細(xì)覆蓋這些功能。下面的例子展示了所有用到Execute immediate的可能方面.希望能給你帶來方便.

5. 對于Forms開發(fā)者,當(dāng)在PL/SQL 8.0.6.3.版本中，F(xiàn)orms 6i不能使用此功能.

EXECUTE IMMEDIATE用法例子

1. 在PL/SQL運(yùn)行DDL語句

 begin  execute immediate 'set role all'; end;

2. 給動態(tài)語句傳值(USING 子句)

 declare  l_depnam varchar2(20) := 'testing';  l_loc    varchar2(10) := 'Dubai';  begin  execute immediate 'insert into dept values  (:1, :2, :3)'    using 50, l_depnam, l_loc;  commit; end;

3. 從動態(tài)語句檢索值(INTO子句)

 declare  l_cnt    varchar2(20); begin  execute immediate 'select count(1) from emp'    into l_cnt;  dbms_output.put_line(l_cnt); end;

4. 動態(tài)調(diào)用例程.例程中用到的綁定變量參數(shù)必須指定參數(shù)類型.黓認(rèn)為IN類型,其它類型必須顯式指定

 declare  l_routin   varchar2(100) := 'gen2161.get_rowcnt';  l_tblnam   varchar2(20) := 'emp';  l_cnt      number;  l_status   varchar2(200); begin  execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'    using in l_tblnam, out l_cnt, in out l_status;

  if l_status != 'OK' then     dbms_output.put_line('error');  end if; end;

5. 將返回值傳遞到PL/SQL記錄類型;同樣也可用%rowtype變量

 declare    type empdtlrec is record (empno  number(4),ename  varchar2(20),deptno  number(2));    empdtl empdtlrec; begin    execute immediate 'select empno, ename, deptno '||'from emp where empno = 7934'    into empdtl; end;

6. 傳遞并檢索值.INTO子句用在USING子句前

 declare   l_dept    pls_integer := 20;   l_nam     varchar2(20);   l_loc     varchar2(20); begin    execute immediate 'select dname, loc from dept where deptno = :1'       into l_nam, l_loc       using l_dept ; end;

7. 多行查詢選項.對此選項用insert語句填充臨時表，用臨時表進(jìn)行進(jìn)一步的處理,也可以用REF cursors糾正此缺憾.  declare  l_sal   pls_integer := 2000; begin  execute immediate 'insert into temp(empno, ename) ' ||                   '          select empno, ename from emp ' ||                   '          where  sal > :1'    using l_sal;  commit; end;

       對于處理動態(tài)語句,EXECUTE IMMEDIATE比以前可能用到的更容易并且更高效.當(dāng)意圖執(zhí)行動態(tài)語句時，適當(dāng)?shù)靥幚懋惓８又匾?應(yīng)該關(guān)注于捕獲所有可能的異常.