Oracle -- 需要另写函数的列转行

 select * from  table(string_split('8,12,14', ','))

注：string_split()不是Oracle数据库自带的函数，可以写在function中，string_split('8,12,14',',')得到的是collection，如

CREATE OR REPLACEFUNCTION string_split (p_str IN long, p_delimiter IN VARCHAR2)

    RETURN str_split PIPELINED

IS

    j INT := 0;

    i INT := 1;

    len INT := 0;

    len1 INT := 0;

    str VARCHAR2 (32767);

BEGIN

    len := LENGTH (p_str);

    len1 := LENGTH (p_delimiter);

 

    WHILE j < len

    LOOP

        j := INSTR (p_str, p_delimiter, i);

 

        IF j = 0

        THEN

            j := len;

            str := SUBSTR (p_str, i);

            PIPE ROW (str);

 

            IF i >= len

            THEN

                EXIT;

            END IF;

        ELSE

            str := SUBSTR (p_str, i, j - i);

            i := j + len1;

            PIPE ROW (str);

        END IF;

    END LOOP;

    RETURN;

END String_split;