| 導購 | 订阅 | 在线投稿
分享
 
 
 

講解一個Informix數據庫存儲過程的實例

來源:互聯網  2008-08-05 07:04:51  評論

一個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;

一個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;
󰈣󰈤
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有