对于日常的报表统计,在oracle中的group by子句产生的查询往往不够直观,还需要excel的透视图才能产生二维报表.虽然在Oracle 11G中实现了这个功能,但我想鉴于我们目前还是10G的数据库.写这么一个函数还是有必要的.

源代码已经在下文中,直接在运行代码就可以得到函数GET_TWO_DIMENSIONAL_QUERY.

函数构成:该函数有3个参数,第一个参数是表名或者查询语句,第二个参数是X轴字段(仅允许单个字段),第三个参数是Y轴字段(允许多个字段,字段之间使用,分隔)

效果展示:

创建测试表:

create table TEST (name varchar2(255) ,sex varchar2(255),expenseability varchar2(255),district varchar2(255));

insert into TEST values ('张三','男','高消费','浦东新区');
insert into TEST values ('王强','男','高消费','浦东新区');
insert into TEST values ('马骏','男','中高消费','徐汇区');
insert into TEST values ('可乐','女','中等消费','闵行区');
commit;

得到表:

NAME	SEX	EXPENSEABILITY	DISTRICT
张三	男	高消费	浦东新区
王强	男	高消费	浦东新区
马骏	男	中高消费	徐汇区
可乐	女	中等消费	闵行区

执行查询函数: select GET_TWO_DIMENSIONAL_QUERY('TEST','district','sex') from dual 后得到查询反馈:

select sex,expenseability,sum (case when district='闵行区' then num else 0 end) as "闵行区",
sum (case when district='徐汇区' then num else 0 end) as "徐汇区",
sum (case when district='浦东新区' then num else 0 end) as "浦东新区"
from (select district,sex,expenseability,count(*) num from (TEST) group by district,sex,expenseability)
group by sex,expenseability

执行查询反馈就可以得到二维报表:

SEX	EXPENSEABILITY	闵行区	徐汇区	浦东新区
男	高消费	0	0	2
女	中等消费	1	0	0
男	中高消费	0	1	0

函数源码:

create or replace function GET_TWO_DIMENSIONAL_QUERY (TABLENAME in varchar2 ,P_X in varchar2 ,P_Y in varchar2)
return clob is
Result clob;
type curtype is ref cursor;
v_cur curtype;
v_BaseQuery varchar2(4000);
v_sqlstr varchar2(4000);
v_temp varchar2(4000);
begin
--获取group by 语句
v_BaseQuery:='(select '||P_X||','||P_Y||',count(*) num from ('||TABLENAME||') group by '||P_X||','||P_Y||')';
--选中Y轴字段
Result:='select '||P_Y||',';
--产生游标SQL
v_sqlstr :=' select distinct '||P_X||' from ('||v_BaseQuery||')';
--打开游标
open v_cur for v_sqlstr;
loop
fetch v_cur into v_temp;
exit when v_cur%notfound;

--判断值是否为空
if v_temp is null then
--产生X轴SQL
Result:=Result||'sum (case when '||P_X||' is null then num else 0 end) as "'||P_X||'为空'||'",';
Result:=Result||chr(13);
else
Result:=Result||'sum (case when '||P_X||'='''||v_temp||''' then num else 0 end) as "'||v_temp||'",';
Result:=Result||chr(13);
end if;

end loop;

--格式对齐
Result:=rtrim(Result,chr(13));
Result:=rtrim(Result,',');

Result:=Result||chr(13);
--将from字句与group by 子句加入
Result:=Result||'from '||v_BaseQuery||''||chr(13)||'group by '||P_Y||' ';

return Result;
end;

其实只是个代码生成函数,不过做报表用用还是挺方便的 ^_^

[@more@]

ITPub博客

Oracle中的二维表查询函数

最新文章