pipelined

最新推荐文章于 2025-06-24 08:58:17 发布

转载最新推荐文章于 2025-06-24 08:58:17 发布 · 93 阅读

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;