Oracle：从内联表函数的 CTE 返回结果集
我能够在 TSQL 中获得一个类似的函数，但我是 PL/SQL 的相对新手，我希望有人可以向我解释为什么这个函数声明无法编译。
t_interval_list_table 是对象 t_interval 的表类型，它具有 varchar2(20) 的单个属性。 Interval_Get_udf 仅返回 varchar(2) 类型的格式化字符串
create or replace
FUNCTION fn_ExplodeIntervals (
startTime IN timestamp,
endTime IN timestamp,
inputInterval IN int)
RETURN t_interval_list_table AS intervalList t_interval_list_table := t_interval_list_table()
BEGIN
with SET0 as( select 1 from dual union all select 1 from dual)
, SET1 as ( select 1 from SET0 s1, SET0 s2)
, SET2 as ( select 1 from SET1 s1, SET1 s2)
, SET3 as ( select 1 from SET3 s1, SET3 s2)
, SET4 as ( select 1 from SET4 s1, SET4 s2)
, ControlSet AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1 from Dual)) rid FROM SET4)
select t_interval(Interval_Get_udf(TO_TIMESTAMP(startTime, 'dd/mm/yyyy') + rid/24 , 1))
into intervalList
from ControlSet;
RETURN intervalList;
END fn_ExplodeIntervals;
I am able to get a similiar function working in TSQL but I am a relative newbie to PL/SQL, I'm hopeing someone can explain to me why this function declaration will not compile.
t_interval_list_table is a table type of objects t_interval which has a single attribute of varchar2(20). Interval_Get_udf just returns a formatted string of type varchar(2)
create or replace
FUNCTION fn_ExplodeIntervals (
startTime IN timestamp,
endTime IN timestamp,
inputInterval IN int)
RETURN t_interval_list_table AS intervalList t_interval_list_table := t_interval_list_table()
BEGIN
with SET0 as( select 1 from dual union all select 1 from dual)
, SET1 as ( select 1 from SET0 s1, SET0 s2)
, SET2 as ( select 1 from SET1 s1, SET1 s2)
, SET3 as ( select 1 from SET3 s1, SET3 s2)
, SET4 as ( select 1 from SET4 s1, SET4 s2)
, ControlSet AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1 from Dual)) rid FROM SET4)
select t_interval(Interval_Get_udf(TO_TIMESTAMP(startTime, 'dd/mm/yyyy') + rid/24 , 1))
into intervalList
from ControlSet;
RETURN intervalList;
END fn_ExplodeIntervals;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可能可以简化 PL/SQL 过程中的查询：
这可能会消除错误。
You probably can simplify the query in your PL/SQL procedure:
That might get rid of the error.
很难阅读这篇文章，所以我可能是错的...
您是否只是缺少以下末尾的分号：
t_interval_list_table := t_interval_list_table();
Its hard to read this one so I'm probably wrong...
Are you just missing the semi-colon at the end of the following:
t_interval_list_table := t_interval_list_table();