PLSQL - Use variable array on where clause IN-CodePudding

I am looking to use an array of values in the WHERE IN condition.

After research, I saw that I had to make a "CREATE TYPE", since a "TYPE" in local does not work. This solution does not suit me, I do not want to make a new TYPE in Oracle, since the database is used for an ERP/PGI I do not wish to pollute with my little need.

My request is the following:

DELETE vip_routage
 WHERE vip_tel_ext = w_array_tel(i)
   AND ((w_cp NOT IN ('14', '27', '50', '61', '76') 
   AND SUBSTR(VIP_CODE_POSTAL, 1, 2) IN ('14', '27', '50', '61', '76')) 
    OR (w_cp IN ('14', '27', '50', '61', '76') 
   AND SUBSTR(VIP_CODE_POSTAL, 1, 2) IN ('14', '27', '50', '61', '76') 
   AND TO_NUMBER(vip_dest1) < w_tri_ordre) 
    OR (w_cp NOT IN ('14', '27', '50', '61', '76') 
   AND SUBSTR(VIP_CODE_POSTAL, 1, 2) NOT IN ('14', '27', '50', '61', '76') 
   AND TO_NUMBER(vip_dest1) < w_tri_ordre));

It is the value "('14','27','50','61','76')" that I would like to set as a variable, but only in my program.

Do you have any ideas other than "CREATE TYPE"?

CodePudding user response：

As you don't want to create your own type, use built-in one - sys.odcivarchar2list. For example, fetch employees who are either clerks or managers:

SQL> select deptno, empno, ename, job, sal
  2  from emp
  3  where job in (select *
  4                from table(sys.odcivarchar2list('CLERK', 'MANAGER'))
  5               );

    DEPTNO      EMPNO ENAME      JOB              SAL
---------- ---------- ---------- --------- ----------
        10       7934 MILLER     CLERK           1300
        30       7900 JAMES      CLERK            950
        20       7876 ADAMS      CLERK           1100
        20       7369 SMITH      CLERK            800
        10       7782 CLARK      MANAGER         2450
        30       7698 BLAKE      MANAGER         2850
        20       7566 JONES      MANAGER         2975

7 rows selected.

SQL>

CodePudding user response：

replace the first with clause and str with your string variable, you can do something like this:

with rws as (
  select '''14'',''27'',''50'',''61'',''76''' str from dual
),
item_list as (
  select regexp_substr (
           str,
           '[^,] ',
           1,
           level
         ) value
  from   rws
  connect by level <= 
    length ( str ) - length ( replace ( str, ',' ) )   1
)
delete from VIP_ROUTAGE
where vip_TEL_EXT = w_array_tel(i)
AND (
 (w_cp NOT IN  (select value from item_list)) ...

CodePudding user response：

You can pass in a string (such as '14,27,50,61,76') as a bind variable and use LIKE to compare the delimited string:

DELETE FROM VIP_ROUTAGE
WHERE vip_TEL_EXT = w_array_tel(i)
AND (    ',' || :your_list || ',' NOT LIKE '%,' || w_cp || ',%'
    AND (  ',' || :your_list || ',' LIKE '%,' || SUBSTR(VIP_CODE_POSTAL,1,2) || '%,'
        OR to_number(vip_dest1) < w_tri_ordre))
OR  (    ',' || :your_list || ',' LIKE '%,' || w_cp || ',%'
    AND  ',' || :your_list || ',' LIKE '%,' || SUBSTR(VIP_CODE_POSTAL,1,2) || ',%'
    AND  to_number(vip_dest1) < w_tri_ordre)

db<>fiddle here