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

〇〇

先来点数据结构

create table eav(tname varchar(20),cname varchar(20),ccode varchar(20),cname2 varchar(40));
insert into eav select 'tren',NULL,NULL,'人口表' from dual;
insert into eav select 'tren','sex',NULL,'性别' from dual;
insert into eav select 'tren','sex','M','男' from dual;
insert into eav select 'tren','sex','F','女' from dual;
insert into eav select 'tren','age',NULL,'年龄' from dual;
insert into eav select 'tren','age','1','1岁' from dual;
insert into eav select 'tren','age','2','2岁' from dual;
insert into eav select 'tren','region',NULL','地区' from dual;
insert into eav select 'tren','region','1','北京' from dual;
insert into eav select 'tren','region','2','天津' from dual;
commit;

create table tren(rn number(10,0),sex varchar(1),age varchar(2),region varchar(3));
insert into tren select level,mod(level,2),mod(level,2),mod(level,2) from dual connect by level<=100;
commit;

〇〇

出错了,哪位帮着看看

select fsqlgen('tren','region','sex','age','eav') from dual;

create or replace function
fsqlgen(v_tname varchar,v_gc1  varchar ,v_gc2 varchar,v_gc3  varchar,v_tcode varchar)
return varchar
is
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(40);
rc1 rc;
g1  vList;
g2  vList;
g3  vList;   

begin
s1:='with t
as (select '||v_gc1||','||v_gc2||','||v_gc3||' from '||v_tname||' group by '||v_gc1||','||v_gc2||','||v_gc3||')';

sqlstr:='SELECT cname,ccode,cname2 FROM '||v_tcode||' WHERE tname=:1 and cname=:2';
   OPEN rc1 FOR sqlstr USING v_tname,v_gc1;
   FETCH rc1 BULK COLLECT INTO g1, g2,g3;
   CLOSE rc1;
   FOR i IN g1.FIRST ..g1.LAST
   LOOP
   if g1(i)=v_gc1 and g2(i)is null then
   s2:='
