推荐订阅源

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;