声明:这是我两星期的成果，第一次用存储过程做报表，希望大家多提问题，并尊重我的劳动成果
by：zyz j2EE交流群：28533286
Oracle存储过程实现报表功能
【摘要】：该文介绍了如何使用Java调用Oracle存储过程实现报表功能，并给出了实例。
【关键词】：Java Oracle PL/SQL
问题提出
企业应用软件系统中报表功能几乎是必须的，业务数据都是通过报表、图表等形式展现给客户，这样的好处是能直观、快捷的让用户获得所需信息。通常，报表上展现的数据分散在不同的业务表中，一张报表对应有一条或者多条SQL语句来完成从不同的业务表中提取符合条件的数据，报表的复杂程度直接决定着SQL语句的复杂度，而且用户的报表经常会变动，这将产生了很多的维护工作，目前为止已经遇到的困难有如下这些：
1）用SQL实现报表功能，复杂的报表通常会写到几百行代码，现在我们写过最多的SQL已经有四百多行，用户报表改变时维护该条SQL语句困难、费时。
2）实现报表的SQL语句写在Java应用程序中，每次调用的时候都要发送到数据库服务器，并且重新编译，这给网络造成了压力，同时每次编译对性能也造成影响。
3）DAO实现类中编写的SQL语句修改后必须重启应用服务器才能将此次修改装载到内存中使用，重启应用服务必须在空闲时段完成。
4）运销系统报表展现工具使用ATGrid，而ATGrid支持的数据源只有ResultSet和Xml两种，我们采用的是ResultSet数据源。Hibernate在DAO层必须将session关闭才能释放数据库连接，这样返回给ATGrid的数据源rs是一个空引用，没有指向任何资源，所以取不到任何数据，只有session不关闭才能取到数据，但是这样数据库连接被挂起，不能释放。
引入存储过程可以解决以上四个问题。
存储过程介绍
存储过程是由流控制和SQL语句集书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。它具有以下优点：
1）存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算，如解决报表问题。
2）在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。
3）可以降低网络的通信量。
4）使体现企业规则的运算程序放入数据库服务器中，以便集中控制。当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。
报表存储过程设计
存储过程实现报表功能主要思想集中在临时表上，即每张用户报表对应一张临时数据表，该临时数据表的字段和报表表头要求的指标一一对应。
Oracle的临时表分为两种，事务级别临时表和Session级别临时表，此设计中使用Session级别临时表，使用Session级别临时表的好处在于其中的数据是Session级别隔离的，不同用户请求的数据相互隔离，互不干扰，每次用户访问报表时，存储过程将符合条件的数据从不同的物理表中抓取到临时数据表中，并且直接从临时数据表中返回数据给用户。
由于运销系统中使用apache的dbcp数据库连接池组件，应用服务器（Tomcat）启动时，就已经与Oracle创建了配置文件中指定数量的连接，此连接会持有Session会话，造成临时表中数据不会即时删除，而连接池又是应用服务器（Tomcat）自己管理的，多线程访问时，数据库连接被分配给线程是随机的，所以会出现数据访问错误的情况，引入临时参数表可以解决以上问题。
临时参数表也是Session级别的，它和临时数据表是同时被一个连接访问的，所以它们的Session隔离应该是同步的。临时参数表中存储的是调用存储过程的参数列表，当调用存储过程时，首先将此次调用的参数列表与临时参数表中的参数列表比较，如果临时参数表中没有记录或者比较不相等时则将此次调用的参数列表写入到临时参数表中，同时将符合条件的数据写入临时数据表中，并且从临时数据表中返回符合条件的数据给Java应用程序，下次调用存储过程时，如果参数列表相同，则直接从临时数据表中返回数据给Java应用程序，这样可以起到缓存的作用，提高存储过程效率。
为什么Hibernate Session能关闭
SQL语句实现报表使用的Statement和ResultSet为：
class org.apache.commons.dbcp.DelegatingStatement
class org.apache.commons.dbcp.DelegatingResultSet
存储过程实现报表使用的Statement和ResultSet为：
class org.apache.commons.dbcp.DelegatingCallableStatement
class oracle.jdbc.driver.OracleResultSetImpl
由于ResultSet接口的实现方式不同，造成ResultSet类型的引用变量rs所指向的资源释放时机不同。
性能测试
按照以上设计方式实现存储过程后，使用JMeter进行了性能测试，测试结果显示，在大并发量的情况下，使用存储过程比使用SQL语句性能更好。测试数据如下表所示：
|
Label
|
# Samples
|
Average
|
Median
|
90% Line
|
Min
|
Max
|
Error %
|
Throughput
|
KB/sec
|
|
存储过程
|
3000
|
230
|
231
|
286
|
36
|
606
|
0
|
42.55681 sec
|
252.3902
|
|
SQL
|
3000
|
254
|
239
|
268
|
37
|
3118
|
0
|
38.79979 sec
|
230.1085
|
二、样例（报表模版、java调用存储过程代码、存储过程代码）
执行流程图
Java调用存储过程代码
String sql="{ call PAK_COLLECT_LOCAL.PRO_MAIN(?,?,?,?,?,?,?) }";
CallableStatement cs = session.connection().prepareCall(sql);
cs.setString(1, mdDateStart);
cs.setString(2, mdDateEnd);
cs.setString(3, csName);
cs.setString(4, mName);
cs.setString(5, ciName);
cs.setString(6, remark);
cs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
rs = (ResultSet) cs.getObject(7);
cs.close();
存储过程代码
create or replace package PAK_COLLECT_LOCAL authid current_user is
-- Author : ZYZ
-- Created : 2009-4-23 11:46:56
-- Purpose : perchaseMeasureToCollect
--定义游标类型
type t_cursor is ref cursor;
-------创建临时表----
procedure pro_temptable;
-----往临时表中插入数据-----
procedure pro_inserttemptable(p_mdDateStart in varchar2,
p_mdDateEnd in varchar2,
p_csName in varchar2,
p_mName in varchar2,
p_ciName in varchar2,
p_remark in varchar2,
o_rs out t_cursor);
------返回结果集-----
procedure pro_get_rs(o_rs out t_cursor);
------Main方法-------
procedure pro_main(p_mdDateStart in varchar2,
p_mdDateEnd in varchar2,
p_csName in varchar2,
p_mName in varchar2,
p_ciName in varchar2,
p_remark in varchar2,
o_rs out t_cursor);
end PAK_COLLECT_LOCAL;
create or replace package body PAK_COLLECT_LOCAL is
--创建临时表
procedure pro_temptable is
v_counter number;
v_str_sql varchar2(1000); --根据DDL的长度调整大小
begin
--查询临时表是否存在
select count(table_name)
into v_counter
from user_tables u
where u.table_name = 'T_COLLECT_LOCAL';
if v_counter < 1 then
--定义临时表DDL
v_str_sql := 'create global temporary table T_COLLECT_LOCAL(
m_name varchar2(50),
ci_name varchar2(50),
ct_name varchar2(50),
md_outnum number(10),
md_outtunnage number(10,2),
md_intruck number(10),
md_intunnage number(10,2),
md_redgreen number(10,2),
smd_outnum number(10),
smd_outtunnage number(10,2),
smd_intruck number(10),
smd_intunnage number(10,2),
smd_redgreen number(10,2)
)on commit preserve rows';
execute immediate v_str_sql; --动态SQL创建临时表
end if;
--创建参数表
begin
select count(table_name)
into v_counter
from user_tables u
where u.table_name = 'T_PARAS';
if v_counter < 1 then
v_str_sql := 'create global temporary table T_PARAS(
report_name varchar2(50),
p_1 varchar2(50),
p_2 varchar2(50),
p_3 varchar2(50),
p_4 varchar2(50),
p_5 varchar2(50),
p_6 varchar2(50),
p_7 varchar2(50),
p_8 varchar2(50),
p_9 varchar2(50),
p_10 varchar2(50),
p_11 varchar2(50),
p_12 varchar2(50),
p_13 varchar2(50),
p_14 varchar2(50),
p_15 varchar2(50),
p_16 varchar2(50),
p_17 varchar2(50),
p_18 varchar2(50),
p_19 varchar2(50),
p_20 varchar2(50)
)on commit preserve rows';
execute immediate v_str_sql;
end if;
end;
end pro_temptable;
------传入参数检测-------
procedure pro_check_parameters(p_mdDateStart in varchar2,
p_mdDateEnd in varchar2,
p_csName in varchar2,
p_mName in varchar2,
p_ciName in varchar2,
p_remark in varchar2,
p_ocounter out number) is
v_str_sql varchar2(500);
v_report_name varchar2(50) := 'COLLECT_LOCAL';
v_counter number;
v_counter2 number;
v_mdDateStart varchar2(50) := p_mdDateStart;
v_mdDateEnd varchar2(50) := p_mdDateEnd;
v_csName varchar2(50) := p_csName;
v_mName varchar2(50) := p_mName;
v_ciName varchar2(50) := p_ciName;
v_remark varchar2(50) := p_remark;
begin
if v_mdDateStart is null then
v_mdDateStart := '#';
end if;
if v_mdDateEnd is null then
v_mdDateEnd := '#';
end if;
if v_csName is null then
v_csName := '#';
end if;
if v_mName is null then
v_mName := '#';
end if;
if v_ciName is null then
v_ciName := '#';
end if;
if v_remark is null then
v_remark := '#';
end if;
v_str_sql := 'select count(tp.report_name) from T_PARAS tp
where tp.report_name = ''' || v_report_name ||
''' and
tp.p_1 = ''' || v_mdDateStart || ''' and
tp.P_2 = ''' || v_mdDateEnd || ''' and
tp.P_3 = ''' || v_csName || ''' and
tp.P_4 = ''' || v_mName || ''' and
tp.P_5 = ''' || v_ciName || ''' and
tp.P_6 = ''' || v_remark || ''' ';
execute immediate v_str_sql
into v_counter;
v_str_sql := 'select count(tp.report_name) from T_PARAS tp
where tp.report_name = ''' || v_report_name || '''';
execute immediate v_str_sql
into v_counter2;
if v_counter2 = 0 then
begin
v_str_sql := 'insert into T_PARAS(report_name,p_1,p_2,p_3,p_4,p_5,p_6)
values(''' || v_report_name || ''',''' ||
v_mdDateStart || ''',''' || v_mdDateEnd || ''',''' ||
v_csName || ''',
''' || v_mName || ''',''' || v_ciName ||
''',''' || v_remark || ''')';
execute immediate v_str_sql;
end;
elsif v_counter = 0 and v_counter2 <> 0 then
begin
v_str_sql := 'update T_PARAS tp set
tp.p_1=''' || v_mdDateStart || ''',
tp.p_2=''' || v_mdDateEnd || ''',
tp.p_3=''' || v_csName || ''',
tp.p_4=''' || v_mName || ''',
tp.p_5=''' || v_ciName || ''',
tp.p_6=''' || v_remark ||
''' where tp.report_name = ''' || v_report_name || '''';
execute immediate v_str_sql;
end;
else
NULL;
end if;
p_ocounter := v_counter;
end pro_check_parameters;
--往临时表中插入数据
procedure pro_inserttemptable(p_mdDateStart in varchar2,
p_mdDateEnd in varchar2,
p_csName in varchar2,
p_mName in varchar2,
p_ciName in varchar2,
p_remark in varchar2,
o_rs out t_cursor) is
--定义变量，此处变量个数和类型与临时表一致，用于存储向临时表中插入的数据
m_name varchar2(50);
ci_name varchar2(50);
ct_name varchar2(50);
md_outnum number(10);
md_outtunnage number(10, 2);
md_intruck number(10);
md_intunnage number(10, 2);
md_redgreen number(10, 2);
smd_outnum number(10);
smd_outtunnage number(10, 2);
smd_intruck number(10);
smd_intunnage number(10, 2);
smd_redgreen number(10, 2);
v_str_sql_thismonth varchar2(1500);
v_str_sql_total varchar2(1500);
v_str_sql varchar2(4000);
v_str_sql_public varchar2(1000);
begin
--公共部分sql
v_str_sql_public := 'select m_name,ci_name,ct_name,sum(md_outtruck) as md_outnum,sum(md_outtunnage) as md_outtunnage,
sum(md_intruck) as md_intruck,sum(md_intunnage) as md_intunnage,
sum(md_redgreen) as md_redgreen
from localmeasuredaily
left join manufacturer on localmeasuredaily.m_id = manufacturer.m_id
left join coalinfo on localmeasuredaily.ci_id = coalinfo.ci_id
left join CHECKSTATION on localmeasuredaily.cs_id = CHECKSTATION.cs_id
left join coaltype on localmeasuredaily.ct_id = coaltype.ct_id
where CHECKSTATION.cs_name like ''%' ||
p_csName || '%''
and manufacturer.m_name like ''%' ||
p_mName || '%''
and coalinfo.ci_name like ''%' ||
p_ciName || '%''
and localmeasuredaily.md_remark like ''%' ||
p_remark || '%''';
--本月
v_str_sql_thismonth := '' || v_str_sql_public || '
and to_char(md_date,''yyyy-MM'') like ''%' ||
p_mdDateEnd || '%''
group by m_name,ci_name,ct_name';
--累计
v_str_sql_total := '' || v_str_sql_public || '
and md_date between to_date(''' ||
p_mdDateStart ||
''',''yyyy-MM-dd'') and add_months(to_date(''' ||
p_mdDateEnd ||
''',''yyyy-MM''),1)
group by m_name,ci_name,ct_name';
--汇总
v_str_sql := 'select singleTs.m_name,singleTs.ci_name,singleTs.ct_name,
singleTs.md_outnum, singleTs.md_outtunnage,singleTs.md_intruck,
singleTs.md_intunnage,singleTs.md_redgreen, sumTs.md_outnum,
sumTs.md_outtunnage,sumTs.md_intruck,sumTs.md_intunnage,sumTs.md_redgreen
from (' || v_str_sql_thismonth ||
') singleTs,(' || v_str_sql_total ||
') sumTs
where sumTs.ci_name = singleTs.ci_name and sumTs.m_name = singleTs.m_name
order by m_name,ct_name,ci_name';
begin
open o_rs for v_str_sql;
execute immediate 'truncate table T_COLLECT_LOCAL'; --动态SQL清除临时表中的数据
loop
--移动游标将结果集中的值赋给变量
fetch o_rs
into m_name, ci_name, ct_name, md_outnum, md_outtunnage, md_intruck, md_intunnage, md_redgreen, smd_outnum, smd_outtunnage, smd_intruck, smd_intunnage, smd_redgreen;
if o_rs %notfound then
exit;
end if;
--动态SQL将数据插入临时表
execute immediate 'insert into T_COLLECT_LOCAL values(''' || m_name ||
''',''' || ci_name || ''',''' || ct_name || ''',
''' ||
md_outnum || ''',''' || md_outtunnage || ''',''' ||
md_intruck || ''',
''' ||
md_intunnage || ''',''' || md_redgreen || ''',''' ||
smd_outnum || ''',
''' ||
smd_outtunnage || ''',''' || smd_intruck ||
''',''' || smd_intunnage || ''',
''' ||
smd_redgreen || ''')';
end loop;
end;
end pro_inserttemptable;
--返回结果集
procedure pro_get_rs(o_rs out t_cursor) is
sql_str varchar2(500);
begin
sql_str := 'select * from T_COLLECT_LOCAL'; --从临时表中检索数据
open o_rs for sql_str; --打开游标
end pro_get_rs;
--Main方法
procedure pro_main(p_mdDateStart in varchar2,
p_mdDateEnd in varchar2,
p_csName in varchar2,
p_mName in varchar2,
p_ciName in varchar2,
p_remark in varchar2,
o_rs out t_cursor) is
v_ocounter number;
ocounter number;
begin
--创建临时表
pro_temptable;
------传入参数检测-------
pro_check_parameters(p_mdDateStart,
p_mdDateEnd,
p_csName,
p_mName,
p_ciName,
p_remark,
v_ocounter);
ocounter := v_ocounter;
if ocounter < 1 then
--往临时表中插入数据
pro_inserttemptable(p_mdDateStart,
p_mdDateEnd,
p_csName,
p_mName,
p_ciName,
p_remark,
o_rs);
end if;
--返回结果集
pro_get_rs(o_rs);
end pro_main;
end PAK_COLLECT_LOCAL;
分享到：
相关推荐
数据库sqlserver(存储过程实现报表数据生成).rar
使用环境：VS2008、SQL2005 功能：带参的存储过程实现数据查询、显示报表网格线、表头显示不同颜色
神州数码易飞产品报表开发,本身报表比较特殊点,功能也比较少.如果能总结一写方法实现 还是很有好处的
查询模板初始化（对报表对应的功能模板编码进行条件设置） -动态建模平台-开发配置-模板管理-查询模板初始化 （具体查询条件来源、配置、需要用友技术支） 分配报表权限给相关人员、角色、职能等。
11、支持采用存储过程查询报表数据 12、支持左右拖动数据单元格，改变每个单元格的宽度 13、高度可扩展性，可以自己增加报表类型、页面类型、输入框类型、资源类型等等。 14、全程AJAX，包括翻页、查询、排序、过滤...
具有全新的设计思想，对中国式报表和很多复杂报表有很强的针对性，他将“对象摆放”类和“电子表格”类报表开发工具完美融合，使用种类丰富且功能强大的报表对象组合生成任意复杂的报表样式。在华报智能报表工具中，...
采购管理： 支持采购订单的录入、审核、入库等功能，实现对采购过程的监控和管理。 销售管理： 实现销售订单的录入、审核、出库等功能，包括销售金额、数量、客户信息等管理。 库存管理： 实时监控库存数量，设定...
在许多报表和统计类产品中，存在大量的复杂计算功能，这些公式往往有数十步的计算过程，上万条数据统计记录，以及跨多个关系型数据库的数据依赖，甚至是多种语言的混合实现。更棘手的是：往往为了节省数据存储资源，...
Oracle关系型数据库以及非关系型数据库（Redis），Oracle 性能调优（PL/SQL语言，SQL查询优化，存储过程等），用Redis做中间缓存，缓存数据 实现异步处理，定时任务，整合Quartz Job以及Spring Task 邮件管理功能...
单元格可以是普通的值、表达式、SQL语句，也可以是调用远端数据库系统的存储过程，也可以是声音、图片、二(三)维图表、影片等多媒体信息，也可以是嵌套报表。 ◆规范的二次开发接口 系统提供200多个属性、500多个...
存储过程等），用Redis做中间缓存，缓存数据 实现异步处理，定时任务，整合Quartz Job以及Spring Task 邮件管理功能， 整合spring-boot-starter-mail发送邮件等， 数据源:druid 用户管理，菜单管理，角色管理，代码...
阐述了基于Android平台的个人理财软件的设计与实现过程，系统基于Android平台，采用Java语言开发，通过XML文档进行界面编程，并通过相应的Activity与后台Service实现指定功能，且实现了对数据的图形处理，数据存储与...
源码描述： 进出口报关业务,增加核销模块功能，实现业务单据在线申请，票据完成后自动汇总成本报表，月底自动生成...4、MSSQL 存储过程应用 5、.net 三层结构划分，狼权限模块应用等。 后续还将完善功能，敬请期待。
该项目通过电脑系统化管理各种仓库事务，可实现货物入库、出库、库存管理、报表生成等功能，提高仓储效率和准确性。 该仓库管理系统项目的主要功能包括： 仓库信息管理：包括仓库基本信息的录入、修改和删除，可以...
报表模块：实现了各种统计报表的生成和查询功能。在项目中，使用了如下技术：Spring Boot：用于快速搭建项目，简化配置和部署过程。MyBatis：用于实现数据持久化操作，与MySQL数据库进行交互。MySQL：作为项目的...
数据持久化：使用JDBC或者ORM框架（如Hibernate）实现数据的持久化操作，将数据存储到关系型数据库中。 安全性：在用户登录过程中进行身份验证，确保系统的安全性；对用户权限进行管理，保障数据的安全性和完整性。...
数据持久化：使用JDBC或者ORM框架（如Hibernate）实现数据的持久化操作，将数据存储到关系型数据库中。 安全性：在用户登录过程中进行身份验证，确保系统的安全性；对用户权限进行管理，保障数据的安全性和完整性。...
系统主要包括用户管理、水费录入、水费查询、统计报表等功能模块，能够实现水费信息的快速录入、查询和统计分析。 在开发过程中，我们采用了VB作为开发工具，利用其强大的编程能力和友好的界面设计，实现了系统的...
Logstash 在其过程中担任搬运工的角色,它为数据存储,报表查询和日志解析创建了一个功能强大的管道链。Logstash 提供了多种多样的 input,filters,codecs 和 output 组件,让使用者轻松实现强大的功能。