Oracle集合中的where子句 [英] Oracle collection in where clause

问题描述

我在oracle代码块中使用集合，因为没有表变量(例如在MS SQL Server中).

I am using collection in a oracle code block because there is no table variable(like in MS SQL Server).

DECLARE
    TYPE I_NAME IS TABLE OF NVARCHAR2(512);     
    I_ITEMNAME      I_NAME := I_NAME(); 
BEGIN 

我正在使用批量收集到I_ITEMNAME"来填充收藏集.
我想在SELECT查询的WHERE子句中使用此集合，但无法找到执行该方法的方法.目前，我正在使用FOR循环并逐一获取项目.
我如何直接在

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

选择*从TBL I_ITEMNAME中的何处?

SELECT * FROM TBL WHERE COL IN I_ITEMNAME?

谢谢

推荐答案

您不能在SQL子句中使用本地声明的集合:

You can't use a locally declared collection in an SQL clause:

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

但是您可以在架构级别进行声明，本质上是让SQL知道类型，而不仅仅是PL/SQL:

But you can if it's declared at schema level, essentially so that SQL knows about the type, not just 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.

您还可以加入table构造，而不是使用子查询:

You can also join the table construct rather than use a subquery:

...
    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).

正如@haki在评论中提到的，您也可以这样做:

As @haki mentioned in comments, you can also do:

...
    select count(*) into c
    from all_tables
    where owner member of (i_itemname);
...

...只要i_name以及您要与属于同一类型.在我的示例中，它发现零行，因为我试图将nvarchar2与varchar2进行比较，但是如果将i_name重新定义为varchar2(512)，则会找到匹配项.在您的情况下，tab.col始终是nvarchar2.

... as long as i_name and the column you're comparing with are the same type. In my example it finds zero rows because I'm trying to compare nvarchar2 with varchar2, but would find a match if redefined i_name as varchar2(512). In your case presumably tab.col is nvarchar2 anyway.

这篇关于Oracle集合中的where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！