oracle 如何使用变量在动态查询中指定 IN 子句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8967652/
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
How do you specify IN clause in a dynamic query using a variable?
提问by Please click here
In PL/SQL, you can specify the values for the IN operator using concatenation:
在 PL/SQL 中，您可以使用串联指定 IN 运算符的值：
v_sql := 'select field1
from table1
where field2 in (' || v_list || ')';
Is it possible to do the same using a variable?
是否可以使用变量来做同样的事情？
v_sql := 'select field1
from table1
where field2 in (:v_list)';
If so, how?
如果是这样，如何？
EDIT: With reference to Marcin's answer, how do I select from the resultant table?
编辑：参考 Marcin 的回答，我如何从结果表中进行选择？
declare
cursor c_get_csv_as_tables is
select in_list(food_list) food_list
from emp_food
where emp_type = 'PERM';
cursor c_get_food_list (v_food_table varchar2Table)is
select *
from v_food_table;
begin
for i in c_get_csv_as_tables loop
for j in c_get_food_list(i.food_list) loop
dbms_output.put_line(j.element);
end loop;
end loop;
end;
I get the following error:
我收到以下错误：
ORA-06550: line 10, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 9, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 34:
PLS-00364: loop index variable 'J' use is invalid
ORA-06550: line 15, column 13:
PL/SQL: Statement ignored
回答by A.B.Cade
Like in @Sathya link, you can bind the varray (I took @Codo example):
就像在@Sathya 链接中一样，您可以绑定 varray（我以@Codo 为例）：
CREATE OR REPLACE TYPE str_tab_type IS VARRAY(10) OF VARCHAR2(200);
/
DECLARE
l_str_tab str_tab_type;
l_count NUMBER;
v_sql varchar2(3000);
BEGIN
l_str_tab := str_tab_type();
l_str_tab.extend(2);
l_str_tab(1) := 'TABLE';
l_str_tab(2) := 'INDEX';
v_sql := 'SELECT COUNT(*) FROM all_objects WHERE object_type IN (SELECT COLUMN_VALUE FROM TABLE(:v_list))';
execute immediate v_sql into l_count using l_str_tab;
dbms_output.put_line(l_count);
END;
/
UPDATE:the first command can be replaced with:
更新：第一个命令可以替换为：
CREATE OR REPLACE TYPE str_tab_type IS TABLE OF VARCHAR2(200);
/
then call:
然后调用：
l_str_tab.extend(1);
when ever you add a value
当你添加一个值时
回答by Marcin Wroblewski
Unfortunately you cannot bind a list like this, however you can use a table function. Read this
不幸的是，您不能绑定这样的列表，但是您可以使用表函数。读 这个
Here's an example of usage based on your code:
以下是基于您的代码的用法示例：
declare
cursor c_get_csv_as_tables is
select in_list(food_list) food_list
from emp_food
where emp_type = 'PERM';
cursor c_get_food_list (v_food_table varchar2Table)is
select column_value food
from TABLE(v_food_table);
begin
for i in c_get_csv_as_tables loop
for j in c_get_food_list(i.food_list) loop
dbms_output.put_line(j.food);
end loop;
end loop;
end;
I used here a column_valuepseudocolumn
我在这里使用了一个column_value伪列
回答by Kat
Bind variable can be used in Oracle SQL query with "in" clause.
绑定变量可以在带有“in”子句的 Oracle SQL 查询中使用。
Works in 10g; I don't know about other versions.
在 10g 中工作；我不知道其他版本。
Bind variable is varchar up to 4000 characters.
绑定变量是最多 4000 个字符的 varchar。
Example: Bind variable containing comma-separated list of values, e.g.
示例：绑定包含逗号分隔值列表的变量，例如
:bindvar = 1,2,3,4,5
:bindvar = 1,2,3,4,5
select * from mytable
where myfield in
(
SELECT regexp_substr(:bindvar,'[^,]+', 1, level) items
FROM dual
CONNECT BY regexp_substr(:bindvar, '[^,]+', 1, level) is not null
);
回答by Ben
As per @Marcin's answer you can't do this, however, there's a fair bit to add to that, as your query should actually work, i.e. run.
根据@Marcin 的回答，您不能这样做，但是，还有一点要补充，因为您的查询实际上应该可以工作，即运行。
Simply put, you cannot use a bind variable for a table or column. Not only that, bind variables they are assumed to be a character, so if you want a number you have to use to_number(:b1)etc.
简而言之，您不能对表或列使用绑定变量。不仅如此，绑定变量它们被假定为一个字符，所以如果你想要一个数字，你必须使用to_number(:b1)等。
This is where your query falls down. As you're passing in a string Oracle assumes that your entire list is a single string. Thus you are effectively running:
这是您的查询失败的地方。当您传入一个字符串时，Oracle 假定您的整个列表都是一个字符串。因此，您正在有效地运行：
select field1
from table1
where field2 = v_list
There is no reason why you can't do this a different way though. I'm going to assume you're dynamically creating v_list, which means that all you need to do is create this list differently. A series of orconditions is, purportedly :-), no different to using an in.
不过，没有理由不能以不同的方式做到这一点。我将假设您正在动态创建v_list，这意味着您需要做的就是以不同的方式创建此列表。一系列or条件据称是:-)，与使用in.
By purportedly, I mean never rely on something that's untested. Although Tom does say in the link that there may be performance constraints there's no guarantee that it wasn't quicker than using into begin with. The best thing to do is to run the trace on your query and his and see what difference there is, if any.
据称，我的意思是永远不要依赖未经测试的东西。尽管汤姆确实在链接中说可能存在性能限制，但不能保证它不会比in开始使用更快。最好的办法是在您的查询和他的查询上运行跟踪，看看有什么区别（如果有的话）。
SQL> set serveroutput on
SQL>
SQL> declare
2
3 l_string varchar2(32767);
4 l_count number;
5
6 begin
7
8 for xx in ( select rownum as rnum, a.*
9 from user_tables a
10 where rownum < 20 ) loop
11
12 if xx.rnum = 1 then
13 l_string := 'table_name = ''' || xx.table_name || '''';
14 else
15 l_string := l_string || ' or table_name = ''' || xx.table_name || '
''';
16 end if;
17
18 end loop;
19
20 execute immediate 'select count(*)
21 from user_tables
22 where ' || l_string
23 into l_count
24 ;
25
26 dbms_output.put_line('count is ' || l_count);
27
28 end;
29 /
count is 19
PL/SQL procedure successfully completed.
