Курсоры — различия между версиями

Материал из Викиконспекты
Перейти к: навигация, поиск
(Добавлены пояснения к примеру применения курсора)
м (rollbackEdits.php mass rollback)
 
(не показаны 3 промежуточные версии 2 участников)
Строка 3: Строка 3:
 
'''Курсор''' — способ перебора результатов запроса, позволяющий достать из результата запроса множество строк.}}
 
'''Курсор''' — способ перебора результатов запроса, позволяющий достать из результата запроса множество строк.}}
 
Курсор можно рассматривать как итератор, специфичный для баз данных.
 
Курсор можно рассматривать как итератор, специфичный для баз данных.
 +
 +
Запрос исполнился, мы получили какие-то результаты, и теперь собираемся достать множество строк. Все, что обсуждалось до этого (set и select into) позволяло доставать только один результат, а из курсора их можно достать множество.
  
 
== Объявление курсора ==
 
== Объявление курсора ==
Строка 10: Строка 12:
 
  [[<font color = black>no</font>] <font color = black>scroll</font>]</font><font color = green> -- возможность ходить произвольным образом по данным вперед и назад (default - no scroll) </font>
 
  [[<font color = black>no</font>] <font color = black>scroll</font>]</font><font color = green> -- возможность ходить произвольным образом по данным вперед и назад (default - no scroll) </font>
 
  cursor select<font color = gray>...</font><font color = green> -- запрос данных, по которым будет ходить курсор</font>
 
  cursor select<font color = gray>...</font><font color = green> -- запрос данных, по которым будет ходить курсор</font>
  <font color = red>[</font>for <font color = red>{</font>read <font color = black>only</font><font color = gray>|</font>update <font color = red>[</font>of <font color = red>столбцы]}]</font></font><font color = gray>;</font> <font color = green> -- права на чтение(дефолтное право) или на обновление (всей таблицы или выбранных столбцов)</font>
+
  <font color = red>[</font>for <font color = red>{</font>read <font color = black>only</font><font color = gray>|</font>update <font color = red>[</font>of <font color = red>столбцы]}]</font></font><font color = gray>;</font> <font color = green>-- права на чтение (дефолтное право) или на обновление (всей таблицы (в частности, этот вариант подходит для удаления) или выбранных столбцов)</font>
  
 
=== Чувствительность курсора ===
 
=== Чувствительность курсора ===
Данные из курсора обрабатываются по одной строке, во время таких итераций можно поменять данные самой таблицы, по которой построен текущий курсор. В зависимости от желаемого поведения в таких случаях можно выбрать разную чувствительность курсора.
+
Данные из курсора обрабатываются по одной строке (способа получить более одной строки за один раз нет). Мы напишем цикл, в рамках которого будут выбираться данные из курсора, и возможно будут меняться данные в нашей базе данных. Получаем, что во время таких итераций можно поменять данные самой таблицы, по которой построен текущий курсор. Представим ситуацию, что мы в сделанном курсоре в явной форме написали update на ту таблицу или таблицы, по которым сделан курсор. Что должно произойти в таком случае? В зависимости от желаемого поведения можно выбрать разную чувствительность курсора.
  
* <tex>\mathtt{sensitive}</tex> - курсор, чувствительный к изменениям. Измененные данные можно будет увидеть в курсоре, если эти данные еще не были пройдены. Иначе, если изменённые данные были просмотрены в прошлом, то они не обновятся. Итерация по реальным данным.
+
* <tex>\mathtt{sensitive}</tex> - курсор, чувствительный к изменениям. Измененные данные можно будет увидеть в курсоре, если эти данные еще не были пройдены. Иначе, если изменённые данные были просмотрены в прошлом и у нас не scrollable курсор, то они не обновятся. В некотором смысле это итерация по реальным данным, динамическая.
* <tex>\mathtt{insensitive}</tex> - курсор фиксированных данных. Данные зафиксированы на момент открытия курсора. Никакие изменения данных не будут отображены через курсор. Итерация по временной копии таблицы.
+
* <tex>\mathtt{insensitive}</tex> - курсор фиксированных данных. Данные зафиксированы на момент открытия курсора. Никакие изменения данных не будут отображены через курсор. В некотором смысле это итерация по временной копии таблицы.
* <tex>\mathtt{asensitive}</tex> - курсор, наиболее предпочтительный для данной СУБД. Связано с различными способами реализациями СУБД: для одних легче реализуем sensitive cursor, а для других - insensitive. В данном случае сама СУБД данных будет решать, какую чувствительность курсора использовать.
+
* <tex>\mathtt{asensitive}</tex> - курсор, наиболее предпочтительный для данной СУБД. Связано с различными способами реализациями СУБД: для одних легче реализуем sensitive cursor, а для других - insensitive. Существует два подхода к реализации СУБД: snapshot-based и lock-based, для них либо sensitive, либо insensitive варианты предпочтительны. Ни одна из опций не является дешевой по умолчанию, поэтому и есть значение asensitive: сама СУБД будет решать, какую чувствительность курсора использовать. Для lock-based проще сделать курсор, чувствительный к изменениям, так как для insensitive потребовалось бы дополнительное копирование. Для snapshot-based предпочтительнее insensitive, так как не потребуется специальным образом учитывать обновления.
  
 
Если данные во время использования курсора меняться не будут, то чувствительность курсора не важна.
 
