【转】Oracle将以特定分隔的字符串转成表格的方法(用于类似游标的遍历)

    CREATE OR REPLACE TYPE TY_OBJECT AS OBJECT(COL_NAME VARCHAR2(200));
    /
    CREATE OR REPLACE TYPE TY_TABLE AS TABLE OF TY_OBJECT;
    /
    CREATE OR REPLACE FUNCTION STR2TABLE(V_STR       IN VARCHAR2,V_DELIMITER IN VARCHAR2)
    --此函数的目的是将以特定字符分隔的字符串转换为游标形式，以例遍历此游标
     RETURN TY_TABLE AS
      V_TY_TABLE TY_TABLE;
    BEGIN
      SELECT TY_OBJECT(REGEXP_SUBSTR(V_STR,'[^' || V_DELIMITER || ']+',1,LEVEL,'i'))
        BULK COLLECT
        INTO V_TY_TABLE
        FROM DUAL
      CONNECT BY LEVEL <=
                 LENGTH(V_STR) -
                 LENGTH(REGEXP_REPLACE(V_STR,'' || V_DELIMITER || '','')) + 1;
      RETURN V_TY_TABLE;
    END;
    /
    --应用示例
    SELECT * FROM TABLE(STR2TABLE('1,2,3',','));
    /