如何从Oracle中的值列表中进行选择 [英] How can I select from list of values in Oracle

问题描述

我指的是这个stackoverflow答案:

我如何从SQL Server的值列表中选择

在 Oracle 中如何做类似的事情?

我在此页面上还看到了其他使用UNION的答案，尽管该方法在技术上可行，但这并不是我想使用的方法.

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

关于create type table

我使用此脚本，但是它不会在BOOK表中插入任何行:

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表中:

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的内置集合类型.

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

I am referring to this stackoverflow answer:

How can I select from list of values in SQL Server

How could something similar be done in Oracle?

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.

UPDATE regarding the create type table answer:

I have a table:

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

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
;

Script output:

TYPE number_tab compiled
Warning: execution completed with warning

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
;

解决方案

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

这篇关于如何从Oracle中的值列表中进行选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！