Если данные во время использования курсора меняться не будут, то чувствительность курсора не важна.
 +
 +
=== Scrollable | No scroll курсоры ===
 +
 +
* <tex>\mathtt{No}</tex> <tex>\mathtt{scroll}</tex> - по такому курсору можно ходить только по одной записи вперед, как из обычного итератора в Java.
 +
* <tex>\mathtt{Scroll}</tex> - по такому курсору можно ходить не только по одной записи вперед, но и вперед, назад, в начало или конец. Такой курсор больше похож на итератор из языка С++.
  
 
===Примеры===
 
===Примеры===
  
Взятие курсора на чтение для студентов, упорядоченных по идентификатору:
+
Взятие курсора на чтение для всех студентов, упорядоченных по идентификатору:
 
  <font color = blue>declare <font color = black>SCursor</font> cursor
 
  <font color = blue>declare <font color = black>SCursor</font> cursor
 
  select <font color = gray>*</font> from <font color = black>Students</font>
 
  select <font color = gray>*</font> from <font color = black>Students</font>
 
  order by <font color = black>SId</font><font color = gray>;</font></font>
 
  order by <font color = black>SId</font><font color = gray>;</font></font>
  
Взятие курсора с правом на изменение поля SName для студентов группы M3439:
+
Взятие курсора с правом на изменение поля SName для студентов группы M3439 в произвольном порядке:
 
  <font color = blue>declare <font color = black>SCursor</font> cursor
 
  <font color = blue>declare <font color = black>SCursor</font> cursor
 
  select <font color = gray>*</font> from <font color = black>Students</font> where <font color = black>GId</font> <font color = gray>=</font> <font color = green>'M3439'</font>
 
  select <font color = gray>*</font> from <font color = black>Students</font> where <font color = black>GId</font> <font color = gray>=</font> <font color = green>'M3439'</font>
Строка 34: Строка 41:
  
 
==Открытие и закрытие==
 
==Открытие и закрытие==
Объявление курсора говорит о намерении получения данных. Реальная выборка в момент объявления не производится. Она происходит в момент открытия курсора. Если курсор ''insensitive'', то в момент открытия фиксируются данные, которые попадут в курсор.
 
  
С курсором ассоциируются ресурсы, поэтому их необходимо закрывать. При выходе из блока, в котором был объявлен курсор, он будет автоматически закрыт. При повторном открытии курсора произойдет повторная инициализация, влекущая новое прочтение данных для ''insensitive'' курсора.  
+
Объявление курсора говорит о намерении получения данных. Реальная выборка в момент объявления не производится. Она происходит в момент открытия курсора (выполнения действия open cursor). Если курсор ''insensitive'', то в момент открытия фиксируются данные, которые попадут в курсор.
 +
 
 +
С курсором ассоциируются множество ресурсов, поэтому их необходимо закрывать. При выходе из блока, в котором был объявлен курсор, он будет автоматически закрыт. Это чем-то напоминает автоматическую сборку ресурсов в sql. При повторном открытии курсора произойдет повторная инициализация, влекущая новое прочтение данных для ''insensitive'' курсора.  
  
 
=== Синтаксис ===
 
=== Синтаксис ===
Строка 44: Строка 52:
  
 
== Получение данных ==
 
