一个二维表查询的实现

1、示例表结构及测试数据

create table ROW_COL_CONVER ( CODE VARCHAR2(10), MONTH VARCHAR2(10), NUM VARCHAR2(10) ) ; insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00001', '200401', '3'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00001', '200402', '1'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00001', '200403', '1'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00001', '200404', '3'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00001', '200405', '3'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00001', '200604', '1'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00002', '200401', '3'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00002', '200402', '2'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00002', '200404', '1'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C00002', '200405', '1'); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C9999', '200401', '5 '); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C9999', '200402', '2 '); insert into Row_Col_Conver (CODE, MONTH, NUM)values ('C9999', '200403', '2 '); commit;

2、用表的临时表建表语句

create global temporary table T_MONTH_200401 ( CODE VARCHAR2(10), COL_200401 VARCHAR2(10) ) on commit delete rows; create global temporary table T_MONTH_200402 ( CODE VARCHAR2(10), COL_200402 VARCHAR2(10) ) on commit delete rows; create global temporary table T_MONTH_200403 ( CODE VARCHAR2(10), COL_200403 VARCHAR2(10) ) on commit delete rows; create global temporary table T_MONTH_200404 ( CODE VARCHAR2(10), COL_200404 VARCHAR2(10) ) on commit delete rows; create global temporary table T_MONTH_200405 ( CODE VARCHAR2(10), COL_200405 VARCHAR2(10) ) on commit delete rows; create global temporary table T_MONTH_200604 ( CODE VARCHAR2(10), COL_200604 VARCHAR2(10) ) on commit delete rows; create global temporary table T_MONTH_XXXXXX ( CODE VARCHAR2(10), COL_XXXXXX VARCHAR2(10) ) on commit delete rows;

3、REF_CUR包

CREATE OR REPLACE PACKAGE IRS_REF IS TYPE t_cursor IS REF CURSOR; end IRS_REF; /

4、实现二维查询的存储过程

CREATE OR REPLACE PROCEDURE SP_ROW_COL_CONVER(P_CUR OUT IRS_REF.T_CURSOR) AS BEGIN --生成一个包含所有可能的模板表 INSERT INTO T_MONTH_XXXXXX SELECT 'C00001', '' FROM ROW_COL_CONVER UNION SELECT 'C00002', '' FROM ROW_COL_CONVER UNION SELECT 'C9999', '' FROM ROW_COL_CONVER; --将各种列值的情况一一插入到临时表中 INSERT INTO T_MONTH_200401 SELECT CODE, DECODE(MONTH, '200401', NUM) COL_200401 FROM ROW_COL_CONVER WHERE DECODE(MONTH, '200401', NUM) IS NOT NULL; INSERT INTO T_MONTH_200402 SELECT CODE, DECODE(MONTH, '200402', NUM) COL_200402 FROM ROW_COL_CONVER WHERE DECODE(MONTH, '200402', NUM) IS NOT NULL; INSERT INTO T_MONTH_200403 SELECT CODE, DECODE(MONTH, '200403', NUM) COL_200403 FROM ROW_COL_CONVER WHERE DECODE(MONTH, '200403', NUM) IS NOT NULL; INSERT INTO T_MONTH_200404 SELECT CODE, DECODE(MONTH, '200404', NUM) COL_200404 FROM ROW_COL_CONVER WHERE DECODE(MONTH, '200404', NUM) IS NOT NULL; INSERT INTO T_MONTH_200405 SELECT CODE, DECODE(MONTH, '200405', NUM) COL_200405 FROM ROW_COL_CONVER WHERE DECODE(MONTH, '200405', NUM) IS NOT NULL; INSERT INTO T_MONTH_200604 SELECT CODE, DECODE(MONTH, '200604', NUM) COL_200604 FROM ROW_COL_CONVER WHERE DECODE(MONTH, '200604', NUM) IS NOT NULL; --打开游标，实现二维表查询 OPEN P_CUR FOR SELECT T_MONTH_200401.CODE ,NVL(COL_200401, 0) COL_200401 ,NVL(COL_200402, 0) COL_200402 ,NVL(COL_200403, 0) COL_200403 ,NVL(COL_200404, 0) COL_200404 ,NVL(COL_200405, 0) COL_200405 ,NVL(COL_200604, 0) COL_200604 FROM T_MONTH_XXXXXX LEFT JOIN T_MONTH_200401 ON T_MONTH_XXXXXX.CODE = T_MONTH_200401.CODE LEFT JOIN T_MONTH_200402 ON T_MONTH_XXXXXX.CODE = T_MONTH_200402.CODE LEFT JOIN T_MONTH_200403 ON T_MONTH_XXXXXX.CODE = T_MONTH_200403.CODE LEFT JOIN T_MONTH_200404 ON T_MONTH_XXXXXX.CODE = T_MONTH_200404.CODE LEFT JOIN T_MONTH_200405 ON T_MONTH_XXXXXX.CODE = T_MONTH_200405.CODE LEFT JOIN T_MONTH_200604 ON T_MONTH_XXXXXX.CODE = T_MONTH_200604.CODE; END; /

说明：通过以上代码，实现了将一维表查询成二维表的过程。