我需要在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
我想补充一些关于这里发生的事情的简单解释.但是这个例子太复杂了我不确定在这里学习是否有任何简单的教训.
