ORACLE字符拆分函數(shù)返回結(jié)果集

    ORACLE不能像MSSQL那樣支持直接返回表類型，所以要先創(chuàng)建一種自定義類型。這裏用到的是嵌套表（Nested Table）。
    -- Nested Table
    CREATE OR REPLACE TYPE split_str IS TABLE OF VARCHAR(100);
    /
    -- Function
    CREATE OR REPLACE FUNCTION fn_Split
    (
    p_Str VARCHAR2,
    p_Delimiter VARCHAR2
    )
    RETURN split_str PIPELINED
    AS
    v_Str VARCHAR(4000) := p_Str;
    v_Index NUMBER;
    v_SubLength NUMBER;
    BEGIN
    -- p_Delimiter is null
    IF p_Delimiter IS NULL THEN
    FOR x IN 1..LENGTH(v_Str)
    LOOP
    PIPE ROW(SUBSTR(V_Str,x,1));
    END LOOP;
    RETURN;
    END IF;
    v_index := INSTR(v_Str,p_Delimiter);
    WHILE v_Index <> 0
    LOOP
    PIPE ROW(SUBSTR(v_Str,1,v_Index-1));
    v_SubLength := LENGTH(v_Str) - (v_index + LENGTH(p_Delimiter) - 1);
    v_Str := SUBSTR(v_Str,-v_SubLength,v_SubLength);
    v_index := INSTR(v_Str,p_Delimiter);
    END LOOP;
    PIPE ROW(v_Str);
    RETURN;
    END;
    /
    -- 調(diào)用（第二種方式更好）
    SELECT fn_split('This@@is@@it','@@') FROM dual;
    SELECT * FROM Table(fn_split('This@@is@@it','@@'));