Simple Pipelined Function

SELECT * FROM TABLE(PKG_TEST.FN_DIC_DB_TAB)

CREATE OR REPLACE PACKAGE PKG_TEST IS   TYPE OBJ_DICDB_ROWTYPE IS RECORD( TRADE_DATE DATE, SDB_TRANSACTION_TYPE_CODE VARCHAR2(10 CHAR), NUMBER_OF_SHARE NUMBER, BROKER_NAME VARCHAR2(50 CHAR));   TYPE TABLETYPE_DIC_DB IS TABLE OF OBJ_DICDB_ROWTYPE;   FUNCTION FN_DIC_DB_TAB RETURN TABLETYPE_DIC_DB PIPELINED;   END PKG_TEST;

CREATE OR REPLACE PACKAGE BODY PKG_TEST IS   FUNCTION FN_DIC_DB_TAB RETURN TABLETYPE_DIC_DB PIPELINED IS V_DIC_DB OBJ_DICDB_ROWTYPE; CUR_RETURN_DATA SYS_REFCURSOR; BEGIN   OPEN CUR_RETURN_DATA FOR SELECT SYSDATE TRADE_DATE, 'S' SDB_TRANSACTION_TYPE_CODE, 100 NUMBER_OF_SHARE, 'Broker 1' BROKER_NAME FROM DUAL UNION ALL SELECT SYSDATE TRADE_DATE, 'B' SDB_TRANSACTION_TYPE_CODE, 200 NUMBER_OF_SHARE, 'Broker 2' BROKER_NAME FROM DUAL;   LOOP FETCH CUR_RETURN_DATA INTO V_DIC_DB; EXIT WHEN(CUR_RETURN_DATA%NOTFOUND); PIPE ROW(V_DIC_DB); END LOOP; RETURN; END; END PKG_TEST;

转载于:https://www.cnblogs.com/teamleader/p/6970179.html