创建返回结果集的函数

–定义一个行类型（参数为结果集的字段）
CREATE OR REPLACE TYPE FHC_JW.TYPE_TTEMP_ROW AS OBJECT(STARTDATE VARCHAR2(20),ENDDATE VARCHAR2(20), WEEKNO VARCHAR2(20));

–定义一个表类型
CREATE OR REPLACE TYPE FHC_JW.TYPE_TEMP_TABLE AS TABLE OF FHC_JW.TYPE_TTEMP_ROW;

–创建函数
CREATE OR REPLACE FUNCTION FHC_JW.FUN_GETWEEKDATE (STARTDATEPARAM IN VARCHAR2,ENDDATEPARAM IN VARCHAR2)
RETURN TYPE_TEMP_TABLE IS
TEMP_ROW TYPE_TTEMP_ROW; – 定义单行
TEMP_TABLE TYPE_TEMP_TABLE := TYPE_TEMP_TABLE(); – 定义返回结果,并初始化
BEGIN
FOR CURROW IN (
–查询结果集sql–开始–(返回的字段为TYPE_TTEMP_ROW中的参数的字段)
with temp as(SELECT count(*) totalNum
FROM DUAL
CONNECT BY ROWNUM <=
round(TRUNC(to_DATE(ENDDATEPARAM, ‘YYYY-MM-DD’) -
to_DATE(STARTDATEPARAM, ‘YYYY-MM-DD’)) / 7 + 1
))
select
(case when rownum=1 and startDate<TRUNC(to_DATE(STARTDATEPARAM, ‘YYYY-MM-DD’))
then to_CHAR(STARTDATEPARAM, ‘YYYY-MM-DD’)
else to_CHAR(startDate, ‘YYYY-MM-DD’)
end
)STARTDATE,
(case when rownum=temp.totalNum and endDate>TRUNC(to_DATE(ENDDATEPARAM, ‘YYYY-MM-DD’))
then to_CHAR(ENDDATEPARAM, ‘YYYY-MM-DD’)
else to_CHAR(endDate, ‘YYYY-MM-DD’)
end
)ENDDATE,‘第’||rownum||‘周’ WEEKNO
from(
SELECT TRUNC(to_DATE(STARTDATEPARAM, ‘YYYY-MM-DD’) + (ROWNUM - 1) * 7, ‘D’) + 1 AS startDate,
TRUNC(to_DATE(STARTDATEPARAM, ‘YYYY-MM-DD’) + (ROWNUM - 1) * 7, ‘D’) + 7 AS endDate
FROM DUAL
CONNECT BY ROWNUM <=
round(TRUNC(to_DATE(ENDDATEPARAM, ‘YYYY-MM-DD’) -
to_DATE(STARTDATEPARAM, ‘YYYY-MM-DD’)) / 7 + 1)
),temp
–查询结果集sql–结束–
) – 查询名字是参数的值的结果
LOOP
TEMP_ROW := TYPE_TTEMP_ROW(CURROW.STARTDATE, CURROW.ENDDATE,CURROW.WEEKNO); – 获得一行
TEMP_TABLE.EXTEND; – 表类型增加一行(EXTEND就是扩展的意思,相当于增加一行数据空间)
TEMP_TABLE(TEMP_TABLE.COUNT) := TEMP_ROW; – 一行放进去
END LOOP;
RETURN(TEMP_TABLE);
END;

–执行函数
SELECT * FROM TABLE(FHC_JW.FUN_GETWEEKDATE(‘2022-09-01’,‘2023-03-01’));