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;