怎么在oracle的查询语句中的in后用变量

Question

这个是table中的其中一个
s_s(1):='(''TMB90208'',''TMB90187'',''TMB90189'',''TMB90119'',''TMB90124'',''TMB90125'',
''TMB90123'',''TMB90121'',''TMB90120'',''TMB90122'',''TMB90191'')';

我想在这个语句中用上面的s_s(1)这个变量
insert into aadf (aa_id,money) values(1,select sum(case when t.prod_id in s_s_t(1) then t.total_due_amount else 0 end)
from t_stat_gw_ue_97_201006 t, t_city_code city
where t.city_id = substr(city.latn_id, 2, 3)||0)

Answer 1

要想在in中用变量，需要用动态SQL，请看下例子，我在oracle 10g中测试通过：

declare
v_1 varchar2(100);
v_2 varchar2(200);
begin
v_1:='(''1'',''2'',''3'')';
v_2:='insert into testa2 select * from testa1 where fch in'|| v_1;
execute immediate v_2;
commit;
end;

Answer 2

delete from table1 t where t.field1 in
(select t.field1 from table1 t, table2 t1
where .......)