教你快速掌握"Oracle"的管道化表函数

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
    RETURN ty_str_split PIPELINED
IS
    j INT := 0;
    i INT := 1;
    len INT := 0;
    len1 INT := 0;
    str VARCHAR2 (4000);
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 fn_split;
/


测试：

SELECT * FROM TABLE (fn_split ('1;;12;;123;;1234;;12345', ';;'));


结果：
1
12
123
1234
12345


又一个简单的例子：

CREATE TYPE mytype AS OBJECT (
    field1 NUMBER,
    field2 VARCHAR2 (50)
);

CREATE TYPE mytypelist AS TABLE OF mytype;

CREATE OR REPLACE FUNCTION pipelineme
    RETURN mytypelist PIPELINED
IS
    v_mytype mytype;
BEGIN
    FOR v_count IN 1 .. 20
    LOOP
        v_mytype := mytype (v_count, 'Row ' || v_count);
        PIPE ROW (v_mytype);
    END LOOP;

    RETURN;
END pipelineme;



SELECT * FROM TABLE (pipelineme);

FIELD1 FIELD2 
------ ------------------------
     1 Row 1
     2 Row 2
     3 Row 3
     4 Row 4
     5 Row 5
     6 Row 6
     7 Row 7
     8 Row 8
     9 Row 9
    10 Row 10
    11 Row 11
    12 Row 12
    13 Row 13
    14 Row 14
    15 Row 15
    16 Row 16
    17 Row 17
    18 Row 18
    19 Row 19
    20 Row 20

20 rows selected