计划用PL/SQL做一个SQL生成器（更新在20/27楼）

iamczy1234

对于日常的报表统计,在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;

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

〇〇

专门为http://www.itpub.net/thread-1336066-1-1.html作的，支持一列的数字汇总

create or replace function fsql3(v_tname varchar,tr  varchar ,tc0 varchar,sum1 varchar,v_tcode varchar) return varchar is tc varchar(4000); sqlstr varchar(4000); s1 varchar(4000); s2 varchar(4000); s3 varchar(4000); s4 varchar(4000); TYPE rc IS REF CURSOR; TYPE nlist IS TABLE OF NUMBER; TYPE vlist IS TABLE OF VARCHAR2(400); rc1 rc; g1  vList; g2  vList; g3  vList;    begin tc:=' '||trim(tc0)||' '; s1:='with t as (select '||tr||','||replace(trim(tc),' ',',')||',count(*) cnt from '|| v_tname||' group by '||tr||','||replace(trim(tc),' ',',')||')'; if sum1 is not null then s1:=replace(s1,'count(*)','sum('||sum1||')'); end if; sqlstr:='select l,substr(tc,instr(tc,'' '',1,l)+1,instr(tc,'' '',1,l+1)-instr(tc,'' '',1,l)-1) pos from (select '''||tr||'''tr,'''||tc||''' from dual)t,(select level l from dual connect by level<=10)a where l<length(tc)-length(replace(tc,'' '',''''))'; sqlstr:=replace(sqlstr,'tc',''''||tc||''''); s3:='select ''sum(case when ''';    OPEN rc1 FOR sqlstr;    FETCH rc1 BULK COLLECT INTO g1, g2;    CLOSE rc1;    FOR i IN g1.FIRST ..g1.LAST    LOOP    if i>1 then    s2:=s2||',';    s3:=s3||''' and ''';   end if;    s2:=s2||'(select * from '||v_tcode||' where cname='''||g2(i)||''' and ccode is not null)'||chr(96+i)||chr(10); --拼凑where    --(a)sqlstr:=q'[select 'sum(case when '||a.cname||'='''||a.ccode||''' and '||b.cname||'='''||b.ccode||''' then cnt end)as c'|| a.cname2||b.cname2    --(b)sum(case when sex='M' and age='1' then cnt end)as c男1岁    s3:=s3||'||'||chr(96+i)||'.cname||''=''''''||'||chr(96+i)||'.ccode||'||'''''' ; --拼凑case when语句,拼出(a),运行得(b)    s4:=s4||case i when 1 then'c''||'else'||'end||chr(96+i)||'.cname2'; --拼凑别名    END LOOP; sqlstr:=s3||''' then cnt end) as '||s4||' from '||replace(s2,'tc',''''||tc||''''); s2:=''; s4:='SELECT cname,ccode,cname2 FROM '||v_tcode||' WHERE tname=:1 and cname=:2';    OPEN rc1 FOR s4 USING v_tname,tr;    FETCH rc1 BULK COLLECT INTO g1, g2,g3;    CLOSE rc1;    FOR i IN g1.FIRST ..g1.LAST    LOOP    if g1(i)=tr and g2(i)is null then    s2:=' ,t1 as(select '||tr||' as '||g3(i)||chr(10);    end if;    END LOOP; if s2 is null then         return 'error'; end if; OPEN rc1 FOR sqlstr;    FETCH rc1 BULK COLLECT INTO g1;    CLOSE rc1;    FOR i IN g1.FIRST ..g1.LAST    LOOP    s2:=s2||','||g1(i)||chr(10);    END LOOP; s3:='group by '||tr; return s1||s2||'from t '||s3||') select * from t1'; end; / SQL> insert into eav values('px','id',null,'id'); 已创建 1 行。 SQL> insert into eav values('px','id','a','a'); 已创建 1 行。 SQL> insert into eav values('px','id','b','b'); 已创建 1 行。 SQL> insert into eav values('px','name',null,'name'); 已创建 1 行。 SQL> insert into eav values('px','name','10','10'); 已创建 1 行。 SQL> insert into eav values('px','name','20','20'); 已创建 1 行。 SQL> insert into eav values('px','name','30','30'); 已创建 1 行。 SQL> insert into eav values('px','name','40','40'); 已创建 1 行。 SQL> insert into eav values('px','val',null,'val'); 已创建 1 行。 SQL> select fsql3('px','name','id','val','eav') from dual; FSQL3('PX','NAME','ID','VAL','EAV') ----------------------------------------------------------- with t as (select name,id,sum(val) cnt from px group by name,id) ,t1 as(select name as name ,sum(case when id='a' then cnt end) as ca ,sum(case when id='b' then cnt end) as cb from t group by name) select * from t1

newkid

原帖由 〇〇 于 2010-8-12 08:04 发表
感觉不管eav表名是固定还是不固定，case还是动态SQL实现比较方便
只要拼出几个group by列的笛卡尔积
如果用静态SQL读入数组再循环实现，不知道要嵌套几层

用视图模拟存在一个问题，如果事实表很大，distinct 取值成本很高
而如果用eav把维度表保存起来读起来快一些

怎么要嵌套几层？等我有空改写一下。
我不是说要从事实表中模拟出eav, 我是说用户很可能有类似的编码表，但是表名、列名和你的EAV不同，只要用视图转换一下就行了。

newkid

我现在就来示范一下，改用静态SQL之后27楼的程序可以变得如何整洁漂亮：


CREATE OR REPLACE FUNCTION fsql1(v_tname varchar2,tr  varchar2 ,tc0 varchar2)
RETURN VARCHAR2
IS
   lv_sql  VARCHAR2(4000);
   lv_name VARCHAR2(30);
BEGIN
   SELECT cname2
     INTO lv_name
     FROM eav
    WHERE tname = v_tname
          AND cname = tr
          AND ccode IS NULL;

   lv_sql := 'WITH t AS (
   SELECT '||tr||','||REPLACE(tc0,' ',',')||',COUNT(*) cnt FROM '||v_tname||' GROUP BY '||tr||','||REPLACE(tc0,' ',',')||')
   ,t1 AS (
   SELECT '||tr||' AS '||lv_name;

   FOR lv_rec IN (SELECT SYS_CONNECT_BY_PATH(cname||'='''||ccode||'''',' AND ') AS s1
                        ,'C'||REPLACE(SYS_CONNECT_BY_PATH(cname2,','),',') AS s2
                    FROM
                  (SELECT e.*,t.id
                     FROM eav e
                         ,(SELECT ROWNUM id, CAST(column_value AS VARCHAR2(30)) c
                             FROM xmltable(('"'||REPLACE(tc0,' ','","')||'"'))
                          ) t
                    WHERE e.tname = v_tname
                          AND e.cname = t.c
                          AND e.ccode IS NOT NULL
                  ) e2
                  WHERE CONNECT_BY_ISLEAF=1
                  START WITH id=1
                  CONNECT BY id = PRIOR id+1
                  ORDER SIBLINGS BY ccode
                )
   LOOP
      lv_sql := lv_sql ||CHR(10)||'         ,SUM(CAS WHEN '||SUBSTR(lv_rec.s1,5)||' THEN cnt END) AS '||lv_rec.s2;
   END LOOP;
   
   lv_sql := lv_sql ||CHR(10)||'   FROM T GROUP BY '||tr||')
SELECT * FROM t1';

   RETURN lv_sql;
END fsql1;
/


jsu@JSU> SELECT fsql1('tren','region','sex nation age class1') FROM DUAL;

FSQL1('TREN','REGION','SEXNATIONAGECLASS1')
----------------------------------------------------------------------------------------------------------------------------
WITH t AS (
   SELECT region,sex,nation,age,class1,COUNT(*) cnt FROM tren GROUP BY region,sex,nation,age,class1)
   ,t1 AS (
   SELECT region AS 地区
         ,SUM(CAS WHEN  sex='F' AND nation='1' AND age='1' AND class1='1' THEN cnt END) AS C女汉1岁学生
         ,SUM(CAS WHEN  sex='F' AND nation='1' AND age='1' AND class1='2' THEN cnt END) AS C女汉1岁工人
         ,SUM(CAS WHEN  sex='F' AND nation='1' AND age='1' AND class1='3' THEN cnt END) AS C女汉1岁农民
         ,SUM(CAS WHEN  sex='F' AND nation='1' AND age='2' AND class1='1' THEN cnt END) AS C女汉2岁学生
         ,SUM(CAS WHEN  sex='F' AND nation='1' AND age='2' AND class1='2' THEN cnt END) AS C女汉2岁工人
         ,SUM(CAS WHEN  sex='F' AND nation='1' AND age='2' AND class1='3' THEN cnt END) AS C女汉2岁农民
         ,SUM(CAS WHEN  sex='F' AND nation='2' AND age='1' AND class1='1' THEN cnt END) AS C女壮1岁学生
         ,SUM(CAS WHEN  sex='F' AND nation='2' AND age='1' AND class1='2' THEN cnt END) AS C女壮1岁工人
         ,SUM(CAS WHEN  sex='F' AND nation='2' AND age='1' AND class1='3' THEN cnt END) AS C女壮1岁农民
         ,SUM(CAS WHEN  sex='F' AND nation='2' AND age='2' AND class1='1' THEN cnt END) AS C女壮2岁学生
         ,SUM(CAS WHEN  sex='F' AND nation='2' AND age='2' AND class1='2' THEN cnt END) AS C女壮2岁工人
         ,SUM(CAS WHEN  sex='F' AND nation='2' AND age='2' AND class1='3' THEN cnt END) AS C女壮2岁农民
         ,SUM(CAS WHEN  sex='M' AND nation='1' AND age='1' AND class1='1' THEN cnt END) AS C男汉1岁学生
         ,SUM(CAS WHEN  sex='M' AND nation='1' AND age='1' AND class1='2' THEN cnt END) AS C男汉1岁工人
         ,SUM(CAS WHEN  sex='M' AND nation='1' AND age='1' AND class1='3' THEN cnt END) AS C男汉1岁农民
         ,SUM(CAS WHEN  sex='M' AND nation='1' AND age='2' AND class1='1' THEN cnt END) AS C男汉2岁学生
         ,SUM(CAS WHEN  sex='M' AND nation='1' AND age='2' AND class1='2' THEN cnt END) AS C男汉2岁工人
         ,SUM(CAS WHEN  sex='M' AND nation='1' AND age='2' AND class1='3' THEN cnt END) AS C男汉2岁农民
         ,SUM(CAS WHEN  sex='M' AND nation='2' AND age='1' AND class1='1' THEN cnt END) AS C男壮1岁学生
         ,SUM(CAS WHEN  sex='M' AND nation='2' AND age='1' AND class1='2' THEN cnt END) AS C男壮1岁工人
         ,SUM(CAS WHEN  sex='M' AND nation='2' AND age='1' AND class1='3' THEN cnt END) AS C男壮1岁农民
         ,SUM(CAS WHEN  sex='M' AND nation='2' AND age='2' AND class1='1' THEN cnt END) AS C男壮2岁学生
         ,SUM(CAS WHEN  sex='M' AND nation='2' AND age='2' AND class1='2' THEN cnt END) AS C男壮2岁工人
         ,SUM(CAS WHEN  sex='M' AND nation='2' AND age='2' AND class1='3' THEN cnt END) AS C男壮2岁农民
   FROM T GROUP BY region)
SELECT * FROM t1



上面用了XMLTABLE做解析，用可以用传统的CONNECT BY和SUBSTR解析：

CREATE OR REPLACE FUNCTION fsql1(v_tname varchar2,tr  varchar2 ,tc0 varchar2)
RETURN VARCHAR2
IS
   lv_sql  VARCHAR2(4000);
   lv_name VARCHAR2(30);
BEGIN
   SELECT cname2
     INTO lv_name
     FROM eav
    WHERE tname = v_tname
          AND cname = tr
          AND ccode IS NULL;

   lv_sql := 'WITH t AS (
   SELECT '||tr||','||REPLACE(tc0,' ',',')||',COUNT(*) cnt FROM '||v_tname||' GROUP BY '||tr||','||REPLACE(tc0,' ',',')||')
   ,t1 AS (
   SELECT '||tr||' AS '||lv_name;

   FOR lv_rec IN (SELECT SYS_CONNECT_BY_PATH(cname||'='''||ccode||'''',' AND ') AS s1
                        ,'C'||REPLACE(SYS_CONNECT_BY_PATH(cname2,','),',') AS s2
                    FROM
                  (SELECT e.*,t.id
                     FROM eav e
                         ,(SELECT ROWNUM id,SUBSTR(tc0,last_pos,pos-last_pos) AS c
                             FROM (SELECT LAG(pos,1,0) OVER(ORDER BY pos)+1 last_pos,pos
                                    FROM (SELECT INSTR(tc0,' ',1,ROWNUM) pos FROM DUAL CONNECT BY INSTR(tc0,' ',1,ROWNUM)>0
                                          UNION ALL SELECT LENGTH(tc0)+1 FROM DUAL
                                         )
                                  )
                           ORDER BY 1
                          ) t
                    WHERE e.tname = v_tname
                          AND e.cname = t.c
                          AND e.ccode IS NOT NULL
                  ) e2
                  WHERE CONNECT_BY_ISLEAF=1
                  START WITH id=1
                  CONNECT BY id = PRIOR id+1
                  ORDER SIBLINGS BY ccode
                )
   LOOP
      lv_sql := lv_sql ||CHR(10)||'         ,SUM(CAS WHEN '||SUBSTR(lv_rec.s1,5)||' THEN cnt END) AS '||lv_rec.s2;
   END LOOP;
   
   lv_sql := lv_sql ||CHR(10)||'   FROM T GROUP BY '||tr||')
SELECT * FROM t1';

   RETURN lv_sql;
END fsql1;
/




[ 本帖最后由 newkid 于 2010-8-13 02:11 编辑 ]

〇〇

newkid出手就是以一当十

dingjun123

太强大了，很好

cghua

现在有开发工具都有这功能！

trb85919

newkid 发表于 2010-8-13 01:49
我现在就来示范一下，改用静态SQL之后27楼的程序可以变得如何整洁漂亮：

  LOOP
      lv_sql := lv_sql ||CHR(10)||'         ,SUM(CAS WHEN '||SUBSTR(lv_rec.s1,5)||' THEN cnt END) AS '||lv_rec.s2;
   END LOOP;

------>CASE少了个E

   LOOP
      lv_sql := lv_sql ||CHR(10)||'         ,SUM(CASE WHEN '||SUBSTR(lv_rec.s1,5)||' THEN cnt END) AS '||lv_rec.s2;
   END LOOP;
向前辈们学习，不愧为大神级人物，膜拜中...........