在 IN 子句中使用 Oracle 表类型 - 编译失败

Question

只是试图将光标返回到我指定的 id 处。

CREATE OR REPLACE PACKAGE some_package AS

  TYPE t_cursor IS REF CURSOR;
  TYPE t_id_table IS TABLE OF NVARCHAR(38) INDEX BY PLS_INTEGER;
  
  PROCEDURE someentity_select(
    p_ids     IN  t_id_table, 
    p_results OUT t_cursor);
  
END;

CREATE OR REPLACE PACKAGE BODY some_package AS
  
  PROCEDURE someentity_select(
    p_ids     IN  t_guid_table, 
    p_results OUT t_cursor)
  IS
  BEGIN
  
    OPEN p_results FOR 
      SELECT * 
      FROM someschema.someentity 
      WHERE id IN (SELECT column_value FROM TABLE(p_ids)); - fails here
      
  END;

END;

注意：someschema.someentity.id 是 NVARCHAR2(38)

PL/SQL: ORA-00382: expression is of wrong type
PL/SQL: ORA-22905: cannot access rows from a non-nested table item

我哪里出错了？

Answer 1

在 12.2 之前的 Oracle 版本中，您只能从通过 CREATE TYPE 语句在数据库中定义的集合类型中进行 SELECT，不能是关联数组：

CREATE TYPE t_id_table IS TABLE OF NVARCHAR(38);

CREATE OR REPLACE PACKAGE some_package AS

  PROCEDURE someentity_select(
    p_ids     IN  t_guid_table, 
    p_results OUT SYS_REFCURSOR);

END;

CREATE OR REPLACE PACKAGE BODY some_package AS

  PROCEDURE someentity_select(
    p_ids     IN  t_guid_table, 
    p_results OUT SYS_REFCURSOR)
  IS
  BEGIN

    OPEN p_results FOR 
      SELECT * 
      FROM someschema.someentity 
      WHERE id IN (SELECT column_value FROM TABLE(p_ids));

  END;

END;

Answer 2

这是一个索引表，它是 PL/SQL 类型。

只能在Oracle的SQL引擎中使用SQL类型。或者 PL/SQL 类型，Oracle 可以将其修改为看起来像 SQL 类型。

您可以拥有一个简单的类似数组的集合并将其用作结果。 （无索引）

type TGuidList is table of NVarchar(38);

但是最好的兼容性和稳定性，可以通过将其声明为全局 SQL 类型并在包内使用它来获得：

create type TGuidList is table of NVarchar(38);

编辑：您不需要 NVarChar 作为 GUID，对吗？一个好的 ol' VarChar 应该可以很好地完成这个任务。