create procudure test as select id, max(decode(name,'a',pwd,null) as a, max(decode(name,'b',pwd,null) as b, max(decode(name,'c',pwd,null) as c, group by id; order by 1;这么写对不?
代码有些冗长,有些地方完全可以放在一起,也有更好的方法,参考一下吧:create or replace procedure p_hl authid current_user is v_cur sys_refcursor; v_str1 varchar2(2000) default ''; v_str2 varchar2(2000) default ''; v_str3 varchar2(2000) default ''; v_zd varchar2(100) default ''; v_data varchar2(1000) := ''; begin open v_cur for select id from test_hl; loop fetch v_cur into v_zd; if v_zd is null then v_zd := 'null'; end if; exit when v_cur%notfound; v_data := v_data || '''' || v_zd || '''"' || v_zd || '",'; end loop; close v_cur; v_str1 := v_str1 || 'union all select ''id'' "id", ' || trim(both ',' from v_data) || ' from dual '; v_data := ''; open v_cur for select name from test_hl; loop fetch v_cur into v_zd; if v_zd is null then v_zd := 'null'; end if; exit when v_cur%notfound; v_data := v_data || '''' || v_zd || ''','; end loop; close v_cur; v_str2 := v_str2 || 'union all select ''name'', ' || trim(both ',' from v_data) || ' from dual '; v_data := ''; open v_cur for select pwd from test_hl; loop fetch v_cur into v_zd; if v_zd is null then v_zd := 'null'; end if; exit when v_cur%notfound; v_data := v_data || '''' || v_zd || ''','; end loop; close v_cur; v_str3 := v_str3 || 'union all select ''pwd'', ' || trim(both ',' from v_data) || ' from dual '; dbms_output.put_line('create or replace view hl_vw as ' || substr(v_str1 || v_str2 || v_str3, 11)); execute immediate 'create or replace view hl_vw as ' || substr(v_str1 || v_str2 || v_str3, 11); end p_hl; 最好得到的视图hl_vw 就是你要的答案。
select id,
max(decode(name,'a',pwd,null) as a,
max(decode(name,'b',pwd,null) as b,
max(decode(name,'c',pwd,null) as c,
group by id;
order by 1;这么写对不?
v_cur sys_refcursor;
v_str1 varchar2(2000) default '';
v_str2 varchar2(2000) default '';
v_str3 varchar2(2000) default '';
v_zd varchar2(100) default '';
v_data varchar2(1000) := '';
begin
open v_cur for
select id from test_hl;
loop
fetch v_cur
into v_zd;
if v_zd is null then
v_zd := 'null';
end if;
exit when v_cur%notfound;
v_data := v_data || '''' || v_zd || '''"' || v_zd || '",';
end loop;
close v_cur;
v_str1 := v_str1 || 'union all select ''id'' "id", ' ||
trim(both ',' from v_data) || ' from dual ';
v_data := '';
open v_cur for
select name from test_hl;
loop
fetch v_cur
into v_zd;
if v_zd is null then
v_zd := 'null';
end if;
exit when v_cur%notfound;
v_data := v_data || '''' || v_zd || ''',';
end loop;
close v_cur;
v_str2 := v_str2 || 'union all select ''name'', ' ||
trim(both ',' from v_data) || ' from dual ';
v_data := '';
open v_cur for
select pwd from test_hl;
loop
fetch v_cur
into v_zd;
if v_zd is null then
v_zd := 'null';
end if;
exit when v_cur%notfound;
v_data := v_data || '''' || v_zd || ''',';
end loop;
close v_cur;
v_str3 := v_str3 || 'union all select ''pwd'', ' ||
trim(both ',' from v_data) || ' from dual '; dbms_output.put_line('create or replace view hl_vw as ' ||
substr(v_str1 || v_str2 || v_str3, 11));
execute immediate 'create or replace view hl_vw as ' ||
substr(v_str1 || v_str2 || v_str3, 11);
end p_hl;
最好得到的视图hl_vw 就是你要的答案。
create view view_test as
select id,
max(decode(name,'a',pwd)) as a,
max(decode(name,'b',pwd)) as b,
max(decode(name,'c',pwd)) as c
group by id;
order by 1;
不固定的话可以参考下
http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html