sqlplus-在"IN"中使用绑定变量条款 [英] sqlplus - using a bind variable in "IN" clause
问题描述
我正在PL/SQL块中设置一个绑定变量,并且试图在另一个查询的IN表达式中使用它.像这样:
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:
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);
尝试使用"IN"列表中的绑定变量的查询出现错误(ORA-01722:无效数字).
And I get an error (ORA-01722: Invalid number) on the query that tries to use the bind variable in the "IN" list.
我希望得到的打印语句为'123','345'
.
The print statement yiels '123','345'
which is what I expect.
是否可以像这样使用绑定变量,还是应该尝试其他方法?
Is it possible to use the bind variable like this or should I try a different approach?
(使用Oracle 10g)
(using Oracle 10g)
说明:
这是为了和解.我要跑步
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'))
在脚本的主要部分(此处未显示)之前,将删除整个记录.之后,我想再次运行它以验证是否未删除some_table
中的记录.但是,other_table
中的数据确实会被此过程删除,所以我不能仅引用other_table
中的数据,因为那里什么也没有.我需要一种方法来保存other_table.id
值,以便以后可以验证父记录.
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_table
have NOT been deleted. However, the data in other_table
DOES get deleted by this process so I can't just refer to the data in other_table
because there's nothing there. I need a way to preserve the other_table.id
values so that I can verify the parent records afterwards.
推荐答案
我将other_table.id
存储在PL/SQL表中,然后在查询中引用该表:
I would store the other_table.id
's in a PL/SQL table and reference that table in the query afterwards:
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;
这篇关于sqlplus-在"IN"中使用绑定变量条款的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!