informix 一个存储过程的实例

---execute procedure revisesff('PLAQ201121090000000002');


CREATE PROCEDURE sffpl3g.revisesff(in_documentno  char(22))

define  v_02pcount Integer;
define v_02f1count Integer;
define v_maxorder02no         Integer;
define v_f1flag Integer;
define v_lastorder02no Integer;
        define v_change02no             Integer;   

define v_ksdm char(8);
define v_policyno char(22);
define v_insurant varchar(120);
define v_assuredcode char(16);
define v_classescode char(6);
define v_currency char(6);
define v_documentno char(22);
define v_kindcode char(3);
define v_order02no Integer;
define v_mainamount decimal(14);
define v_foreamount decimal(14);
define v_departcode char(8);
define v_acceptdepname  char(80);
define v_mancode char(11);
define v_acceptopname char(80);
define v_agentcode char(12);
define v_refundname char(20);
define v_procflag char(1);
define v_serialno Integer;
define v_tcol1 char(30);
define v_tcol2 char(30);
define v_sendtime datetime year to second;
define v_sffid Integer;
        define v_clausetype             char(11);

select count(*) into v_02pcount from sf02p where documentno=in_documentno;
if v_02pcount > 0
    then
delete from sf02p where documentno=in_documentno;
end if;  
  
  
         select max(order02no) into v_maxorder02no from sf02 where documentno=in_documentno;

--insert into sf02p
foreach   v_sf02Cursor    for
select ksdm, policyno, insurant, assuredcode, classescode, currency,documentno, kindcode, order02no,mainamount, foreamount, departcode, acceptdeptname, mancode, acceptopname, agentcode, refundname, procflag, serialno, tcol1, tcol2, sendtime, sffid into   v_ksdm, v_policyno, v_insurant, v_assuredcode, v_classescode, v_currency, v_documentno, v_kindcode,v_order02no, v_mainamount, v_foreamount, v_departcode, v_acceptdepname, v_mancode, v_acceptopname, v_agentcode, v_refundname, v_procflag, v_serialno, v_tcol1, v_tcol2, v_sendtime, v_sffid from   sf02 where  documentno=in_documentno  order by order02no
let v_order02no=v_maxorder02no+1;  
let v_mainamount=-v_mainamount;
let v_foreamount=-v_foreamount;


if (v_kindcode ='R10' or v_kindcode='R91' or v_kindcode='R81' or v_kindcode='R82' or v_kindcode='P98' or v_kindcode='P94' or v_kindcode='P96' or v_kindcode='R18' or v_kindcode='R19' or v_kindcode='R92'or v_kindcode='R98'or v_kindcode='R96'or v_kindcode='RM0' or v_kindcode='RM1'or v_kindcode='RM2'
            or v_kindcode='RJ2'or v_kindcode='RJ3'or v_kindcode='R95'or v_kindcode='RD5'or v_kindcode='RD6'or v_kindcode='RD7'or v_kindcode='RD8'or v_kindcode='RD9'or v_kindcode='RC0'or v_kindcode='RC1'or v_kindcode='RC2'or v_kindcode='RC3'or v_kindcode='RC4'or v_kindcode='RD0'or v_kindcode='RD1'
    or v_kindcode='RD2'or v_kindcode='RD3'or v_kindcode='RD4'or v_kindcode='RJ2'or v_kindcode='RJ3'or v_kindcode='R83'or v_kindcode='R84' or (CHAR_LENGTH(v_kindcode)>=2 and substr(v_kindcode, 0, 2)='P0'))
then
let v_procflag = 'A';
else
let  v_procflag = 'N';
end if;


insert into sf02p values (v_ksdm, v_policyno, v_insurant, v_assuredcode, v_classescode, v_currency, v_documentno,v_kindcode, v_order02no, v_mainamount, v_foreamount, v_departcode, v_acceptdepname, v_mancode, v_acceptopname, v_agentcode, v_refundname, v_procflag, v_serialno,null,null, v_sendtime, '0' );

let  v_maxorder02no=v_maxorder02no+1;
end foreach;

-- insert into sf02pf
  select count(*) into v_02f1count  from  sf02f1 where documentno = in_documentno;  
  let v_f1flag ='1';
  if v_02f1count>0
     then
         foreach  v_sf02f1Cursor  for
          select ksdm, policyno, classescode, clausetype, kindcode, documentno, order02no, mainamount, foreamount,
                 tcol1, tcol2, sffid
           into  v_ksdm, v_policyno, v_classescode, v_clausetype, v_kindcode, v_documentno, v_order02no,
     v_mainamount, v_foreamount, v_tcol1, v_tcol2, v_sffid
           from  sf02f1
           where documentno=in_documentno  order by order02no
          
           if v_f1flag ='1'
then
      select max(order02no) into v_maxorder02no from sf02 where documentno=in_documentno;
      let v_lastorder02no=v_order02no;
      let v_order02no=v_maxorder02no+1; 
                                      let v_change02no=v_order02no;
           elif  v_order02no <> v_lastorder02no
then
                                       select max(order02no) into v_maxorder02no from sf02pf where documentno=in_documentno;
                                       let v_lastorder02no=v_order02no;
                                       let v_order02no=v_maxorder02no+1; 
                                       let v_change02no=v_order02no;
                            elif  v_order02no = v_lastorder02no
                                 then
                                       let v_order02no=v_change02no;
            end if;
            
let v_mainamount=-v_mainamount;
let v_foreamount=-v_foreamount;

insert into sf02pf values(v_ksdm, v_policyno, v_classescode, v_clausetype, v_kindcode,
v_documentno, v_order02no, v_mainamount, v_foreamount,null,null, '0');

        let v_f1flag = v_f1flag +1;
end foreach;
end if;
  end Procedure;