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

講解一個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;
󰈣󰈤
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
王朝網路微信公眾號
微信掃碼關註本站公眾號 wangchaonetcn
 
  免責聲明:本文僅代表作者個人觀點,與王朝網絡無關。王朝網絡登載此文出於傳遞更多信息之目的,並不意味著贊同其觀點或證實其描述,其原創性以及文中陳述文字和內容未經本站證實,對本文以及其中全部或者部分內容、文字的真實性、完整性、及時性本站不作任何保證或承諾,請讀者僅作參考,並請自行核實相關內容。
 
© 2005- 王朝網路 版權所有