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实现了根据动态日期展示指标数据，很实用哦。如果大家有其他更好的办法可以一起讨论。相关阅读： Java中用JDBC调用Oracle存储过程和函数 http://www.linuxidc.com/Linux/2013-09/89922.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12Ubuntu 下修改MySQL的字符为UTF-8Oracle索引重建到底会提高多少性能？相关资讯 Oracle存储过程结果集

Oracle存储过程拼接in语句 & 自定（今 09:21）
【PL/SQL系列】Oracle存储过程使用（04月23日）
Oracle存储过程及Java调用（05/28/2015 20:29:33）

Java调用Oracle存储过程返回多条结（04月29日）
Oracle中的存储过程简单应用（04月10日）
判断点是否落在面中的Oracle存储过（05/09/2015 09:39:30）

本文评论查看全部评论（0）

评论声明

尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任