选择静态值以合并到另一个查询中(Selecting static values to union into another query)

选择静态值以合并到另一个查询中(Selecting static values to union into another query)

从本质上讲，我的问题是我需要在Oracle中运行一个查询静态值列表（'静态'意味着它是从我无法从数据库获取的其他地方获得的，但实际上是我插入的任意值列表查询）具有从查询返回的动态值列表。

所以，我的初始查询看起来像：

select * from (select ('18776') as instanceid from dual) union (<more complex query>)

我想，万岁！ 然后尝试使用更长的静态值列表。 事实证明，如果我尝试运行，我会得到'Missing Right Parenthesis'：

select ('18776','18775') as instanceid from dual

所以，我的基本问题是如何将静态值列表集成到此联合中？

注意：这是问题的简化示例。 在生成查询之前，实际列表是从API生成的，因此这个“静态”值列表是不可预测且任意大的。 我不是只处理2个静态值，它是一个任意列表。

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.

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.

最满意答案

select '18776' as instanceid from dual union all select '18775' as instanceid from dual

要么

select column_value from table(sys.odcivarchar2list('18776', '18775'))

或某种分层查询，可以将您的逗号分隔为字符串并将其拆分为一组varchars。

将这些与您的初始查询相结合。

更新 ：“我不是只处理2个静态值，它是一个任意列表。”

仍然可以作为集合传递给查询（下面只是许多可能的方法之一）

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

Union these to your initial query.

update: "I'm not dealing with just 2 static values, it is an arbitrary list."

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