从本质上讲,我的问题是我需要在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 dualSo, 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 dualor
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更多推荐
发布评论