求动态的行转动态的列

看看这个吧需求：
      昨天客户有个新的需求，就是根据原始表的数据转行后在报表中显示。其实就是一个行列转换
具体来说，这里简化一些其它需求，只是讲述行列转换的具体内容：
客户有张原始的表(假设temp1)，表中只有4个栏位(假设id,name,sex,provience)，但是表中数据量不确定，随时会改变。
现在客户要每跑一次批次就把temp1表的信息转换到另一张表(假设temp2)，需要注意的是，temp2表的栏位跟temp1的数据是对应的
举个例子来说，
假设 temp1表数据有4条：
ID         NAME  SEX  PROVENCE
---------- ----- ---- ----------
XQ07020001 张三   男  成都
XQ07020002 李四   男  眉山
XQ07020003 王五   女  乐山
XQ07020004 刘倩   男  南充

而现在客户由于报表上要显示temp1转换后的temp2中的数据，批次中固定只会最多抓取temp1表的10条数据
一个一个的写入temp2与之对应的栏位。也就是说现在temp2表结构式固定的，
如下：(其中最后两列是异动日期txdat和移动时间txtm，不管temp1中数据有多少，这两个栏位都是存在的！)
COL1       COL2       COL3       COL4       COL5 ... COL10  TXDAT    TXTM
-------------------------------------------------------------------------------
XQ07020001 XQ07020002 XQ07020003 XQ07020004                 20101228 13125000
张三       李四       王五       刘倩                       20101228 13125000
男         男         女         男                         20101228 13125000
成都       眉山       乐山       南充                       20101228 13125000

当源数据temp1中不足10笔数据的时候，temp2后面小于10的栏位就是空值，如上数据。

这里需要补充一点最关键的是，栏位的类型和长度，COL1，COL2，COL3.都是varchar2(20)，
而原表temp1中各个栏位都是varchar2，长度也不超过10，这就对后面的实现操作就简单多了。

测试SQL脚本：
DROP TABLE temp2;

CREATE TABLE temp1 (id VARCHAR2(10),name VARCHAR2(10),sex VARCHAR2(6),provence VARCHAR2(20) );
CREATE TABLE temp2 (col1 VARCHAR2(20),col2 VARCHAR2(20),col3 VARCHAR2(20),col4 VARCHAR2(20),col5 VARCHAR2(20)
                    ,col6 VARCHAR2(20),col7 VARCHAR2(20),col8 VARCHAR2(20),col9 VARCHAR2(20),col10 VARCHAR2(20)
                    ,txdat VARCHAR2(8),txtm VARCHAR2(8)) ;

INSERT INTO temp1 VALUES('XQ07020001','张三','男','成都');
INSERT INTO temp1 VALUES('XQ07020002','李四','男','眉山');
INSERT INTO temp1 VALUES('XQ07020003','王五','女','乐山');
INSERT INTO temp1 VALUES('XQ07020004','刘倩','男','南充');
INSERT INTO temp1 VALUES('XQ07020005','潘金莲','女','泸州');
INSERT INTO temp1 VALUES('XQ07020006','西门庆','男','宜宾');
INSERT INTO temp1 VALUES('XQ07020007','赵六','女','雅安');
INSERT INTO temp1 VALUES('XQ07020008','刘德华','男','攀枝花');
INSERT INTO temp1 VALUES('XQ07020009','黎明','男','宜宾');
INSERT INTO temp1 VALUES('XQ07020010','春春','女','雅安');
INSERT INTO temp1 VALUES('XQ07020011','小丽','男','攀枝花');
。
。

需求基本如上，简化了其它逻辑，只考虑行列转换这个逻辑。后面我还会讨论temp2表结构不固定的情况。
由于这个需求应该说是固定的行列转换，单纯的group+Max 就能实现。但是我这里采用的二维数组来实现，批次调用过程来做。

--过程：
CREATE OR REPLACE PROCEDURE two_table_arrary_test
AS
   --定义二维数组:
   TYPE one_table_type IS TABLE OF VARCHAR(200) INDEX BY BINARY_INTEGER;
   TYPE two_table_type IS TABLE OF one_table_type INDEX BY BINARY_INTEGER;
   rec_arr       two_table_type;

   --定义游标,获取来源资料集
   CURSOR cur_temp1 IS SELECT id,name,sex,provence FROM temp1 ORDER BY id;
   rec_temp1     cur_temp1%ROWTYPE;

   wrk_txdat     VARCHAR2(8);    --异动日期
   wrk_txtm      VARCHAR2(8);    --异动时间
   temp_cnt          NUMBER  :=0;    --数组二维下标

