oracle sqlplus - 在“IN”子句中使用绑定变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4973809/
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
sqlplus - using a bind variable in "IN" clause
提问by FrustratedWithFormsDesigner
I am setting a bind variable in a PL/SQL block, and I'm trying to use it in another query's IN expression. Something like this:
我正在 PL/SQL 块中设置一个绑定变量，并且我试图在另一个查询的 IN 表达式中使用它。像这样的东西：
variable x varchar2(255)
declare
x varchar2(100);
begin
for r in (select id from other_table where abc in ('&val1','&val2','&val3') ) loop
x := x||''''||r.id||''',';
end loop;
--get rid of the trailing ','
x:= substr(x,1,length(x)-1);
select x into :bind_var from dual;
end;
/
print :bind_var;
select *
from some_table
where id in (:bind_var);
And I get an error (ORA-01722: Invalid number) on the query that tries to use the bind variable in the "IN" list.
我在尝试使用“IN”列表中的绑定变量的查询中收到错误（ORA-01722：无效数字）。
The print statement yiels '123','345'which is what I expect.
打印语句产生'123','345'了我所期望的。
Is it possible to use the bind variable like this or should I try a different approach?
是否可以像这样使用绑定变量，还是应该尝试不同的方法？
(using Oracle 10g)
（使用 Oracle 10g）
Clarification:
澄清：
This is for a reconcilliation sort of thing. I want to run
这是为了和解之类的事情。我想跑
select *
from some_table
where id in (select id from other_table where abc in ('&val1','&val2','&val3'))
before the main part of the script (not pictured here) deletes a whole bunch of records. I want to run it again afterwards to verify that records in some_tablehave NOT been deleted. However, the data in other_tableDOES get deleted by this process so I can't just refer to the data in other_tablebecause there's nothing there. I need a way to preserve the other_table.idvalues so that I can verify the parent records afterwards.
在脚本的主要部分（此处未显示）删除一大堆记录之前。之后我想再次运行它以验证some_table没有删除中的记录。但是，other_tableDOES 中的数据会被此过程删除，因此我不能只引用其中的数据，other_table因为那里什么都没有。我需要一种方法来保留这些other_table.id值，以便之后可以验证父记录。
回答by diederikh
I would store the other_table.id's in a PL/SQL table and reference that table in the query afterwards:
我会将other_table.id's存储在 PL/SQL 表中，然后在查询中引用该表：
type t_id_table is table OF other_table.id%type index by binary_integer;
v_table t_id_table;
-- fill the table
select id
bulk collect into v_table
from other_table
where abc in ('&val1','&val2','&val3');
-- then at a later stage...
select *
from some_table st
, table(cast(v_table AS t_id_table)) idt
where st.id = idt.id;
回答by Gerrat
You can't use comma-separated values in one bind variable.
您不能在一个绑定变量中使用逗号分隔值。
You could say:
你可以说：
select * from some_table where id in (:bind_var1, :bind_var2)
though
尽管
You're better off using something like:
你最好使用类似的东西：
select * from some_table where id in ("select blah blah blah...");
回答by Marcin Wroblewski
I would use a global temporary table for this purpose
为此，我会使用全局临时表
create global temporary table gtt_ids( id number ) ;
then
然后
...
for r in (select id from other_table where ... ) loop
insert into gtt_ids(id) values (r.id) ;
end loop;
...
and at the end
最后
select *
from some_table
where id in (select id from gtt_ids);
回答by Harrison
changed the loop to use listagg(sadly this will only work in 11gr2).
将循环更改为使用listagg（遗憾的是，这只适用于 11gr2）。
but for the variable in list, I used a regular expressionto accomplish the goal (but pre 10g you can use substrto do the same) this is lifted from the asktom question linked.
但是对于列表中的变量，我使用了一个正则表达式来实现目标（但是在 10g 之前，您可以使用substr来做同样的事情）这是从链接的 asktom 问题中提取的。
variable bind_var varchar2(255)
variable dataSeperationChar varchar2(255)
declare
x varchar2(100);
begin
select listagg(id,',') within group(order by id) idList
into x
from(select level id
from dual
connect by level < 100 )
where id in (&val1,&val2,&val3) ;
select x into :bind_var from dual;
:dataSeperationChar := ',';
end;
/
print :bind_var;
/
select *
from (
select level id2
from dual
connect by level < 100
)
where id2 in(
select -- transform the comma seperated string into a result set
regexp_substr(:dataSeperationChar||:bind_var||','
, '[^'||:dataSeperationChar||']+'
,1
,level) as parsed_value
from dual
connect by level <= length(regexp_replace(:bind_var, '([^'||:dataSeperationChar||'])', '')) + 1
)
;
/*
values of 1,5, and 25
BIND_VAR
------
1,5,25
ID2
----------------------
1
5
25
*/
EDIT
编辑
Oops just noticed that you did mark 10g, the only thing to do is NOT to use the listagg that I did at the start
哎呀刚刚注意到你确实标记了 10g，唯一要做的就是不要使用我在开始时所做的 listagg
回答by FrustratedWithFormsDesigner
Ok, I have a kind of ugly solution that also uses substitution variables...
好的，我有一种丑陋的解决方案，它也使用替换变量......
col idList NEW_VALUE v_id_list /* This is NEW! */
variable x varchar2(255)
declare
x varchar2(100);
begin
for r in (select id from other_table where abc in ('&val1','&val2','&val3') ) loop
x := x||''''||r.id||''',';
end loop;
--get rid of the trailing ','
x:= substr(x,1,length(x)-1);
select x into :bind_var from dual;
end;
/
print :bind_var;
select :x idList from dual; /* This is NEW! */
select *
from some_table
where id in (&idList); /* This is CHANGED! */
It works, but I'll accept an answer from someone else if it's more elegant.
它有效，但如果它更优雅，我会接受其他人的答案。
