--------------包头
CREATE OR REPLACE PACKAGE PKG_CURSOR AS
/* 定义REF CURSOR类型
不加RETURN类型,为弱类型,允许动态SQL查询,
否则为强类型,无法使用动态SQL查询;
*/
TYPE MYRCTYPE IS REF CURSOR;
/* 定义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;
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;
--包体
--定义列对象
--定义表对象
-- 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博客 ” ,链接:http://blog.itpub.net/23754390/viewspace-675076/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23754390/viewspace-675076/