Курсоры

Определение:

Курсор — способ перебора результатов запроса, позволяющий достать из результата запроса множество строк.

Курсор можно рассматривать как итератор, специфичный для баз данных.

Объявление курсора

Синтаксис

declare имя -- объявление курсора с именем 
{sensitive|insensitive|asensitive} -- чувствительность курсора
[[no] scroll] -- возможность ходить произвольным образом по данным вперед и назад (default - no scroll) 
cursor select... -- запрос данных, по которым будет ходить курсор
[for {read only|update [of столбцы]}];  -- права на чтение(дефолтное право) или на обновление (всей таблицы или выбранных столбцов)

Чувствительность курсора

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

• [math]\mathtt{sensitive}[/math] - курсор, чувствительный к изменениям. Измененные данные можно будет увидеть в курсоре, если эти данные еще не были пройдены. Иначе, если изменённые данные были просмотрены в прошлом, то они не обновятся. Итерация по реальным данным.
• [math]\mathtt{insensitive}[/math] - курсор фиксированных данных. Данные зафиксированы на момент открытия курсора. Никакие изменения данных не будут отображены через курсор. Итерация по временной копии таблицы.
• [math]\mathtt{asensitive}[/math] - курсор, наиболее предпочтительный для данной СУБД. Связано с различными способами реализациями СУБД: для одних легче реализуем sensitive cursor, а для других - insensitive. В данном случае сама СУБД данных будет решать, какую чувствительность курсора использовать.

Если данные во время использования курсора меняться не будут, то чувствительность курсора не важна.

Примеры

Взятие курсора на чтение для студентов, упорядоченных по идентификатору:

declare SCursor cursor
select * from Students
order by SId;

Взятие курсора с правом на изменение поля SName для студентов группы M3439:

declare SCursor cursor
select * from Students where GId = 'M3439'
for update of SName;

Открытие и закрытие

Объявление курсора говорит о намерении получения данных. Реальная выборка в момент объявления не производится. Она происходит в момент открытия курсора. Если курсор insensitive, то в момент открытия фиксируются данные, которые попадут в курсор.

С курсором ассоциируются ресурсы, поэтому их необходимо закрывать. При выходе из блока, в котором был объявлен курсор, он будет автоматически закрыт. При повторном открытии курсора произойдет повторная инициализация, влекущая новое прочтение данных для insensitive курсора.

Синтаксис

open курсор;
close курсор;

Получение данных

Синтаксис

fetch
[next|prior|last|first|absolute n|relative n]
курсор into переменные;

Описание команд

• [math]\mathtt{next}[/math] - получение следующей записи (доступно по умолчанию, все команды ниже доступны только для scroll курсора)
• [math]\mathtt{prior}[/math] - предыдущая запись
• [math]\mathtt{last}[/math] - последняя запись
• [math]\mathtt{first}[/math] - первая запись
• [math]\mathtt{absolute}[/math] [math]\mathtt{n}[/math] - получить запись с номером n от начала
• [math]\mathtt{relative}[/math] [math]\mathtt{n}[/math] - запись с номером n, нумерация с текущей позиции (n может быть отрицательным, в этом случае движение назад)

Отсутствие данных

Если данные закончились, либо при помощи команд был осуществлен невалидный переход, то случится событие not found.

Пример

fetch SCursor into Sid, FirstName, LastName;

Для получения всех данных из курсора, можно создать цикл, предварительно создав обработчик ошибок на not found, в котором выйдем из цикла, когда все элементы будут обработаны.

Позиционированные изменения

Изменение или удаление ровно одной строчки, которую последней выдал курсор.

Синтаксис

update таблица set что where current of курсор;
delete from таблица where current of курсор;

Пример

if FirstName = 'Иван' then
   delete from Students where current of SCursor;
end if;

Пример применения курсора

create procedure updateNameStat(name)
begin
   declare cnt int default 0;
   declare exit handler for sqlstate not found begin
       insert into Stats (Name, Cnt) values (name, cnt);
   end;
   declare SCursor cursor
       select FirstName from Students;
   open SCursor;
   loop
       declare sname varchar(20);
       fetch SCursor into sname;
       if name = sname then set cnt = cnt + 1; end if;
   end loop;
end; 

Пояснения

В данном примере объявлен счетчик cnt, по умолчанию проинициализированный 0, курсор SCursor, перебирающий значение столбца FirstName из таблицы Students. Далее в бесконечном цикле:

1. определяем переменную sname, в которой будем хранить имя студента
2. достаем следующую запись в эту переменную (напоминание: fetch по умолчанию - это fetch next)
3. проверяем, если текущее имя sname имя соответствует выделенному имени name, то увеличиваем счетчик.

Повторяем данные операции до тех пор, пока у нас не случится not found, при возникновении которого в обработчике not found обновим данные статистики, и выйдем из блока, в котором был объявлен handler - то есть в данном случае выйдем из блока процедуры.

Примечания

Некоторые базы данных позволяют проверить, есть ли в курсоре еще данные. В таких случая пишется цикл while с вызовом метода такой проверки. Но стандартом наличие такой проверки не предусмотрено.

Особенности курсоров

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

Внешние курсоры

Результат запроса к базе данных может быть слишком велик и стать виной нехватки памяти как на стороне клиента, так и на стороны сервера, увеличить нагрузку на сеть. Поэтому наружу данные выдаются в виде внешнего курсора. После исполнения команды select выдается внешний курсор на данные, аналогично внешний курсор выдается при возврате результата из хранимой процедуры.

Размер выборки

Может быть указан для внешнего курсора каким-либо образом (в количестве записей или в объеме данных).

При выдаче курсора приходит метаинформация о курсоре и первая порция данных, размер которой мы указали. Для получения последующих порций данных необходимо обращаться к серверу. В конце сервер сообщит о том, что данные закончились, автоматически освободит ресурсы.

Возникают альтернативы:

• Малый размер выборки — много повторных обращений.
• Большой размер выборки — требуется много памяти.

Выдача курсора приводит к расходу ресурсов сервера (память, блокировки и так далее). Поэтому внешний курсор стоит закрыть как можно раньше.

Литература

• Дейт К. Введение в системы баз данных (главы 4 и 17)
• Уидом Д., Ульман Д. Основы реляционных баз данных (раздел 7.4)
• Gulutzan P., Pelzer T. SQL-99 complete, really
  • Chapter 25 – SQL-Invoked Routine
  • Chapter 26 – PSM: Not Just Persistent Stored Modules