一分多行列转换

问题描述：

将以下表方式存放的数据：

1	CMS8000290693	,011101251020006862,
2	CMS8000692449	,9011101012510200105,011201251020000015,041101251020000193,
3	CMS6000320019	,011101251010001452,9011101012510200100,9011301012510200100,
4	CMS8000695502	,9011001012510200009,9011101012510200102,011101251020005485,

转换成如下方式存放：

1	CMS8000290693	011101251020006862
2	CMS8000692449	9011101012510200105
3	CMS8000692449	011201251020000015
4	CMS8000692449	041101251020000193
5	CMS6000320019	011101251010001452
6	CMS6000320019	9011101012510200100
7	CMS6000320019	9011301012510200100
8	CMS8000695502	9011001012510200009
9	CMS8000695502	9011101012510200102
10	CMS8000695502	011101251020005485

1、 建表语句及插入测试数据

 --建表语句 create table INFC_C_GUARANTOR_NISJ ( CUST_NO VARCHAR2(30), BAIL_ACCT_NO VARCHAR2(2000) ); create table INFC_C_GUARANTOR_NISJ_NEW ( CUST_NO VARCHAR2(30), BAIL_ACCT_NO VARCHAR2(2000) ); --测试数据 insert into Infc_c_Guarantor_Nisj (CUST_NO, BAIL_ACCT_NO) values ('CMS8000290693', ',011101251020006862,'); insert into Infc_c_Guarantor_Nisj (CUST_NO, BAIL_ACCT_NO) values ('CMS8000692449', ',9011101012510200105,011201251020000015,041101251020000193,'); insert into Infc_c_Guarantor_Nisj (CUST_NO, BAIL_ACCT_NO) values ('CMS6000320019', ',011101251010001452,9011101012510200100,9011301012510200100,'); insert into Infc_c_Guarantor_Nisj (CUST_NO, BAIL_ACCT_NO) values ('CMS8000695502', ',9011001012510200009,9011101012510200102,011101251020005485,'); commit;

2、 实现转换的存储过程

 CREATE OR REPLACE PROCEDURE SP_NISJ IS V_DOUHAO_NUM NUMBER; V_CUST_NO VARCHAR2(30); CURSOR CUR_CUST_NO IS SELECT CUST_NO FROM INFC_C_GUARANTOR_NISJ; BEGIN OPEN CUR_CUST_NO; EXECUTE IMMEDIATE 'TRUNCATE TABLE INFC_C_GUARANTOR_NISJ_NEW'; LOOP FETCH CUR_CUST_NO INTO V_CUST_NO; EXIT WHEN CUR_CUST_NO%NOTFOUND; SELECT LENGTHB(BAIL_ACCT_NO)-LENGTHB(REPLACE(BAIL_ACCT_NO,',','')) INTO V_DOUHAO_NUM FROM INFC_C_GUARANTOR_NISJ WHERE CUST_NO=V_CUST_NO; FOR I IN 1..V_DOUHAO_NUM-1 LOOP IF V_DOUHAO_NUM > 0 THEN INSERT INTO INFC_C_GUARANTOR_NISJ_NEW SELECT CUST_NO,SUBSTR(BAIL_ACCT_NO,INSTR(BAIL_ACCT_NO, ',', 1,I) + 1, ((INSTR(BAIL_ACCT_NO, ',', 1,I+1))-(INSTR(BAIL_ACCT_NO, ',', 1,I))-1) ) FROM INFC_C_GUARANTOR_NISJ WHERE CUST_NO=V_CUST_NO; END IF; END LOOP; END LOOP; CLOSE CUR_CUST_NO; COMMIT; END; /

3、 说明

写代码过程中，用到的中间存储过程：

 CREATE OR REPLACE PROCEDURE ONE_TO_DUO IS V_DOUHAO_NUM NUMBER; V_CUST_NO VARCHAR2(30) := 'CMS6000340435'; BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE INFC_C_GUARANTOR_NISJ_NEW'; SELECT LENGTHB(BAIL_ACCT_NO)-LENGTHB(REPLACE(BAIL_ACCT_NO,',','')) INTO V_DOUHAO_NUM FROM INFC_C_GUARANTOR_NISJ WHERE CUST_NO=V_CUST_NO; FOR I IN 1..V_DOUHAO_NUM-1 LOOP IF V_DOUHAO_NUM > 0 THEN INSERT INTO INFC_C_GUARANTOR_NISJ_NEW SELECT CUST_NO,SUBSTR(BAIL_ACCT_NO,INSTR(BAIL_ACCT_NO, ',', 1,I) + 1, ((INSTR(BAIL_ACCT_NO, ',', 1,I+1))-(INSTR(BAIL_ACCT_NO, ',', 1,I))-1) ) FROM INFC_C_GUARANTOR_NISJ WHERE CUST_NO=V_CUST_NO; END IF; END LOOP; COMMIT; END; /