Динамический запрос в оракуле с использованием курсора

Question

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

Вот код:

create or replace
procedure GET_ITEM_DETAILS_ALL
(
  cur_out out sys_refcursor,
  PSTRING VARCHAR2 DEFAULT NULL
)
is
  query_string  VARCHAR2(1000);
Begin
  query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in (:sl) order by IT.SL_NO';
  OPEN cur_out FOR query_string USING PSTRING;
End;

А для вызова / тестирования процедуры я использую следующий код:

VAR R REFCURSOR
EXEC GET_ITEM_DETAILS_ALL (:R, '4')
PRINT R 

Проблема в том, что когда я передаю одно целое число, такое как "4" или "2", процедура возвращает данные, но когда я передаю "1,2" или "3,4", она показывает ошибку.

Answer 1

Проблема в том, что PSTRING - это одна переменная, а не массив. Таким образом, ваше утверждение на самом деле приравнивается к

.... where IT.SL_NO = PSTRING

Вот почему это работает, когда вы проходите 4 и терпит неудачу, где вы проходите 1,2,

Вы не получаете никакого значения в использовании динамического SQL так, как вы это делаете (мы можем открыть курсор без использования строк). Однако использование динамического SQL - один из способов решения вашей проблемы:

query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in ('
                   ||PSTRING||
                   ') order by IT.SL_NO';
OPEN cur_out FOR query_string;

В качестве альтернативы вы можете использовать токенайзер строк, чтобы превратить строку в токены. К сожалению, Oracle не имеет стандартной встроенной функции, но существуют разные обходные пути для разных версий базы данных. У Адриана Биллингтона есть хорошие обзоры на его сайте. Использование одного из этих подходов позволяет отказаться от динамического SQL:

OPEN cur_out FOR select IT.SL_NO from ITEM_DETAILS IT 
   where IT.SL_NO in ( select * from table ( your_string_tokenizer( PSTRING ) ) ) 
   order by IT.SL_NO;

Answer 2

   OPEN cur_out FOR Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in (SELECT REGEXP_SUBSTR(sl,'[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(sl, '[^,]+', 1, LEVEL) IS NOT NULL ) order by IT.SL_NO

Answer 3

Вы не можете передать список значений в запрос, используя одну переменную связывания.
После передачи строки с разделенным запятыми списком ваш запрос становится
Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in ('1,2') order by IT.SL_NO
что неверно.
Вы должны вручную преобразовать входную строку в список значений внутри вашего запроса.

Answer 4

Решение приведено ниже:

create or replace procedure GETITEM_DETAILS_ALL

(
    cur_out out sys_refcursor,
    PSTRING VARCHAR2 DEFAULT NULL
 )

is

query_string  VARCHAR2(1000);

Begin

query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in (' || PSTRING || ') order by IT.SL_NO';

OPEN cur_out FOR query_string;

End;