Блог

Я пытаюсь написать простой запрос, в котором требуется использовать переменную подстановки, которая может использоваться для ввода нескольких возможных значений для столбца, используемого для фильтрации запроса.

Требование заключается в создании следующего запроса

 select
CONTRACT, 
ORDER_NO,
CUSTOMER_NO
from CUSTOMER_ORDER
where state='Picked'
and contract in ('ABC','DEF')
  

но значения контракта необходимо будет вводить во время выполнения с помощью переменной подстановки. Я работаю с ограничением возможности записи только статического SQL-запроса («select ..from ..where ..»), и никакой динамический код не может быть записан внутри блоков pl sql.

Итак, то, что я попробовал, было следующим,

 select
CONTRACT, 
ORDER_NO,
CUSTOMER_NO
from CUSTOMER_ORDER_JOIN
where contract in (select '''' || REPLACE('amp;CONTRACT',';',''',''') || '''' from dual)
  

Когда появляется запрос на подстановку, я ввожу ABC;DEF

Но, похоже, это не работает. Хотя, когда я запускаю следующее отдельно,

 select '''' || REPLACE('amp;CONTRACT',';',''',''') || '''' from dual
  

В результате я получаю ‘ABC’, ‘DEF’.

Почему это не работает? Есть ли способ достичь желаемого результата?

Спасибо

Один из вариантов — разделить эти значения на строки и использовать их в качестве подзапроса.

Пример, основанный на таблице EMP Скотта:

 SQL> select ename, job, sal from emp;

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            920
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
BLAKE      MANAGER         2850
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT      10000
TURNER     SALESMAN        1500
ADAMS      CLERK           1100
JAMES      CLERK            950
FORD       ANALYST         3000
MILLER     CLERK           1300

14 rows selected.

SQL> select ename, job, sal
  2  from emp
  3  where ename in (select regexp_substr(replace(q'[amp;amp;par_ename]', chr(39), ''), '[^,] ', 1, level)
  4                  from dual
  5                  connect by level <= regexp_count(q'[amp;amp;par_ename]', ',')   1
  6                 );
Enter value for par_ename: 'SMITH','ALLEN'

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            920
ALLEN      SALESMAN        1600

SQL>
  

В качестве альтернативы используйте sys.odcivarchar2list :

 SQL> select ename, job, sal
  2  from emp
  3  where ename in (select column_value
  4                  from table(sys.odcivarchar2list(amp;par_ename))
  5                 );
Enter value for par_ename: 'SMITH','ALLEN'

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            920
ALLEN      SALESMAN        1600

SQL>
  

Он также работает без одинарных кавычек:

 SQL> Select ename, job, sal
  2      from emp
  3      where ename in (select regexp_substr(replace(q'[amp;amp;par_ename]', chr(39), ''), '[^,] ', 1, level)
  4                      from dual
  5                      connect by level <= regexp_count(q'[amp;amp;par_ename]', ',')   1
  6                     );
Enter value for par_ename: SMITH,ALLEN,KING

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            920
ALLEN      SALESMAN        1600
KING       PRESIDENT      10000

SQL>