Базовый курс SQL. Курсоры

[к оглавлению]

Что это такое

До этого мы с вами рассматривали синтаксис различных SQL-запросов, работающих с данными в базе. К примеру, запрос SELECT извлекает наборы строк, соответствующих установленным условиям. Эти наборы могут иметь ноль, одну и более строк. И они называются результирующими.

Результирующий набор

Результирующий набор - это все строки, выбранные в результате SQL-запроса.

Результат запроса SELECT мы получаем всегда целиком. Но что если есть необходимость получить доступ конкретно к первой строке выборки, или следующей, или даже просмотреть строки в обратном порядке, по нескольку за раз? Все эти возможности предоставляют курсоры.

В разных СУБД спектр применения курсоров различается, но чаще всего реализуется:

• использование курсоров "только для чтения" - данные нельзя будет изменить или удалить
• указание направления чтения данных - вперёд, назад, первая или последняя строка, абсолютное или относительное положение
• разделение столбцов на редактируемые и нередактируемые
• ограничение области видимости - доступ к курсору будет только внутри запроса, который его создал или в любых запросах
• создание копии данных из таблицы с целью сохранения их в неизменном виде до обращения к курсору

Где поддерживаются курсоры

В MySQL курсоры поддерживаются начиная с 5 версии. В PostgreSQL и SQLite курсоры тоже есть, но могут быть различия в синтаксисе. А вот в Microsoft Access возможности, описанные в данной главе, не применимы.

Основной практический смысл использования курсоров - интерактивные приложения, позволяющие пользователям прокручивать какие-либо записи на экране вперёд и назад, просматривать и изменять их.

Как работать с курсорами

Есть несколько этапов, которые можно выделить при работе с курсорами:

1. Создание. Извлечения данных на этом этапе не происходит, а только создаётся текст запроса SELECT и курсору задаются параметры
2. Открытие для получения данных. Здесь данные извлекаются из таблиц
3. Чтение данных из курсора. Можно сделать, когда курсор уже заполнен
4. Закрытие. А также, возможно, освобождение занятых им ресурсов памяти (не для всех СУБД)

Целиком процесс работы с курсором выглядит так:

• когда курсор объявлен, его можно сколько угодно раз открывать и закрывать
• когда курсор открыт, можно сколько угодно раз извлекать из него строки

Создание

Для создания курсоров используется ключевое слово DECLIRE. Как всегда, в зависимости от СУБД, синтаксис команды немного различается. DECLARE назначает курсору имя и запрос SELECT, который определит, какие данные будут храниться внутри. Для примера давайте создадим корсор, который будет служить для внесения недостающих e-mail адресов в карточки студентов:

DECLARE NewCursor CURSOR
FOR
SELECT * FROM Students
WHERE student_emeil IS NULL;

Это версия кода для MySQL, MariaDB, SQLServer и DB2. Ниже представим вариант для PostgreSQL и Oracle:

DECLARE CURSOR NewCursor
IS
SELECT * FROM Students
WHERE student_emeil IS NULL;

NewCursor - это имя курсора - указывается после ключевого слова DECLARE. Следом, за словами FOR или IS, идут условия для выбора строк из БД, переданные с помощью запроса SELECT.

После того, как мы создали курсор, можно его открыть.

Работа с курсорами

Синтаксис команды для открытия курсора очень простой и поддерживается большинством СУБД:

OPEN CURSOR NewCursor

В этот момент выполняется заданный в команде DECLARE запрос, и строки, полученные в результате него, сохраняются (например, в переменной) и в дальнейшем могут быть просмотрены без обращения к БД.
Для доступа к данным используется команда FETCH. Для неё можно указать параметры: какие строки извлекать, откуда, и куда сохранить. Рассмотрим пример получения первой строки курсора в СУБД Oracle:

DECLARE TYPE StudentsCursor IS REF CURSOR
    RETURN Students%ROWTYPE;
DECLARE StudentRecord Students%ROWTYPE
BEGIN
    OPEN StudentsCursor;
    FETCH StudentsCursor INTO StudentRecord;
    CLOSE StudentsCursor;
END;

FETCH извлекает текущую строку таблицы Students и записывает её в переменную StudentRecord. По умолчанию считывание начинается с первой строки. Чтобы обработать все строки таблицы, воспользуемся циклом:

DECLARE TYPE StudentsCursor IS REF CURSOR
    RETURN Students%ROWTYPE;
DECLARE StudentRecord Students%ROWTYPE
BEGIN
    OPEN StudentsCursor;
    LOOP
        FETCH StudentsCursor INTO StudentRecord;
        EXIT WHEN StudentsCursor%NOTFOUND;
          ... -- Здесь могут быть какие-то действия с текущей строкой
    END LOOP;
    CLOSE StudentsCursor;
END;

Здесь, так же, как и раньше, производится запись текущей строки в переменную StudentRecord с помощью команды FETCH, но на этот раз, с помощью цикла LOOP, мы обходим все строки таблицы, пока не будет удовлетворено условие StudentsCursor%NOTFOUND. На месте ... можно добавить любой код для обработки, который будет применён последовательно к каждой строке.

Ещё один пример работы с курсором для SQL Server:

DECLARE @teacher_id INTEGER(10),
        @teacher_name CHAR(50),
        @teacher_surname CHAR(50),
        @teacher_middle_name CHAR(50),
OPEN TeacherCursor
FETCH NEXT FROM TeacherCursor
    INTO @teacher_id, @teacher_name, @teacher_surname, @teacher_middle_name
WHILE @@FETCH_STATUS = 0
BEGIN
...  -- Здесь могут быть какие-то действия с текущей строкой
FETCH NEXT FROM TeacherCursor
    INTO @teacher_id, @teacher_name, @teacher_surname, @teacher_middle_name
END
CLOSE TeacherCursor

Выборка осуществляется аналогично, командой FETCH, но каждый столбец сохранятеся в отдельную переменную. Строки перебирает цикл WHILE, который будет прерван при условии @@FETCH_STATUS = 0 - когда все строки таблицы будут обработаны. Здесь также опущено, что конкретно делается с данными, т.к. это зависит от того, что нужно пользователю, а задача курсора - только предоставить доступ к строкам таблицы.

Закрытие

После работы с данными, курсор следует закрыть. Чтобы снова обратиться к данному курсору, достаточно будет его открыть с помощью ключевого слова OPEN. Заново объявлять имя и условие не потребуется.

CLOSE NewCursor;

Закрыть курсор можно с помощью слова CLOSE. Для PostgreSQL, Oracle и DB2 этого достаточно. А в SQL Server придётся ещё явно освободить ресурсы, занятые курсором:

CLOSE NewCursor
DEALLOCATE CURSOR NewCursor;

Следующая страница. Другие возможности SQL.
Оглавление.