返回自定义游标的存储过程
create or replace procedure proc_xm_zjdw(
? xzqhid in varchar2,????? --行政区划id
? queryYear in varchar2,??? --查询年份
? resultzj out? FPJC_TYPE.MY_CURSOR? --输出的明细结果表
) is
?? xzqh_length INTEGER := length(xzqhid);
begin
--资金到位监察
? open resultzj for
? select
? xzqhttt.xzqh_code as? xzqh_id,????? --行政区划id
? xzqhttt.xzqh_name as xzqh_name,???? --行政区划名称
? nvl(jjsqtj.xms,0) as xms,?????????? --项目数量
? nvl(zjfpfa.xbtotal,0) as xbtotal??? --下拨资金
?from t_fp_yw_xzqh xzqhttt
?left join
?(
? select xzqh.xzqh_id as id,count(par.xmsb_id) as xms
? from t_fp_yw_xzqh xzqh?
? join(
???? select sb.xmsb_id,p.parentids from t_fp_yw_xzqh q
???? join t_fp_yw_xmsb sb on sb.xzqh_id=q.xzqh_id
???? join t_sys_xzqh_parents p on p.xzqh=q.xzqh_code
???? where to_char(sb.sb_time,'yyyy')=queryYear
?? ) par on instr(par.parentids,xzqh.xzqh_code)>0
? group by xzqh.xzqh_code,xzqh.xzqh_id
?)jjsqtj on jjsqtj.id=xzqhttt.xzqh_id
left join(
? select fa.xzqh_id1 as id,sum(fa.xb_total) as xbtotal
? from t_fp_zj_fpfa fa where fa.fp_year=queryYear
? group by fa.xzqh_id1
)zjfpfa
on zjfpfa.id=xzqhttt.xzqh_id
where xzqhttt.xzqh_code=xzqhid
or xzqhttt.first_id=xzqhid
order by xzqhttt.xzjb_id,xzqhttt.xzqh_number ;
end proc_xm_zjdw;
