最近在论坛上看见很多人都在问如何进行数据库表的行列转换,很多武林高手都各显身手,我自己也写了一个存储过程供大家分享!希望大家能提出更好更宝贵的意见和建议!共勉! ****************************************************
比如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; /
----------------------------------------------------------------------------------