最近在论坛上看见很多人都在问如何进行数据库表的行列转换，很多武林高手都各显身手，我自己也写了一个存储过程供大家分享！希望大家能提出更好更宝贵的意见和建议！共勉！ ****************************************************
比如emp中有这样的纪录：
ID NAME DEPT_ID SALARY
001 a01 b01 1000
002 a02 b02 2000
003 a03 b03 3000
经过转换之后，表emp_rc中应该是这样的：
COL_1 COL_2 COL_3 COL_4
ID 001 002 003
NAME a01 a02 a03
DEPT_ID b01 b02 b03
SALARY 1000 2000 3000
****************************************************
源代码如下：
----------------------------------------------------------------------------------
CREATE OR REPLACE procedure ZBTOWN.TRANSFER_RC(tb_in varchar,tb_out varchar) AS
begin
declare
cnt number;
arr_cnt number;
select_cmd varchar2(1000) := '';
create_cmd varchar2(1000) := '';
insert_cmd varchar2(1000) := '';
type arr_type is table of varchar2(100) index by binary_integer;
arr arr_type;
type refcursor is ref cursor;
ref_cv refcursor;
cursor c_cols is
select column_name
from user_tab_columns
where table_name = upper(tb_in);
r_cols c_cols%rowtype;
begin
arr_cnt := 1;
-- drop
begin
execute immediate 'drop table ' || tb_out;
exception when others then
null;
end;
-- create
begin
select_cmd := 'select count(0) as cnt from ' || tb_in;
execute immediate select_cmd into cnt;
create_cmd := 'create table ' || tb_out || '(';
for t in 1..cnt+1 loop
create_cmd := create_cmd || ' col_'|| t || ' varchar2(100),';
end loop;
create_cmd := create_cmd || 'constraint ' || tb_out || '_pk primary key(col_1) using index)';
execute immediate create_cmd;
exception when others then
null;
end;
-- insert
begin
for r_cols in c_cols loop
exit when c_cols%notfound;
insert_cmd := 'insert into '|| tb_out ||' values(';
arr(arr_cnt) := r_cols.column_name;
insert_cmd := insert_cmd || '''' || arr(arr_cnt) || ''',';
select_cmd := 'select ' || r_cols.column_name || ' from ' || tb_in;
open ref_cv for select_cmd;
loop
arr_cnt := arr_cnt + 1;
fetch ref_cv into arr(arr_cnt);
exit when ref_cv%notfound;
insert_cmd := insert_cmd || '''' || arr(arr_cnt) || ''',';
end loop;
insert_cmd := substr(insert_cmd,1,length(insert_cmd)-1) || ')';
execute immediate insert_cmd;
end loop;
exception when others then
null;
end;
-- last commit
commit;
end;
end;
/
----------------------------------------------------------------------------------