--------------包头

CREATE OR REPLACE PACKAGE PKG_CURSOR AS
/* 定义REF CURSOR类型
    不加RETURN类型，为弱类型，允许动态SQL查询，
    否则为强类型，无法使用动态SQL查询;
*/
   TYPE MYRCTYPE IS REF CURSOR;

--函数申明
FUNCTION SP_SUB_EXTENT(BEGINDATE IN DATE,
ENDDATE IN DATE)
RETURN TABLE_EXTENT_TYPE PIPELINED;
END PKG_CURSOR;

------包体

CREATE OR REPLACE PACKAGE BODY PKG_CURSOR AS
--包体
--定义列对象
--定义表对象
--   CREATE TYPE ROW_DATA_TYPE AS OBJECT(BEGINDATE DATE,ENDDATE DATE);
--   CREATE TYPE TABLE_EXTENT_TYPE AS TABLE OF ROW_DATA_TYPE;
FUNCTION SP_SUB_EXTENT(BEGINDATE IN DATE,
              ENDDATE IN DATE)
RETURN TABLE_EXTENT_TYPE PIPELINED
IS
    V   ROW_DATA_TYPE;
    V_Q_ENDDATE     DATE;
    V_Q_BGDATE    DATE;
BEGIN
----开始日期的季度最后一天
    V_Q_ENDDATE:= ADD_MONTHS(TRUNC(BEGINDATE,'Q'),3)-1;
    IF V_Q_ENDDATE >= ENDDATE THEN
      V:=ROW_DATA_TYPE(BEGINDATE,ENDDATE);
      PIPE ROW(V);
    ELSE
      V:=ROW_DATA_TYPE(BEGINDATE,V_Q_ENDDATE);
      PIPE ROW(V);
      V_Q_ENDDATE :=ADD_MONTHS(TRUNC(V_Q_ENDDATE,'Q'),6)-1;
      V_Q_BGDATE:=TRUNC(V_Q_ENDDATE,'Q');
      WHILE V_Q_ENDDATE <= ENDDATE LOOP
      ----求出本季度第一天
        V_Q_BGDATE:=TRUNC(V_Q_ENDDATE,'Q');
        V:=ROW_DATA_TYPE(V_Q_BGDATE,V_Q_ENDDATE);
         PIPE ROW(V);
      ---求出下个季度最后一天
        V_Q_ENDDATE :=ADD_MONTHS(TRUNC(V_Q_ENDDATE,'Q'),6)-1;
      END LOOP;
        V_Q_BGDATE:=TRUNC(ENDDATE,'Q');
        V:=ROW_DATA_TYPE(V_Q_BGDATE,ENDDATE);
         PIPE ROW(V);
    END IF;
    RETURN;
END;

END PKG_CURSOR;

ITPub博客

输入时间周期,返回根据季度截取的时间区间

最新文章