利用Oracle存储过程实现中国式的交叉统计报表

很多时候，我们需要做一些中国式的报表，例如：我们有一下几张数据表来表示某大学，对各个省份的招生计划信息：

 

省份信息表 Area_Id 名称 1 辽宁 2 吉林 3 黑龙江 … …

 

 

专业信息表 speciality_Id 院系名称 1 外语系 2 计算机系 3 中文系 … …

 

 

招生计划信息表 Area_Id speciality_Id 年度 招生人数 1 1 2010 3 2 1 2010 5 3 1 2010 3 1 2 2010 2 2 2 2010 5 3 2 2010 3 1 3 2010 1 2 3 2010 4 3 3 2010 5 … … … …

 

 

那么我们想要统计一个交叉的报表，统计2010年度，每个专业在各个省份的招生人数，即下面这个交叉统计报表（中国式报表）：

 

本年度各个专业在各个省份的招生计划信息   外语系 计算机系 中文系 辽宁 3 2 1 吉林 5 5 4 黑龙江 3 3 5 … … … …

 

 

      下面我们使用一个存储过程来实现此类型的报表统计，由于我们首先需要取得此大学院系数据游标，并通过循环此游标生成一个二次查询统计的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_sqlwhere  in varchar2 , --行查询条件从and开始
                                          
                                          l_tablename in varchar2 , --列表名
                                          l_fieldcode in varchar2 , --列编码
                                          l_fieldname in varchar2 , --列名称
                                          l_sqlwhere  in 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;