一个入门级存储过程

create or replace procedure proc_autoAuditCreditAmount
as
  --声明游标 结果集A：需要核销的机构（有未核销单据）
  cursor ivan_cur is
    select customerDepaID, min(CreditAccount_ID) acBeginID, max(CreditAccount_ID) acEndID
    from Tbl_Creditaccount
    where alterationType<=0 and isPayback=0
    group by customerDepaID
    order by customerDepaID;
  --声明游标(带参数) 结果集B：某机构需要核销的单据，某机构的id来源于结果集A的循环
  cursor ivan_cur_b(
         p_depa_id Tbl_Creditaccount.Creditaccount_Id%type
         ,p_beginID Tbl_Creditaccount.Creditaccount_Id%type
         ,p_endID Tbl_Creditaccount.Creditaccount_Id%type ) 
    is
    select *
    from Tbl_Creditaccount
    where alterationType<=0 and isPayback=0 and customerDepaID=p_depa_id
    and CreditAccount_ID between p_beginID and p_endID
    order by alterationTime asc, CreditAccount_ID asc;

    
  --声明变量paybackmoney，它的类型是表Tbl_Creditaccount中字段Alterationamount的类型，初始化为0
  v_paybackNum number := 0; --某机构还款单数量
  v_paybackmoney Tbl_Creditaccount.Alterationamount%type := 0;--某机构还款单总金额
  v_minPaybackID Tbl_Creditaccount.Creditaccount_Id%type; --某机构还款单开始ID
  v_maxPaybackID Tbl_Creditaccount.Creditaccount_Id%type; --某机构还款单结束ID
  v_checkNum number := 0; --某机构已核销记录数量 
  v_lastSettleBalance Tbl_Cancelrecord.Settlebalance%type := 0; --某机构最后一次结余金额
  v_settleBalance Tbl_Cancelrecord.Settlebalance%type := 0;--某机构本次可用来还款的总金额
  v_totalAmount Tbl_Cancelrecord.totalamount%type := 0;--某机构本次的核销了的单据的总金额
  v_recordNo number := 0; --核销单编号yyyyMMddsys00001
  v_index number;
  v_depaName Tbl_Depa.Depaname%type := 0; --机构名称（确保最新）
  v_cancelrecord_id Tbl_Cancelrecord.cancelrecord_id%type := 0; --核销主表ID
  v_calculateenddatetime Tbl_Cancelrecord.calculateenddatetime%type; --核销结束时间
  --v_alterationAmount Tbl_Creditaccount.Alterationamount%type := 0;--某机构每笔借贷的金额
