一分多行列转换

问题描述：

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

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_NISJISV_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_NUMFROM INFC_C_GUARANTOR_NISJWHERE CUST_NO=V_CUST_NO;FOR I IN 1..V_DOUHAO_NUM-1 LOOPIF 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_DUOISV_DOUHAO_NUM NUMBER;V_CUST_NO VARCHAR2(30) := 'CMS6000340435';BEGINEXECUTE 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 LOOPIF 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;/