動態SQL和PL/SQL的EXECUTE IMMEDIATE選項

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

 提示

 

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'
    into l_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 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語句填充暫存資料表，用暫存資料表進行進一步的處理,也可以用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;

 

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


 在oracle的PL程式塊中執行一個varchar2類型變數中的sql 語句

例如： 
create or replace procedure sd_gt_jyz_tj_test (v_flag in varchar2, v_yxnx in varchar2,o_jyztj out number)
is
tmp varchar2(100) := '';

begin
        if v_flag = 'd' then
            tmp:='select count(*)  from b$comm_table where G3E_ID' || v_yxnx;
            execute immediate tmp into o_jyztj;  
        end if;
exception
     when others then
          o_jyztj:=0;
end;