ORACLE DB行列转换

时间:2021-07-02 00:45:53

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

比如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;

/

----------------------------------------------------------------------------------