CREATE OR REPLACE TYPE PP_TYPE as Object(
ROWNUM1 NUMBER,
AGENTID NUMBER(5) not null,
ACTBEGIN DATE not null,
ACTEND DATE not null,
SERVICENO NUMBER(5),
OPERATETYPE NUMBER(5) not null,
DEPT_CITY_CODE VARCHAR2(30)
);
CREATE OR REPLACE TYPE PP_TABLE AS TABLE OF PP_TYPE;
FUNCTION F_PP_TEST
RETURN PP_TABLE pipelined
as
TYPE refcur is REF CURSOR;
myCursor refcur;
out_rec PP_TYPE:= PP_TYPE(1,1,SYSDATE-1,SYSDATE,1,1,020);
BEGIN
--OPEN RESULT FOR
--SELECT * FROM LXY_0903 where rownum<10;
OPEN myCursor FOR
SELECT * FROM LXY_0903 where rownum<10;
LOOP FETCH myCursor into
out_rec.ROWNUM1,out_rec.AGENTID,out_rec.ACTBEGIN,
out_rec.ACTEND,
out_rec.SERVICENO,
out_rec.OPERATETYPE,
out_rec.DEPT_CITY_CODE
;
EXIT WHEN myCursor%NOTFOUND;
PIPE ROW(out_rec);
END LOOP;
RETURN;
CLOSE myCursor;
EXCEPTION
WHEN others THEN
RETURN;
END F_PP_TEST;
select * from table(cast(pkg_luoxy_test.f_pp_test as PP_TABLE));
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12122734/viewspace-675709/ ，如需转载，请注明出处，否则将追究法律责任。
