数据库实例:讲解一个Informix数据库存储过程的实例来源: 发布时间:星期三, 2008年8月6日 浏览:397次 评论:0
一个Informix数据库存储过程的实例: 注意:foreach后跟的select 语句不需要结束符“;”
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_ipaytype CHAR(2); --缴费方式 define ls_empname VARCHAR(20); --业务员姓名 define ls_emptele CHAR (20); --业务员电话 define ls_fpayamount decimal (16,2); --缴费金额 define ls_classname VARCHAR(125); --险种名称
--取分公司代码 select fgsno into ls_fgscode from fgsno_sjt; --遍历riskcon,抽取符合条件保单
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;
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)
continue foreach; end if --投保人处理
--插入回访清单表 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_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;
let ls_recaddr = ls_recaddr_rc; end if if ls_recaddr is null then let ls_recaddr =''; end if
let ls_rectele = ls_rectele_rc; end if if ls_rectele is null then let ls_rectele =''; end if
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;
0
相关文章
读者评论
发表评论 |