begin
  --1.循环核销每一个需要核销的机构
  for ivan_rec in ivan_cur loop
    begin
         savepoint sp1;
         v_recordNo := v_recordNo + 1;
         v_index := 0; --每次循环开头都要初始化，因为这是子循环的index
         select depaname into v_depaName from tbl_Depa where depa_id = ivan_rec.customerDepaID;--取机构此刻的名称
         --DBMS_OUTPUT.put_line('The customerID is '||ivan_rec.customerDepaID||', record from '||ivan_rec.acBeginID||' to '||ivan_rec.acEndID);
         
         --2.查找该机构可使用(未核销)的还款单总金额
         --（2.1）首先判断该机构是否有可还款的记录，防止Not_Data_Found Exception
         select count(ca.creditaccount_id) into v_paybackNum
         from Tbl_Creditaccount ca
         where alterationType=1 and isPayback=0 and customerDepaID=ivan_rec.customerDepaID;
         
         if v_paybackNum = 0 then
           v_paybackmoney := 0;
           v_minPaybackID := 0;
           v_maxPaybackID := 0;
         else 
           --（2.2）查找该机构可使用(未核销)的还款单总金额，放入变量paybackmoney
           select 
           sum(alterationamount) into v_paybackmoney
           from Tbl_Creditaccount ca
           where alterationType=1 and isPayback=0 and customerDepaID=ivan_rec.customerDepaID
           group by customerDepaID;
           
           select min(Creditaccount_Id) into v_minPaybackID
           from Tbl_Creditaccount ca
           where alterationType=1 and isPayback=0 and customerDepaID=ivan_rec.customerDepaID;
           
           select max(Creditaccount_Id) into v_maxPaybackID
           from Tbl_Creditaccount ca
           where alterationType=1 and isPayback=0 and customerDepaID=ivan_rec.customerDepaID;
         end if;
         --DBMS_OUTPUT.put_line('This agent has '||v_paybackmoney||' RMB to pay!');
         
         --3.在核销主表中，查找该机构最后一条核销记录，取出该记录的结余金额
         --（3.1）首先判断该机构是否有结余金额数据，防止Not_Data_Found Exception
         select count(cr.cancelrecord_id) into v_checkNum
         from Tbl_Cancelrecord cr
         where recordState=2 and agentDepaID=ivan_rec.customerDepaID;
         
         if v_checkNum = 0 then
           v_lastSettleBalance := 0;
         else
           --（3.2）查找机构上一次的结余金额
           select settleBalance into v_lastSettleBalance
           from (
             select rank() over(partition by agentDepaID order by cancelRecordDatetime desc, cancelRecord_ID desc) rn
             , cr.settleBalance
             from Tbl_Cancelrecord cr
             where recordState=2 and agentDepaID=ivan_rec.customerDepaID
           ) a
           where rn=1;
         end if;
         --DBMS_OUTPUT.put_line('The lastest settle Balance is '||v_lastSettleBalance||' RMB!');
         v_settleBalance := v_paybackmoney + v_lastSettleBalance;                 
         DBMS_OUTPUT.put_line('Begin : Agent '||ivan_rec.customerDepaID||'_'||v_depaName||' has '||v_settleBalance||' RMB to pay this time!' );
         
       
         
       --前面是准备工作，现在开始为机构核销单据了！
       
       --循环结果集B，核销该机构的订单
       for ivan_rec_b in ivan_cur_b(ivan_rec.customerDepaID, ivan_rec.acBeginID, ivan_rec.acEndID) loop
         begin 
           DBMS_OUTPUT.put_line('No.'||ivan_rec_b.billNo||', borrow money :'||ivan_rec_b.alterationAmount||', current settleBalance :'||v_settleBalance);
           if v_settleBalance < (-ivan_rec_b.alterationAmount) then --无法核销本单据(钱不够)，跳出子循环（如果是第一笔，那么v_cancelrecord_id=0）
              DBMS_OUTPUT.put_line('无法继续核销该机构，跳出子循环!' );
              exit; 
           else 
             if v_index = 0 then --有钱核销第一笔单据，那么首先往核销主表插入一条数据
               insert into tbl_cancelrecord(
                 cancelrecord_id,agentdepaid, agentdepaname
                 ,cancelrecorddatetime,cancelrecordtype--核销种类:0人工  1自动
                 ,calculatebegindatetime--,calculateenddatetime 
                 ,cancelrecordno
                 ,createpersonid,createpersonaccount,createdepaid,createdepaname,createdatetime
                 ,lastmodifydepaid,lastmodifypersonname,lastmodifypersonid,lastmodifydepaname,lastmodifydatetime
                 ,recordstate
               )
               values(
                 seq_cancelrecord.nextval, ivan_rec_b.customerdepaid, v_depaName
                 ,sysdate, 0 --核销种类:0自动 1人工 
                 ,ivan_rec_b.alterationtime
                 ,to_char(sysdate,'yyyyMMdd')||'sys'||Lpad(v_recordNo,5,0)--核销单编号yyyyMMddsys00001
                 ,1,'Admin',3,'综合业务部',sysdate --写死
                 ,1,'Admin',3,'综合业务部',sysdate --写死
                 ,2 --已核销
               );   
               select seq_cancelrecord.currval into v_cancelrecord_id from dual; --赋值到变量，记录核销主表ID
             end if;
           end if;
           --够钱核销该单据
           --将本条单据置为已核销
           update tbl_creditaccount
           set ispayback = 1, payBackType = 0
           where creditaccount_id = ivan_rec_b.creditaccount_id;
           --插入一条核销记录至核销明细表
           insert into tbl_cancelrecorddetails(
             cancelrecorddetails_id
             ,cancelrecordid
             ,agentdepaid, agentdepaname
             ,orderid, orderno
             ,ordertype
           )
           values(
             seq_cancelrecorddetails.nextval
             ,v_cancelrecord_id
             ,ivan_rec_b.customerdepaid, v_depaName
             ,ivan_rec_b.billid, ivan_rec_b.billno
             ,ivan_rec_b.billtype
           );
           v_index := v_index + 1;
           v_settleBalance := v_settleBalance + ivan_rec_b.alterationAmount; --记住是+，因为ivan_rec_b.alterationAmount带正负号
           v_totalAmount := v_totalAmount - ivan_rec_b.alterationAmount; --记住是-，因为ivan_rec_b.alterationAmount带正负号
           v_calculateenddatetime := ivan_rec_b.alterationtime;
         end;
       end loop;
       
       if v_index > 0 then --说明本机构核销了单据      
         update tbl_cancelrecord --更新核销主表某些字段
         set calculateenddatetime = v_calculateenddatetime --本次核销最后一笔单据时间
             ,totalamount = v_totalAmount--本次共核销金额
             ,settleBalance = v_settleBalance --本次核销后，结余金额
         where cancelrecord_id = v_cancelrecord_id;
         
         update Tbl_Creditaccount --将所有未核销的还款单置为已核销（已用来还款）
         set ispayback = 1, payBackType = 0
         where alterationType=1 and isPayback=0 --单据：还款单，状态：未核销(未使用)
               and customerDepaID=ivan_rec.customerDepaID 
               --一定要加修改范围，因为这途中可能增加了未核销的还款单，虽然几率很微小
               and creditaccount_id between v_minPaybackID and v_maxPaybackID;
       end if;
       
       /*
       --测试异常
       if v_recordNo = 3 then 
         v_index := v_index / 0;
       end if;
       */
       --提交一个机构的数据
       commit;
       DBMS_OUTPUT.put_line('v_recordNo ' || v_recordNo || '-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*' );
    
    exception
    when others then
        dbms_output.put_line('Error!!!! Error on agent: ' || v_depaName); 
        rollback to savepoint sp1; --回滚数据，并继续循环下一个机构
    
    end;
  end loop;
end;