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

本文介绍如何利用Oracle存储过程生成中国式的交叉统计报表,以解决2010年各专业在不同省份的招生人数统计问题。由于可能超过varchar2的长度限制,存储过程使用dbms_sql.varchar2s和视图来动态生成并执行SQL,最终返回所需数据游标。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

 

省份信息表
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_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;

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值