where 子句中的 Oracle 集合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16185277/
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
Oracle collection in where clause
提问by par
I am using collection in a oracle code block because there is no table variable(like in MS SQL Server).
我在 oracle 代码块中使用集合，因为没有表变量（如在 MS SQL Server 中）。
DECLARE
TYPE I_NAME IS TABLE OF NVARCHAR2(512);
I_ITEMNAME I_NAME := I_NAME();
BEGIN
I am using "BULK COLLECT INTO I_ITEMNAME" to fill collection.
I want to use this collection in WHERE clause in a SELECT query but not able to find method to do it. Currently i and using FOR loop and getting item one by one.
How can i use collection directly in WHERE clause somethin like
我正在使用“BULK COLLECT INTO I_ITEMNAME”来填充集合。
我想在 SELECT 查询的 WHERE 子句中使用此集合，但无法找到执行此操作的方法。目前我正在使用 FOR 循环并一一获取项目。
我如何直接在 WHERE 子句中使用集合
SELECT * FROM TBL WHERE COL IN I_ITEMNAME?
SELECT * FROM TBL WHERE COL IN I_ITEMNAME？
Thank you,
谢谢，
回答by Alex Poole
You can't use a locally declared collection in an SQL clause:
您不能在 SQL 子句中使用本地声明的集合：
declare
type i_name is table of nvarchar2(512);
i_itemname i_name := i_name();
c number;
begin
select distinct owner bulk collect into i_itemname from all_objects;
dbms_output.put_line(i_itemname.count);
select count(*) into c
from all_tables
where owner in (select * from table(i_itemname));
dbms_output.put_line(c);
end;
/
where owner in (select * from table(i_itemname));
*
ERROR at line 10:
ORA-06550: line 10, column 41:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 10, column 35:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored
But you can if it's declared at schema level, essentially so that SQL knows about the type, not just PL/SQL:
但是如果它是在模式级别声明的，那么你可以，基本上是为了让 SQL 知道类型，而不仅仅是 PL/SQL：
create type i_name is table of nvarchar2(512);
/
Type created.
declare
i_itemname i_name := i_name();
c number;
begin
select distinct owner bulk collect into i_itemname from all_objects;
dbms_output.put_line(i_itemname.count);
select count(*) into c from all_tables
where owner in (select * from table(i_itemname));
dbms_output.put_line(c);
end;
/
No errors.
18
128
PL/SQL procedure successfully completed.
You can also join the tableconstruct rather than use a subquery:
您还可以加入table构造而不是使用子查询：
...
select count(*) into c
from table(i_itemname) t
join all_tables at on at.owner = t.column_value;
...
I'm not quite clear what you're dong though. (If you aren't using the collection for anything else, you'd be better off just joining the raw data, but I assume the collection is there for a reason).
不过我不太清楚你是什么东东。（如果您不将集合用于其他任何用途，最好只加入原始数据，但我认为该集合存在是有原因的）。
As @haki mentioned in comments, you can also do:
正如@haki 在评论中提到的，您还可以执行以下操作：
...
select count(*) into c
from all_tables
where owner member of (i_itemname);
...
... as long as i_nameand the column you're comparing with are the same type. In my example it finds zero rows because I'm trying to compare nvarchar2with varchar2, but would find a match if redefined i_nameas varchar2(512). In your case presumably tab.colis nvarchar2anyway.
...只要i_name和您要比较的列类型相同。在我的例子找到零行，因为我想比较nvarchar2有varchar2，但如果重新定义会找到一个匹配i_name的varchar2(512)。在你的情况下大概tab.col是nvarchar2无论如何。