== Получение данных ==
 +
 +
Для получения данных из курсора есть конструкция fetch.
  
 
=== Синтаксис ===  
 
=== Синтаксис ===  
Строка 57: Строка 67:
 
* <tex>\mathtt{first}</tex> - первая запись
 
* <tex>\mathtt{first}</tex> - первая запись
 
* <tex>\mathtt{absolute}</tex> <tex>\mathtt{n}</tex> - получить запись с номером n от начала
 
* <tex>\mathtt{absolute}</tex> <tex>\mathtt{n}</tex> - получить запись с номером n от начала
* <tex>\mathtt{relative}</tex> <tex>\mathtt{n}</tex> - запись с номером n, нумерация с текущей позиции (n может быть отрицательным, в этом случае движение назад)
+
* <tex>\mathtt{relative}</tex> <tex>\mathtt{n}</tex> - запись с номером n, нумерация с текущей позиции (n может быть как положительным (движение в сторону конца), так и отрицательным (в этом случае движение назад))
  
 
=== Отсутствие данных ===
 
=== Отсутствие данных ===
Если данные закончились, либо при помощи команд был осуществлен невалидный переход, то случится событие '''not found'''.
+
 
 +
Если данные закончились, либо при помощи команд выше был осуществлен невалидный переход, то случится событие '''not found'''.
  
 
=== Пример ===
 
=== Пример ===
 +
 +
В данном примере из курсора для студентов мы получим идентификатор, имя и фамилию того студента, на которого сейчас указывает курсор.
 +
 
  <font color = blue>fetch </font><font color = black>SCursor <font color = blue>into </font>Sid<font color = gray>,</font> FirstName<font color = gray>,</font> LastName<font color = gray>;</font></font>
 
  <font color = blue>fetch </font><font color = black>SCursor <font color = blue>into </font>Sid<font color = gray>,</font> FirstName<font color = gray>,</font> LastName<font color = gray>;</font></font>
  
Для получения всех данных из курсора, можно создать цикл, предварительно создав обработчик ошибок на '''not found''', в котором выйдем из цикла, когда все элементы будут обработаны.
+
Для получения всех данных из курсора, можно создать цикл, предварительно создав обработчик ошибок на '''not found''' снаружи цикла, в котором выйдем из цикла, когда все элементы будут обработаны. Состояние '''sqlstate 02xxx''' не является исключительной ситуацией, а просто предупреждение от СУБД, которое зачастую игнорируется. Вопрос производительности зависит от реализации СУБД.
  
 
== Позиционированные изменения ==
 
== Позиционированные изменения ==
Изменение или удаление ровно одной строчки, которую последней выдал курсор.
+
 
 +
Изменение или удаление ровно одной строчки, которую последней выдал курсор (только в <code>update</code> и <code>delete</code>).
 +
 
 
=== Синтаксис ===
 
=== Синтаксис ===
  <font color = blue>update <font color = red>таблица</font> set <font color = red>что</font> where current of <font color = red>курсор</font><font color = gray>;</font></font>
+
 
  <font color = blue>delete from <font color = red>таблица</font> where current of <font color = red>курсор</font><font color = gray>;</font></font>
+
  <font color = blue>update <font color = red>таблица</font> set <font color = red>что</font> where current of <font color = red>курсор</font><font color = gray>;</font></font> <font color = green> -- изменение ровно одной строки, которую последней выдал курсор </font>
 +
  <font color = blue>delete from <font color = red>таблица</font> where current of <font color = red>курсор</font><font color = gray>;</font></font><font color = green> -- удаление последней выданной строки </font>
 +
 
 
===Пример===
 
===Пример===
 
  <font color = blue>if <font color = black>FirstName</font> <font color = gray>=</font> <font color = green>'Иван'</font> then
 
  <font color = blue>if <font color = black>FirstName</font> <font color = gray>=</font> <font color = green>'Иван'</font> then
 
     delete from <font color = black>Students</font> where current of <font color = black>SCursor</font><font color = gray>;</font>
 
     delete from <font color = black>Students</font> where current of <font color = black>SCursor</font><font color = gray>;</font>
  end if<font color = gray>;</font>
+
  end if<font color = gray>;</font></font>
 +
 
 +
В данном примере мы удалим текущего студента, если его имя Иван.
 +
 
 
