테이블 함수 예제 2

CREATE OR REPLACE PACKAGE P_CORR_DATA is
  type tab_CORR_DATA is record (CalcStr VARCHAR2(200), Result NUMBER);
  type tab_CORR_DATA_table is table of tab_CORR_DATA;
END P_CORR_DATA;
/

CREATE OR REPLACE FUNCTION F_CORR_DATA
                           RETURN P_CORR_DATA.tab_CORR_DATA_table PIPELINED IS
TYPE         ref0 IS REF CURSOR;
cur0         ref0;
out_rec      P_CORR_DATA.tab_CORR_DATA; -- := MY_TYPES2.tab_CORR_DATA(NULL,NULL,NULL,0);
BEGIN

    OPEN cur0 FOR
        SELECT CalcFunction, fnCalcStr(CalcFunction) FROM TESTTABLE;
    LOOP
        FETCH cur0 INTO out_rec.CalcStr, out_rec.Result;
        EXIT WHEN cur0%NOTFOUND;
        PIPE ROW(out_rec);
    END LOOP;
    CLOSE cur0;

RETURN;
END F_CORR_DATA;
/

 

 

Usage : select * from table(F_CORR_DATA());