如何将内联表作为参数发送给接收表的函数?
问题描述:
我需要在WITH子句中使用returning_tbl(),然后将WITH子句创建的内联表作为参数传递给函数。像using_tbl_v2(不此时工作)如何将内联表作为参数发送给接收表的函数?
using_tbl_v1只是事物,它们的例子(但他们是简单的对我来说)。
我意识到,一旦我创建了一个内联表,我退出PLSQL模式并进入SQL模式。但我怎么回去PLSQL模式中提供original_tbl到receiving_tbl(...)
create or replace type SOME_OBJ force as object (
SOME_VARCHAR varchar2(20 byte)
);
create or replace type SOME_TBL is table of SOME_OBJ;
create or replace function returning_tbl
return SOME_TBL pipelined is
begin
for current_row in (
select
'SOME_VALUE' as SOME_VARCHAR
from dual
)
loop
pipe row (
SOME_OBJ(
current_row.SOME_VARCHAR
)
);
end loop;
return;
END returning_tbl;
select * from table(returning_tbl());
create or replace function receiving_tbl(tbl SOME_TBL)
return SOME_TBL pipelined is
begin
for current_row in (
with filtered_tbl as (
select
SOME_VARCHAR
from table(tbl)
where SOME_VARCHAR = 'SOME_VALUE'
)
select * from filtered_tbl
)
loop
pipe row (
SOME_OBJ(
current_row.SOME_VARCHAR
)
);
end loop;
return;
END receiving_tbl;
select * from table(receiving_tbl(returning_tbl()));
create or replace function using_tbl_v1
return SOME_TBL pipelined is
begin
for current_row in (
with original_tbl as (
select
SOME_VARCHAR
from table(returning_tbl())
where SOME_VARCHAR = 'SOME_VALUE'
),
outside_inlined_tbl as (--just as example
select * from table(receiving_tbl(returning_tbl()))
)
select * from outside_inlined_tbl
)
loop
pipe row (
SOME_OBJ(
current_row.SOME_VARCHAR
)
);
end loop;
return;
END using_tbl_v1;
select * from table(using_tbl_v1());
create or replace function using_tbl_v2
return SOME_TBL pipelined is
begin
for current_row in (
with original_tbl as (
select
SOME_VARCHAR
from table(returning_tbl())
where SOME_VARCHAR = 'SOME_VALUE'
),
outside_tbl as (
select * from table(receiving_tbl(original_tbl))
)
select * from outside_tbl
)
loop
pipe row (
SOME_OBJ(
current_row.SOME_VARCHAR
)
);
end loop;
return;
END using_tbl_v2;
select * from table(using_tbl(_v2));
答
替换:
with original_tbl as (
select
SOME_VARCHAR
from table(returning_tbl())
where SOME_VARCHAR = 'SOME_VALUE'
),
outside_tbl as (
select * from table(receiving_tbl(original_tbl
))
)
select * from outside_tbl
有了:
with original_tbl as (
select
SOME_VARCHAR
from table(returning_tbl())
where SOME_VARCHAR = 'SOME_VALUE'
),
outside_tbl as (
select * from table(receiving_tbl(
(select cast(collect(SOME_OBJ(SOME_VARCHAR)) as SOME_TBL) from original_tbl)
))
)
select * from outside_tbl
我我想补充一些关于这里发生的事情的简单解释。但这个例子非常复杂,我不确定是否有任何简单的教训可以在这里学习。