== Пример применения курсора ==
 
== Пример применения курсора ==
  create procedure <font color = black>updateNameStat<font color = gray>(</font>name<font color = gray>)</font></font>
+
  <font color = blue>create procedure <font color = black>updateNameStat<font color = gray>(</font>name<font color = gray>)</font></font>
 
  begin
 
  begin
 
     declare <font color = black>cnt int</font> default <font color = darkgreen>0</font><font color = gray>;</font>
 
     declare <font color = black>cnt int</font> default <font color = darkgreen>0</font><font color = gray>;</font>
Строка 94: Строка 115:
  
 
=== Пояснения ===  
 
=== Пояснения ===  
В данном примере объявлен счетчик <code>cnt</code>, по умолчанию проинициализированный 0, курсор <code>SCursor</code>, перебирающий значение столбца <code>FirstName</code> из таблицы <code>Students</code>. Далее в бесконечном цикле:  
+
В данном примере объявлен счетчик <code>cnt</code>, по умолчанию проинициализированный 0, курсор <code>SCursor</code>, перебирающий значение столбца <code>FirstName</code> из таблицы <code>Students</code>. Далее в бесконечном цикле (так как мы не имеет возможности написать условие выхода из цикла):  
 
# определяем переменную <code>sname</code>, в которой будем хранить имя студента
 
# определяем переменную <code>sname</code>, в которой будем хранить имя студента
# достаем следующую запись в эту переменную (напоминание: <code>fetch</code> по умолчанию - это <code>fetch</code> next)
+
# достаем следующую запись в эту переменную (напоминание: <code>fetch</code> по умолчанию - это <code>fetch next</code>)
 
# проверяем, если текущее имя <code>sname</code> имя соответствует выделенному имени <code>name</code>, то увеличиваем счетчик.
 
# проверяем, если текущее имя <code>sname</code> имя соответствует выделенному имени <code>name</code>, то увеличиваем счетчик.
Повторяем данные операции до тех пор, пока у нас не случится <code>not found</code>, при возникновении которого в обработчике <code>not found</code> обновим данные статистики, и выйдем из блока, в котором был объявлен <code>handler</code> - то есть в данном случае выйдем из блока процедуры.
+
Повторяем данные операции до тех пор, пока у нас не случится <code>not found</code>, при возникновении которого в обработчике <code>not found</code> обновим данные статистики и (по определению обработчика в sql) выйдем из блока, в котором был объявлен <code>handler</code> - то есть в данном случае выйдем из блока процедуры.
 +
 
 +
Обработчик <code>not found</code> может быть только один. Если бы у нас было несколько курсоров, и мы бы захотели знать в обработчике, в каком из курсоров закончились данные, то предварительно вели бы записи в переменные, из каких курсоров планируем достать данные. И в обработчике проверили бы значение переменных.
 +
 
 +
Данные пример написан исключительно для демонстрации работы с курсорами. В реальной жизни подобное поведение может быть достигнут проще с использованием <code>select</code> и <code>count</code>.
  
 
===== Примечания =====
 
===== Примечания =====
Некоторые базы данных позволяют проверить, есть ли в курсоре еще данные. В таких случая пишется цикл <code>while</code> с вызовом метода такой проверки. Но стандартом наличие такой проверки не предусмотрено.
+
Некоторые базы данных позволяют проверить, есть ли в курсоре еще данные. В этих случаях пишется цикл <code>while</code> с вызовом метода проверки. Однако стандартом наличие этой проверки не предусмотрено.
  
 
==Особенности курсоров==
 
==Особенности курсоров==
 +
 
Курсоры, описанные в данной статье, являются '''внутренними'''. Они существуют внутри базы данных, используются, в том числе для взаимодействия между различными частями базы данных при планировании.
 
Курсоры, описанные в данной статье, являются '''внутренними'''. Они существуют внутри базы данных, используются, в том числе для взаимодействия между различными частями базы данных при планировании.
  
 
===Внешние курсоры===
 
===Внешние курсоры===
Результат запроса к базе данных может быть слишком велик и стать виной нехватки памяти как на стороне клиента, так и на стороны сервера, увеличить нагрузку на сеть. Поэтому наружу данные выдаются в виде внешнего курсора. После исполнения команды select выдается внешний курсор на данные, аналогично внешний курсор выдается при возврате результата из хранимой процедуры.
+
 
 +
