sql 存儲過程實例教程

create or replace PROCEDURE CMPP_SUBMIT_REMOVE_PROCEDURE
Is
  dEndTime date; -- 每次處理是時間點   -- 取得要處理的數據ID
  cursor c_cmpp_submit(endTime date) is
  select id from cmpp_submit
    where ((ih_process <> 'insert_cmpp_submit' and ih_process <> 'wait_for_response')
      or (ih_process = 'wait_for_response' and ih_retry <= 0))
      and ih_timestamp < endTime and rownum<100001 ;  iId number; -- id
 iCount number; -- 計數器
begin   --dEndTime := trunc((SYSDATE - 1/288),'mi'); -- 取得處理時間點,處理5分鐘前的數據
  dEndTime := trunc(SYSDATE,'mi'); -- 取得處理時間點   iCount := 0; -- 初始化計數   open c_cmpp_submit(dEndTime);
  loop
    fetch c_cmpp_submit into iId;
    exit when c_cmpp_submit%notfound;     iCount := iCount+1;     insert into cmpp_submit_backup select * from cmpp_submit where id=iId;
    delete from cmpp_submit where id=iId;     if ( iCount=1000 ) then -- 每1000條提交一次
    begin
      commit;
    end;
    end if;
  end loop; -- end of loop
  close c_cmpp_submit;   commit;   return;
EXCEPTION
  WHEN OTHERS THEN
  BEGIN
    rollback;
    if c_cmpp_submit%isopen then
       close c_cmpp_submit;
    end if;   EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;

end CMPP_SUBMIT_REMOVE_PROCEDURE;

這是一款數據庫的存儲過程實例教程