用Pipelined Table实现split函数的示例

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 PIPELINEDIS    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', ';;'));结果：112123123412345又一个简单的例子：CREATE TYPE mytype AS OBJECT (    field1 NUMBER,    field2 VARCHAR2 (50));CREATE TYPE mytypelist AS TABLE OF mytype;CREATE OR REPLACE FUNCTION pipelineme    RETURN mytypelist PIPELINEDIS    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 2020 rows selected