EXECUTE IMMEDIATE鐢ㄦ硶灏忚В

Posted by 楗洅 on 2009骞11鏈19鏃 , No comment

EXECUTE IMMEDIATE 浠ｆ浛浜嗕互鍓峅racle8i涓璂BMS_SQL package鍖.
瀹冭В鏋愬苟椹笂鎵ц鍔ㄦ佺殑SQL璇彞鎴栭潪杩愯鏃跺垱寤虹殑PL/SQL鍧.鍔ㄦ佸垱寤哄拰鎵цSQL璇彞鎬ц兘瓒呭墠锛孍XECUTE IMMEDIATE鐨勭洰鏍囧湪浜庡噺灏忎紒涓氳垂鐢ㄥ苟鑾峰緱杈冮珮鐨勬ц兘锛岃緝涔嬩互鍓嶅畠鐩稿綋瀹规槗缂栫爜.灏界DBMS_SQL浠嶇劧鍙敤锛屼絾鏄帹鑽愪娇鐢‥XECUTE IMMEDIATE,鍥犱负瀹冭幏鐨勬敹鐩婂湪鍖呬箣涓娿


– 浣跨敤鎶宸

1. EXECUTE IMMEDIATE灏嗕笉浼氭彁浜や竴涓狣ML浜嬪姟鎵ц锛屽簲璇ユ樉寮忔彁浜
濡傛灉閫氳繃EXECUTE IMMEDIATE澶勭悊DML鍛戒护锛
閭ｄ箞鍦ㄥ畬鎴愪互鍓嶉渶瑕佹樉寮忔彁浜ゆ垨鑰呬綔涓篍XECUTE IMMEDIATE鑷繁鐨勪竴閮ㄥ垎.
濡傛灉閫氳繃EXECUTE IMMEDIATE澶勭悊DDL鍛戒护,瀹冩彁浜ゆ墍鏈変互鍓嶆敼鍙樼殑鏁版嵁

2. 涓嶆敮鎸佽繑鍥炲琛岀殑鏌ヨ,杩欑浜や簰灏嗙敤涓存椂琛ㄦ潵瀛樺偍璁板綍(鍙傜収渚嬪瓙濡備笅)鎴栬呯敤REF cursors.

3. 褰撴墽琛孲QL璇彞鏃讹紝涓嶈鐢ㄥ垎鍙凤紝褰撴墽琛孭L/SQL鍧楁椂锛屽湪鍏跺熬閮ㄧ敤鍒嗗彿.

4. 鍦∣racle鎵嬪唽涓紝鏈缁嗚鐩栬繖浜涘姛鑳姐
涓嬮潰鐨勪緥瀛愬睍绀轰簡鎵鏈夌敤鍒癊xecute immediate鐨勫彲鑳芥柟闈.甯屾湜鑳界粰浣犲甫鏉ユ柟渚.

5. 瀵逛簬Forms寮鍙戣,褰撳湪PL/SQL 8.0.6.3.鐗堟湰涓紝Forms 6i涓嶈兘浣跨敤姝ゅ姛鑳.

EXECUTE IMMEDIATE — 鐢ㄦ硶渚嬪瓙

1. 鍦≒L/SQL杩愯DDL璇彞

  1. begin
  2.    execute immediate 'set role all';
  3. end;

2. 缁欏姩鎬佽鍙ヤ紶鍊(USING 瀛愬彞)

  1. declare
  2.    l_depnam varchar2(20) := 'testing';
  3.    l_loc     varchar2(10) := 'Dubai';
  4.    begin
  5.    execute immediate 'insert into dept values   (:1, :2, :3)'
  6.      USING 50, l_depnam, l_loc;
  7.    commit;
  8. end;

3. 浠庡姩鎬佽鍙ユ绱㈠(INTO瀛愬彞)

  1. declare
  2.    l_cnt     varchar2(20);
  3. begin
  4.    execute immediate 'select count(1) from emp'
  5.      INTO l_cnt;
  6.    dbms_output.put_line(l_cnt);
  7. end;

4. 鍔ㄦ佽皟鐢ㄤ緥绋.渚嬬▼涓敤鍒扮殑缁戝畾鍙橀噺鍙傛暟蹇呴』鎸囧畾鍙傛暟绫诲瀷.
榛撹涓篒N绫诲瀷,鍏跺畠绫诲瀷蹇呴』鏄惧紡鎸囧畾

  1. declare
  2.    l_routin    varchar2(100) := 'gen2161.get_rowcnt';
  3.    l_tblnam    varchar2(20) := 'emp';
  4.    l_cnt       number;
  5.    l_status    varchar2(200);
  6. begin
  7.    execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
  8.      USING IN l_tblnam, out l_cnt, IN out l_status;
  9.  
  10.    IF l_status != 'OK' then
  11.       dbms_output.put_line('error');
  12.    end IF;
  13. end;

5. 灏嗚繑鍥炲间紶閫掑埌PL/SQL璁板綍绫诲瀷;鍚屾牱涔熷彲鐢%rowtype鍙橀噺

  1. declare
  2.    type empdtlrec IS record (empno   number(4),
  3.                             ename   varchar2(20),
  4.                             deptno   number(2));
  5.    empdtl empdtlrec;
  6. begin
  7.    execute immediate 'select empno, ename, deptno ' ||
  8.                     'from emp where empno = 7934'
  9.      INTO empdtl;
  10. end;

6. 浼犻掑苟妫绱㈠.INTO瀛愬彞鐢ㄥ湪USING瀛愬彞鍓

  1. declare
  2.    l_dept     pls_integer := 20;
  3.    l_nam      varchar2(20);
  4.    l_loc      varchar2(20);
  5. begin
  6.    execute immediate 'select dname, loc from dept where deptno = :1'
  7.      INTO l_nam, l_loc
  8.      USING l_dept ;
  9. end;

7. 澶氳鏌ヨ閫夐」.瀵规閫夐」鐢╥nsert璇彞濉厖涓存椂琛紝
鐢ㄤ复鏃惰〃杩涜杩涗竴姝ョ殑澶勭悊,涔熷彲浠ョ敤REF cursors绾犳姝ょ己鎲.

  1. declare
  2.    l_sal    pls_integer := 2000;
  3. begin
  4.    execute immediate 'insert into temp(empno, ename) ' ||
  5.                     '           select empno, ename from emp ' ||
  6.                     '           where   sal > :1'
  7.      USING l_sal;
  8.    commit;
  9. end;

瀵逛簬澶勭悊鍔ㄦ佽鍙,EXECUTE IMMEDIATE 姣斾互鍓嶅彲鑳界敤鍒扮殑鏇村鏄撳苟涓旀洿楂樻晥.
褰撴剰鍥炬墽琛屽姩鎬佽鍙ユ椂锛岄傚綋鍦板鐞嗗紓甯告洿鍔犻噸瑕.搴旇鍏虫敞浜庢崟鑾锋墍鏈夊彲鑳界殑寮傚父.

Tags : , , ,

Filed under : 鏁版嵁搴 | Trackback URI | Comments RSS