31. Курсоры. Основные функции. Правила применения. Примеры.

Курсор позволяет программе на базовом языке построчно обрабатывать результаты выполнения запроса. С точки зрения программы курсор представляет собой указатель на определенную строку в результирующем наборе данных. Курсор можно передвигать с одной строки на другую и после обработки предыдущей строки переходить к следующей. Прежде чем курсор можно будет использовать, его следует объявить и открыть, а после завершения работы с ним — закрыть для освобождения занимаемых им ресурсов, если он больше не потребуется. После того как курсор открыт, строки результирующего набора данных запроса могут выбираться из него по одной с помощью оператора FETCH, а не SELECT.

Функции выполняемые курсором:

• Обрабатывать последовательно, строка за строкой, наборы строк возвращаемые запросом.

• Контроль текущей обрабатываемой строки запроса, на основе которого сформирован курсор.

• Возможность программисту управлять курсором в процедурах или функциях

Управление курсором:

• DECLARE – Объявление курсора, создание именованной SQL-области.

• OPEN – Выполнение запроса. Установка указателя активного набора на первую строку.

• FETCH – Считывание текущей строки из курсора в переменную(ые).

• CLOSE – закрыть курсор, освободить ресурсы выделенные курсору.

Оператор DECLARE CURSOR описывает конкретный оператор SELECT, который должен быть выполнен, и связывает имя курсора cursorName с запросом select statement. Формат: DECLARE cursorName CURSOR FOR select Statement

Оператор OPEN вызывает запрос на выполнение, в результате чего определяются все строки, соответствующие условию поиска; после этого курсор устанавливается перед первой строкой результирующей таблицы. Если оператор SELECT содержит ошибку, например, в нем использовано имя реально не существующего столбца, то в этот момент активизируется ошибка. Формат: OPEN cursorName

Оператор FETCH предназначен для выборки очередной строки из активного набора. Формат: FETCH [FIRST |NEXT | LAST | ...] FROM <имя_курсора> INTO <имя_переменной>, <имя_переменной>, ....

• <имя_курсора> - имя открытого курсора.

• FIRST/NEXT – возвращает первую/последнюю строку и делает её текущей

• NEXT - Возвращает строку результата сразу же за текущей строкой и перемещает указатель текущей строки на возвращенную строку. Если инструкция FETCH NEXT выполняет первую выборку в отношении курсора, она возвращает первую строку в результирующем наборе. NEXT является параметром по умолчанию выборки из курсора.

• <имя_переменной> - позволяет поместить данные из столбцов выборки в локальные переменные. Каждая переменная из списка, слева направо, связывается с соответствующим столбцом в результирующем наборе курсора. Тип данных каждой переменной должен соответствовать типу данных соответствующего столбца результирующего набора, или должна обеспечиваться поддержка неявного преобразования в тип данных этого столбца. Количество переменных должно совпадать с количеством столбцов в списке выбора курсора.

Функция @@FETCH_STATUS возвращает состояние последней инструкции FETCH. Эти сведения о состоянии должны использоваться для определения действительности данных, возвращаемых инструкцией FETCH перед попыткой выполнения любой операции над этими данными. Первоначально значение не определено. Значения:

• =0 - инструкция FETCH выполнена успешно.

• = -1 - выполнение инструкции FETCH завершилось неудачно или строка оказалась вне пределов результирующего набора.

• = -2 - выбранная строка отсутствует.

Оператор CLOSE закрывает открытый курсор, высвобождая текущий результирующий набор. Инструкция CLOSE оставляет структуры данных доступными для повторного открытия, но данные без повторного открытия недоступны. Формат: CLOSE cursorName. Здесь параметр cursorName представляет собой имя курсора, который открыт в настоящее время.

Инструкция DEALLOCATE удаляет только ссылку именованной переменной на курсор. Если это последнее имя или переменная, ссылающаяся на курсор, сам курсор удаляется и освобождаются все используемые им ресурсы. После выполнения инструкции DEALLOCATE cursorName эту переменную можно связать с другим курсором, используя инструкцию SET. Формат: DEALLOCATE cursorName.

Пример. Открывается курсор и выбираются все его строки:

DECLARE Employee_Cursor CURSOR FOR

SELECT LastName, FirstName

FROM AdventureWorks2012.HumanResources.vEmployee

WHERE LastName like 'B%';

OPEN Employee_Cursor;

FETCH NEXT FROM Employee_Cursor;

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM Employee_Cursor

END;

CLOSE Employee_Cursor;

DEALLOCATE Employee_Cursor;