Использование OPEN…FOR в динамическом SQL

Представьте себе вариант решения задачи, когда вам нужно распечатать список сотрудников, но вы не знаете какой должен быть фильтр (условие WHERE). Реализация этой логики выглядит следующим бразом:
1. Курсор по таблице EMP.
2. Печать всей информации с помощью процедуры DBMS_OUTPUT.PUT_LINE.

В данном подходе есть значительный камень преткновения. Как вы можете построить курсор, если не знаете, содержимое фильтра данных? (То есть, что делать, если вы не знаете, какие столбцы и условия необходимы в блоках WHERE и SELECT запроса?) Динамический SQL позволяет вам ответить на этот вопрос и в сочетании с Oracle построить так называемый REF CURSOR.

Вы можете использовать этот подход для создания указателей на целые наборы данных, но определив Oracle-курсор, необходимо задать запрос для его использования. Конструкции же типа REF CURSOR просто логические указатели, которые не требуют определять используемый в курсоре запрос в момент декларации.

Создание типа данных REF CURSOR

Для использования REF CURSOR переменных, необходимо использовать тип данных REF CURSOR.
Тип данных REF CURSOR не может быть использован вне PL/SQL среды. Есть два типа REF CURSOR-ов: слабые и сильные (weak and strong). Слабый REF CURSOR может указывать на любой набор данных, как показано здесь:

DECLARE
   TYPE weak_rcty IS REF CURSOR;
   c_weak rcty weak_rcty;

Для небольших проектов с ограниченными возможностями, создавая свой собственный пользовательский тип может быть жизнеспособной стратегией. Тем не менее, в сложных ситуациях или нескольких окружающей среды, это лучшая идея использовать встроенные в стандартные слабого типа данных REF CURSOR чтобы избежать путаницы между разработчиками, как показано здесь:

DECLARE
   c_weak sys_refcursor;

Сильный REF CURSOR явно заявляет тип данных, на которые можно ссылаться. В следующем примере, только запросы, которые возвращают строки так же, как в таблице EMP допускаются:

DECLARE
   TYPE strong_rcty IS REF CURSOR RETURN emp%ROWTYPE;
   c_strong_rcty strong_rcty;

Определение запроса с OPEN…FOR

Работа с переменными типа REF CURSOR аналогична работе с явными курсорами. (Переменные курсоров также применимы к REF CURSOR-ам.) Вы можете определить запрос для реф-курсора напрямую в конструкции OPEN…FOR вместо того чтобы опредлять его при декларации курсора:

DECLARE
   TYPE strong_rcty IS REF CURSOR RETURN
      table%ROWTYPE;
   c_strong_rcty strong_rcty;
   v_table_rec table%ROWTYPE;
BEGIN
   OPEN c_strong_rcty FOR SELECT * FROM TABLE;
   LOOP
      FETCH c_strong_rcty INTO v_table_rec;
      EXIT WHEN c_strong_rcty%NOTFOUND;
      ...
   END LOOP;
   CLOSE c_strong_rcty;
END;

Также возможно открыть рефкурсор для SELECT-запроса заключенного в строку (в данном случае используется слабый REF CURSOR):

DECLARE
   c_weak_ref SYS_REFCURSOR;
BEGIN
   OPEN c_weak_ref FOR 'any_select_statement_you_want';
      ...
END;

Или

DECLARE
   v_string_tx VARCHAR2(32000);
   c_weak_ref sys_refcursor;
BEGIN
   v_string_tx:= 'any_select_statement_you_want'
   OPEN c_weak_ref FOR v_string_tx;
      ...
END;

Использование слабого реф-курсора:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE PROCEDURE p_report 
   (i_where_tx VARCHAR2)
IS
   c_emp_ref sys_refcursor;
BEGIN
   DBMS_OUTPUT.put_line('-----------------------');
   OPEN c_emp_ref FOR
      'select * from emp where'|| i_where_tx;
   LOOP
      FETCH c_emp_ref INTO ???;
      EXIT WHEN c_emp_ref%NOTFOUND;
      DBMS_OUTPUT.put_line(???);        
   END LOOP;
   DBMS_OUTPUT.put_line('-----------------------');    
END;

Примечания:
Строка 4: Создание переменной курсора. Как отметили ранее, лучше использовать
предопределенных типов данных SYS_REFCURSOR для слабых REF-курсоров вместо определения одного из ваших собственных.
Строка 7-8: Открытие переменной курсора для всей строки SELECT-запроса, построенного на лету.

Объявление типа возвращаемых данных

Как вы заметили в предыдущем примере отсутствует часть кода (???) имена переменных в которых происходит выборка строки данных. Так SELECT-запрос строится динамически, у Oracle нет возможности проверить количество и тип полей которые планируется вернуть. Т.к. мы планируем вернуть все поля, рассмотрим три варианта как это возможно сделать:

  • Явно объявить переменные для каждого столбца с соответствующим типом данных. Если количество столбцов больше, чем несколько, вероятно увеличение трудоемкости дальнейшего обслуживания кода.
  • Объявить переменную типа RECORD со всеми переменными в ней. Это решение жизнеспособной, но оно по-прежнему вызывает вопрос обслуживания.
  • Объявить переменную EMP%ROWTYPE. Это лучший выбор, потому что Вы получаете типы данных непосредственно из таблицы EMP и не заботитесь о любых возможных изменениях типов ее полей.

Пример использования типа данных EMP%ROWTYPE

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE PROCEDURE p_report
      (i_where_tx VARCHAR2)
IS
   c_emp_ref sys_refcursor;
   v_emp_rec emp%ROWTYPE;
BEGIN
   DBMS_OUTPUT.put_line('-----------------------');
   OPEN c_emp_ref FOR 
      'select * from emp where ' ||i_where_tx;
   LOOP
      FETCH c_emp_ref INTO v_emp_rec; ?11
      EXIT WHEN c_emp_ref%notfound;
      DBMS_OUTPUT.put_line(v_emp_rec.empNo||' '
         || v_emp_rec.eName||' - '|| v_emp_rec.job);    
   END LOOP;
   DBMS_OUTPUT.put_line('-----------------------'); 
END p_report;

Примечания:
Строка 5: Создание новой переменной v_emp_rec.
Строка 11: Выборка данных из курсора в новую переменную.
Строка 13: Вывод на экран всей информации о сотруднике.
Используем следующий код для проверки функциональности:

SQL> begin
2     p_report('deptNo=10');
3  end;
4  /
-----------------------
7782 CLARK - MANAGER
7839 KING - PRESIDENT
7934 MILLER - CLERK
-----------------------
PL/SQL procedure successfully completed.

В результате мы видим всю информацию о сотрудниках из отдела №10.

Оставить комментарий