Oracle 建立預存程序

create or replace procedure DongjieToTable                             --建立一個名為donjietotable的預存程序
as                                                                                                        --定義資料區
  cursor c_qydjsx                                                                              --定義一個遊標
    is select nbxh from aqydjsx;
  cursor c_sttzqk
    is select tznbxh from asttzqk;

  V_nbxh varchar2(40);                                                                  --定義一個變數
  V_tznbxh varchar2(40);
 
begin                                                                                                --代碼塊開始區
     open c_qydjsx();                                                                       --開啟一個遊標
     fetch c_qydjsx into V_nbxh;   
     while c_qydjsx%found loop
       update aqydjsx set djlx = '01'
       where '1' in
       (select sfjd from qydongjie where qynbxh=V_nbxh and djjdjzsj = (select max(djjdjzsj) from qydongjie where qynbxh=V_nbxh))
       and '0' not in
       (select sfjd from qydongjie where qynbxh=V_nbxh and djjdjzsj = (select max(djjdjzsj) from qydongjie where qynbxh=V_nbxh))
       and nbxh = V_nbxh;         
     fetch c_qydjsx into V_nbxh;
     end loop;
     commit;
     open c_sttzqk();
     fetch c_sttzqk into V_tznbxh;   
     while c_sttzqk%found loop
       update asttzqk set djlx = '01'
       where '1' in
       (select sfjd from qydongjie where qynbxh=V_tznbxh and djjdjzsj = (select max(djjdjzsj) from qydongjie where qynbxh=V_tznbxh))
       and '0' not in
       (select sfjd from qydongjie where qynbxh=V_tznbxh and djjdjzsj = (select max(djjdjzsj) from qydongjie where qynbxh=V_tznbxh))
       and tznbxh = V_tznbxh;          
     fetch c_sttzqk into V_tznbxh;
     end loop;
     commit;
  EXCEPTION                                                                           --異常塊,當以上代碼執行有異常時將執行這裡
  WHEN others THEN
    rollback;
end;                                                                                            --結束代碼塊

 要執行建立好的預存程序在Command中exec dongjietotable;

 

如果在代碼中要根據一個動態值去查一個表,那麼可以用有參遊標

建立方法

cursor c_qynjjbqk(EpNbxh varchar2)                                         --變數名為EpNbxh,類型為varchar2
   is select max(nd) from qynjjbqk where nbxh=EpNbxh;

調用方法

open c_qynjjbqk(nbxh);                                                               --"()"內為要傳入的參數值