procedure工作实战备忘

--create or replace procedure proc_card_operate is   工作模式用其替换declare
declare
      cursor vcursor_pay is select * from jn1_pay where tgflag = '1' and gencardflag ='0' and risktype<>'C' order by id;
      type vtype_pay is table of jn1_pay%rowtype index by binary_integer;
      --type vtype_amount is table of jn1_pay.amount%type; 
      vrow_card jn_card%rowtype;
      v_flag jn_card.flag%type;
      v_max_tmount jn1_pay.tmount%type;
      v_allamt jn_card.allamt%type;
      v_update_count integer := 0;
      --以上五个为升降级变量
      vrow_pay jn1_pay%rowtype;
	  vtable_pay vtype_pay;
      v_risktype jn1_pay.risktype%type;
      v_paycode jn1_pay.paycode%type; 
      v_max_amount jn1_pay.amount%type;
      v_a_amount jn1_pay.amount%type;
      v_e_amount jn1_pay.amount%type;
      v_f_amount jn1_pay.amount%type;
      v_cardno cardno.newcardno%type;
      v_cardtype jn_card.cardtype%type;
      v_date jn_card.recdate%type;
      v_xztype jn_card.xztype%type;
      v_jftype jn_card.jftype%type;
      v_check_id boolean;
      v_islast boolean;
      v_pay_count integer;
      v_count integer;
      v_s_type_count integer := 0;
      v_j_type_count integer := 0;
      c_factor constant number := 0.05;
