Базовый курс SQL. Курсоры
Primary tabs
Forums:
Что это такое
До этого мы с вами рассматривали синтаксис различных SQL-запросов, работающих с данными в базе. К примеру, запрос SELECT
извлекает наборы строк, соответствующих установленным условиям. Эти наборы могут иметь ноль, одну и более строк. И они называются результирующими.
Результирующий набор
Результирующий набор - это все строки, выбранные в результате SQL-запроса.
Результат запроса SELECT
мы получаем всегда целиком. Но что если есть необходимость получить доступ конкретно к первой строке выборки, или следующей, или даже просмотреть строки в обратном порядке, по нескольку за раз? Все эти возможности предоставляют курсоры.
В разных СУБД спектр применения курсоров различается, но чаще всего реализуется:
- использование курсоров "только для чтения" - данные нельзя будет изменить или удалить
- указание направления чтения данных - вперёд, назад, первая или последняя строка, абсолютное или относительное положение
- разделение столбцов на редактируемые и нередактируемые
- ограничение области видимости - доступ к курсору будет только внутри запроса, который его создал или в любых запросах
- создание копии данных из таблицы с целью сохранения их в неизменном виде до обращения к курсору
Где поддерживаются курсоры
В MySQL курсоры поддерживаются начиная с 5 версии. В PostgreSQL и SQLite курсоры тоже есть, но могут быть различия в синтаксисе. А вот в Microsoft Access возможности, описанные в данной главе, не применимы.
Основной практический смысл использования курсоров - интерактивные приложения, позволяющие пользователям прокручивать какие-либо записи на экране вперёд и назад, просматривать и изменять их.
Как работать с курсорами
Есть несколько этапов, которые можно выделить при работе с курсорами:
- Создание. Извлечения данных на этом этапе не происходит, а только создаётся текст запроса
SELECT
и курсору задаются параметры - Открытие для получения данных. Здесь данные извлекаются из таблиц
- Чтение данных из курсора. Можно сделать, когда курсор уже заполнен
- Закрытие. А также, возможно, освобождение занятых им ресурсов памяти (не для всех СУБД)
Целиком процесс работы с курсором выглядит так:
- когда курсор объявлен, его можно сколько угодно раз открывать и закрывать
- когда курсор открыт, можно сколько угодно раз извлекать из него строки
Создание
Для создания курсоров используется ключевое слово 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;
- Log in to post comments
- 2052 reads