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

dzhuowei · 发表于 2010-8-6 16:20

呵呵 sql生成器这名字牛了

〇〇 · 发表于 2010-8-6 20:46

下步准备让列方向支持〉2列，用空格隔开的列串做参数

onano · 发表于 2010-8-7 11:11

期待中回去测一下

〇〇 · 发表于 2010-8-7 20:48

饱受单引号折磨后出来的半成品

create or replace function
fsql1(v_tname varchar,tr  varchar ,tc0 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 tren group by '||tr||','||replace(trim(tc),' ',',')||')';

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||'''');
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||' and ';
  end if;
s2:=s2||chr(96+i)||'.cname='''||g2(i)||'''';
END LOOP;
return s1||chr(10)||replace(s2,'tc',''''||tc||'''');
end;
/
select fsql1('a','region','sex nation age class teacher grade','eav')from dual;

〇〇 · 发表于 2010-8-8 07:19

其实用过程语言拆分字符串更方便，昨天糊涂了

〇〇 · 发表于 2010-8-9 11:05

在20楼基础上

insert into eav select 'tren','nation',NULL,'民族' from dual;
insert into eav select 'tren','nation','1','汉' from dual;
insert into eav select 'tren','nation','2','壮' from dual;

insert into eav select 'tren','class1',NULL,'民族' from dual;
insert into eav select 'tren','class1','1','学生' from dual;
insert into eav select 'tren','class1','2','工人' from dual;
insert into eav select 'tren','class1','3','农民' from dual;

alter table tren add nation varchar(2);

alter table tren add class1 varchar(2);

update tren set nation=mod(abs(dbms_random.random),2)+1,class1=mod(abs(dbms_random.random),3)+1;

create or replace function
fsql1(v_tname varchar,tr  varchar ,tc0 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),' ',',')||')';

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;
/
--select fsql1('tren','region','sex age','eav') from dual;
select fsql1('tren','region','sex nation age class1','eav')from dual;

〇〇 · 发表于 2010-8-9 11:15

SQL> with t
  2  as (select class1,sex,nation,age,region,count(*) cnt from tren group by class1,sex,nation,age,region)
  3  ,t1 as(select class1 as 民族
  4  ,sum(case when sex='M' and nation='1' and age='1' and region='1' then cnt end) as c男汉1岁北京
  5  ,sum(case when sex='M' and nation='1' and age='1' and region='2' then cnt end) as c男汉1岁天津
  6  ,sum(case when sex='M' and nation='1' and age='2' and region='1' then cnt end) as c男汉2岁北京
  7  ,sum(case when sex='M' and nation='1' and age='2' and region='2' then cnt end) as c男汉2岁天津
  8  ,sum(case when sex='F' and nation='1' and age='1' and region='1' then cnt end) as c女汉1岁北京
  9  ,sum(case when sex='F' and nation='1' and age='1' and region='2' then cnt end) as c女汉1岁天津
10  ,sum(case when sex='F' and nation='1' and age='2' and region='1' then cnt end) as c女汉2岁北京
11  ,sum(case when sex='F' and nation='1' and age='2' and region='2' then cnt end) as c女汉2岁天津
12  ,sum(case when sex='M' and nation='2' and age='1' and region='1' then cnt end) as c男壮1岁北京
13  ,sum(case when sex='M' and nation='2' and age='1' and region='2' then cnt end) as c男壮1岁天津
14  ,sum(case when sex='M' and nation='2' and age='2' and region='1' then cnt end) as c男壮2岁北京
15  ,sum(case when sex='M' and nation='2' and age='2' and region='2' then cnt end) as c男壮2岁天津
16  ,sum(case when sex='F' and nation='2' and age='1' and region='1' then cnt end) as c女壮1岁北京
17  ,sum(case when sex='F' and nation='2' and age='1' and region='2' then cnt end) as c女壮1岁天津
18  ,sum(case when sex='F' and nation='2' and age='2' and region='1' then cnt end) as c女壮2岁北京
19  ,sum(case when sex='F' and nation='2' and age='2' and region='2' then cnt end) as c女壮2岁天津
20  from t group by class1)
21  select * from t1;

民 C男汉1岁北京 C男汉1岁天津 C男汉2岁北京 C男汉2岁天津 C女汉1岁北京 C女汉1岁天津 C女汉2岁北京 C女汉2岁天津 C男壮1岁北京 C男壮1岁天津
-- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
C男壮2岁北京 C男壮2岁天津 C女壮1岁北京 C女壮1岁天津 C女壮2岁北京 C女壮2岁天津
------------ ------------ ------------ ------------ ------------ ------------
1          24          27          21          15          20          14          30       17    25          25
      21          26          15          19          12          24

3          22          23          18          16          15          29          18       15    25          18
      26          24          18          23          17          20

2          18          19          22          24          18          24          17       23    12          30
      28          19          16          16          29          23

Coast_lichao · 发表于 2010-8-9 15:37

这得要多深的功夫咧！顶啊@

〇〇 · 发表于 2010-8-10 14:49

专门为不带汇总的行列转换写了一个

create or replace function
fsql2(v_tname varchar,tr  varchar ,tc0 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),' ',',')||')';

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 ''min(case when ''';
OPEN rc1 FOR sqlstr;
FETCH rc1 BULK COLLECT INTO g1, g2;
CLOSE rc1;
FOR i IN g1.FIRST ..g1.LAST-1
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 '||g2(g1.LAST)||' 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;
--return sqlstr;
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;
/

create table faq(ID varchar(10),NAME varchar(10),VALUE varchar(10));
insert into faq select * from (
select'1'id,'a1'name,'x1'value from dual union all
select'1','a2','x2'from dual union all
select'2','b1','y1'from dual union all
select'2','b2','y2'from dual union all
select'2','b3','y3'from dual);
commit;
insert into eav values('FAQ','ID',NULL,'ID');
insert into eav select 'FAQ','ID',ID,ID from dual,(select unique id from faq);

insert into eav values('FAQ','NAME',NULL,'NAME');
insert into eav select 'FAQ','NAME',NAME,NAME from dual,(select unique NAME from faq);

insert into eav values('FAQ','VALUE',NULL,'VALUE');
insert into eav select 'FAQ','VALUE',VALUE,VALUE from dual,(select unique VALUE from faq);

commit;

SQL> select fsql2('FAQ','NAME','ID VALUE','eav')from dual;

FSQL2('FAQ','NAME','IDVALUE','EAV')
-------------------------------------------------------------------------------
with t
as (select NAME,ID,VALUE,count(*) cnt from FAQ group by NAME,ID,VALUE)
,t1 as(select NAME as NAME
,min(case when ID='1' then VALUE end) as c1
,min(case when ID='2' then VALUE end) as c2
from t group by NAME)
select * from t1

已用时间:  00: 00: 00.01
SQL> with t
  2  as (select NAME,ID,VALUE,count(*) cnt from FAQ group by NAME,ID,VALUE)
  3  ,t1 as(select NAME as NAME
  4  ,min(case when ID='1' then VALUE end) as c1
  5  ,min(case when ID='2' then VALUE end) as c2
  6  from t group by NAME)
  7  select * from t1;

NAME    C1       C2
---------- ---------- ----------
a1       x1
b2                   y2
a2       x2
b1                   y1
b3                   y3

dingjun123 · 发表于 2010-8-10 15:58

不错，研究一下！

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

！