Курсорные выражения (CURSOR) в PL/SQL на примерах

CURSOR выражения в PL/SQLOracle расширяет язык SQL еще одним мощным элементом: курсорными выражениями. Курсорное выражение — это выражение со специальным оператором CURSOR, использу­емое в SQL-запросе и определяющее вложенный курсор. Каждая строка результиру­ющего набора вложенного курсора может содержать диапазон значений, допустимых для SQL-запросов; кроме того, она может включать другие курсоры, определяемые вложенными запросами.

Синтаксис CURSOR, введенный в Oracle8i, не мог использоваться в программах PL/SQL. В Oracle9i недостаток был устранен, и теперь курсорные выражения могут применяться в командах SQL программ PL/SQL.



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

Курсорное выражение может быть довольно сложным, что зависит от сложности извле­каемых с его помощью данных. Тем не менее разработчику полезно знать все возможные способы получения данных из баз данных Oracle.

Курсорные выражения могут использоваться в следующих структурных компонентах языка:

  •  объявления явных курсоров;
  •  динамические SQL-запросы;
  •  объявления и переменные типа REF CURSOR.

В неявных курсорах курсорные выражения никогда не используются.

Синтаксис курсорного выражения очень прост:

CURSOR (вложенный_запрос)

Oracle неявно открывает вложенный курсор при выборке строки, содержащей его вы­ражение из родительского или внешнего курсора. Он закрывается, когда:

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

 

Использование курсорных выражений

Существует два разных, но очень полезных способа использования курсорных вы­ражений:

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

 

Выборка вложенного запроса как столбца

Следующая процедура демонстрирует первый способ использования вложенного кур­сорного выражения. Запрос верхнего уровня выбирает два элемента данных: название города и отделение компании в этом городе, информация о котором содержится во вложенном курсоре. Этот вложенный курсор, в свою очередь, извлекает с помощью курсорного выражения еще один вложенный курсор — на этот раз с фамилиями всех сотрудников каждого отделения.

Те же данные можно было бы извлечь из базы данных с помощью нескольких явных курсоров, открываемых и обрабатываемых во вложенных циклах. Однако курсорное выражение позволяет применить другой подход, более лаконичный и эффективный (поскольку вся обработка происходит в исполнителе команд SQL, что сокращает ко­личество переключений контекста):


PROCEDURE emp_report (p_locid NUMBER)
IS
   TYPE refcursor IS REF CURSOR;
   -- Запрос возвращает 2 столбца, второй из которых является курсором,
   -- а значит, позволяет перебрать набор связанных строк.
   CURSOR all_in_one_cur is
       SELECT l.city,
         CURSOR (SELECT d.department_name,
            CURSOR(SELECT e.last_name
                FROM employees e
                   WHERE e.department_id =
                        d.department_id)
                  AS ename
            FROM departments d
            WHERE l.location_id = d.location_id) AS dname
         FROM locations l
          WHERE l.location_id = p_locid;
    departments_cur refcursor;
    employees_cur refcursor;
    
    v_city locations.city%TYPE;
    v_dname departments.department_name%TYPE;
    v_ename employees.last_name%TYPE;
BEGIN
   OPEN all_in_one_cur;
   
   LOOP
      FETCH all_in_one_cur INTO v_city, departments_cur;
      EXIT WHEN all_in_one_cur%NOTFOUND;
      -- Перебираем список отделений, причем для этого НЕ НУЖНО
      -- явно открывать курсор. Oracle делает это автоматически.
      LOOP
         FETCH departments_cur INTO v_dname, employees_cur;
         EXIT WHEN departments_cur%NOTFOUND;

         -- Теперь можно перебрать всех сотрудников текущего -- отделения. И снова явно открывать курсор не нужно.
         LOOP
            FETCH employees_cur INTO v_ename;
            EXIT WHEN employees_cur%NOTFOUND;
            DBMS_OUTPUT.put_line
                ( v_city
               || ' '
               || v_dname
               || ' '
               || v_ename
               );
         END LOOP;
      END LOOP;
   END LOOP;
   CLOSE all_in_one_cur;
END;

 

Реализация потоковых функций в выражениях CURSOR

Потоковые функции, также называемые преобразующими функциями, позволяют преоб­разовать данные из одного состояния в другое без использования локальных структур данных для промежуточного хранения результатов. Предположим, нужно взять данные из таблицы StockTable и переместить их в таблицу TickerTable, преобразуя одну строку StockTable в две строки TickerTable. С курсорными выражениями и табличными функ­циями решение может быть реализовано следующим образом:


INSERT INTO
    TickerTable SELECT *
      FROM TABLE (StockPivot (CURSOR (SELECT * FROM StockTable)));

Функция StockPivot содержит всю сложную логику, необходимую для выполнения преобразования.

 

Ограничения, связанные с курсорными выражениями

Для применения курсорных выражений установлены некоторые ограничения:

  •  Курсорное выражение не может использоваться в неявном курсоре по причине от­сутствия механизма выборки из вложенного курсора в структуру данных PL/SQL.
  •  Курсорные выражения могут задаваться только в «самом внешнем» списке выборки запроса.
  •  Курсорные выражения нельзя использовать в команде SELECT, вложенной в другой запрос (исключение составляет вложенный запрос курсорного выражения).
  •  Курсорные выражения не могут использоваться при объявлении представления.
  •  Если курсорное выражение задано в динамической команде SQL, к нему нельзя применять операции BIND и EXECUTE.

 

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6920 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3068 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Символьные функции и аргументы...
Символьные функции и аргументы... 10761 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 5673 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Войдите чтобы комментировать

apv аватар
apv ответил в теме #9196 10 сен 2018 13:50

MaxNiko пишет: REF CURSOR использую постоянно при программировании на PL/SQL, но из вашей статьи узнал немало нового. Приемы кодинга из примеров весьма полезны, дают почву для размышлений о своем профессиональном уровне :-)))


Курсоры, конечно, не шедевральный метод программирования, но порой без них действительно никак не обойтись...
MaxNiko аватар
MaxNiko ответил в теме #9164 03 сен 2018 11:28
REF CURSOR использую постоянно при программировании на PL/SQL, но из вашей статьи узнал немало нового. Приемы кодинга из примеров весьма полезны, дают почву для размышлений о своем профессиональном уровне :-)))