创建表：TEMP_XMGX_LOOP

CREATE TABLE TEMP_XMGX_LOOP(

ID NUMBER,

SUB_STR VARCHAR2(200)

);

插入数据：

创建表：TEMP_CESHI_A

CREATE TABLE TEMP_CESHI_A

AS 

SELECT A.*,0 AS NUMS FROM TEMP_XMGX_LOOP A WHERE 1=2;

UPDATE TEMP_CESHI_A a

SET a.nums=NULL;

COMMIT;

创建存储过程：

CREATE OR REPLACE PROCEDURE hz_jjhygl.P_SYBSTR02 AS
  CURSOR T_SOR IS
    SELECT * FROM TEMP_XMGX_LOOP A;
BEGIN
  FOR V_XMGX IN T_SOR LOOP
    EXIT WHEN T_SOR%NOTFOUND;
    INSERT INTO TEMP_CESHI_A
      WITH TEMP_XM AS
       (SELECT V_XMGX.ID,
               V_XMGX.SUB_STR,
               REGEXP_COUNT(V_XMGX.SUB_STR, '\@') + 1 RN
          FROM DUAL A
         WHERE V_XMGX.SUB_STR LIKE '%@%')
      SELECT DISTINCT ID,
                      REGEXP_SUBSTR(V_XMGX.SUB_STR, '[^@]+', 1, RN) SUB_STR,
                      ROWNUM
        FROM (SELECT ID, SUB_STR, T2.RN AS RN
                FROM TEMP_XM T1,
                     (SELECT LEVEL RN
                        FROM DUAL
                      CONNECT BY ROWNUM <= (SELECT MAX(RN) FROM TEMP_XM)) T2
               WHERE T1.RN >= T2.RN);
  END LOOP;
END;

SELECT ID, SUB_STR, T2.RN AS RN
  FROM TEMP_XM T1,
       (SELECT LEVEL RN
          FROM DUAL
        CONNECT BY ROWNUM <= (SELECT MAX(RN) FROM TEMP_XM)) T2
 WHERE T1.RN >= T2.RN;
 

执行存储过程：

begin
P_SYBSTR02;
end;

有空的时候可以思考：'@开发1&开发2&开发3@运维1&运维2&运维3@实施1&实施2&实施3 '怎么

循环做