BEGIN
      --获取异动日期和时间
      SELECT TO_CHAR(sysdate,'YYYY')||TO_CHAR(sysdate,'MM')||TO_CHAR(sysdate,'DD') into wrk_txdat FROM dual;
      SELECT TO_CHAR(sysdate,'hh24miss')||'00' into wrk_txtm FROM dual;

      --每次清掉temp2中数据
      DELETE FROM temp2;

      OPEN cur_temp1;
      LOOP
          FETCH cur_temp1 INTO rec_temp1;
          EXIT WHEN cur_temp1%NOTFOUND;
              --初始化数组
              rec_arr(0)(temp_cnt) := rec_temp1.id;
              rec_arr(1)(temp_cnt) := rec_temp1.name;
              rec_arr(2)(temp_cnt) := rec_temp1.sex;
              rec_arr(3)(temp_cnt) := rec_temp1.provence;

              temp_cnt := temp_cnt + 1;
      END LOOP;
      CLOSE cur_temp1;

      --每次只抓取10笔资料，不足10笔补空
      IF temp_cnt < 10 THEN
         FOR i IN temp_cnt..9 loop
             rec_arr(0)(temp_cnt) := '' ;
             rec_arr(1)(temp_cnt) := '' ;
             rec_arr(2)(temp_cnt) := '' ;
             rec_arr(3)(temp_cnt) := '' ;
             temp_cnt := temp_cnt + 1 ;
         END LOOP;
      END IF;

      --往temp2插入数据
      FOR i IN 0..3 LOOP
           INSERT INTO temp2 (col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,txdat,txtm)
               VALUES (rec_arr(i)(0),rec_arr(i)(1),rec_arr(i)(2),rec_arr(i)(3),rec_arr(i)(4)
                  ,rec_arr(i)(5),rec_arr(i)(6),rec_arr(i)(7),rec_arr(i)(8),rec_arr(i)(9)
                  ,wrk_txdat,wrk_txtm);
      END LOOP;
      COMMIT;

EXCEPTION
   WHEN OTHERS THEN
     raise_application_error(-20102,'** two_table_arrary_test error **' ||sqlerrm);
END two_table_arrary_test;
/


客户需求已经实现，由于temp2表结构是固定的，很单纯，因为对于客户来说，报表中显示的栏位肯定是固定的，
也就是temp2为什么每次只会抓取temp1中10笔资料。

下面来讨论假设temp2表结构不固定的情况。也就是说temp1中数据有几笔，temp2中col就有几个，对应col1,col2,col3....

--如下，动态过程
CREATE OR REPLACE PROCEDURE two_table_arrary_test2
AS
   --定义二维数组:
   TYPE one_table_type IS TABLE OF VARCHAR(200) INDEX BY BINARY_INTEGER;
   TYPE two_table_type IS TABLE OF one_table_type INDEX BY BINARY_INTEGER;
   rec_arr       two_table_type;

   --定义游标,获取来源资料集
   CURSOR cur_temp1 IS SELECT id,name,sex,provence FROM temp1 ORDER BY id;
   rec_temp1     cur_temp1%ROWTYPE;

   wrk_txdat     VARCHAR2(8);    --异动日期
   wrk_txtm      VARCHAR2(8);    --异动时间
   temp_cnt          NUMBER  :=0;    --数组二维下标
   cnt           NUMBER  :=0;    --判断是否temp3存在
   create_sql    VARCHAR2(4000); --动态create temp3的SQL
   insert_sql    VARCHAR2(4000); --动态insert temp3的SQL

BEGIN
      --获取异动日期和时间
      SELECT TO_CHAR(sysdate,'YYYY')||TO_CHAR(sysdate,'MM')||TO_CHAR(sysdate,'DD') into wrk_txdat FROM dual;
      SELECT TO_CHAR(sysdate,'hh24miss')||'00' into wrk_txtm FROM dual;

      --判断表temp3是否存在，存在就drop
      SELECT Count(*) INTO cnt FROM user_tables WHERE table_name='TEMP2';
      IF cnt>0 THEN
         EXECUTE IMMEDIATE 'drop table temp2';
      END IF;

      OPEN cur_temp1;
      LOOP
          FETCH cur_temp1 INTO rec_temp1;
          EXIT WHEN cur_temp1%NOTFOUND;
              --初始化数组
              rec_arr(0)(temp_cnt) := rec_temp1.id;
              rec_arr(1)(temp_cnt) := rec_temp1.name;
              rec_arr(2)(temp_cnt) := rec_temp1.sex;
              rec_arr(3)(temp_cnt) := rec_temp1.provence;

              temp_cnt := temp_cnt + 1;
      END LOOP;
      CLOSE cur_temp1;
      /*
      --每次只抓取10笔资料
      IF temp_cnt < 10 THEN
         FOR i IN temp_cnt..9 loop
             rec_arr(0)(temp_cnt) := '' ;
             rec_arr(1)(temp_cnt) := '' ;
             rec_arr(2)(temp_cnt) := '' ;
             rec_arr(3)(temp_cnt) := '' ;
             temp_cnt := temp_cnt + 1 ;
         END LOOP;
      END IF;
      */

      --动态创建表temp3
      create_sql:='create table temp2 (';
      FOR i IN 1..temp_cnt LOOP
              create_sql:=create_sql||'col'||i||' varchar2(20),';
      END LOOP;
      create_sql:=create_sql||'txdat  varchar2(8),txtm varchar2(8))';
      EXECUTE IMMEDIATE create_sql;

      --动态往temp3插入数据
      FOR i IN 0..3 LOOP
          insert_sql:='insert into temp2 (';
          FOR j IN 1..temp_cnt LOOP
              insert_sql:=insert_sql||'col'||j||',';
          END LOOP;
          insert_sql:=insert_sql||'txdat,txtm) values(''';
          FOR j IN 0..temp_cnt-1 LOOP
              insert_sql:=insert_sql||rec_arr(i)(j)||''',''';
          END LOOP;
          insert_sql:=insert_sql||wrk_txdat||''','''||wrk_txtm||''')';
          EXECUTE IMMEDIATE insert_sql;
      END LOOP;
      COMMIT;

EXCEPTION
   WHEN OTHERS THEN
     raise_application_error(-20102,'** two_table_arrary_test2 error **' ||sqlerrm);
END two_table_arrary_test2;

调试易

求动态的行转动态的列

解决方案 »