v_vc_ycbz varchar2(2):= 'Y';
begin
return_code:=-99;
return_str := '[pkg_dubbo.pkgsp_sp_hsdubbo_new_cxPz]获取凭证表数据失败，请联系系统管理员';
open return_data for
/* with v_gzbdate as (
select i.l_ztbh ,i.d_ywrq
from tfundinfo t ,ttmpgzb_index i
where t.l_fundid = i.l_ztbh
and i.l_sfqr = 1
and instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0
and i.d_ywrq >= to_date(a_d_date_b,'yyyy-mm-dd')
and i.d_ywrq <= to_date(a_d_date_e,'yyyy-mm-dd')
) , */
with v_pzbpage as (
select t.*,ROW_NUMBER() OVER (order by t.vc_fundname asc , t.d_pzrq desc ,t.vc_pzh asc ) rowno
from (
select a.vc_code vc_fundcode,
a.vc_name vc_fundname,
a.vc_glr vc_glrmc,
v.d_make d_pzrq,
lpad(v.l_no, 6, 0) vc_pzh,
vs.vc_code vc_kmdm,
vs.vc_fullname vc_kmmc,
vs.VC_DIGEST vc_zy,
decode(vs.EN_DEBIT, 0, -1, 1) en_jd,
decode(vs.EN_DEBIT, 0, vs.EN_CREDIT, vs.EN_DEBIT) en_je,
vs.EN_QUANTITY en_sl,
vs.VC_JSBZ vc_currency,
vs.EN_FOREIGN EN_YBJE,
vs.en_exch EN_HL,
nvl((select nvl((select d.vc_item_name
from tdictionary d
where 10249 = l_dictionary_no
and d.c_lemma_item = z.vc_type), z.vc_sm) vc_name
from tzdyzzszz z
where z.l_ztbh = (select s.l_type from tsysinfo s where s.l_id=vs.l_fundid )
and z.l_bh = v.l_zzgsbh ),'手工凭证'
) vc_pzlb, 1 l_sjly, vs.l_fundid vc_ztbh
from tfundinfo a ,tvoucher v, tvouchers vs
where instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0
and vs.l_FundID = a.l_fundid
and (vs.vc_code like a_vc_kmdm||'%' or a_vc_kmdm is null)
and (vs.vc_fullname like '%'||a_vc_kmmc||'%' or a_vc_kmmc is null)
and (vs.VC_DIGEST like '%'||a_vc_zy||'%' or a_vc_zy is null)
and vs.d_make between a_d_date_b and a_d_date_e
and v.d_make between a_d_date_b and a_d_date_e
and v.l_fundid = vs.l_fundid
and v.l_id = vs.l_mainid
and v.L_STATE < 32
union all
select a.vc_code vc_fundcode,
a.vc_name vc_fundname,
a.vc_glr vc_glrmc,
v.d_make d_pzrq,
lpad(v.l_no, 6, 0) vc_pzh,
vs.vc_code vc_kmdm,
vs.vc_fullname vc_kmmc,
vs.VC_DIGEST vc_zy,
decode(vs.EN_DEBIT, 0, -1, 1) en_jd,
decode(vs.EN_DEBIT, 0, vs.EN_CREDIT, vs.EN_DEBIT) en_je,
vs.EN_QUANTITY en_sl,
vs.VC_JSBZ vc_currency,
vs.EN_FOREIGN EN_YBJE,
vs.en_exch EN_HL,
nvl((select nvl((select d.vc_item_name
from tdictionary d
where 10249 = l_dictionary_no
and d.c_lemma_item = z.vc_type), z.vc_sm) vc_name
from tzdyzzszz z
where z.l_ztbh = (select s.l_type from tsysinfo s where s.l_id=vs.l_fundid )
and z.l_bh = v.l_zzgsbh ),'手工凭证'
) vc_pzlb, 1 l_sjly, vs.l_fundid vc_ztbh
from tfundinfo a , t_h_voucher v ,t_h_vouchers vs
where instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0
and vs.l_FundID = a.l_fundid
and (vs.vc_code like a_vc_kmdm||'%' or a_vc_kmdm is null)
and (vs.vc_fullname like '%'||a_vc_kmmc||'%' or a_vc_kmmc is null)
and (vs.VC_DIGEST like '%'||a_vc_zy||'%' or a_vc_zy is null)
and v.l_fundid = vs.l_fundid
and vs.d_make between a_d_date_b and a_d_date_e
and v.d_make between a_d_date_b and a_d_date_e
and v.l_id = vs.l_mainid
and v.L_STATE < 32
) t
--order by t.vc_fundname desc , t.d_pzrq desc ,t.vc_pzh
)
select pp.vc_fundcode, pp.vc_fundname, pp.vc_glrmc, pp.d_pzrq, pp.vc_pzh, pp.vc_kmdm,
pp.vc_kmmc , pp.vc_zy , pp.en_jd , pp.en_je , pp.en_sl , pp.vc_currency,
pp.EN_YBJE , pp.EN_HL , pp.vc_pzlb , pp.l_sjly, pp.vc_ztbh
from v_pzbpage pp
where pp.rowno>=((a_l_pageno-1)* a_l_pagenum + 1) and pp.rowno<=a_l_pageno*a_l_pagenum ;
-- select count(1) into return_recnum from v_pzbpage ;
select count(*) into return_totalcount
from (
select a.vc_code vc_fundcode,
a.vc_name vc_fundname,
a.vc_glr vc_glrmc,
v.d_make d_pzrq,
lpad(v.l_no, 6, 0) vc_pzh,
vs.vc_code vc_kmdm,
vs.vc_fullname vc_kmmc,
vs.VC_DIGEST vc_zy,
decode(vs.EN_DEBIT, 0, -1, 1) en_jd,
decode(vs.EN_DEBIT, 0, vs.EN_CREDIT, vs.EN_DEBIT) en_je,
vs.EN_QUANTITY en_sl,
vs.VC_JSBZ vc_currency,
vs.EN_FOREIGN EN_YBJE,
vs.en_exch EN_HL,
nvl((select nvl((select d.vc_item_name
from tdictionary d
where 10249 = l_dictionary_no
and d.c_lemma_item = z.vc_type), z.vc_sm) vc_name
from tzdyzzszz z
where z.l_ztbh = (select s.l_type from tsysinfo s where s.l_id=vs.l_fundid )
and z.l_bh = v.l_zzgsbh ),'手工凭证'
) vc_pzlb, 1 l_sjly, vs.l_fundid vc_ztbh
from tfundinfo a ,tvoucher v, tvouchers vs
where instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0
and vs.l_FundID = a.l_fundid
and (vs.vc_code like a_vc_kmdm||'%' or a_vc_kmdm is null)
and (vs.vc_fullname like '%'||a_vc_kmmc||'%' or a_vc_kmmc is null)
and (vs.VC_DIGEST like '%'||a_vc_zy||'%' or a_vc_zy is null)
and v.l_fundid = vs.l_fundid
and vs.d_make between a_d_date_b and a_d_date_e
and v.d_make between a_d_date_b and a_d_date_e
and v.l_id = vs.l_mainid
and v.L_STATE < 32
union all
select a.vc_code vc_fundcode,
a.vc_name vc_fundname,
a.vc_glr vc_glrmc,
v.d_make d_pzrq,
lpad(v.l_no, 6, 0) vc_pzh,
vs.vc_code vc_kmdm,
vs.vc_fullname vc_kmmc,
vs.VC_DIGEST vc_zy,
decode(vs.EN_DEBIT, 0, -1, 1) en_jd,
decode(vs.EN_DEBIT, 0, vs.EN_CREDIT, vs.EN_DEBIT) en_je,
vs.EN_QUANTITY en_sl,
vs.VC_JSBZ vc_currency,
vs.EN_FOREIGN EN_YBJE,
vs.en_exch EN_HL,
nvl((select nvl((select d.vc_item_name
from tdictionary d
where 10249 = l_dictionary_no
and d.c_lemma_item = z.vc_type), z.vc_sm) vc_name
from tzdyzzszz z
where z.l_ztbh = (select s.l_type from tsysinfo s where s.l_id=vs.l_fundid )
and z.l_bh = v.l_zzgsbh ),'手工凭证'
) vc_pzlb, 1 l_sjly, vs.l_fundid vc_ztbh
from tfundinfo a , t_h_voucher v ,t_h_vouchers vs
where instr(',' || a_vc_cpdm || ',', ',' || a.vc_code || ',') > 0
and vs.l_FundID = a.l_fundid
and (vs.vc_code like a_vc_kmdm||'%' or a_vc_kmdm is null)
and (vs.vc_fullname like '%'||a_vc_kmmc||'%' or a_vc_kmmc is null)
and (vs.VC_DIGEST like '%'||a_vc_zy||'%' or a_vc_zy is null)
and v.l_fundid = vs.l_fundid
and vs.d_make between a_d_date_b and a_d_date_e
and v.d_make between a_d_date_b and a_d_date_e
and v.l_id = vs.l_mainid
and v.L_STATE < 32
) t ;
return_pages :=floor((return_totalcount + a_l_pagenum -1)/ a_l_pagenum ) ;
v_vc_ycbz := 'N';
return_code := 0;
return_str := '成功执行';
exception when others then
--系统自动异常捕捉
if v_vc_ycbz = 'N' then
return_code:=-1;
return_str := '[pkg_dubbo.pkgsp_sp_hsdubbo_new_cxPz]异常错误：'||chr(13)||sqlerrm;
end if;
--人为考虑系统异常
if v_vc_ycbz = 'Y' then
return_str := return_str||chr(13)||sqlerrm;
end if;
--人为的制造了异常
if v_vc_ycbz = 'H' then
return_str := return_str;
end if;
end pkgsp_sp_hsdubbo_new_cxPzbPage;返回的return_data里面结构是什么
本文详细介绍了一种复杂的SQL查询技巧，包括使用row_number()进行结果集分区排序、DECODE函数进行条件判断以及instr函数进行字符串定位等高级用法，旨在帮助读者理解和掌握这些SQL技巧。
扫一扫
3503
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
