声明:这是我两星期的成果，第一次用存储过程做报表，希望大家多提问题，并尊重我的劳动成果
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;
分享到：
相关推荐
本文将深入探讨如何利用SQL Server的存储过程来实现报表数据的生成。 首先，理解存储过程的基本概念。存储过程是一组为了完成特定功能的SQL语句，这些语句被编译并存储在数据库中。用户通过调用存储过程的名字来...
在这个场景中，我们关注的是如何通过带参数的存储过程来实现数据查询，同时结合VS2008（Visual Studio 2008）和SQL2005这两个工具，创建一个能够展示网格线和彩色表头的报表。下面将详细讲解这一技术栈中的关键知识...
在IT行业中，数据库管理和数据分析是至关重要的领域，而“存储过程后台实现报表”是一个常见的技术实践，它涉及到数据库编程和报表生成。存储过程是预编译的SQL语句集合，通常用于执行复杂的数据库操作，而报表则是...
用友U8自定义报表使用存储过程时，使用系统的分组和小计、累计、合计是指在用友U8自定义报表中使用存储过程来生成报表，同时利用系统的分组和小计、累计、合计功能来实现报表的统计和分析。 在用友U8中，自定义报表...
根据提供的文档信息，本总结将围绕用友UAP报表的二次开发过程，特别是针对SQL Server存储过程的编写以及报表中数据权限控制和过滤条件的实现进行详细的知识点阐述。 ### 用友UAP报表二次开发概述 用友UAP报表是...
综上所述，文档详细介绍了Oracle数据库环境下的PL/SQL存储过程技术，分析了其结构和使用场景，并结合实际案例，解释了如何应用这些技术实现动态报表的生成。文档还对PL/SQL Gateway的配置和应用提供了实用指导，为...
本文将探讨如何在SQL Server数据库中利用存储过程实现动态交叉表。 首先，我们需要理解交叉表的基本概念。在数据分析和报表生成中，交叉表是一种以行列交叉形式展示数据统计结果的表格，通常用于展示分类数据的汇总...
在实际应用中，存储过程广泛用于业务逻辑复杂的场景，如数据清洗、报表生成、批处理操作等。例如，一个存储过程可能用于处理用户的注册，包括检查用户名是否已存在、插入新用户记录、发送验证邮件等步骤。 总的来说...
本文以无锡华瑞制药有限公司项目为例，详细阐述了通过WinCC的Global Script实现报表功能的过程，以及在这一过程中所采用的技术和方法。 首先，文章介绍了华瑞制药项目的基本情况，包括企业的背景、生产规模、产品...
#### 使用用户归档实现报表简介 用户归档作为西门子WinCC系统中一个强大的工具，可以帮助用户收集、存储及处理自动化过程中的实时数据，进而形成各类报表。本文将详细介绍如何通过用户归档功能以及WinCC报表编辑器...
存储过程可以应用于各种场景，如数据查询、数据修改、数据报表等。例如，可以使用存储过程来实现数据查询： ```sql CREATE PROCEDURE sp_GetData @city nvarchar(20), @town nvarchar(20), @village nvarchar(20)...
oracle 是一个功能强大的关系型数据库管理系统，可以执行各种复杂的任务，其中包括定时执行存储过程。定时执行存储过程可以让 oracle 自动执行某些操作，而不需要人工干预。下面我们将详细讲解 oracle 中的定时执行...
5. **文件名称列表**：`oracle定时任务`和`存储过程`这两个文件名可能是指包含有关如何在实际环境中设置和使用这些功能的文档或脚本。在学习和实践中，可以参考这些文件以获取更具体的步骤和示例。 综上所述，...
过滤器存储过程（Filter Stored Procedure）通常指的是在SQL Server或类似的数据库系统中，通过存储过程来实现对数据进行筛选和处理的功能。存储过程本身是预编译的SQL代码集合，能够提高数据库操作的效率，并提供...
综上所述，这个系统使用存储过程实现了列转行和分页查询的功能，适用于处理大型数据集时的数据展示和导航。在实际应用中，这样的设计可以提高查询效率，减少服务器负载，并提供良好的用户体验。
接着，使用SqlCommand对象执行SQL查询或存储过程，获取报表所需的数据。例如： ```csharp SqlCommand command = new SqlCommand("SELECT * FROM MyTable", connection); SqlDataReader reader = command....
C#作为.NET框架的主要编程语言，提供了丰富的库和功能来帮助开发者实现这样的需求。本篇文章将深入探讨如何利用C#实现打印报表生成器，以及其中涉及的关键技术和步骤。 首先，报表生成器的核心功能包括设计报表布局...
在使用报表开发工具FineReport进行报表开发时，理解其内部执行过程对于优化报表性能、调试和监控报表生成至关重要。FineReport中的报表执行过程可以分为两个主要步骤，即报表计算和页面转换。 首先，报表计算过程...
这个存储过程可能实现了插入新数据的功能。它可能接收一个或多个参数，对应要插入的数据值，并将这些值插入到指定的表中。使用存储过程进行数据添加可以确保数据的一致性和完整性，因为可以在存储过程中添加事务...
"SQL存储过程入门级教程" ...同时，存储过程也可以用于实现数据报表、数据分析、数据挖掘等功能。 存储过程是SQL Server中的一种重要组件，它可以帮助开发者和DBA更好地管理和维护数据库，提高数据库的性能和安全性。