oracle - 如何将内联表作为参数发送给接收表的函数？

我需要在WITH子句中使用下面代码中的returning_tbl（），然后将使用WITH子句创建的内联表作为参数传递给函数。就像在using_tbl_v2中一样（目前不起作用）

using_tbl_v1只是可以正常工作的示例（但对我来说很简单）。

而且我意识到，一旦创建了一个内联表，就将退出PLSQL模式并进入SQL模式。但是我该如何回到PLSQL模式下以将original_tbl传递给receive_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

我想对这里发生的事情添加一些简单的解释。但是这个例子太复杂了，我不确定这里是否有简单的教训可以学习。