一个Informix数据库存储过程的实例:
注意：foreach后跟的select 语句不需要结束符“;”
create procedure p_95500_cxxqy_v5(dat date)
define ls_policyno char(15);
define ls_polist char(1);
define ls_classcode char(8);
define ls_apid char(18);
define ls_apname varchar(60,10);
define ls_apsex char(1);
define ls_pid char(18);
define ls_pname varchar(60,10);
define ls_recaddr varchar(60,30);
define ls_rectele char(14);
define ls_recaddr_rc varchar(60,30);
define ls_rectele_rc char(14);
define ls_ctele char(14);
define ls_ftele char(14);
define ls_mtele char(14);
define ls_appf char(1);
define ls_begdate date;
define ls_paydate date;
define ls_dbdate date;
define ls_empno char(9);
define ls_fgscode varchar(18); --负责分公司
define ls_governid varchar(18); --负责机构
define ls_ichannelcode CHAR(2); --渠道
define ls_ipaytype CHAR(2); --缴费方式
define ls_empname VARCHAR(20); --业务员姓名
define ls_emptele CHAR (20); --业务员电话
define ls_fpayamount decimal (16,2); --缴费金额
define ls_classname VARCHAR(125); --险种名称
delete from p95500_cxxqy where statdate=dat;
--取分公司代码
select fgsno into ls_fgscode from fgsno_sjt;
--遍历riskcon,抽取符合条件保单
set isolation to dirty read;
select classcode from riskclass where timestr='1' into temp risk_time_tmp with no log;
select classcode from risklist where ( (( a4 ='01') and (a1='01')) or ((a4='04') and (a1='01'))) into temp risk_type_tmp with no log;
select policyno from effective where passyn=1 and opdate=dat into temp policyno_tmp;
select policyno,polist,begdate,classcode,dbdate,apid,pid,empno,appf,recaddr,rectele,paycode policyno_tmp)
and classcode in (select classcode from risk_time_tmp) and classcode in
(select classcode from risk_type_tmp) and polist in ('2','D','E') and appf='1'
into temp cxxqy_tmp with no log;
foreach
select policyno,polist,begdate,classcode,dbdate,apid,pid ,empno,appf,recaddr,rectele,paycode
into ls_policyno,ls_polist,ls_begdate,ls_classcode,ls_dbdate,ls_apid,ls_pid,ls_empno,ls_appf,
ls_recaddr_rc,ls_rectele_rc,ls_ipaytype
from cxxqy_tmp where policyno not in (select policyno from grplist)
if ls_policyno is null or ls_policyno = '' then
continue foreach;
end if
--投保人处理
execute procedure p_95500_gvid(ls_empno) into ls_governid;
--插入回访清单表
if ls_governid is null then
let
ls_governid ='';
end if
--渠道
let ls_ichannelcode='';
foreach select xsqd into ls_ichannelcode from empno_xsqd where empno=ls_empno
exit foreach;
end foreach;
--业务员姓名
let ls_empname='';
foreach select name into ls_empname from empno where empno=ls_empno
exit foreach;
end foreach;
--业务员电话
let ls_emptele='';
foreach select link_tele1 into ls_emptele from empbrief where id in
(select distinct id from empno WHERE empno =ls_empno)
exit foreach;
end foreach;
--缴费金额
let ls_fpayamount='';
--foreach
--险种名称
let ls_classname='';
foreach select classname into ls_classname from risklist where classcode=ls_classcode
exit foreach;
end foreach;
let ls_apname='';
let ls_apsex='';
let ls_ctele='';
let ls_ftele='';
foreach select name,sex,ctele,ftele into ls_apname,ls_apsex,ls_ctele,ls_ftele from custmatl where id = ls_apid
exit foreach;
end foreach;
if ls_apname is null or ls_apname='' then
let ls_apname='';
end if
--被保人处理
let ls_pname ='';
foreach select name into ls_pname from custmatl where id = ls_pid
exit foreach;
end foreach;
let ls_mtele='';
let ls_rectele='';
let ls_recaddr='';
foreach select mobile,rectele,recaddr into ls_mtele,ls_rectele,ls_recaddr from custaddi where id=ls_apid
exit foreach;
end foreach;
if ls_recaddr is null or ls_recaddr ='' then
let ls_recaddr = ls_recaddr_rc;
end if
if ls_recaddr is null then
let ls_recaddr ='';
end if
if ls_rectele is null or ls_rectele ='' then
let ls_rectele = ls_rectele_rc;
end if
if ls_rectele is null then
let ls_rectele ='';
end if
insert into p95500_cxxqy(fgscode,governid,policyno,classcode,polist,appf,begdate,paydate,apid,apname,apsex,pid,pname,
recaddr,rectele,ctele,ftele,mtele,empno,statdate,ichannelcode,
ipaytype, empname, emptele, fpayamount,classname)
values(ls_fgscode,ls_governid,ls_policyno,ls_classcode,
ls_polist,ls_appf,ls_begdate,dat,ls_apid,ls_apname,ls_apsex,
ls_pid,ls_pname,ls_recaddr,ls_rectele,ls_ctele,ls_ftele,ls_mtele,ls_empno,dat,
ls_ichannelcode,ls_ipaytype, ls_empname, ls_emptele, ls_fpayamount,ls_classname);
end foreach;
--删除临时表
drop table policyno_tmp;
drop table cxxqy_tmp;
drop table risk_time_tmp;
drop table risk_type_tmp;
end procedure;
