如何从 Oracle 中的值列表中进行选择

2021-12-05 00:00:00 sql oracle11g select oracle

我指的是这个 stackoverflow 答案:

I am referring to this stackoverflow answer:

我该怎么办从 SQL Server 中的值列表中选择

如何在 Oracle 中完成类似的操作?

How could something similar be done in Oracle?

我在此页面上看到了其他使用 UNION 的答案，虽然这种方法在技术上可行，但我并不想在我的情况下使用它.

I've seen the other answers on this page that use UNION and although this method technically works, it's not what I would like to use in my case.

所以我想保留或多或少看起来像逗号分隔的值列表的语法.

So I would like to stay with syntax that more or less looks like a comma-separated list of values.

更新 关于创建类型表 答案:

我有一张桌子:

CREATE TABLE BOOK
(   "BOOK_ID" NUMBER(38,0)
)

我使用这个脚本，但它没有向 BOOK 表中插入任何行:

I use this script but it does not insert any rows to the BOOK table:

create type number_tab is table of number;

INSERT INTO BOOK (
    BOOK_ID
)
SELECT A.NOTEBOOK_ID FROM
    (select column_value AS NOTEBOOK_ID from table (number_tab(1,2,3,4,5,6))) A
;

脚本输出:

TYPE number_tab compiled
Warning: execution completed with warning

但如果我使用这个脚本，它会向 BOOK 表中插入新行:

But if I use this script it does insert new rows to the BOOK table:

INSERT INTO BOOK (
    BOOK_ID
)
SELECT A.NOTEBOOK_ID FROM
    (SELECT (LEVEL-1)+1 AS NOTEBOOK_ID FROM DUAL CONNECT BY LEVEL<=6) A
;

推荐答案

您不需要创建任何存储类型，您可以评估 Oracle 的内置集合类型.

You don't need to create any stored types, you can evaluate Oracle's built-in collection types. 

select distinct column_value from table(sys.odcinumberlist(1,1,2,3,3,4,4,5))

相关文章