如何在oracle SQL中从双重选择10,000个唯一ID的列表 [英] how to select a list of 10,000 unique ids from dual in oracle SQL

问题描述

所以我无法创建或编辑表(我是具有只读权限的用户)，并且想查找10,000个唯一ID.我无法将它们放在IN()语句中，因为oracle限制了1000多个项目.

So I can't create or edit tables (I'm a user with read only permission) and I want to look up 10,000 unique id's. I can't put them inside of an IN() statement because oracle limits over 1000 items.

是否可以从oracle的DUAL表中选择整个列表?像这样:

Is it possible to select this entire list from the DUAL table in oracle? Something like:

select  
'id123,id8923,id32983,id032098,id308230,id32983289'  
from DUAL

推荐答案

使用集合(像IN子句一样，它们不限于1000个项目):

Use a collection (they are not limited to 1000 items like an IN clause is):

SELECT COLUMN_VALUE AS id
FROM   TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       )

SYS.ODCIVARCHAR2LIST和SYS.ODCINUMBERLIST是SYS模式中提供的集合类型.

SYS.ODCIVARCHAR2LIST and SYS.ODCINUMBERLIST are collection types that are supplied in the SYS schema.

您可以将其直接连接到要从哪个SELECT表中，而无需使用DUAL表:

You can join this directly to whichever table you are SELECTing from without needing to use the DUAL table:

SELECT y.*
FROM   your_table y,
       TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       ) i
WHERE  y.id = i.COLUMN_VALUE;

如果您可以创建一个集合类型，那么您甚至不需要TABLE表达式，就可以使用MEMBER OF运算符直接在WHERE子句中使用它:

If you can get a collection type created then you do not even need the TABLE expression and can use it directly in the WHERE clause using the MEMBER OF operator:

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(200);
/

SELECT *
FROM   yourtable
WHERE  id MEMBER OF stringlist(
                      'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
                    );

您甚至可以将值作为绑定参数传递-请参见我的回答此处

You can even pass the values as a bind parameter - see my answer here

这篇关于如何在oracle SQL中从双重选择10,000个唯一ID的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！