fxg2590315 发表于 2013-2-1 12:50:03

利用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]
查看完整版本: 利用Oracle存储过程实现中国式的交叉统计报表