Oracle 从表中选择 * (PL/SQL表) 不起作用

嗨，康纳，克里斯

你能帮忙吗，
从表中选择 * (强制转换 (t_daz作为daz_test_tab)) 在这里不起作用。

create table tb_test
as select object_name, object_id from user_objects where rownum <= 10;

create type daz_test as object (name varchar2(100));

create type daz_test_tab as table of Daz_test index by binary integer;

declare
  t_daz daz_test_tab := daz_test_tab();
begin
select object_name 
bulk collect into t_daz
from tb_test 
where rownum <= 10;

  for r_item in (select * from table(cast(t_daz as daz_test_tab)))
  loop
    dbms_output.put_line(r_item.id);
  end loop;
end;

这里有几个问题:

-“按二进制整数索引” 仅是PL/SQL，但是您正在使用SQL创建它
-批量收集数据时，您需要选择对象
-ID不是您类型的属性，因此在put_line中对此的引用将失败

您可以将daz_test_tab创建为PL/SQL类型。但是您只能从12.1开始在table() 运算符中使用该类型的变量。你在11.2，所以你需要坚持使用SQL类型:

create table tb_test
as select object_name, object_id from user_objects where rownum <= 10;

create type daz_test as object (name varchar2(100));
/
create type daz_test_tab as table of Daz_test;
/
declare
  t_daz daz_test_tab := daz_test_tab();
begin
  select daz_test(object_name)
  bulk collect into t_daz
  from tb_test 
  where rownum <= 5;

  for r_item in (select * from table(cast(t_daz as daz_test_tab)))
  loop
    dbms_output.put_line(r_item.name);
  end loop;
end;
/

AAA
ABC
ADDRESS_TY
ADDRESS_TYY
AGE_I

如果要在12.1中使用PL/SQL类型，则需要在包中声明它:

create table tb_test
as select object_name, object_id from user_objects where rownum <= 10;

create type daz_test as object (name varchar2(100));
/

create or replace package pkg as
  type daz_test_tab is table of Daz_test;
end pkg;
/

declare
  t_daz pkg.daz_test_tab;
begin
  select daz_test(object_name)
  bulk collect into t_daz
  from tb_test 
  where rownum <= 5;

  for r_item in (select * from table(t_daz))
  loop
    null;
    dbms_output.put_line(r_item.name);
  end loop;
end;
/

A
AAA
ABC
ABC_TEMP_TABLE
ACCOUNTS