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;
這是一款數據庫的存儲過程實例教程
