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;

posted @ 2017-06-09 08:30  队长  阅读(215)  评论(0)  编辑  收藏  举报