2021年11月1日 星期一

Oracle Pipelined Table Functions

 Oracle Pipelined Table Functions



drop TABLE test_table;
drop PACKAGE test_pkg;
CREATE TABLE test_table(tt_id INTEGER,tt_text VARCHAR2(40));

CREATE PACKAGE test_pkg IS
    TYPE tp_rec IS RECORD(tt_id INTEGER,tt_text VARCHAR2(40));
    TYPE tp_recs IS TABLE OF tp_rec;

    FUNCTION test_func RETURN tp_recs PIPELINED;
    FUNCTION test_func1 RETURN tp_recs PIPELINED;
    FUNCTION test_func2(ivar INTEGER) RETURN tp_recs PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY test_pkg IS
    FUNCTION test_func RETURN tp_recs PIPELINED
    AS
       currec tp_rec;
    BEGIN
       currec.tt_id := 1;
       currec.tt_text := 'test1';
       PIPE ROW(currec);
    END;

    FUNCTION test_func1 RETURN tp_recs PIPELINED
    AS
       currec tp_rec;
       CURSOR t_cursor IS
           SELECT * FROM test_table;
    BEGIN
        OPEN t_cursor;
        LOOP
            FETCH t_cursor INTO currec;
            EXIT WHEN t_cursor%NOTFOUND;
            PIPE ROW(currec);
        END LOOP;
        CLOSE t_cursor;
    END;

    FUNCTION test_func2(ivar INTEGER) RETURN tp_recs PIPELINED
    AS
       currec tp_rec;
    BEGIN
       SELECT * INTO currec FROM test_table WHERE tt_id = ivar;
       PIPE ROW(currec);
    END;

END;
/

BEGIN
    INSERT INTO test_table VALUES(1,'test1');
    INSERT INTO test_table VALUES(2,'test2');
    INSERT INTO test_table VALUES(3,'test3');
    COMMIT;
END;
/

SELECT * FROM TABLE(test_pkg.test_func());
SELECT * FROM TABLE(test_pkg.test_func1());
SELECT * FROM TABLE(test_pkg.test_func2(2));


drop PACKAGE PIPELINED_FUN_TEST_PKG;

CREATE OR REPLACE PACKAGE PIPELINED_FUN_TEST_PKG AS
    TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER;
    FUNCTION EXAMPLE1(V_MAX_NUMBER NUMBER) RETURN NUMBER_TBL_TYPE PIPELINED;
END PIPELINED_FUN_TEST_PKG;

CREATE OR REPLACE PACKAGE BODY PIPELINED_FUN_TEST_PKG AS
    FUNCTION EXAMPLE1(V_MAX_NUMBER NUMBER) RETURN NUMBER_TBL_TYPE PIPELINED IS
    BEGIN
        FOR i IN 1..V_MAX_NUMBER LOOP
            PIPE ROW(i);
        END LOOP;
        RETURN;
    END;
END PIPELINED_FUN_TEST_PKG;


SELECT * FROM TABLE(PIPELINED_FUN_TEST_PKG.EXAMPLE1(:X))

標籤： ,