1、现在oracle中创建一个存储过程：create or replace procedure proc_jbylbxqk(as_ny in string) is ll_tczz number(12); ll_tctx number(12); ll_dbzz number(12); ll_dbtx number(12); ll_zzxj number(12); ll_txxj number(12); ll_zj number(12); ll_lhjy number(12); ll_num number(12); begin select nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(PERS_TYPE,'1',ROW_PERS,0),0)),0), nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(PERS_TYPE,'2',ROW_PERS,0),0)),0), 0, 0, nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(PERS_TYPE,'1',ROW_PERS,0),0)),0) + 0, nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(PERS_TYPE,'2',ROW_PERS,0),0)),0) + 0, nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(PERS_TYPE,'1',ROW_PERS,0),0)),0) + nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(PERS_TYPE,'2',ROW_PERS,0),0)),0) + 0 + 0, nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(corp_type_code,40,ROW_PERS,0),0)),0) + nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(corp_type_code,50,ROW_PERS,0),0)),0) + nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(corp_type_code,60,ROW_PERS,0),0)),0) into ll_tczz,ll_tctx,ll_dbzz,ll_dbtx,ll_zzxj,ll_txxj,ll_zj,ll_lhjy from (select count(*) as ROW_PERS,B.PERS_TYPE as PERS_TYPE,A.INSR_DETAIL_CODE as INSR_DETAIL_CODE,d.corp_type_code as corp_type_code from bs_pres_insur a, bs_insured b,bs_corp_pres t,bs_corp d where a.indi_id = b.indi_id and to_char(BEGIN_DATE,'yyyy-mm') <= as_ny and INDI_JOIN_STA = '1' and t.corp_id = d.corp_id and b.indi_id = t.indi_id GROUP BY B.PERS_TYPE,A.INSR_DETAIL_CODE,d.corp_type_code); begin select count(*) into ll_num from rpt_jbylbxqk where tjny = as_ny; if ll_num = 0 then Insert into rpt_jbylbxqk (tczz,tctx,dbzz,dbtx,zzxj,txxj,zj,lhjy,tjny,tjsj) values (ll_tczz,ll_tctx,ll_dbzz,ll_dbtx,ll_zzxj,ll_txxj,ll_zj,ll_lhjy,as_ny,sysdate); else delete from rpt_jbylbxqk where tjny = as_ny; Insert into rpt_jbylbxqk (tczz,tctx,dbzz,dbtx,zzxj,txxj,zj,lhjy,tjny,tjsj) values (ll_tczz,ll_tctx,ll_dbzz,ll_dbtx,ll_zzxj,ll_txxj,ll_zj,ll_lhjy,as_ny,sysdate); end if; commit; exception when others then rollback; end; end proc_jbylbxqk; 2、在pb中声明声明一个函数wf_proc_ylbxybb，函数代码如下： DECLARE p_tj PROCEDURE FOR proc_jbylbxqk (:as_ny) USING sqlca; EXECUTE p_tj; 3、在适当的地方调用这个函数即可。
