Как использовать CSV параметр процедуры в операторе IN?

Question

Группирую серию ID с помощью функции LISTAGG.

Затем пытаюсь передать полученное значение в следующую процедуру:

PROCEDURE abc(id_list IN VARCHAR2) IS
BEGIN
  UPDATE table_a SET flag = 1 WHERE id IN (id_list);
END;

Но это выдаёт ошибку:

ORA-01722: invalid number

, потому что id_list это строка '1,2,3,4,5', а не лист числовых значений 1,2,3,4,5.

Как можно сделать так, чтобы это работало?

Желательно не использовать динамический SQL, если это возможно.

_{Свободный перевод вопроса How to use CSV parameter in Oracle procedure where clause от участника @Bob}

Answer 1

Внутренней функции для преобразования CSV данных нет, но существует ряд различных решений. Эти решения скорее предназначены для преобразования денормализованных данных из сторонних источников. Не стоит преобразовывать табличные данные в символьный лист значений, а затем, также затратно, преобразовывать эти данные обратно в табличную форму.

Вот одно из простейших решений, оно будет работать:

create table t (id, flag) as
    select rownum, 0 from dual connect by level<=5;  

with q (idlist) as ( 
    select listagg (id, ',') within group (order by id)
    from t 
    where id <= 3)
select id, idlist 
from t, q
where id in (select to_number (column_value) from xmlTable (idlist));

        ID IDLIST  
---------- --------
         1 1,2,3   
         2 1,2,3   
         3 1,2,3

Но это не будет лучшим решением. Более подходяшим решением будет, получить лист значений как коллекцию, а затем использовать её в IN операторе не составит затруднений.

Воспроизводимый пример:

create type integerlist is table of int
/
declare
    idlist integerlist;
    procedure proc (idlist integerlist) is
    begin
        update t set flag=1 
        where id in (select column_value from table (idlist));
    end;
begin 
    select rownum bulk collect into idlist
    from dual connect by level<=3;
    proc (idlist);
end;
/

Результат:

select * from t where flag=1;

        ID       FLAG
---------- ----------
         1          1
         2          1
         3          1

БЛОГ НА HUSL

Как использовать CSV параметр процедуры в операторе IN?

Ответы (1 шт):