20221118_数据库过程_表内容横竖转置

使用实例

-----测试表(因为会在后面增加三个字符，需要注意长度)
SELECT * FROM ZHYW.jzc_all_priv_sjaphz_zzq;

-----执行过程
EXEC zhyw.SHA_table_transpose(‘jzc_all_priv_sjaphz_zzq’,‘zhyw’);

-----执行结果(在原表的表名后面增加’_TR’ 代表转置)
SELECT * FROM ZHYW.jzc_all_priv_sjaphz_zzq_TR;

代码:

in_time 插入时间，num_xl 序列号，COLUMN_NAME 字段名

create or replace procedure zhyw.SHA_table_transpose(tablename in varchar2,owner_in in varchar2 DEFAULT 'ZHYW') as

----tablename  传入表名字，后期会去空格与大写
----owner_in    传入数据库，默认ZHYW,后期会去空格与大写

SQLSTMT0 varchar2(30000);
SQLSTMT1 varchar2(30000);
SQL_STRING varchar2(30000);
COLUMN_NUM number; ----表格字符数
num_xl number;

v_tablename varchar2(300);
v_owner_in  varchar2(300);

v_tablename_bt varchar2(300);

begin


v_tablename:=upper(trim(tablename));
v_owner_in :=upper(trim(owner_in));

-----获取表头按照 COLUMN_ID 顺序
zhyw.shc_drop_retable(upper('table_transpose_tmp_bt'),'ZHYW');
SQL_STRING:='create table ZHYW.table_transpose_tmp_bt as
select a.COLUMN_NAME from ALL_TAB_COLUMNS a where a.TABLE_NAME =upper('''||v_tablename||''')
      and a.OWNER='''||v_owner_in||'''
      order by COLUMN_ID ';
EXECUTE IMMEDIATE (SQL_STRING);


---以第一列算
select COLUMN_NAME into v_tablename_bt from ZHYW.table_transpose_tmp_bt WHERE ROWNUM=1;

---计算行数
zhyw.shc_drop_retable(upper('table_transpose_tmp_hs'),'ZHYW');
SQL_STRING:='create table ZHYW.table_transpose_tmp_hs as
select count(a.'||v_tablename_bt||') hs from '||v_owner_in||'.'||v_tablename||' a  ';
EXECUTE IMMEDIATE (SQL_STRING);



---COLUMN_NUM，增加 in_time，num_xl，COLUMN_NAME
select hs into COLUMN_NUM from ZHYW.table_transpose_tmp_hs;

----获取表头数量，然后创建一个全字符的表格
zhyw.shc_drop_retable(v_tablename||'_TR',v_owner_in);
---清空后创建
SQLSTMT0 := 'create table '||v_owner_in||'.'||v_tablename||'_TR ( ';
--- COLUMN_NUM +3 ，增加 in_time，num_xl，COLUMN_NAME
FOR a IN (select  level num from dual connect by level<=(COLUMN_NUM) ) LOOP
    SQLSTMT0 := SQLSTMT0 || 'COLUMN'||a.num||' varchar2(80),';
END LOOP;
SQLSTMT1 := SUBSTR(SQLSTMT0 , 1 , length( SQLSTMT0 )) ;
SQLSTMT1 :=SQLSTMT1 || 'in_time varchar2(80)，num_xl varchar2(80)，COLUMN_NAME varchar2(80))';
EXECUTE IMMEDIATE (SQLSTMT1);


-----插入数据行数
--select COUNT(COLUMN_NAME) into COLUMN_NUM from ZHYW.table_transpose_tmp_bt;


-----变量声明
    num_xl := 0;
    FOR d IN (select  COLUMN_NAME from ZHYW.table_transpose_tmp_bt) LOOP-----内循环开始
      
         SQLSTMT0 := 'insert into '||v_owner_in||'.'||v_tablename||'_TR  select ';
                      
             FOR a IN (select  level num from dual connect by level<=(COLUMN_NUM) ) LOOP
                 SQLSTMT0 := SQLSTMT0 || 'zhyw.shzc_zfc_zftq_tscl(a.note,''^'||a.num||'^'',''&'')  note'||a.num||',';
             END LOOP;  
                                  
         SQLSTMT0 :=SQLSTMT0 || 'to_char(sysdate,''yyyy/mm/dd hh24:mi:ss'') in_time,'''||num_xl||''' num_xl,'''||d.column_name||'''COLUMN_NAME
                      from  
                      (select listagg (''^''||a.xuhao||''^''||to_char(a.'||d.column_name||'),''&'')  within group (order by rownum) note
                      from 
                      (select a.'||d.column_name||', row_number() over (partition by 1  order by rownum ) xuhao
                      from '||v_owner_in||'.'||v_tablename||' a 
                      order by rownum ) a 
                      group by 1) a ';
          execute immediate (SQLSTMT0);
          commit;  
      num_xl := num_xl + 1;   ----num_xl 增加 1                                                         
      END LOOP;

end;