Oracle的存储过程基础
create or replace procedure p_im_zjgl_yhzhzjgl_cx(v_ztbh decimal, --账套编号
v_sszh varchar,--所属总行
v_zhlx varchar,--账户类型
v_ywqd varchar,--业务渠道
v_sfhz varchar,--是否汇总 01 汇总02 不汇总
v_userid int,--操作员id
v_msg in out varchar,--返回信息
v_ret in out smallint--返回值，0查询成功 99查询失败
)
as
v_errorcode integer;
v_errormsg varchar(200);
v_zhlxbm varchar(6);
v_yhzh varchar(30);
v_yhzhs varchar2(32767):='';
v_jgbm varchar(50);
v_zxjgbm varchar(50);
v_zhlx1 varchar(200);
v_sszh1 varchar(200);
--v_nrets smallint;
cursor tmp_cur is select bm from bm_im_zhlx whereinstr(v_zhlx1,bm)0 and substr(bm,3,2)='01' ;
cursor tmp_cur1 is selectyhzhhm,jgbm from im_zhsz whereinstr(v_sszh1,sszh)0andinstr(v_zhlx1,zhlx)0 and zhzt = '01'
and jgbm like v_zxjgbm||'%' and (exists(select p015 from hr_yg_sjqx_yh where p015=ssyh and userid=v_userid and qxlbbm='03')
or ssyh like (select nvl(min(p015),'%') from hr_yg_sjqx_yh where userid=v_userid and qxlbbm='03')) and ztbh = v_ztbh and exists (select column_value from table(f_hfsc_get_user_cwhsjgbm(v_ztbh,v_userid)) where jgbm like column_value||'%') ;
cursor tmp_cur2 is select bm from bm_im_zhlx wherebmlike v_zhlx and substr(bm,3,2)='01';
cursor tmp_cur3 is selectyhzhhm,jgbm from im_zhsz wheresszh like v_sszhandinstr(v_zhlx1,zhlx)0 and zhzt = '01'
and jgbmlike v_zxjgbm||'%' and (exists(select p015 from hr_yg_sjqx_yh where p015=ssyh and userid=v_userid and qxlbbm='03')
or ssyh like (select nvl(min(p015),'%') from hr_yg_sjqx_yh where userid=v_userid and qxlbbm='03')) and ztbh = v_ztbh and exists (select column_value from table(f_hfsc_get_user_cwhsjgbm(v_ztbh,v_userid)) where jgbm like column_value||'%') ;
begin
delete TMP_IM_ZJZHGL;
if trim(v_zhlx)='%' then
selectlistagg(bm,',')within group(order by bm) bm into v_zhlx1 from bm_im_zhlx wherelength(bm)=6 and substr(bm,3,2)='01' ;
else
v_zhlx1:=v_zhlx;
end if;
if v_sfhz = '01' then
if trim(v_sszh)='%' then
selectlistagg(bm,',')within group(order by bm) bm into v_sszh1 from bm_sszh;
else
v_sszh1:=v_sszh;
end if;
end if;
v_zxjgbm:=f_hfsc_get_user_zxjgbm(v_userid);
ifv_sfhz = '01' then
open tmp_cur1;
loop
fetch tmp_cur1 into v_yhzh,v_jgbm;
exit when tmp_cur1%notfound;
v_yhzhs:=v_yhzhs||v_yhzh||',';
insert intoTMP_IM_ZJZHGL(id,sszh,sszhbm,ssyh,ssyhbm,yhzh,zhlx,zhlxbm,khyhmc,zhhm,zhhqje,zhdqje,wwcdbbs,gxsj)
select f_newid,nvl(b.mc,' ')sszhmc,a.sszh,c.mc ssyhmc,a.ssyh,a.yhzhhm,d.mc zhlxmc,a.zhlx,a.yhmc,a.yhzhmc,f_hfsc_zhzj_ye_im(v_jgbm,'05',v_yhzh,0),nvl(f_hfsc_zhzj_ye_im(v_jgbm,'05',v_yhzh,1),0),
(select count(*) from im_syxxb m where a.yhzhhm=m.yhzh and m.zt = 0 ), (select nvl(max(jyczsj),null)fromim_tx_zhyewhere zh = v_yhzh) from im_zhsz a
left join bm_sszh b on a.sszh=b.bm
left join bm_yhbm c on c.bm = a.ssyh and c.jgbm like v_zxjgbm||'%'
left join bm_im_zhlx d on a.zhlx = d.bm wherea.yhzhhm = v_yhzh and a.jgbm = v_jgbm and a.ztbh = v_ztbh;
end loop;
close tmp_cur1;
else
open tmp_cur3;
loop
fetch tmp_cur3 into v_yhzh,v_jgbm;
exit when tmp_cur3%notfound;
v_yhzhs:=v_yhzhs||v_yhzh||',';
insert intoTMP_IM_ZJZHGL(id,sszh,sszhbm,ssyh,ssyhbm,yhzh,zhlx,zhlxbm,khyhmc,zhhm,zhhqje,zhdqje,wwcdbbs,gxsj)
select f_newid,nvl(b.mc,' ') sszhmc,a.sszh,c.mc ssyhmc,a.ssyh,a.yhzhhm,d.mc zhlxmc,a.zhlx,a.yhmc,a.yhzhmc,f_hfsc_zhzj_ye_im(v_jgbm,'05',v_yhzh,0),nvl(f_hfsc_zhzj_ye_im(v_jgbm,'05',v_yhzh,1),0),
(select count(*) from im_syxxb m where a.yhzhhm=m.yhzh and m.zt = 0 ), (select nvl(max(jyczsj),null)fromim_tx_zhyewhere zh = v_yhzh) from im_zhsz a
left join bm_sszh b on a.sszh=b.bm
left join bm_yhbm c on c.bm = a.ssyh and c.jgbm like v_zxjgbm||'%'
left join bm_im_zhlx d on a.zhlx = d.bm wherea.yhzhhm = v_yhzh and a.jgbm = v_jgbm and a.ztbh = v_ztbh;
end loop;
close tmp_cur3;
end if;
if v_sfhz = '01' then
insert intoTMP_IM_ZJZHGL(id,sszh,sszhbm,ssyh,ssyhbm,yhzh,zhlx,zhlxbm,khyhmc,zhhm,zhhqje,zhdqje,wwcdbbs)
select f_newid,max(sszh)||'合计',max(sszhbm),' ',' ',' ',' ',' ',' ',' ',sum(zhhqje),sum(zhdqje),sum(wwcdbbs) from TMP_IM_ZJZHGL
group by sszhbm order by sszhbm;
end if;
p_sys_optlog_add(v_zxjgbm,' ',' ',' ','06','99','061026',' ','银行账户查询',0,0,v_userid,v_ywqd,v_msg);
commit;
v_msg:=v_yhzhs;
v_ret:=0;
exception
when others then
v_errorcode:=sqlcode;
v_errormsg :=sqlerrm;
rollback;
insert into t_wa_sys_log_err(err_date,name_proc,err_code,err_msg)
values(sysdate,'p_im_zjgl_yhzhzjgl_cx',v_errorcode,v_errormsg);
v_msg:='查询失败';
v_ret:=99;
commit;
end;
页:
[1]