oracle 选择要合并到另一个查询中的静态值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18744771/
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
Selecting static values to union into another query
提问by huntmaster
Essentially, my problem is that I need to run a query in Oracle that unions a static list of values ('Static' meaning it's obtained from somewhere else that I cannot get from the database, but is actually an arbitrary list of values I plug into the query) with a dynamic list of values returned from a query.
本质上，我的问题是我需要在 Oracle 中运行一个查询，该查询结合了一个静态值列表（“静态”意味着它是从我无法从数据库中获取的其他地方获得的，但实际上是我插入的任意值列表查询）带有从查询返回的动态值列表。
So, my initial query looks like:
因此，我的初始查询如下所示：
select * from (select ('18776') as instanceid from dual) union (<more complex query>)
I think, hooray! And then try to do it with a longer list of static values. Turns out, I get 'Missing Right Parenthesis' if I try to run:
我想，万岁！然后尝试使用更长的静态值列表来完成它。事实证明，如果我尝试运行，我会得到“缺少右括号”：
select ('18776','18775') as instanceid from dual
So, my basic issue is how can I integrate a list of static values into this union?
所以，我的基本问题是如何将静态值列表集成到这个联合中？
NOTE: This is a simplified example of the problem. The actual list is generated from an API before I generate a query, and so this list of "static" values is unpredictably and arbitrarily large. I'm not dealing with just 2 static values, it is an arbitrary list.
注意：这是问题的一个简化示例。实际列表是在我生成查询之前从 API 生成的，因此这个“静态”值列表不可预测且任意大。我不只处理 2 个静态值，它是一个任意列表。
回答by Kirill Leontev
select '18776' as instanceid from dual union all
select '18775' as instanceid from dual
or
或者
select column_value from table(sys.odcivarchar2list('18776', '18775'))
or some sort of hierarchical query that could take your comma separated-string and split it into a set of varchars.
或某种分层查询，可以将您的逗号分隔字符串拆分为一组 varchars。
Union these to your initial query.
将这些与您的初始查询结合起来。
update: "I'm not dealing with just 2 static values, it is an arbitrary list."
更新：“我不只处理 2 个静态值，它是一个任意列表。”
Still can pass to a query as a collection (below is just one of many possible approaches)
仍然可以作为集合传递给查询（以下只是许多可能的方法之一）
23:15:36 LKU@sandbox> ed
Wrote file S:\spool\sandbox\BUFFER_LKU_39.sql
1 declare
2 cnt int := 10;
3 coll sys.odcivarchar2list := sys.odcivarchar2list();
4 begin
5 coll.extend(cnt);
6 for i in 1 .. cnt loop
7 coll(i) := dbms_random.string('l', i);
8 end loop;
9 open :result for 'select * from table(:c)' using coll;
10* end;
23:37:03 11 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.50
23:37:04 LKU@sandbox> print result
COLUMN_VALUE
-------------------------------------------------------------
g
kd
qdv
soth
rvwnq
uyfhbq
xxvxvtw
eprralmd
edbcajvfq
ewveyljsjn
10 rows selected.
Elapsed: 00:00:00.01
回答by Gordon Linoff
I think you want to break it into two subqueries:
我想你想把它分成两个子查询：
select *
from ((select '18776' as instanceid from dual)
union
(select '18775' as instanceid from dual)
union
(<more complex query>)
) t;
Note that union allperforms better than union. If you know there are no duplicates (or duplicates don't matter) then use union allinstead.
请注意，union all性能优于union. 如果您知道没有重复项（或重复项无关紧要），请union all改用。
回答by adona9
If you have the ability/permission to create a table type, you can do this:
如果您有能力/权限创建表类型，则可以执行以下操作：
CREATE OR REPLACE
TYPE TYP_NUMBER_TABLE AS TABLE OF NUMBER(11);
And then you can use the TABLE function to select from a instance of that type that you initialize on the fly in your SQL:
然后您可以使用 TABLE 函数从您在 SQL 中动态初始化的该类型的实例中进行选择：
SELECT COLUMN_VALUE FROM TABLE(TYP_NUMBER_TABLE(1, 2, 3));
Result:
结果：
COLUMN_VALUE
------------
1
2
3
