--建立字段名名 create table colName as select column_name from User_Tab_Columns t where table_name = '表' ; --建立结果表 create table resultTable as select f1 as f from 原表.declare sqlstr varchar2(500); v_FN ttt.column_name%type; cursor c_F is select column_name from ttt begin sqlstr := ''; open c_F; loop fetch c_F into v_FN, v_type; exit when C_F%notfound;
sqlstr := 'insert into resultTable select ' || v_FN || ' as f from 原表'; -- dbms_output.put_line(sqlstr); EXECUTE IMMEDIATE sqlstr; -- exit when C_F%notfound; end loop; close C_F; end;--结果 select * from resultTable;
谢谢leecyi(leecyi)的回复,稍等一下马上接帖。顺便问一下,oracle中有没有这样的函数,例如: select 函数(F1,F2,F3) from Table 直接实现这样的功能。
create table colName as
select column_name
from User_Tab_Columns t where table_name = '表' ;
--建立结果表
create table resultTable
as
select f1 as f from 原表.declare
sqlstr varchar2(500);
v_FN ttt.column_name%type;
cursor c_F is
select column_name from ttt
begin
sqlstr := '';
open c_F;
loop
fetch c_F
into v_FN, v_type;
exit when C_F%notfound;
sqlstr := 'insert into resultTable select ' || v_FN || ' as f from 原表';
-- dbms_output.put_line(sqlstr);
EXECUTE IMMEDIATE sqlstr;
-- exit when C_F%notfound;
end loop;
close C_F;
end;--结果
select * from resultTable;