table中:
ID, CONTENT
A str1@str2@str3@...
A str4@str5@...
.....
B str6@str7...
...现在要写一个procedure,把上面的内容分解,然后insert到table1
分解的形式:
A str1
A str2
A str3
A str4
A str5
...
B str6
B str7
...
ID, CONTENT
A str1@str2@str3@...
A str4@str5@...
.....
B str6@str7...
...现在要写一个procedure,把上面的内容分解,然后insert到table1
分解的形式:
A str1
A str2
A str3
A str4
A str5
...
B str6
B str7
...
ID CONTENT
---------- --------------------------------------------------
A str1@str2@str3
B str4@str5
C str6
SQL> select * from table2;
ID CONTENT
---------- --------------------------------------------------
SQL>
SQL> CREATE OR REPLACE PROCEDURE sp_split IS
2 --table1数据转入table2
3 BEGIN
4 FOR c IN (SELECT * FROM TABLE1) LOOP
5 INSERT INTO table2
6 (id, content)
7 SELECT c.id, str
8 FROM ((WITH t AS (SELECT c.content s FROM dual)
9 SELECT substr(s,
10 instr(s, '@', 1, LEVEL) + 1,
11 instr(s, '@', 1, LEVEL + 1) -
12 instr(s, '@', 1, LEVEL) - 1) str
13 FROM (SELECT '@' || s || '@' s FROM t)
14 CONNECT BY LEVEL < length(s) - length(REPLACE(s, '@'))));
15 END LOOP;
16 COMMIT;
17 END;
18 /
Procedure created
SQL> exec sp_split;
PL/SQL procedure successfully completed
SQL> select * from table2;
ID CONTENT
---------- --------------------------------------------------
A str1
A str2
A str3
B str4
B str5
C str6
6 rows selected
SQL>
(
"ID" VARCHAR2(2) NOT NULL,
"CONTENT" VARCHAR(100) NOT NULL
);
INSERT INTO TABLE0("ID","CONTENT") SELECT 'A','str1@str2@str3@' FROM DUAL;
INSERT INTO TABLE0("ID","CONTENT") SELECT 'A','str4@str5@' FROM DUAL;
INSERT INTO TABLE0("ID","CONTENT") SELECT 'B','str6@str7' FROM DUAL;
COMMIT;
CREATE TABLE TABLE1
(
"ID" VARCHAR2(2) NOT NULL,
"CONTENT" VARCHAR(100) NOT NULL
);CREATE OR REPLACE PROCEDURE P_REGEXP_SUBSTR IS
BEGIN
FOR CUR IN (SELECT * FROM TABLE0) LOOP
INSERT INTO TABLE1("ID","CONTENT")
SELECT CUR."ID",CNT
FROM
(
SELECT REGEXP_SUBSTR(CUR."CONTENT",'[^@]+',1,LEVEL) CNT
FROM DUAL
CONNECT BY LEVEL <= LENGTH(CUR."CONTENT")-LENGTH(REPLACE(CUR."CONTENT",'@',''))+1
)
WHERE CNT IS NOT NULL;
END LOOP;
COMMIT;
END P_REGEXP_SUBSTR;BEGIN
P_REGEXP_SUBSTR;
END;SELECT * FROM TABLE1;