begin 
      savepoint p_full;
	  open vcursor_pay;
		loop
          fetch vcursor_pay bulk collect into vtable_pay limit 1000;  
          if v_islast then
              v_pay_count:=1;
          else
              v_pay_count:=vtable_pay.count;
          end if;
          for i in 1..v_pay_count loop  
              savepoint p_part;
             --vrow_pay.id is null用于第一条记录,v_islast用于最后一条记录
             if( vrow_pay.id is null or v_islast or vtable_pay(i).id<>vrow_pay.id) then  
                 if(v_check_id) then
                       --缴费方式 A-年缴 B-趸缴 C-其它(不定期)
                       v_jftype := 'A';
                       if(v_a_amount>=v_e_amount and v_a_amount>=v_f_amount) then
                          v_max_amount:=v_a_amount;
                          v_xztype := 'A';  
                       elsif(v_e_amount>=v_f_amount) then
                          v_max_amount:=v_e_amount;
                          v_xztype := 'E'; 
                          v_jftype := 'C';
                       else
                          v_max_amount:=v_f_amount;
                          v_xztype := 'F'; 
                       end if;  
                       --判断是否有卡
                       select count(id) into v_count from jn_card where cardflag='1' and ompno=vrow_pay.ompno and id=vrow_pay.id;
                       select sysdate into v_date from dual;
                       if v_count=0 then
                           if(v_max_amount>=60000) then
                              if(v_max_amount>=100000) then
                                  v_cardtype:='S';
                                  v_s_type_count := v_s_type_count+1;
                              else
                                  v_cardtype:='J';
                                  v_j_type_count := v_j_type_count+1;
                              end if;
                              select replace(lpad(max(newcardno)+1,11,'0'),'4','5') into v_cardno from cardno;
                              --flag发卡标志 0-新 1-升降 2-已制卡 3-失效后不能制卡
                              --status领卡状态 Y-已领卡 N-未领卡
                              insert into jn_card(ompno,id,cardflag,grpcode,cardtype,cardno,xztype,jftype,allamt,recdate,flag,operno,operdate,empno,deptno,frecdate,status) 
										          values(vrow_pay.ompno,vrow_pay.id,'1',null,v_cardtype,v_cardno,v_xztype,v_jftype,v_max_amount,v_date,'0',null,vrow_pay.otime,vrow_pay.empno,vrow_pay.deptno,v_date,'N');
                              update jn1_pay set gencardflag='1' where id =vrow_pay.id;
                              insert into cardno (newcardno) values (v_cardno);
                           else
                               update jn1_pay set gencardflag='2',otime=sysdate where id =vrow_pay.id;
                           end if; 
                       else
                         --新标准升降级
                          if(v_max_amount>=60000) then
                              if(v_max_amount>=100000) then
                                      v_cardtype:='S';
                              else
                                      v_cardtype:='J';
                              end if;
                          else
                              v_cardtype:='D';
                          end if;
                          select * into vrow_card from jn_card where cardflag='1' and ompno=vrow_pay.ompno and id=vrow_pay.id;
                          --A、B需要结合新老两种标准
                          if(vrow_card.cardtype='B' and vrow_card.xztype<>'E') then
                              --cardtype='B'和xztype<>'E'的情况用tmount计算
                              v_allamt:=v_max_tmount;
                          else
                              v_allamt:=v_max_amount;
                          end if;
                          if((vrow_card.cardtype='A' or vrow_card.cardtype='B') and v_cardtype='D' and v_allamt>=vrow_card.allamt) then
							  	v_cardtype:=vrow_card.cardtype;
                          end if;
                          if(vrow_card.cardtype<>v_cardtype) then
                             v_flag:=1;
                             select replace(lpad(max(newcardno)+1,11,'0'),'4','5') into v_cardno from cardno;
                             insert into jn_chgcard(ompno,id,oldcardno,oldcardtype,newcardno,newcardtype,begdate,empno,deptno,operno,operdate)
	                           values (vrow_card.ompno,vrow_card.id,vrow_card.cardno,vrow_card.cardtype,v_cardno,v_cardtype,vrow_card.frecdate,vrow_card.empno,vrow_card.deptno,vrow_card.operno,v_date); 
                             insert into cardno (newcardno) values (v_cardno);
                             v_update_count:=v_update_count+1;
                             dbms_output.put_line(vrow_card.cardtype||','||vrow_card.allamt||'===>'||v_cardtype||','||v_allamt);
                          else
                             v_flag:=0;
                             v_cardno:=vrow_card.cardno;
                          end if;
                          update jn_card set cardno=v_cardno,cardtype=v_cardtype,xztype=v_xztype,jftype=v_jftype,allamt=v_allamt,operdate=v_date,flag=v_flag,recdate=v_date 
                          where cardflag='1' and ompno=vrow_card.ompno and id=vrow_card.id;
                          update jn1_pay set gencardflag='1' where id =vrow_card.id;
                       end if;               
                  end if;
                  --退出当前循环
                  exit when v_islast;
                  select count(id) into v_count from jn1_custmatl where id not in 
                  (select id from jn_dummy) and id=vtable_pay(i).id;
                  if v_count=0 then
                     v_check_id := false;
                     update jn1_pay set gencardflag='2',otime=sysdate where id =vtable_pay(i).id; 
                  else  
                     v_check_id := true;
                     v_a_amount:=0;
                     v_e_amount:=0;
                     v_f_amount:=0; 
                     v_max_tmount:=0;
                  end if;  
              end if;
              if(v_check_id) then
                v_risktype := vtable_pay(i).risktype;
                v_paycode := vtable_pay(i).paycode;
                case 
                   when v_risktype='A'then
                      if(v_paycode='01') then
                        v_a_amount := v_a_amount+(vtable_pay(i).amount*c_factor*2);
                      elsif(v_paycode='13') then
                        v_a_amount := v_a_amount+(vtable_pay(i).amount*c_factor*2*10);
                      end if;
                   when v_risktype='E'then
                      v_e_amount := v_e_amount+(vtable_pay(i).amount*c_factor);  
                   when v_risktype='F'then
                      if(v_paycode='01') then
                        v_f_amount := v_f_amount+(vtable_pay(i).amount*c_factor);
                      elsif(v_paycode='13') then
                        v_f_amount := v_f_amount+(vtable_pay(i).amount*c_factor*10);
                      end if;
                   else null;
                end case;
                --老银卡需要计算tmount，E类账户险种除外
                if(vtable_pay(i).tmount>v_max_tmount) then
                    v_max_tmount := vtable_pay(i).tmount;
                end if;  
                vrow_pay:=vtable_pay(i);
              end if;
          end loop;
          --exit when vcursor_pay%notfound;
          --在最后一条记录后面再循环一次
          exit when v_islast;
          if vcursor_pay%notfound then 
              v_islast := true; 
          end if;
			end loop;
			close vcursor_pay; 
      insert into ids values('J:'||v_j_type_count||' S:'||v_s_type_count||' U:'||v_update_count,sysdate);
      exception  when others then 
         rollback to savepoint p_full; 
end;