sql - ORA-22905 - 使用 select 语句查询表类型时

Question

 DECLARE
 TYPE record_AB IS RECORD
   (
      AA              VARCHAR2 (16 BYTE),
      BB    VARCHAR2 (16 BYTE)
   );

  TYPE type_tab_AB IS TABLE OF record_AB
                        INDEX BY BINARY_INTEGER;

  tab_AB   type_tab_AB;

  BEGIN
   SELECT *
    BULK COLLECT INTO tab_AB FROM...
    ..
    SELECT * FROM TABLE (tab_AB) ;

当它到达 SELECT from TABLE 语句时，我得到“ORA-22905:无法访问来自非嵌套表项的行”。

甚至可以在 PLSQL 中查询表类型吗？

Answer 1

可以在 PL/SQL 中查询表类型，但只能查询其类型在模式级别声明的嵌套表和变量，即在 PL/SQL 之外。

错误

ORA-22905: cannot access rows from a non-nested table item

意味着您正在尝试从不受支持的表类型进行查询。您的类型 type_tab_AB是一个关联数组，因为 INDEX BY BINARY_INTEGER条款。删除 INDEX BY BINARY_INTEGER条款使您的 type_tab_AB嵌套表类型。 (可变数组也可以在这里工作，但我不建议使用它们，除非您知道预期行数的上限。声明可变数组类型时，您需要指定最大元素数，而嵌套表类型有没有这样的限制。)

进行此更改后，您的代码可能仍无法正常工作。您可能会遇到的下一个错误(如果没有，请参阅底部的注释)是

PLS-00642: local collection types not allowed in SQL statements

这是因为您选择的类型是在 PL/SQL 中声明的。您需要申报 type_tab_AB , 和 record_AB在 PL/SQL 之外，使用 CREATE TYPE ... .

您遇到的下一个问题将是因为关键字 RECORD .记录类型只能在 PL/SQL 中创建， they cannot be created at schema level .更改 RECORD至 OBJECT来解决这个问题。

您将遇到的最后一个问题是 SELECT t.AA, t.BB BULK COLLECT INTO tab_AB FROM ...陈述。就目前而言，此查询会给您以下错误:

PL/SQL: ORA-00947: not enough values

您要从每一行中选择两个项目，并且仅提供一个表以将数据批量插入到其中。 Oracle 无法确定您要将这两个项目塞入您的 record_AB类型。您可以通过将查询更改为 SELECT record_AB(t.AA, t.BB) BULK COLLECT INTO tab_AB FROM ... 来轻松解决此问题。 .

总的来说，这些更改应该可以解决问题。这是一个完整的 SQL*Plus 脚本，它使用一些测试数据创建一个测试表并验证它是否可以查询表类型:

CREATE TABLE some_table (AA VARCHAR2(16 BYTE), BB VARCHAR2(16 BYTE));

INSERT INTO some_table (AA, BB) VALUES ('aa 1', 'bb 1');
INSERT INTO some_table (AA, BB) VALUES ('aaaaaaaaaa 2', 'b 2');
INSERT INTO some_table (AA, BB) VALUES ('aaaaa 3', 'bbbbbbbbbbbbbb 3');
COMMIT;

VARIABLE curs REFCURSOR;

CREATE OR REPLACE TYPE record_AB AS OBJECT
   (
      AA    VARCHAR2 (16 BYTE),
      BB    VARCHAR2 (16 BYTE)
   );
/

CREATE OR REPLACE TYPE type_tab_AB IS TABLE OF record_AB;
/

DECLARE
  tab_AB   type_tab_AB;
BEGIN
  SELECT record_AB(t.AA, t.BB)
    BULK COLLECT INTO tab_AB 
    FROM some_table t;

  OPEN :curs FOR SELECT * FROM TABLE (tab_AB) ;
END;
/

PRINT :curs

我已经把 SELECT 的结果正在查看 tab_AB 的内容进入游标，并使用 SQL*Plus 游标变量列出其内容。在所有“类型已创建”和“PL/SQL 过程成功完成”消息之后，我在 Oracle 11g XE 上运行脚本时得到的输出如下:

AA               BB
---------------- ----------------
aa 1             bb 1
aaaaaaaaaa 2     b 2
aaaaa 3          bbbbbbbbbbbbbb 3

注意:为简单起见，我假设提问者使用的是 Oracle 11 或更早版本。在 Oracle 12 中，我相信您可以在 SQL 查询中使用 PL/SQL 中声明的类型，因此您可能不会遇到 PLS-00642 错误。我不能说 Oracle 12 还需要对我的答案进行哪些其他更改，因为我尚未使用 Oracle 12。