利用Oracle存储过程实现中国式的交叉统计报表
原文链接:http://blog.csdn.net/sandloves/archive/2010/06/24/5691836.aspx很多时候,我们需要做一些中国式的报表,例如:我们有一下几张数据表来表示某大学,对各个省份的招生计划信息:
省份信息表
Area_Id名称
1辽宁
2吉林
3黑龙江
……
专业信息表
speciality_Id院系名称
1外语系
2计算机系
3中文系
……
招生计划信息表
Area_Idspeciality_Id年度招生人数
1120103
2120105
3120103
1220102
2220105
3220103
1320101
2320104
3320105
…………
那么我们想要统计一个交叉的报表,统计2010年度,每个专业在各个省份的招生人数,即下面这个交叉统计报表(中国式报表):
本年度各个专业在各个省份的招生计划信息
外语系计算机系中文系
辽宁321
吉林554
黑龙江335
…………
下面我们使用一个存储过程来实现此类型的报表统计,由于我们首先需要取得此大学院系数据游标,并通过循环此游标生成一个二次查询统计的sql语句,此sql在生成之后会十分庞大,可能会超过oracle中varchar2的长度限制(32767)。所以此存储过程采用dbms_sql.varchar2s字符串数组来实现动态sql。
但是问题又出现了,由于dbms_sql.varchar2s执行动态sql之后无法返回一个游标,所以当dbms_sql.varchar2s执行sql时我们考虑使用view来完成此功能,也就是说,通过dbms_sql.varchar2s执行之后会生成一个我们需要的临时视图view,然后我们再通过普通的sql去查询此视图,并返回游标。具体的存储过程实现如下:
create or replace procedure P_QueryReport(h_tablename in varchar2 , --行表名
h_fieldcode in varchar2 , --行编码
h_fieldname in varchar2 , --行名称
h_sqlwherein varchar2 , --行查询条件从and开始
l_tablename in varchar2 , --列表名
l_fieldcode in varchar2 , --列编码
l_fieldname in varchar2 , --列名称
l_sqlwherein varchar2 , --列查询条件
c_tablename in varchar2 , --统计表名
c_sum_fieldcode in varchar2 , --汇总字段名
c_h_fieldcode in varchar2 , --统计表关联行编码
c_l_fieldcode in varchar2 , --统计表关联列编码
c_sqlwhere in varchar2 , --统计表查询条件
resultlist out sys_refcursor --返回结果集
) Authid Current_User is
l_sql varchar2 (32767);
l_rs sys_refcursor;
l_r_code varchar2 (32767);
l_r_name varchar2 (32767);
h_sql varchar2 (32767);
p_c_sqlwhere varchar (32767);
------------------------------------------
l_stmt dbms_sql .varchar2s;
l_cursor integer default dbms_sql .open_cursor;
l_rows number default 0;
l_length number := 0;
i number := 1;
e number := 0;
--------------------------------------------
begin
p_c_sqlwhere := c_sqlwhere;
e := length (p_c_sqlwhere);
l_stmt(i) := 'create or replace view LHTEMPVIEW as select ' ||
h_fieldcode || ',' || h_fieldname || ',' ;
i := i + 1;
l_sql := 'select ' || l_fieldcode || ',' || l_fieldname || ' from ' ||
l_tablename || ' where 1=1 ' || trim(l_sqlwhere) || ' ' ;
open l_rs for l_sql;
loop
Fetch l_rs
Into l_r_code, l_r_name;
Exit When l_rs%notfound;
l_stmt(i) := ' (select sum(' || c_sum_fieldcode ||')from ' ;
i := i + 1;
l_stmt(i) := c_tablename;
i := i + 1;
l_stmt(i) := ' where ' ;
i := i + 1;
l_stmt(i) := c_l_fieldcode;
i := i + 1;
l_stmt(i) := ' = ' '' || l_r_code || '' ' ' ;
i := i + 1;
l_stmt(i) := trim(p_c_sqlwhere);
i := i + 1;
l_stmt(i) := ' and ' || c_h_fieldcode || ' = ' || h_fieldcode ||
') as "' || l_r_name || '_' || i || '",' ;
i := i + 1;
end loop ;
if i > 2 then
l_stmt(i - 1) := substr (l_stmt(i - 1), 0, length (l_stmt(i - 1)) - 1);
end if ;
l_stmt(i) := ' from ' || h_tablename || ' where 1=1 ' || trim(h_sqlwhere);
l_sql := l_stmt(i - 1);
dbms_sql .parse (c => l_cursor,
statement => l_stmt,
lb => l_stmt.first,
ub => l_stmt.last,
lfflg => TRUE ,
language_flag => dbms_sql .native);
l_rows := dbms_sql .execute (l_cursor);
dbms_sql .close_cursor (l_cursor);
h_sql := 'select * from LHTEMPVIEW' ;
open resultlist for h_sql;
end P_QueryReport;
页:
[1]