一:创建
CREATE OR REPLACE FUNCTION FN_T_EM_RT_RUNSTATE_DETAIL
/*
结果:返回表数据
步骤:1.首先需要创建临时表T_EM_RT_RUNSTA_TEMP(用于返回数据的临时表中)
2.在PACKAGE文件下创建包FN_T_EM_RT_RUNSTATE_DETAIL_PKG,声明T_EM_RT_RUNSTA_TEMP表作为返回数据的表
3.正文:将查询到的数据插入临时表
4.打开游标,按行读取临时表,并通过管道函数将整个数据集一次性返回出去(等同于sqlserver的表值函数)
*/
(
v_shopId CHAR-- 函数参数
)
RETURN FN_T_EM_RT_RUNSTATE_DETAIL_PKG.T_EM_RT_RUNSTA_TEMP_type PIPELINED
AS
v_runTimeFr_ft char(14);
v_temp SYS_REFCURSOR;
v_temp_1 T_EM_RT_RUNSTA_TEMP%ROWTYPE; --行对象
PRAGMA AUTONOMOUS_TRANSACTION; --事务
BEGIN
-- 先删除临时表
DELETE FROM T_EM_RT_RUNSTA_TEMP;
-- 插入临时表
INSERT INTO T_EM_RT_RUNSTA_TEMP
(UPD_DATE, NODEID, DEVICENAME, RUNTIMEFR, RUNTIMETO, DURATION, RUNSTATUS)
SELECT
... from ....
COMMIT;
-- 返回设备运行明细数据
OPEN v_temp FOR
SELECT *
FROM T_EM_RT_RUNSTA_TEMP;
LOOP
FETCH v_temp INTO v_temp_1;
EXIT WHEN v_temp%NOTFOUND;
PIPE ROW ( v_temp_1 );
END LOOP;
END;
二:调用:
SELECT * FROM TABLE(FN_T_EM_RT_RUNSTATE_DETAIL('参数'));