17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure proc(tabname in varchar2,
col1 in varchar2,
col2 in varchar2,
col3 in varchar2,
viewname in varchar2 default 'v_tmp')
as
sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' ';
c1 sys_refcursor;
v1 varchar2(100);
begin
open c1 for 'select distinct to_char('||col2||') from '||tabname;
loop
fetch c1 into v1;
exit when c1%notfound;
sqlstr:=sqlstr||'
,max(decode('||col2||','''||v1||''','||col3||'))"'||v1||'"';
end loop;
close c1;
sqlstr:=sqlstr||' from '||tabname||' group by '||col1;
execute immediate sqlstr;
end proc;
begin
proc('tab','subject','class','counter');
end;
--结果
select * from v_tmp;
SUBJECT 一班 三班 二班
数学 44
英语 36 44
语文 33 39
--看到生成的代码
create or replace view v_tmp as select subject
,max(decode(class,'一班',counter))"一班"
,max(decode(class,'三班',counter))"三班"
,max(decode(class,'二班',counter))"二班" from tab group by subject
declare
tabname varchar2(20):='XXX';--'XXX'分别用相应的表名和字段名代替
col1 varchar2(10):='XXX';
col2 varchar2(10):='XXX';
col3 varchar2(10):='XXX';
viewname in varchar2(10):= 'v_tmp';
sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||col1||' ';
c1 sys_refcursor;
v1 varchar2(100);
begin
open c1 for 'select distinct to_char('||col2||') from '||tabname;
loop
fetch c1 into v1;
exit when c1%notfound;
sqlstr:=sqlstr||'
,max(decode('||col2||','''||v1||''','||col3||'))"'||v1||'"';
end loop;
close c1;
sqlstr:=sqlstr||' from '||tabname||' group by '||col1;
--execute immediate sqlstr;
dbms_output.put_line(sqlstr);
end;
select * from tab
pivot(
sum(counter) for class in ('one' as one,'two' as two,'three' as three)
)
order by subject;