sqlplus - использование переменной связывания в предложении "IN"

Question

Я устанавливаю переменную связывания в блоке 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);

И я получаю сообщение об ошибке (ORA-01722: Неверное число) в запросе, который пытается использовать переменную связывания в списке "IN".

Печатное заявление дает '123','345' чего я и ожидаю

Можно ли использовать переменную связывания, как это, или я должен попробовать другой подход?

(используя Oracle 10g)

---

Разъяснение:

Это для чего-то вроде примирения. Я хочу бежать

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 значения, чтобы я мог проверить родительские записи впоследствии.

Answer 1

Я бы сохранил other_table.idнаходится в таблице 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;

Answer 2

Вы не можете использовать значения через запятую в одной переменной связывания.

Ты мог бы сказать:

select * from some_table where id in (:bind_var1, :bind_var2)

хоть

Вам лучше использовать что-то вроде:

select * from some_table where id in ("select blah blah blah...");

Answer 3

Я бы использовал глобальную временную таблицу для этой цели

create global temporary table gtt_ids( id number ) ;

затем

...
for r in (select id from other_table where ... ) loop
   insert into gtt_ids(id) values (r.id) ;
end loop;
...

и в конце

select *
from some_table
where id in (select id from gtt_ids);

Answer 4

Изменил цикл, чтобы использовать listagg (к сожалению, это будет работать только в 11gr2).

но для переменной в списке я использовал регулярное выражение для достижения цели (но до 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   
*/

---

РЕДАКТИРОВАТЬ

---

Ой только что заметил, что вы отметили 10g, единственное, что нужно сделать, это НЕ использовать листаг, который я сделал в начале

Answer 5

Хорошо, у меня есть некрасивое решение, которое также использует переменные подстановки...

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! */

Это работает, но я приму ответ от кого-то другого, если он будет более элегантным.