Oracle расширяет язык 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.
))