,t1 as(select '||v_gc1||' as '||g3(i)||',';
else
        return 'error';
   end if;
   END LOOP;
   
return s1||s2;
end;
/

〇〇

自己犯了2个错
1 insert into eav select 'tren','region',NULL','地区' from dual;
2
FOR i IN g1.FIRST ..g1.LAST
   LOOP
   if g1(i)=v_gc1 and g2(i)is null then
   s2:='
,t1 as(select '||v_gc1||' as '||g3(i)||',';
else
        return 'error';
   end if;
   END LOOP;
====>
   FOR i IN g1.FIRST ..g1.LAST
   LOOP
   if g1(i)=v_gc1 and g2(i)is null then
   s2:='
,t1 as(select '||v_gc1||' as '||g3(i)||',';
   end if;
   END LOOP;
if s2 is null then
   return 'error';
end if;

〇〇

用动态sql比用for loop拼笛卡尔积好像要容易些。。。

select 'sum(case when '||a.cname||'='''||a.ccode||''' and '||b.cname||'='''||b.ccode||''' then cnt end)as 人数'|| a.cname2||b.cname2
from
(select * from eav where cname='age' and ccode is not null)a,
(select * from eav where cname='sex' and ccode is not null)b
;

newkid

11G用PIVOT, UNPIVOT

lastwinner

感觉没必要做这事儿

〇〇

我们有几百张表要行列转化,不自动化写得手疼
create table t3_1_1306 as
select ID,
sum(case when ID<>0 then cnt end) c1,
sum(case when ra1=0   then cnt end) c2,
sum(case when ra1=0 and r03=1  then cnt end) c3,
sum(case when ra1=0 and r03=2  then cnt end) c4,
sum(case when ra1>=1 and ra1<=4  then cnt end) c5  ,
sum(case when ra1>=1 and ra1<=4  and r03=1  then cnt end) c6  ,
sum(case when ra1>=1 and ra1<=4  and r03=2  then cnt end) c7 ,
sum(case when ra1>=5 and ra1<=9  then cnt end) c8  ,
sum(case when ra1>=5 and ra1<=9  and r03=1  then cnt end) c9  ,
sum(case when ra1>=5 and ra1<=9  and r03=2  then cnt end) c10 ,
sum(case when ra1>=10 and ra1<=14  then cnt end) c11  ,
sum(case when ra1>=10 and ra1<=14  and r03=1  then cnt end) c12  ,
sum(case when ra1>=10 and ra1<=14  and r03=2  then cnt end) c13 ,
sum(case when ra1>=15 and ra1<=19  then cnt end) c14  ,
sum(case when ra1>=15 and ra1<=19  and r03=1  then cnt end) c15  ,
sum(case when ra1>=15 and ra1<=19  and r03=2  then cnt end) c16 ,
sum(case when ra1>=20 and ra1<=24  then cnt end) c17  ,
sum(case when ra1>=20 and ra1<=24  and r03=1  then cnt end) c18  ,
sum(case when ra1>=20 and ra1<=24  and r03=2  then cnt end) c19 ,
sum(case when ra1>=25 and ra1<=29  then cnt end) c20  ,
sum(case when ra1>=25 and ra1<=29  and r03=1  then cnt end) c21  ,
sum(case when ra1>=25 and ra1<=29  and r03=2  then cnt end) c22 ,
sum(case when ra1>=30 and ra1<=34  then cnt end) c23  ,
sum(case when ra1>=30 and ra1<=34  and r03=1  then cnt end) c24  ,
sum(case when ra1>=30 and ra1<=34  and r03=2  then cnt end) c25 ,
sum(case when ra1>=35 and ra1<=39  then cnt end) c26  ,
sum(case when ra1>=35 and ra1<=39  and r03=1  then cnt end) c27  ,
sum(case when ra1>=35 and ra1<=39  and r03=2  then cnt end) c28 ,
sum(case when ra1>=40 and ra1<=44  then cnt end) c29  ,
sum(case when ra1>=40 and ra1<=44  and r03=1  then cnt end) c30  ,
sum(case when ra1>=40 and ra1<=44  and r03=2  then cnt end) c31 ,
sum(case when ra1>=45 and ra1<=49  then cnt end) c32  ,
sum(case when ra1>=45 and ra1<=49  and r03=1  then cnt end) c33  ,
sum(case when ra1>=45 and ra1<=49  and r03=2  then cnt end) c34 ,
sum(case when ra1>=50 and ra1<=54  then cnt end) c35  ,
sum(case when ra1>=50 and ra1<=54  and r03=1  then cnt end) c36  ,
sum(case when ra1>=50 and ra1<=54  and r03=2  then cnt end) c37 ,
sum(case when ra1>=55 and ra1<=59  then cnt end) c38  ,
sum(case when ra1>=55 and ra1<=59  and r03=1  then cnt end) c39  ,
sum(case when ra1>=55 and ra1<=59  and r03=2  then cnt end) c40 ,
sum(case when ra1>=60 and ra1<=64  then cnt end) c41  ,
sum(case when ra1>=60 and ra1<=64  and r03=1  then cnt end) c42  ,
sum(case when ra1>=60 and ra1<=64  and r03=2  then cnt end) c43 ,
sum(case when ra1>=65 and ra1<=69  then cnt end) c44  ,
sum(case when ra1>=65 and ra1<=69  and r03=1  then cnt end) c45  ,
sum(case when ra1>=65 and ra1<=69  and r03=2  then cnt end) c46 ,
sum(case when ra1>=70 and ra1<=74  then cnt end) c47  ,
sum(case when ra1>=70 and ra1<=74  and r03=1  then cnt end) c48  ,
sum(case when ra1>=70 and ra1<=74  and r03=2  then cnt end) c49 ,
sum(case when ra1>=75 and ra1<=79  then cnt end) c50  ,
sum(case when ra1>=75 and ra1<=79  and r03=1  then cnt end) c51  ,
sum(case when ra1>=75 and ra1<=79  and r03=2  then cnt end) c52 ,
sum(case when ra1>=80 and ra1<=84  then cnt end) c53  ,
sum(case when ra1>=80 and ra1<=84  and r03=1  then cnt end) c54  ,
sum(case when ra1>=80 and ra1<=84  and r03=2  then cnt end) c55 ,
sum(case when ra1>=85 and ra1<=89  then cnt end) c56  ,
sum(case when ra1>=85 and ra1<=89  and r03=1  then cnt end) c57  ,
sum(case when ra1>=85 and ra1<=89  and r03=2  then cnt end) c58 ,
sum(case when ra1>=90 and ra1<=94  then cnt end) c59  ,
sum(case when ra1>=90 and ra1<=94  and r03=1  then cnt end) c60  ,
sum(case when ra1>=90 and ra1<=94  and r03=2  then cnt end) c61 ,
sum(case when ra1>=95 and ra1<=99  then cnt end) c62  ,
sum(case when ra1>=95 and ra1<=99  and r03=1  then cnt end) c63  ,
sum(case when ra1>=95 and ra1<=99  and r03=2  then cnt end) c64 ,
sum(case when ra1>=100 then cnt end) c65  ,
sum(case when ra1>=100 and r03=1  then cnt end) c66  ,
sum(case when ra1>=100 and r03=2  then cnt end) c67
from (
select /*+ parallel(ren1306,8)*/ substr(ID,1,6) ID,count(*)cnt,ra1,r03
from ren ren1306
group by substr(ID,1,6),ra1,r03
)
group by ID
order by ID;

onano

支持OO，这个做出来就太方便了

〇〇

第一个勉强能用的版本，

只能在10g以上用，因为单引号的问题
行方向只支持1个group by
列方向只支持2个group by

1. 
   
2. create table eav(tname varchar(20),cname varchar(20),ccode varchar(20),cname2 varchar(40));
   
3. insert into eav select 'tren',NULL,NULL,'人口表' from dual;
   
4. insert into eav select 'tren','sex',NULL,'性别' from dual;
   
5. insert into eav select 'tren','sex','M','男' from dual;
   
6. insert into eav select 'tren','sex','F','女' from dual;
   
7. insert into eav select 'tren','age',NULL,'年龄' from dual;
   
8. insert into eav select 'tren','age','1','1岁' from dual;
   
9. insert into eav select 'tren','age','2','2岁' from dual;
   
10. insert into eav select 'tren','region',NULL,'地区' from dual;
    
11. insert into eav select 'tren','region','1','北京' from dual;
    
12. insert into eav select 'tren','region','2','天津' from dual;
    
13. commit;
    
14. 
    
15. create table tren(rn number(10,0),sex varchar(1),age varchar(2),region varchar(3));
    
16. insert into tren select level,
    
17. decode(mod(abs(dbms_random.random),2),0,'M','F'),
    
18. mod(abs(dbms_random.random),2)+1,
    
19. mod(abs(dbms_random.random),2)+1 from dual connect by level<=1000;
    
20. commit;
    
21. 
    
22. 
    
23. 
    
24. create or replace function
    
25. fsqlgen(v_tname varchar,v_gc1  varchar ,v_gc2 varchar,v_gc3  varchar,v_tcode varchar)
    
26. return varchar
    
27. is
    
28. sqlstr varchar(4000);
    
29. s1 varchar(4000);
    
30. s2 varchar(4000);
    
31. s3 varchar(4000);
    
32. s4 varchar(4000);
    
33. TYPE rc IS REF CURSOR;
    
34. TYPE nlist IS TABLE OF NUMBER;
    
35. TYPE vlist IS TABLE OF VARCHAR2(400);
    
36. rc1 rc;
    
37. g1  vList;
    
38. g2  vList;
    
39. g3  vList;   
    
40. 
    
41. begin
    
42. s1:='with t
    
43. as (select '||v_gc1||','||v_gc2||','||v_gc3||',count(*)cnt from '||v_tname||' group by '||v_gc1||','||v_gc2||','||v_gc3||')';
    
44. 
    
45. sqlstr:='SELECT cname,ccode,cname2 FROM '||v_tcode||' WHERE tname=:1 and cname=:2';
    
46.    OPEN rc1 FOR sqlstr USING v_tname,v_gc1;
    
47.    FETCH rc1 BULK COLLECT INTO g1, g2,g3;
    
48.    CLOSE rc1;
    
49.    FOR i IN g1.FIRST ..g1.LAST
    
50.    LOOP
    
51.    if g1(i)=v_gc1 and g2(i)is null then
    
52.    s2:='
    
53. ,t1 as(select '||v_gc1||' as '||g3(i)||chr(10);
    
54.    end if;
    
55.    END LOOP;
    
56. if s2 is null then
    
57.         return 'error';
    
58. end if;
    
59. 
    
60. sqlstr:=q'[select 'sum(case when '||a.cname||'='''||a.ccode||''' and '||b.cname||'='''||b.ccode||''' then cnt end)as c'|| a.cname2||b.cname2
    
61. from
    
62. (select * from ]'||v_tcode||q'[ where cname=']'||v_gc2||q'[' and ccode is not null)a,
    
63. (select * from ]'||v_tcode||q'[ where cname=']'||v_gc3||q'[' and ccode is not null)b
    
64. ]' ;
    
65.    OPEN rc1 FOR sqlstr;
    
66.    FETCH rc1 BULK COLLECT INTO g1;
    
67.    CLOSE rc1;
    
68.    FOR i IN g1.FIRST ..g1.LAST
    
69.    LOOP
    
70.    s2:=s2||','||g1(i)||chr(10);
    
71.    END LOOP;
    
72. s3:='group by '||v_gc1;
    
73. return s1||s2||'from t '||s3||')
    
74. select * from t1';
    
75. end;
    
76. /
    

复制代码

SQL> select fsqlgen('tren','region','sex','age','eav') from dual; FSQLGEN('TREN','REGION','SEX','AGE','EAV') ------------------------------------------------------------------------------------------------------------------------- with t as (select region,sex,age,count(*)cnt from tren group by region,sex,age) ,t1 as(select region as 地区 ,sum(case when sex='M' and age='1' then cnt end)as c男1岁 ,sum(case when sex='F' and age='1' then cnt end)as c女1岁 ,sum(case when sex='M' and age='2' then cnt end)as c男2岁 ,sum(case when sex='F' and age='2' then cnt end)as c女2岁 from t group by region) select * from t1 SQL> with t   2  as (select region,sex,age,count(*)cnt from tren group by region,sex,age)   3  ,t1 as(select region as 地区   4  ,sum(case when sex='M' and age='1' then cnt end)as c男1岁   5  ,sum(case when sex='F' and age='1' then cnt end)as c女1岁   6  ,sum(case when sex='M' and age='2' then cnt end)as c男2岁   7  ,sum(case when sex='F' and age='2' then cnt end)as c女2岁   8  from t group by region)   9  select * from t1; 地      C男1岁     C女1岁     C男2岁     C女2岁 --- ---------- ---------- ---------- ---------- 1          126        102        136        123 2          142        125        124        122 SQL> select fsqlgen('tren','sex','region','age','eav') from dual; FSQLGEN('TREN','SEX','REGION','AGE','EAV') ------------------------------------------------------------------------------------------------------------------------- with t as (select sex,region,age,count(*)cnt from tren group by sex,region,age) ,t1 as(select sex as 性别 ,sum(case when region='1' and age='1' then cnt end)as c北京1岁 ,sum(case when region='1' and age='2' then cnt end)as c北京2岁 ,sum(case when region='2' and age='1' then cnt end)as c天津1岁 ,sum(case when region='2' and age='2' then cnt end)as c天津2岁 from t group by sex) select * from t1 SQL> with t   2  as (select sex,region,age,count(*)cnt from tren group by sex,region,age)   3  ,t1 as(select sex as 性别   4  ,sum(case when region='1' and age='1' then cnt end)as c北京1岁   5  ,sum(case when region='1' and age='2' then cnt end)as c北京2岁   6  ,sum(case when region='2' and age='1' then cnt end)as c天津1岁   7  ,sum(case when region='2' and age='2' then cnt end)as c天津2岁   8  from t group by sex)   9  select * from t1;     C北京1岁   C北京2岁   C天津1岁   C天津2岁 - ---------- ---------- ---------- ---------- M        126        136        142        124 F        102        123        125        122 SQL> select fsqlgen('tren','age','sex','region','eav') from dual; FSQLGEN('TREN','AGE','SEX','REGION','EAV') ------------------------------------------------------------------------------------------------------------------------- with t as (select age,sex,region,count(*)cnt from tren group by age,sex,region) ,t1 as(select age as 年龄 ,sum(case when sex='M' and region='1' then cnt end)as c男北京 ,sum(case when sex='F' and region='1' then cnt end)as c女北京 ,sum(case when sex='M' and region='2' then cnt end)as c男天津 ,sum(case when sex='F' and region='2' then cnt end)as c女天津 from t group by age) select * from t1 SQL> with t   2  as (select age,sex,region,count(*)cnt from tren group by age,sex,region)   3  ,t1 as(select age as 年龄   4  ,sum(case when sex='M' and region='1' then cnt end)as c男北京   5  ,sum(case when sex='F' and region='1' then cnt end)as c女北京   6  ,sum(case when sex='M' and region='2' then cnt end)as c男天津   7  ,sum(case when sex='F' and region='2' then cnt end)as c女天津   8  from t group by age)   9  select * from t1; 年    C男北京    C女北京    C男天津    C女天津 -- ---------- ---------- ---------- ---------- 1         126        102        142        125 2         136        123        124        122 SQL>

[ 本帖最后由 〇〇 于 2010-8-6 15:40 编辑 ]

wabjtam123

TOM写过的行列转换通用包

procedure pivot(p_max_cols       in number default NULL,
                  p_max_cols_query in varchar2 default NULL,
                  p_field_query    in varchar2,
                  p_query          in varchar2,
                  p_anchor         in t_array,
                  p_pivot          in t_array,
                  p_cursor         in out refcursor) as
    l_max_cols number;
    l_query    long;
    l_cnames   t_array;
    l_field    varchar2(50);
    i          number;
    cur        refCursor;
  begin
    i       := 0;
    l_query := 'select ';
    for i in 1 .. p_anchor.count loop
      l_query := l_query || p_anchor(i) || ',';
    end loop;
    if (p_max_cols is not null) then
      l_max_cols := p_max_cols;
    elsif (p_max_cols_query is not null) then
      execute immediate p_max_cols_query
        into l_max_cols;
      for i in 1 .. l_max_cols loop
        for j in 1 .. p_pivot.count loop
          l_query := l_query || 'max(decode(rn,' || i || ',' || p_pivot(j) ||
                     ',null)) ' || p_pivot(j) || '_' || i || ',';
        end loop;
      end loop;
    elsif (p_field_query is not null) then
      open cur for p_field_query;
      loop
        fetch cur
          into l_field;
        exit when cur%notfound;
        i := i + 1;
        --标识符长度不能超过18
        if (length(l_field) > 18) then
          l_field := substr(l_field, 1, 18);
        end if;
        for j in 1 .. p_pivot.count loop
          l_query := l_query || 'max(decode(rn,' || l_field || ',' ||
                     p_pivot(j) || ',null)) ' || '"' || 'ATTR_' || l_field || '"' || ',';
          /*
           l_query := l_query || 'max(decode(rn,' || i || ',' || p_pivot(j) ||
                      ',null)) ' || '"' || 'ATTR_' || l_field || '"' || ',';
                      DBMS_OUTPUT.put_line(l_query);
          */
        end loop;
      end loop;
    else
      raise_application_error(-20001, 'Cannot figure out max cols');
    end if;

    l_query := rtrim(l_query, ',') || ' from ( ' || p_query ||
               ') group by ';
    for i in 1 .. p_anchor.count loop
      l_query := l_query || p_anchor(i) || ',';
    end loop;
    l_query := rtrim(l_query, ',');
    execute immediate 'alter session set cursor_sharing=force';
    open p_cursor for l_query;
    execute immediate 'alter session set cursor_sharing=exact';
  end;