oracle execute immediate__oracle

轉自：http://blog.sina.com.cn/s/blog_621a2bdf0100gayl.html

當我們碰到有一類儲存名稱和過程的參數上都相似時，可以使用 execute immediate來動態執行這些預存程序，在這次的項目中我有一類用“P_InsertInto_”開頭的預存程序，後面是表名如：P_InsertInto_AC01、P_InsertInto_AC02等等，用來給AC01、AC02........這些表插入資料的，我們就只能用 execute immediate動態執行這些過程，像這樣速度和代碼編寫上就會方便很多了。
這個方法整整研究了一大半天，不容易啊，呵呵。。。。，最終還是成功了。
我對 execute immediate的用法的理解用一個執行個體說明一下，有一個預存程序名為：p_test，它有三個參數，分別為：兩個輸入參數和一個輸出參數，實現過程如：
declare
v_sql varchar2(1000);
c varchar2(1000);
a varchar2(1) :='1';
b number :=1;
begin
--當然動態儲存裝置過程可以在這實現了，直接放到v_sql這個變數中就可以了(在這裡是一個指定的過程p_test)
v_sql:='begin p_test(:v1,:v2,:v3); end;';
execute immediate v_sql usingin '1',in '2', out c;
--或 execute immediate v_sql using'1', '2', out c;
dbms_output.put_line(c);
end;
上面我們就實現了動態傳遞參數和動態儲存裝置過程的調用方法。值得注意的是：預設為in，in可省， out不可省。
還有一個值得注意的地方就是：每一個動態（ execute immediate）執行的方法都有自己的begin.........end;包住才行呀，如果有多個就這樣寫：
--第一個
begin
v_sql:='begin p_test1(:v1,:v2,:v3); end;';
execute immediate v_sql usingin '1',in '2', out c;
--或 execute immediate v_sql using'1', '2', out c;
dbms_output.put_line(c);
end;
--第二個
begin
v_sql:='begin p_test2(:v1,:v2,:v3); end;';
execute immediate v_sql usingin '1',in '2', out c;
--或 execute immediate v_sql using'1', '2', out c;
dbms_output.put_line(c);
end;
。。。。。。。
--第N個
begin
--
end;

 

 

EXECUTE IMMEDIATE代替了以前Oracle8i中DBMS_SQLpackage包.它解析並馬上執行動態SQL語句或非運行時建立的PL/SQL塊.動態建立和執行SQL語句效能超前，EXECUTEIMMEDIATE的目標在於減小企業費用並獲得較高的效能，較之以前它相當容易編碼.儘管DBMS_SQL仍然可用，但是推薦使用EXECUTEIMMEDIATE,因為它獲的收益在包之上。

提示

 

1. EXECUTE IMMEDIATE將不會提交一個DML事務執行，應該顯式提交
如果通過EXECUTE IMMEDIATE處理DML命令，那麼在完成以前需要顯式提交或者作為EXECUTE IMMEDIATE自己的一部分. 如果通過EXECUTE IMMEDIATE處理DDL命令,它提交所有以前改變的資料

 

2. 不支援返回多行的查詢,這種互動將用暫存資料表來儲存記錄(參照例子如下)或者用REF cursors.

 

3. 當執行SQL語句時，不要用分號，當執行PL/SQL塊時，在其尾部用分號.

 

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

 

5. 對於Forms開發人員,當在PL/SQL 8.0.6.3.版本中，Forms 6i不能使用此功能.

 

EXECUTE IMMEDIATE用法例子

 

1. 在PL/SQL運行DDL語句

begin
execute immediate 'set role all';
end;

 

2. 給動態語句傳值(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. 從動態語句檢索值(INTO子句)

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

 

4. 動態調用常式.常式中用到的綁定變數參數必須指定參數類型.黓認為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 outl_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'
    intoempdtl;
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 wheredeptno = :1'
    into l_nam,l_loc
   using l_dept ;
end;

 

7. 多行查詢選項.對此選項用insert語句填充暫存資料表，用暫存資料表進行進一步的處理,也可以用REFcursors糾正此缺憾.

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;

 

      對於處理動態語句,EXECUTE IMMEDIATE比以前可能用到的更容易並且更高效.當意圖執行動態語句時，適當地處理異常更加重要.應該關注於捕獲所有可能的異常.