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','小丽','男','攀枝花'); 。 。
--过程: 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; /
--如下,动态过程 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;
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;
昨天客户有个新的需求,就是根据原始表的数据转行后在报表中显示。其实就是一个行列转换
具体来说,这里简化一些其它需求,只是讲述行列转换的具体内容:
客户有张原始的表(假设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;