二维数组实现行列转换

2010.12.28 需求： 昨天客户有个新的需求，就是根据原始表的数据转行后在报表中显示。其实就是一个行列转换 具体来说，这里简化一些其它需求，只是讲述行列转换的具体内容： 客户有张原始的表(假设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; /