Oracle 10g Dynamic report column(eg)

oboaix

浏览: 273891 次

最近访客更多访客>>

binashen

dongguangming88

crazydoudou

cao348922336

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Database

动态列返回游标集

今日(2012-05-14)在群信息里(Oracle 10.2.0)讨论到一个报表，效果是汇总计算同时包括横向和纵向，如果指定列，看上去还是容易的，但是如果动态报表列头，感觉稍微有点难度，这里记录下来，以备资用，好久没有记录点东西，同时老爸也生病了(希望渡过难关)。可以是函数返回游标集，也可以是存储过程返回游标集，这种SQL确实也是有些冗长，这里仅仅是提供一种方案，没有深入性能优化考虑，具体到实际问题中，我们应该从业务出发，进行各方面性能诊断调优。

---create table
create table T_SALE
(
  DP    VARCHAR2(10),
  SALE  NUMBER(10,2),
  SDATE DATE
);
---build data
begin
insert into t_sale (DP, SALE, SDATE) values ('TB01', 2800.00, to_date('01-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB02', 2900.00, to_date('01-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB03', 3000.00, to_date('01-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB04', 3100.00, to_date('01-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB05', 3200.00, to_date('01-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB06', 3300.00, to_date('01-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB07', 3400.00, to_date('01-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB08', 3500.00, to_date('01-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB01', 2900.00, to_date('02-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB02', 3000.00, to_date('02-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB03', 3100.00, to_date('02-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB04', 3200.00, to_date('02-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB05', 3300.00, to_date('02-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB06', 3400.00, to_date('02-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB07', 3500.00, to_date('02-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB08', 3600.00, to_date('02-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB01', 3000.00, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB02', 3100.00, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB03', 3200.00, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB04', 3300.00, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB05', 3400.00, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB06', 3500.00, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB07', 3600.00, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB08', 3700.00, to_date('03-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB01', 3100.00, to_date('04-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB02', 3200.00, to_date('04-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB03', 3300.00, to_date('04-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB04', 3400.00, to_date('04-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB05', 3500.00, to_date('04-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB06', 3600.00, to_date('04-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB07', 3700.00, to_date('04-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB08', 3800.00, to_date('04-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB01', 3200.00, to_date('05-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB02', 3300.00, to_date('05-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB03', 3400.00, to_date('05-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB04', 3500.00, to_date('05-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB05', 3600.00, to_date('05-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB06', 3700.00, to_date('05-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB07', 3800.00, to_date('05-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB08', 3900.00, to_date('05-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB01', 3300.00, to_date('06-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB02', 3400.00, to_date('06-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB03', 3500.00, to_date('06-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB04', 3600.00, to_date('06-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB05', 3700.00, to_date('06-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB06', 3800.00, to_date('06-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB07', 3900.00, to_date('06-04-2012', 'dd-mm-yyyy'));
insert into t_sale (DP, SALE, SDATE) values ('TB08', 4000.00, to_date('06-04-2012', 'dd-mm-yyyy'));
commit;
end;
---create package cursor type
create or replace package pkg_test
as
    type   myCursor   is   ref   cursor;
end   pkg_test;
---create procedure
create or replace procedure sp_DynamicReport(dp varchar2,startDate date,endDate date,
rc out pkg_test.myCursor)
 is 
strSql long;
tempSql varchar2(4000);
days number;
querySql varchar2(200):=' dp like ''%'||dp||'%'' AND ';
begin
   strSql := 'select '' error from dual';
   select (endDate - startDate) into days from dual;
   if dp is null then
      querySql :=' ';
   end if;
   if days >0 then
      strSql := 'select a.dp as "店铺号"';
     for i in 0..days loop
         tempSql := tempSql||',B'||i||'."'||to_char(startDate+i,'yyyy-mm-dd')||'"';
     end loop;
     strSql := strSql||tempSql||',a."总计" from ';
     strSql := strSql||'(select dp,"总计" from (SELECT DP,SUM(SALE) "总计", 1 as orderflag FROM T_SALE
     where '||querySql||' SDATE between to_date('''||to_char(startDate,'yyyy-mm-dd')||''',''yyyy-mm-dd'') and to_date('''||to_char(endDate,'yyyy-mm-dd')||''',''yyyy-mm-dd'')
      GROUP BY DP
     union all
     SELECT ''总计'' as dp, SUM(SALE) "总计", 2 as orderflag FROM T_SALE
     where '||querySql||' SDATE between to_date('''||to_char(startDate,'yyyy-mm-dd')||''',''yyyy-mm-dd'') and to_date('''||to_char(endDate,'yyyy-mm-dd')||''',''yyyy-mm-dd'')
     ) order by orderflag,dp) a ';
     --1 dbms_output.put_line(strSql);
     tempSql :='';
     for i in 0..days loop
         tempSql := tempSql||', (
         SELECT dp,sum(sale) "'||to_char(startDate+i,'yyyy-mm-dd')||'" from T_SALE where '||querySql||' SDATE=TO_DATE('''||to_char(startDate+i,'yyyy-mm-dd')||''',''YYYY-MM-DD'') group by dp,sdate
         union all
         SELECT ''总计'' as dp,sum(sale) "'||to_char(startDate+i,'yyyy-mm-dd')||'" from T_SALE where '||querySql||' SDATE=TO_DATE('''||to_char(startDate+i,'yyyy-mm-dd')||''',''YYYY-MM-DD'') group by sdate
         ) b'||i;
     end loop;
     strSql := strSql||tempSql||' where 1=1 ';
     --2 dbms_output.put_line(tempSql||' where 1=1 ');
     tempSql :='';
     for i in 0..days loop
         tempSql := tempSql||' and a.dp=b'||i||'.dp(+) ';
     end loop;
     strSql := strSql||tempSql||' order by a.dp';
     --3 dbms_output.put_line(tempSql||' order by a.dp');
   end if;
   open rc for strSql;
  --return rc;
  exception when others then
    dbms_output.put_line('Error exception!!!');
    dbms_output.put_line('店铺号: '||dp);
    dbms_output.put_line('开始日期: '||to_char(startDate,'yyyy-mm-dd'));
    dbms_output.put_line('结束日期: '||to_char(endDate,'yyyy-mm-dd'));
    open rc for select '' error from dual;
    --return rc;
end;

测试数据，效果图如下：

效果展示：