如何从Oracle中的值列表中进行选择 [英] How can I select from list of values in Oracle
问题描述
我指的是这个stackoverflow
答案:
在 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屋!