一句sql实现等高人出现吧,, 我的笨方法:declare c1 varchar2(10); c2 varchar2(10); c3 varchar2(10); c4 varchar2(10); r1 varchar2(100); r2 varchar2(100); r3 varchar2(100); r4 varchar2(100); sqlstr varchar2(4000); cursor c_emp is select * from t13; begin r1:='select '; r2:='select '; r3:='select '; r4:='select '; open c_emp; LOOP FETCH c_emp INTO c1,c2,c3,c4; EXIT WHEN c_emp%NOTFOUND; r1:=r1||''''||c1||''''||' a,'; r2:=r2||''''||c2||''''||' b,'; r3:=r3||''''||c3||''''||' c,'; r4:=r4||''''||c4||''''||' d,';
end loop; close c_emp; r1:=substr(r1,1,length(r1)-1)||' from dual union all '; r2:=substr(r2,1,length(r2)-1)||' from dual union all '; r3:=substr(r3,1,length(r3)-1)||' from dual union all '; r4:=substr(r4,1,length(r4)-1)||' from dual '; dbms_output.put_line(r1||r2||r3||r4); --select 'a' a,'A' a,'1' a,'I' a from dual union all select 'b' b,'B' b,'2' b,'II' b from dual union all select 'c' c,'C' c,'3' c,'III' c from dual union all select 'd' d,'D' d,'4' d,'IV' d from dual --sqlstr:=r1||r2||r3||r4; --执行sqlstr返回结果集 end;
我的笨方法:declare
c1 varchar2(10);
c2 varchar2(10);
c3 varchar2(10);
c4 varchar2(10);
r1 varchar2(100);
r2 varchar2(100);
r3 varchar2(100);
r4 varchar2(100);
sqlstr varchar2(4000);
cursor c_emp is select * from t13;
begin
r1:='select ';
r2:='select ';
r3:='select ';
r4:='select ';
open c_emp;
LOOP
FETCH c_emp INTO c1,c2,c3,c4;
EXIT WHEN c_emp%NOTFOUND;
r1:=r1||''''||c1||''''||' a,';
r2:=r2||''''||c2||''''||' b,';
r3:=r3||''''||c3||''''||' c,';
r4:=r4||''''||c4||''''||' d,';
end loop;
close c_emp;
r1:=substr(r1,1,length(r1)-1)||' from dual union all ';
r2:=substr(r2,1,length(r2)-1)||' from dual union all ';
r3:=substr(r3,1,length(r3)-1)||' from dual union all ';
r4:=substr(r4,1,length(r4)-1)||' from dual ';
dbms_output.put_line(r1||r2||r3||r4);
--select 'a' a,'A' a,'1' a,'I' a from dual union all select 'b' b,'B' b,'2' b,'II' b from dual union all select 'c' c,'C' c,'3' c,'III' c from dual union all select 'd' d,'D' d,'4' d,'IV' d from dual
--sqlstr:=r1||r2||r3||r4;
--执行sqlstr返回结果集
end;
动态就不好说了。