pb调用oracle中的存储过程

来源：互联网发布：腾讯视频 mac 旧版本编辑：程序博客网时间：2024/05/18 09:00

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、在适当的地方调用这个函数即可。