oracle 游标错误处理

create or replace
procedure costmonthlymony(acid IN VARCHAR2 )
as
  costdate varchar(20);
  cid varchar(20);
  costmoney varchar(20);
  cursor cur_1 is select A_C_ID,a_m_amount from zj_c_account_info;
  costmonth varchar(20);
  sqlstr varchar(200);
  costcount varchar(10);
  earlymoney number(18,0);
  oldmonthlymoney number(18,0);
  czjnumber varchar(20);
  czjid varchar(20);
  type cur_type is ref cursor; 
  cur cur_type;
  selectstr varchar(200);
  monthlymoney number(18,0);
  resultflag number(18,0);
begin

---判断执行时间 

select  to_char(last_day(sysdate),'yyyy-MM-DD') into costdate from dual;
costdate:=concat(costdate,'-23'); --每月最后一天23：00执行

if to_char(sysdate,'MM-DD-hh24')=costdate then  --如果当前时间为每月的最后一天的23：00
begin 
  select  to_char(ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1),'yyyy-MM') into costmonth from dual; --如果执行时间是月底 那么扣除月租的月份为下月月初
end;

---判断执行时间 

--如果传过来的cid不为空  那么查找这一个客户下扣费的 情况，并扣费
 if acid is not null then
 begin
     select a_m_amount into earlymoney  from zj_c_account_info  where a_c_id=acid;
  
     cid:=acid;
     sqlstr:=' select count(*) from sys_MonthlymoneyLog where M_c_id='''||cid||''' and substr(M_costDate,5,2)='''||substr(costmonth,5,2)||'''';
      execute immediate sqlstr into costcount;
      if costcount='0' then
       begin
          monthlymoney:=0;
           selectstr:='select Z_ZJ_NUMBER,z_zj_id  from zj_c_zj_info where z_c_id ='''||cid||'''';
           open cur for selectstr;
            fetch cur into czjnumber,czjid;
            while cur%found
            loop
              pro_yz(2,czjnumber,0,'',cid,costmoney,resultflag);
              monthlymoney:=monthlymoney+costmoney;
             insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.Nextval,cid,czjid,'0','0',costmoney,to_char(sysdate,'yyyy-mm-DD-hh24:mi:ss'),'存储过程','系统',costmonth,'','2');
              fetch cur into czjnumber,czjid; 
              EXIT WHEN cur%NOTFOUND;
            end loop;
         update zj_c_account_info set a_m_amount=a_m_amount-monthlymoney where a_c_id=cid;  --跟新客户月租 金额
         insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.nextval,cid,'0',earlymoney,earlymoney-monthlymoney,monthlymoney,to_char(sysdate,'yyyy-MM'),'存储过程','系统',costmonth,'','1');
        insert into zj_logg_monthly_info values(S_S_LOGG_MONTHLY.Nextval,'客户',cid,'每月扣费','',0,0,'','','存储过程',to_char(sysdate,'yyyy-mm-DD-hh24:mi:ss'),'每月扣除客户月租',earlymoney,earlymoney-monthlymoney,costmonth); --月租账户金额
       end;
         commit;
      end if;
      select a_m_amount into oldmonthlymoney from zj_c_account_info where a_c_id=cid;
       ---如果月租小于零 了 那么就要把客户的状态置为欠费冻结
         if oldmonthlymoney<0 then 
           begin 
            update zj_c_account_info set a_account_state='3' where a_c_id=cid; --如果客户的月租小于0 把状态置为欠费冻结
           end;
         end if;
      exception 
      when others then 
      rollback;
 end;
 else --如果传过来的cid为空  查询所有客户
    if cur_1%isopen = false then
   begin
    open cur_1;
     fetch cur_1 into cid,earlymoney;
   while cur_1%found
   loop
   sqlstr:=' select count(*) from sys_MonthlymoneyLog where M_c_id='''||cid||''' and substr(M_costDate,5,2)='''||substr(costmonth,5,2)||'''';
      execute immediate sqlstr into costcount;
      if costcount='0' then --没有扣除 要扣月份的记录
       begin
       monthlymoney:=0;
           selectstr:='select Z_ZJ_NUMBER,z_zj_id  from zj_c_zj_info where z_c_id ='''||cid||'''';
           open cur for selectstr;
            fetch cur into czjnumber,czjid;
             while cur%found
             loop
               if czjnumber is not null then 
                pro_yz(2,czjnumber,0,'',cid,costmoney,resultflag);
                  --扣除
                  monthlymoney:=monthlymoney+costmoney;
                  --写日志
                  insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.Nextval,cid,czjid,'0','0',costmoney,to_char(sysdate,'yyyy-MM'),'存储过程','系统',costmonth,'','2');
               end if;
                  ---循环计算 总机的月租 并写日志
                 fetch cur into czjnumber,czjid;
                 EXIT WHEN cur%NOTFOUND;
             end loop;
       end;
         update zj_c_account_info set a_m_amount=a_m_amount-monthlymoney where a_c_id=cid;  --跟新客户月租 金额
         insert into sys_MonthlymoneyLog values (S_S_MONTHLYLOG.nextval,cid,czjid,earlymoney,earlymoney-monthlymoney,monthlymoney,to_char(sysdate,'yyyy-MM'),'存储过程/正常执行','系统',monthlymoney,'','1');
         insert into zj_logg_monthly_info values(S_S_LOGG_MONTHLY.Nextval,'客户',cid,'每月扣费','',0,0,'','','存储过程',to_char(sysdate,'yyyy-mm-DD-hh24:mi:ss'),'每月扣除客户月租',earlymoney,earlymoney-monthlymoney,costmonth); --月租账户金额
      end if;
      commit;
       select a_m_amount into oldmonthlymoney from zj_c_account_info where a_c_id=cid;
       ---如果月租小于零 了 那么就要把客户的状态置为欠费冻结
       if oldmonthlymoney<0 then 
         begin 
          update zj_c_account_info set a_account_state='3' where a_c_id=cid; --如果客户的月租小于0 把状态置为欠费冻结
         end;
       end if;
       
      fetch cur_1 into cid,earlymoney;
      EXIT WHEN cur_1%NOTFOUND;
   end loop;
   close cur_1;
    exception
          when others  then 
          rollback;
   end;
  end if;
 end if;

end if;
end;