Oracle SQL 中的内联表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5497437/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inline table in Oracle SQL
提问by Clinton
I'm trying to integrate with some software (that I can't modify) that queries a database that I can modify.
我正在尝试与一些查询我可以修改的数据库的软件（我无法修改）集成。
I can give this software SQL queries, like so "select username, firstname, lastname from users where username in ?"
我可以给这个软件 SQL 查询，比如“从用户名中选择用户名、名字、姓氏？”
The software than fills in the ? with something like ('alice', 'bob'), and gets user information for them.
该软件比填写？使用 ('alice', 'bob') 之类的东西，并为他们获取用户信息。
Thing is, there's another piece of software, which I again can't modify, which occasionally generates users like 'user2343290' and feeds them through to the first piece of software. Of course, it throws errors because it can't find that user.
问题是，还有另一个软件，我又不能修改，它偶尔会生成像“user2343290”这样的用户，并将它们提供给第一个软件。当然，它会抛出错误，因为它找不到那个用户。
So the query I want to run is something like this:
所以我想运行的查询是这样的：
select username, firstname, lastname from users where username in ?
UNION ALL
select t.column1, 'Unknown', 'Unknown' from create_table(?) t
where create_table generates a table with the rows mentioned in ?, with the first column named column1.
其中 create_table 生成一个表，其中包含 ? 中提到的行，第一列名为 column1。
Or alternatively:
或者：
select username, firstname, lastname from users where username in ?
UNION ALL
select t.column1, 'Unknown', 'Unknown' from _universe_ t where t.column1 in ?
where _universe_is some fake table that contains possible every value in column1 (i.e. infinitely large).
哪里_universe_是一些假表，其中包含 column1 中可能的每个值（即无限大）。
I've tried select ? from dual, but unfortunately this only worked when ? was something like ('x'), not ('x', 'y').
我试过选择？来自双重，但不幸的是这只在什么时候有效？类似于 ('x')，而不是 ('x', 'y')。
Keep in mind I can't change the format of how the ? comes out, so I can't do select 'alice' from dual union all select 'bob' from dual.
请记住，我无法更改 ? 出来，所以我做不到select 'alice' from dual union all select 'bob' from dual。
Anyone know how I could do what I've mentioned, or something else to have a similar effect?
任何人都知道我如何做我提到的事情，或者其他有类似效果的东西？
采纳答案by Dave Costa
You can turn the delimited string of names into a table type like so:
您可以将分隔的名称字符串转换为表类型，如下所示：
CREATE TYPE name_tab AS TABLE OF VARCHAR2(30);
/
SELECT * FROM table(name_tab('alice','bob'));
So you would just need to create the type then your example would become:
所以你只需要创建类型然后你的例子就会变成：
select username, firstname, lastname from users where username in ?
UNION ALL
select t.column1, 'Unknown', 'Unknown' from table(name_tab ?) t
(I'm assuming that the ? is replaced by simple text substitution -- because the IN wouldn't work if it was done as a bind variable -- and that the substituted text includes the parentheses.)
（我假设 ? 被简单的文本替换替换——因为如果将 IN 作为绑定变量完成，它将不起作用——并且替换的文本包括括号。）
However, I am not sure the result of this will be helpful, since when a list of good usernames is given, you'll now have two result rows for each username, one with the actual information and another with the 'Unknown' values.
但是，我不确定这样做的结果是否会有所帮助，因为当给出一个好的用户名列表时，现在每个用户名都有两个结果行，一个包含实际信息，另一个包含“未知”值。
A better way to phrase the query might be:
表达查询的更好方法可能是：
select t.column_value username,
NVL(users.firstname,'Unknown'),
NVL(users.lastname,'Unknown')
from table(name_tab ?) t left join users on users.username = t.column_value
That should give you one row per username, with the actual data if it exists, or the 'Unknown' values if it does not.
这应该为每个用户名提供一行，如果存在则为实际数据，如果不存在则为“未知”值。
回答by Frank Schmitt
You could use a pipelined function:
您可以使用流水线函数：
create type empname_t is table of varchar2(100);
create or replace function to_list(p_Names in string) return empname_t pipelined is
begin
pipe row(p_Names);
return;
end;
select * from table(to_list('bob'))
If you need to split the names (e.g. 'bob,alice'), you could use a function accepting a string and returning a empname_t, e.g. Tom Kyte's in_list, see
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:210612357425
and modify the to_list function to iterate over the collection and pipe each item from the collection.
如果您需要拆分名称（例如“bob,alice”），您可以使用接受字符串并返回 empname_t 的函数，例如 Tom Kyte 的 in_list，请参阅
http://asktom.oracle.com/pls/apex/f ?p=100:11:0::::P11_QUESTION_ID:210612357425
并修改 to_list 函数以遍历集合并管道化集合中的每个项目。
