表中的数据是这样的.
PROPERTY_NAME 列的值是以 "+" 号分割.
SCOTT@PROD>select DATA_DT,INVESTOR_ID,PROPERTY_NAME from DM_CUST_ACCOUNT_INFO_TEST;
DATA_DT INVESTOR_ID PROPERTY_NAME
-------- --------------- ------------------------------
20180820 666062009 ??????1.0-YD6+NST-ZJ2
20180820 666062011 ??????1.0-YD6
20180817 666001431 ??????mini-3
需要转换为.就是把每列有 "+" 号的分为两行,其余不变
SCOTT@PROD>select DATA_DT,INVESTOR_ID,PROPERTY_NAME from DM_CUST_ACCOUNT_INFO_TEST_TMP;
DATA_DT INVESTOR_ID PROPERTY_NAME
-------- --------------- ------------------------------
20180820 666062009 ??????1.0-YD6
20180820 666062009 NST-ZJ2
20180820 666062011 ??????1.0-YD6
20180817 666001431 ??????mini-3
建立与原表结构相同的表
SCOTT@PROD>CREATE TABLE DM_CUST_ACCOUNT_INFO_TEST_TMP AS SELECT * FROM DM_CUST_ACCOUNT_INFO_TEST WHERE 1=2;
Table created
存储过程为
CREATE OR REPLACE PROCEDURE SP_STR(V_OUT OUT SYS_REFCURSOR)
AS
CURSOR C1 IS SELECT * FROM DM_CUST_ACCOUNT_INFO_TEST;
TYPE C3 IS TABLE OF DM_CUST_ACCOUNT_INFO_TEST%ROWTYPE;
C4 C3;
V_START NUMBER :=1;
V_LOCATE NUMBER;
V_STR_BEFORE VARCHAR2(4000);
V_STR VARCHAR2(100);
BEGIN
OPEN C1;
LOOP
EXIT WHEN C1%NOTFOUND;
FETCH C1 BULK COLLECT INTO C4;
FOR I IN 1..C4.COUNT LOOP
V_STR:=C4(I).PROPERTY_NAME;
WHILE INSTR(V_STR,'+')>0
LOOP
V_LOCATE :=INSTR(V_STR,'+');
V_STR_BEFORE :=SUBSTR(V_STR,V_START,V_LOCATE-V_START);
INSERT INTO DM_CUST_ACCOUNT_INFO_TEST_TMP(DATA_DT,INVESTOR_ID,PROPERTY_NAME) VALUES(C4(I).DATA_DT,C4(I).INVESTOR_ID,V_STR_BEFORE);
V_STR :=SUBSTR(V_STR,V_LOCATE+1);
END LOOP;
INSERT INTO DM_CUST_ACCOUNT_INFO_TEST_TMP(DATA_DT,INVESTOR_ID,PROPERTY_NAME) VALUES(C4(I).DATA_DT,C4(I).INVESTOR_ID,V_STR);
commit;
END LOOP;
COMMIT;
END LOOP;
CLOSE C1;
OPEN V_OUT FOR SELECT DATA_DT,INVESTOR_ID,PROPERTY_NAME FROM DM_CUST_ACCOUNT_INFO_TEST_TMP;
END;
/
SCOTT@PROD>CREATE OR REPLACE PROCEDURE SP_STR(V_OUT OUT SYS_REFCURSOR)
2 AS
3 CURSOR C1 IS SELECT * FROM DM_CUST_ACCOUNT_INFO_TEST;
4 TYPE C3 IS TABLE OF DM_CUST_ACCOUNT_INFO_TEST%ROWTYPE;
5 C4 C3;
6 V_START NUMBER :=1;
7 V_LOCATE NUMBER;
8 V_STR_BEFORE VARCHAR2(4000);
9 V_STR VARCHAR2(100);
10 BEGIN
11 OPEN C1;
12 LOOP
13 EXIT WHEN C1%NOTFOUND;
14 FETCH C1 BULK COLLECT INTO C4;
15 FOR I IN 1..C4.COUNT LOOP
16 V_STR:=C4(I).PROPERTY_NAME;
17 WHILE INSTR(V_STR,'+')>0
18 LOOP
19 V_LOCATE :=INSTR(V_STR,'+');
20 V_STR_BEFORE :=SUBSTR(V_STR,V_START,V_LOCATE-V_START);
21 INSERT INTO DM_CUST_ACCOUNT_INFO_TEST_TMP(DATA_DT,INVESTOR_ID,PROPERTY_NAME) VALUES(C4(I).DATA_DT,C4(I).INVESTOR_ID,V_STR_BEFORE);
22 V_STR :=SUBSTR(V_STR,V_LOCATE+1);
23 END LOOP;
24 INSERT INTO DM_CUST_ACCOUNT_INFO_TEST_TMP(DATA_DT,INVESTOR_ID,PROPERTY_NAME) VALUES(C4(I).DATA_DT,C4(I).INVESTOR_ID,V_STR);
25 commit;
26 END LOOP;
27 COMMIT;
28 END LOOP;
29 CLOSE C1;
30 OPEN V_OUT FOR SELECT DATA_DT,INVESTOR_ID,PROPERTY_NAME FROM DM_CUST_ACCOUNT_INFO_TEST_TMP;
31 END;
32 /
Procedure created.
查看最终结果
DECLARE
C1 SYS_REFCURSOR;
DATA_DT_1 DM_CUST_ACCOUNT_INFO_TEST_TMP.DATA_DT%TYPE;
INVESTOR_ID_1 DM_CUST_ACCOUNT_INFO_TEST_TMP.INVESTOR_ID%TYPE;
PROPERTY_NAME_1 DM_CUST_ACCOUNT_INFO_TEST_TMP.PROPERTY_NAME%TYPE;
BEGIN
SP_STR(C1);
LOOP
BEGIN
EXIT WHEN C1%NOTFOUND;
FETCH C1 INTO DATA_DT_1,INVESTOR_ID_1,PROPERTY_NAME_1;
DBMS_OUTPUT.PUT_LINE(DATA_DT_1||','||INVESTOR_ID_1||','||PROPERTY_NAME_1);
END;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DELETE FROM DM_CUST_ACCOUNT_INFO_TEST_TMP;
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/
SCOTT@PROD>DECLARE
2 C1 SYS_REFCURSOR;
3 DATA_DT_1 DM_CUST_ACCOUNT_INFO_TEST_TMP.DATA_DT%TYPE;
PROPERTY_NAME_1 DM_CUST_ACCOUNT_INFO_TEST_TMP.PROPERTY_NAME%TYPE;
INVESTOR_ID_1 DM_CUST_ACCOUNT_INFO_TEST_TMP.INVESTOR_ID%TYPE;
5 PROPERTY_NAME_1 DM_CUST_ACCOUNT_INFO_TEST_TMP.PROPERTY_NAME%TYPE;
6 BEGIN
7 SP_STR(C1);
8 LOOP
9 BEGIN
10 EXIT WHEN C1%NOTFOUND;
11 FETCH C1 INTO DATA_DT_1,INVESTOR_ID_1,PROPERTY_NAME_1;
12 DBMS_OUTPUT.PUT_LINE(DATA_DT_1||','||INVESTOR_ID_1||','||PROPERTY_NAME_1);
13 END;
14 END LOOP;
15 EXCEPTION
16 WHEN OTHERS THEN
17 DELETE FROM DM_CUST_ACCOUNT_INFO_TEST_TMP;
18 DBMS_OUTPUT.PUT_LINE(SQLERRM);
19 END;
20 /
20180820,666062009,??????1.0-YD6
20180820,666062009,NST-ZJ2
20180820,666062011,??????1.0-YD6
20180817,666001431,??????mini-3
20180817,666001431,??????mini-3
PL/SQL procedure successfully completed.
每次执行完存储过程可以删除TMP的数据,避免TMP表数据重复