Oracle利用存储过程返回结果集开发报表
在Oracle开发过程中，经常会遇到各种形式的报表展示，有些报表用sql语句直接是写不出来的，需要程序与sql配合着实现，但是这样写出来的报表查询速度慢，而且不利于维护，这里给大家分享一下UncleToo经常用的报表开发方式。
思路：
利用存储过程，将需要的格式、字段以结果集的形式直接返回到页面，这样在程序里不需要关心报表需要哪些字段、如何排序等，只需要将报表条件传入写好的存储过程，将结果集展示就可以了，以后需要对报表格式、字段调整时，只需要调整Oracle存储过程，页面代码不需要更改。
此方法sql的整体结构其实很简单：
v_sql="select * from A";
open ds for v_sql;
这里重点就是v_sql变量，将所有需要的逻辑关系写好，整体赋值给v_sql，最后打开输出游标（ds）即可。
下面是一个完整的Oracle存储过程返回结果集的例子，大家可以参考：
先看一下最终效果：
这里同时也实现了动态列、层级展示的效果。
SQL代码：
procedure pro_tj_getDate_normal_year(v_sDate varchar2,ds out ds_cur) as
d_date date;
d_eDate date;
n_diff number :=0; --年份差
n_item number :=0; --是否保存有指标
v_sql varchar2(10000);
v_sql_t varchar2(5000);
v_sql_where varchar2(5000);
v_itemCode varchar2(100);--指标类别
n_i number;
v_fields varchar2(1000);
v_year varchar(5);
begin
--计算年份差
select substr(v_eDate,1,4) - substr(v_sDate,1,4)
into n_diff
from dual;
--判断当前用户在所选公司是否保存指标
select count(*) into n_item from BP_J_TJZH_ITEM_LIST l
where l.org_code=v_complany and l.work_code=v_user;
if n_item>0 then
v_sql_where := ' and i.item_code in(
select l.item_code from BP_J_TJZH_ITEM_LIST l
where l.org_code='''|| v_complany || ''' and l.work_code=''' || v_user || ''')';
else
v_sql_where := '';
end if;
--判断指标类别是否为空
if v_type='' or v_type is null then
v_itemCode := v_complany || '-0-000001';
else
v_itemCode := v_type;
end if;
if n_diff<=0 then --以截止接月份为准
v_year := substr(v_eDate,1,4);
v_sql := 'select i.item_code_prefix,
lpad('' '',2 * level - 2) || i.item_name as item_name,
u.unit_name,
t.data_value "'||v_year||'" from
bp_c_stat_item i
left join bp_c_measure_unit u
on i.unit_id=u.unit_id
left join
(
select n.data_date,n.item_code_prefix,n.data_value,
row_number() over(partition by to_char(n.data_date,''yyyy-mm''),n.item_code_prefix order by n.data_date desc) rn
from bp_j_stat_ntz n
where n.org_code='''||v_complany||'''
and to_char(n.data_date,''yyyy-mm'') = '''||v_eDate||'''
) t
on i.item_code_prefix=t.item_code_prefix and t.rn=1
where i.is_use=''Y'' and i.org_code=''' || v_complany || ''' ' || v_sql_where || '
start with i.item_code_prefix = '''||v_itemCode || '''
connect by prior i.item_code_prefix=i.parent_item_code
order SIBLINGS BY i.order_by asc';
else --时间段查询
d_date := to_date(v_sDate,'yyyy-mm');
d_eDate := to_date(v_eDate,'yyyy-mm');
n_i:=1;
v_fields := '';
v_sql := '';
while d_date<=d_eDate loop
v_year := to_char(d_date,'yyyy');
if EXTRACT(year FROM d_eDate) - EXTRACT(year FROM d_date)>0 then
v_sql := v_sql||'
left join (select t.item_code_prefix,t.data_value from (
select n.data_date,n.item_code_prefix,n.data_value,
row_number() over(partition by to_char(n.data_date,''yyyy''),n.item_code_prefix order by n.data_date desc) rn
from bp_j_stat_ntz n
where n.org_code='''||v_complany||'''
and to_char(n.data_date,''yyyy'') = '''||to_char(d_date,'yyyy') ||'''
) t where t.rn=1) t'||n_i||'
on i.item_code_prefix=t'||n_i||'.item_code_prefix
';
else
v_sql := v_sql||'
left join (select t.item_code_prefix,t.data_value from (
select n.data_date,n.item_code_prefix,n.data_value,
row_number() over(partition by to_char(n.data_date,''yyyy-mm''),n.item_code_prefix order by n.data_date desc) rn
from bp_j_stat_ntz n
where n.org_code='''||v_complany||'''
and to_char(n.data_date,''yyyy-mm'') = '''||to_char(d_date,'yyyy-mm') ||'''
) t where t.rn=1) t'||n_i||'
on i.item_code_prefix=t'||n_i||'.item_code_prefix
';
end if;
v_fields := v_fields || 't' || n_i || '.data_value as "'||v_year||'",';
n_i := n_i+1;
d_date :=add_months(d_date,12);
end loop;
v_sql_t := 'select i.item_code_prefix,
lpad('' '',2 * level - 2) || i.item_name as item_name,
u.unit_name,
'||v_fields||'
null
from bp_c_stat_item i
left join bp_c_measure_unit u
on i.unit_id=u.unit_id
';
v_sql := v_sql_t || v_sql || '
where i.is_use=''Y'' and i.org_code=''' || v_complany || ''' ' || v_sql_where ||
'start with i.item_code_prefix = '''||v_itemCode || '''
connect by prior i.item_code_prefix=i.parent_item_code
order SIBLINGS BY i.order_by asc';
end if;
--打开游标
open ds for v_sql;
end pro_tj_getDate_normal_year;
这个sql实现了根据动态日期展示指标数据，很实用哦。如果大家有其他更好的办法可以一起讨论。