在 IN 子句中使用 Oracle 表类型 - 编译失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2045554/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using an Oracle Table Type in IN-clause - compile fails
提问by Travis Heseman
Simply trying to get a cursor back for the ids that I specify.
只是试图为我指定的 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;
Note: someschema.someentity.id is a NVARCHAR2(38)
注意：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
PL/SQL：ORA-00382：表达式类型错误
PL/SQL：ORA-22905：无法访问来自非嵌套表项的行
Where am I going wrong?
我哪里错了？
回答by Tony Andrews
In Oracle versions prior to 12.2 you can only SELECT from a collection type that is defined in the database via a CREATE TYPE statement, notan associative array:
在 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;
回答by Robert Giesecke
This is an index-by table, which is a PL/SQL type.
这是一个索引表，它是一种 PL/SQL 类型。
You can only use SQL types in the SQL engine of Oracle. Or PL/SQL types, that Oracle can hack around to look like SQL types.
您只能在 Oracle 的 SQL 引擎中使用 SQL 类型。或者 PL/SQL 类型，Oracle 可以将其修改为看起来像 SQL 类型。
You can have a simple array-like collection and use it as a result. (no index by)
您可以拥有一个简单的类似数组的集合并将其用作结果。（没有索引）
type TGuidList is table of NVarchar(38);
But the best compatibility and stability, you get by declaring it as a global SQL type and use that inside your package:
但是最好的兼容性和稳定性，您可以通过将其声明为全局 SQL 类型并在您的包中使用它来获得：
create type TGuidList is table of NVarchar(38);
创建类型 TGuidList 是 NVarchar(38) 表；
Edit: You will not need an NVarChar for a GUID, will you? A good ol' VarChar should do the trick just fine.
编辑：GUID 不需要 NVarChar，对吗？一个好的 ol' VarChar 应该可以很好地解决问题。
