Oracle 10g Dynamic report column(eg)

习5477

　　今日(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;

　　
　　测试数据，效果图如下：
　　

 效果展示：