SQL Stored Procedure Instance tutorial

Create or replace PROCEDURE cmpp_submit_remove_procedure
Is
Dendtime date; --Each processing is a point in time

--Get the data ID to process
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; --Counter
Begin

--dendtime: = Trunc ((sysdate-1/288), ' mi '); --Get the processing time, process the data 5 minutes ago
Dendtime: = trunc (sysdate, ' mi '); --Get processing time point

Icount: = 0; --Initialization count

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 * Cmpp_submit where id=iid;
Delete from Cmpp_submit where id=iid;

if (icount=1000) then--submit once per 1000 article
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;

This is a database of stored procedure Examples tutorial