如何在Oracle中编写表文字? [英] How to write a table literal in Oracle?

问题描述

可以使用以下方法创建具有一列和一行的表:

A table with one column and one row can be created with:

select 'create' as col from dual;

这可用于建立表联接:

with
  a as (select 'create' as ac from dual),
  b as (select 'delete' as bc from dual)
select * from a left outer join b on (ac = bc);

现在我想有两行.我是这样做的:

Now I would like to have two rows. I did it in this way:

select 'create' as col from dual
union
select 'delete' as col from dual;

但是对此有更紧凑的表示法吗?我尝试过

But is there a more compact notation for this? I tried

select ('create', 'delete') as col from dual;

但它不起作用.

推荐答案

例如，您可以使用集合类型和TABLE运算符(在Oracle 10g中有效):

You can use collection type and TABLE operator, for example (works in Oracle 10g):

SQL> SELECT column_value FROM TABLE(SYS.ODCIVARCHAR2LIST('abc', 'def', 'ghi'));

COLUMN_VALUE
--------------------------------------------------------------------------------
abc
def
ghi

这篇关于如何在Oracle中编写表文字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！