游标储存过程

create or replace procedure delete_SC_CHARGE_DETAIL(nowtime in number,overtime in number,updatenum out number) is
　　bill_no varchar2(50); -- 计费主单编号
　　countnum number(20); --累加
　　CURSOR C_EMP IS --声明显式游标
　　SELECT *
　　FROM SC_SALE_ORDER o
　　where o.status = 0
　　and o.deleteflag = 0
　　and o.createdate is not null;
　　C_ROW C_EMP%ROWTYPE; --定义游标变量，该变量的类型为基于游标C_EMP的记录

begin
　　countnum := 0;
--For 循环
　　FOR C_ROW IN C_EMP LOOP 

    if (nowtime - C_ROW.CREATEDATE) > overtime then
　　select nvl(max(info.bill_no), 0)
　　INTO bill_no
　　from CONSIGN_BILL_INFO info
　　where info.consign_no = C_ROW.Tid;

　　if bill_no != 0 then
　　　　update CONSIGN_BILL_INFO info
　　　　set info.deleteflag = -1
　　　　where info.bill_no = bill_no;

　　　　update SC_CHARGE_DETAIL d
　　set d.deleteflag = -1
　　where d.orderid = bill_no;
　　countnum := countnum + 1;
　　　　end if;
　　end if;
　　END LOOP;
　　updatenum := countnum;

end delete_SC_CHARGE_DETAIL;

转载于:https://www.cnblogs.com/bug1024/p/9083497.html