一个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;
我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。
我原创,你原创,我们的内容世界才会更加精彩!
【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
![TechTarget微信公众号二维码](/wp-content/uploads/res/images/spriteimg/ttweixin.jpg)
TechTarget
官方微博
![TechTarget中国官方微博二维码](/wp-content/uploads/res/images/spriteimg/ttweibo.jpg)
TechTarget中国
相关推荐
-
南大通用发布GBase 8t 定位高端交易型数据库
本周二,国产数据库厂商南大通用在北京国际会议中心发布了最新产品GBase 8t,目标锁定高端交易型数据库市场。
-
南大通用联手IBM 将提供基于Informix的自有数据库
IBM (NYSE: IBM)与天津南大通用数据技术股份有限公司今日宣布将在中国创建自主创新的数据库产品。
-
IBM推出全新Informix 11.7数据库软件
IBM公司今天发布了全新Informix数据库软件Informix 11.7(又称Panther)。Informix 11.7除了在性能、易用性等方面有大幅提升之外,还增强了分析功能。
-
海量数据库管理系统实施案例:合理定制
Hildebrand公司开发了自己的基于内存的前端关系型数据库,它基本上作为TimeSeries组件的一个过滤器。