Oracle从IN子句中选择数字

I'm looking for the best way to select numbers directly from an in clause.

我正在寻找直接从in子句中选择数字的最佳方法。

Basically like:

SELECT * FROM (2,6,1,8);

That doesn't work. I can do it this way:

这不起作用。我可以这样做:

SELECT Lv FROM (  SELECT Level LV
                  FROM DUAL
                  CONNECT BY Level < 20)
WHERE Lv IN (2,6,1,8);

But that seems to be a bit clunky. Is there a more elegant way?

但这似乎有点笨重。有更优雅的方式吗?

3 个解决方案

#1

It's more elegant if you materialize an auxiliary numbers table:

如果您实现辅助数字表,它会更优雅:

SELECT num FROM numbers WHERE num IN (2,6,1,8);

And this is also useful when combined with another table.

当与另一个表结合使用时,这也很有用。

For instance, I've had a case where I needed to populate large configuration tables with changes from piecewise results:

例如,我有一个案例,我需要使用分段结果的变化来填充大型配置表:

Big SP or Excel sheet or report identifies missing cost centers in config gives a large set of results which need to be inserted with varying data in some groups.

大型SP或Excel工作表或报告标识了配置中缺少的成本中心,提供了大量结果,需要在某些组中插入不同的数据。

Paste partial results into a individual comma separated lists:

将部分结果粘贴到单独的逗号分隔列表中:

INSERT INTO {stuff}
SELECT {stuff}, 130 as line_item
FROM numbers
WHERE numbers.num IN ({pasted a section of results})

INSERT INTO {stuff}
SELECT {stuff}, 135 as line_item
FROM numbers
WHERE numbers.num IN ({pasted another section of results})

#2

You can do

你可以做

select column_value from table(sys.dbms_debug_vc2coll(1,2,3,4,5));

but that actually returns a varchar2. You can create your own TYPE and use that

但实际上这会返回一个varchar2。您可以创建自己的TYPE并使用它

create type tab_num is table of number;
/
select column_value from table(tab_num(1,2,3,4,5));

It's also worth looking at the MODEL clause. It looks complicated, but it is very good at generating data

它也值得看看MODEL子句。它看起来很复杂,但它非常擅长生成数据

SELECT x from dual
MODEL DIMENSION BY (1 AS z) MEASURES (1 x)
  RULES ITERATE (7) (x[ITERATION_NUMBER]=ITERATION_NUMBER+1)

#3

If you don't explicitly need the IN clause, you could use UNION:

如果您不明确需要IN子句,则可以使用UNION:

  select 2 from dual
  union
  select 6 from dual
  union
  select 1 from dual
  union
  select 8 from dual

There is a more elegant variant to INSERT multiple rows into a table:

将多行INSERT到表中有一个更优雅的变体:

INSERT ALL
   INTO table (col) VALUES ('a')
   INTO table (col) VALUES ('b')
   INTO table (col) VALUES ('c')
SELECT * FROM dual;

But I don't know a way to do that for a SELECT.

但我不知道如何为SELECT做到这一点。

#1