声明:这是我两星期的成果，第一次用存储过程做报表，希望大家多提问题，并尊重我的劳动成果
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;
956
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