Результат запроса к базе данных может быть слишком велик и стать виной нехватки памяти как на стороне клиента, так и на стороны сервера, увеличить нагрузку на сеть, вызывать и другие проблемы. Поэтому наружу данные выдаются в виде внешнего курсора. После исполнения команды select выдается внешний курсор на данные, аналогично внешний курсор выдается при возврате результата из хранимой процедуры.
  
 
===Размер выборки===
 
===Размер выборки===
Может быть указан для внешнего курсора каким-либо образом (в количестве записей или в объеме данных).
+
Может быть указан для внешнего курсора каким-либо образом (в количестве записей или в объеме данных, который выдается за один раз).
  
При выдаче курсора приходит метаинформация о курсоре и первая порция данных, размер которой мы указали. Для получения последующих порций данных необходимо обращаться к серверу. В конце сервер сообщит о том, что данные закончились, автоматически освободит ресурсы.  
+
При выдаче курсора приходит метаинформация о курсоре и первая порция данных, размер которой мы указали. Для получения последующих порций данных необходимо обращаться к серверу, что будет медленнее, чем получение первой порции. В конце сервер сообщит о том, что данные закончились, автоматически освободит ресурсы.  
  
 
Возникают альтернативы:
 
Возникают альтернативы:
  
* Малый размер выборки — много повторных обращений.
+
* Малый размер выборки — много повторных обращений к серверу базы данных, а так как обычно такое обращение происходит по сети, то взаимодействие не слишком быстрое.
* Большой размер выборки — требуется много памяти.
+
* Большой размер выборки — требуется много памяти, как со стороны сервера для генерации данных, так и со стороны прикладного приложения.
 
   
 
   
Выдача курсора приводит к расходу ресурсов сервера (память, блокировки и так далее). Поэтому внешний курсор стоит закрыть как можно раньше.
+
Выдача курсора приводит к расходу ресурсов сервера (память, возможно дисковая память, блокировки и так далее). Поэтому внешний курсор стоит закрыть как можно раньше.
 +
 
 +
Однако чтобы не получить слишком много данных по запросу, они выдаются в виде внешнего курсора.
  
 
==Литература==
 
==Литература==

Текущая версия на 19:24, 4 сентября 2022

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

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

Запрос исполнился, мы получили какие-то результаты, и теперь собираемся достать множество строк. Все, что обсуждалось до этого (set и select into) позволяло доставать только один результат, а из курсора их можно достать множество.

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

Синтаксис

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

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

Данные из курсора обрабатываются по одной строке (способа получить более одной строки за один раз нет). Мы напишем цикл, в рамках которого будут выбираться данные из курсора, и возможно будут меняться данные в нашей базе данных. Получаем, что во время таких итераций можно поменять данные самой таблицы, по которой построен текущий курсор. Представим ситуацию, что мы в сделанном курсоре в явной форме написали update на ту таблицу или таблицы, по которым сделан курсор. Что должно произойти в таком случае? В зависимости от желаемого поведения можно выбрать разную чувствительность курсора.

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

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

Scrollable | No scroll курсоры

  • [math]\mathtt{No}[/math] [math]\mathtt{scroll}[/math] - по такому курсору можно ходить только по одной записи вперед, как из обычного итератора в Java.
  • [math]\mathtt{Scroll}[/math] - по такому курсору можно ходить не только по одной записи вперед, но и вперед, назад, в начало или конец. Такой курсор больше похож на итератор из языка С++.

Примеры

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

declare SCursor cursor
select * from Students
order by SId;

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

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

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

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

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

Синтаксис

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

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

Для получения данных из курсора есть конструкция fetch.

Синтаксис

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 снаружи цикла, в котором выйдем из цикла, когда все элементы будут обработаны. Состояние sqlstate 02xxx не является исключительной ситуацией, а просто предупреждение от СУБД, которое зачастую игнорируется. Вопрос производительности зависит от реализации СУБД.

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

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

Синтаксис

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 обновим данные статистики и (по определению обработчика в sql) выйдем из блока, в котором был объявлен handler - то есть в данном случае выйдем из блока процедуры.

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

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

Примечания

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

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

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

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

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

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

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

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

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

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

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

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

Литература