如何从Oracle中选择值列表?

I am referring to this * answer:

我指的是这个*的答案:

How can I select from list of values in SQL Server

如何从SQL Server中选择值列表?

How could something similar be done in Oracle?

在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.

我已经在这个页面上看到了使用UNION的其他答案，尽管这个方法在技术上是可行的，但在我的案例中并不是我想要使用的。

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:

我使用这个脚本，但它不会将任何行插入到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
;

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:

但是如果我使用这个脚本，它会向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
;

5 个解决方案

#1

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

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

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

#2

If you are seeking to convert a comma delimited list of values:

如果你想要转换一个逗号分隔的值列表:

select column_value 
from table(sys.dbms_debug_vc2coll('One', 'Two', 'Three', 'Four'));

-- Or

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

If you wish to convert a string of comma delimited values then I would recommend Justin Cave's regular expression SQL solution.

如果您希望转换一串逗号分隔值，那么我将推荐Justin Cave的正则表达式SQL解决方案。

#3

There are various ways to take a comma-separated list and parse it into multiple rows of data. In SQL

使用逗号分隔的列表并将其解析成多行数据的方法多种多样。在SQL

SQL> ed
Wrote file afiedt.buf

  1  with x as (
  2    select '1,2,3,a,b,c,d' str from dual
  3  )
  4   select regexp_substr(str,'[^,]+',1,level) element
  5     from x
  6* connect by level <= length(regexp_replace(str,'[^,]+')) + 1
SQL> /

ELEMENT
----------------------------------------------------
1
2
3
a
b
c
d

7 rows selected.

Or in PL/SQL

或PL / SQL

SQL> create type str_tbl is table of varchar2(100);
  2  /

Type created.

SQL> create or replace function parse_list( p_list in varchar2 )
  2    return str_tbl
  3    pipelined
  4  is
  5  begin
  6    for x in (select regexp_substr( p_list, '[^,]', 1, level ) element
  7                from dual
  8             connect by level <= length( regexp_replace( p_list, '[^,]+')) + 1)
  9    loop
 10      pipe row( x.element );
 11    end loop
 12    return;
 13  end;
 14
 15  /

Function created.

SQL> select *
  2    from table( parse_list( 'a,b,c,1,2,3,d,e,foo' ));

COLUMN_VALUE
--------------------------------------------------------------------------------
a
b
c
1
2
3
d
e
f

9 rows selected.

#4

You can do this:

你可以这样做:

create type number_tab is table of number;

select * from table (number_tab(1,2,3,4,5,6));

The column is given the name COLUMN_VALUE by Oracle, so this works too:

该列由Oracle提供名为COLUMN_VALUE的列，因此它也可以工作:

select column_value from table (number_tab(1,2,3,4,5,6));

#5

Hi it is also possible for Strings with XML-Table

Hi，也可以使用xml表的字符串。

SELECT trim(COLUMN_VALUE) str FROM xmltable(('"'||REPLACE('a1, b2, a2, c1', ',', '","')||'"'));

#1