Курсоры: доступ к отдельным строкам

Дата: 01.01.2007

Оператор выбора (select) возвращает обычно несколько строк, либо ничего не возвращает. Если оператор выбора возвращает в результате несколько строк, то с помощью курсоров можно получить доступ к каждой строке в отдельности.

В этой главе рассматриваются следующие темы:

Дается общий обзор курсоров;
Описывается, как объявить и открыть курсор;
Объясняется, как получить данные с помощью курсора;
Объясняется, как обновить или удалить данные с помощью курсора;
Описывается, как закрыть курсор и освободить занимаемую им память;
Даются примеры использования курсоров;
Описывается,как нейтрализовать воздействие (locking affects) курсоров;
Описывается, как получить информацию о курсорах.

Что такое курсор ?

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

Результирующего множества курсора - множества (таблицы) строк, которое получено в результате запроса и с которым связывается курсор;
Позиции курсора - указателя на одну из строк результирующего множества.

Позиция курсора указывает на текущую строку курсора. Пользователь может непосредственно модифицировать или удалять эту строку операторами update или delete, используя конструкцию с названием курсора. Можно изменить текущую позицию курсора с помощью операции fetch (передвинуть и загрузить). Эта операция переводит курсор на одну или несколько строк ниже в результирующем множестве.

Курсор аналогичен указателю на записи файла. Однако курсор можно сдвигать только вперед по результирующему множеству (последовательный доступ). Если несколько строк уже пройдены, то нельзя вернуться назад и снова получить к ним доступ, переведя на них курсор. Этот процесс позволяет просматривать строки друг за другом.

После объявления курсора он может находиться в двух состояниях:

Закрытом - В этом случае не существует результирующего множества, поэтому нельзя считывать из него информацию. Первоначально курсор находится в этом состоянии. Чтобы использовать курсор, его необходимо явно открыть. После окончания работы курсор необходимо явно закрыть. SQL Сервер может неявно закрыть курсор в некоторых случаях, которые будут далее перечислены;
Открытом - В этом случае с помощью курсора можно считывать и модифицировать строки.

Курсор можно закрыть и затем снова отрыть его. Повторное открытие курсора вновь создает результирующее множество и курсор устанавливается непосредственно перед первой строкой в этом множестве. Это позволяет пройти по результирующему множеству столько раз, сколько это необходимо. Курсор можно закрыть в любое время; необязательно проходить все результирующее множество.

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

Как SQL Сервер обрабатывает курсоры

Когда доступ к данным осуществляется через курсор, SQL Сервер разбивает процесс на следующие шаги:

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

SQL Сервер создает структуру курсора и компилирует запрос, определенный для курсора. Он сохраняет скомпилированный план, но не выполняет его.
Открытие курсора;

SQL Сервер выполняет план запроса. Он просматривает базовые таблицы (столько, сколько это необходимо как в обычном операторе выбора) и создает результирующее множество курсора. Он подготавливает необходимые временные таблицы, порожденные запросом, и выделяет ресурсы (такие как память) для реализации курсора. Он также располагает курсор перед первой строкой результирующего множества.
Передвижение курсора;

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

SQL Сервер обновляет или удаляет данные в указанной курсором строке результирующего множества (и в соответствующих базовых таблицах, откуда были выбраны данные). Этот шаг необязателен.
Закрытие курсора;

SQL Сервер закрывает результирующее множества курсора, удаляет все временные таблицы и освобождает ресурсы, занятые курсором. Однако он сохраняет план выполнения запроса, чтобы снова можно было открыть курсор.
Удаление курсора.

SQL Сервер удаляет план выполнения курсора из памяти и все ссылки на структуру курсора. После этого нужно объявить курсор, чтобы снова использовать его.

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

Пользователь должен объявить курсор прежде, чем можно будет его использовать. В объявлении указывается запрос, который определяет результирующее множество курсора. Пользователь может явно объявить курсор для обновления или только для чтения с помощью ключевых слов for update (для обновления) или for read only (только для чтения). Если эти слова не указаны, то SQL Сервер определяет можно ли обновлять через курсор, основываясь на типе запроса, который формирует результирующее множество курсора. Нельзя использовать операторы update и delete в формирующем запросе для курсора, объявленного только для чтения.

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

Синтаксис оператора declare cursor (объявление курсора) имеет следующий вид:

declare название_курсора cursor
  for оператор_выбора
  [for {read only | update [of список_столбцов]}]

Оператор declare cursor должен предшествовать любому оператору open (открыть) для этого курсора. Оператор declare cursor нельзя совмещать с другими операторами в одном Transact-SQL пакете за исключением случая, когда курсор используется в сохраненной процедуре.

Оператор_выбора (select) является запросом, который определяет результирующее множество данного курсора. Вообще говоря, в этом операторе можно использовать весь синтаксис и семантику оператора select, включая ключевое слово holdlock. Однако, в нем нельзя использовать конструкции compute, for browse и into.

Например, в следующем операторе объявляется курсор authors_crsr на результирующем множестве, которое содержит всех авторов, которые не живут в Калифорнии:

declare authors_crsr cursor
for select au_id, au_lname, au_fname
from authors
where state != 'CA'

Оператор выбора в объявлении курсора может содержать ссылки на названия параметров и локальные переменные. Однако эти параметры и локальные переменные должны быть определены в сохраненной процедуре, которая содержит оператор объявления курсора declare cursor. Если курсор используется в триггере, то в соответствующем операторе выбора можно ссылаться на временные триггерные таблицы inserted и deleted. Более подробную информацию об операторе выбора можно посмотреть в главе 2 «Запросы: выбор данных из таблицы».

Области действия курсора

Курсор определяется своей областью (диапазоном) действия, которая определяет временной промежуток (region), в течении которого курсор существует. Вне этого промежутка курсор перестает существовать. Области действия курсора определяются следующим образом:

Сессия - В этом случае область действия курсора начинается в момент регистрации клиента SQL Сервера и заканчивается, когда клиент заканчивает работу. Эта область действия отличается от областей, определяемых сохраненными процедурами и триггерами;
Сохраненная процедура - В этом случае область действия курсора начинается с момента начала выполнения сохраненной процедуры и заканчивается, когда она заканчивает выполнение. Когда сохраненная процедура вызывает другую процедуру, то SQL Сервер начинает отсчет новой области действия и рассматривает ее, как подобласть области действия первой процедуры;
Триггер - В этом случае область действия курсора начинается с момента начала выполнения триггера и заканчивается, когда триггер заканчивает свою работу.

Название курсора должно быть уникально в области его действия. В различных областях названия курсоров могут совпадать. Курсор определенный для одной области недоступен из других областей (диапазонов) действия. Однако, SQL Сервер позволяет использовать курсор в подобласти, если в ней не был определен курсор с тем же названием.

SQL Сервер определяет конфликты в названиях курсоров лишь в процессе выполнения. В сохраненной процедуре или триггере можно определить два курсора с одним названием, если в процессе исполнения они используются раздельно, как в следующем примере:

create procedure proc1 (@flag int)
as
if (@flag)
   declare names_crsr cursor
   for select au_fname from authors
else
   declare names_crsr cursor
   for select au_lname from authors
return

Эта процедура будет успешно выполнена, поскольку только один из курсоров names_crsr будет определен в процессе выполнения этой процедуры.

Развертывание курсора и результирующее множество

Результирующее множество курсора может не совпадать с данными из базовых таблиц. Например, курсор объявленный с конструкцией order by (упорядочить по) обычно требует создания внутренней таблицы для упорядочения строк результирующего множества. Кроме того, SQL Сервер не блокирует строки базовых таблиц, которые соответствуют строкам внутренней таблицы, что позволяет другим клиентам обновлять эти строки в базовых таблицах. В этом случае строки, которые видит клиент в результирующем множестве могут не отражать последних изменений, произошедших в базовых таблицах.

Результирующее множество курсора порождается по мере его продвижения. Это означает, что оператор выбора курсора выполняется как обычный запрос на выбор. Этот процесс известный как развертывание курсора (cursor scans) обеспечивает быстрое время ответа и не требует считывания строк, которые не нужны приложению в данный момент.

SQL Сервер требует, чтобы при развертывании курсора использовался уникальный индекс таблицы, особенно на нулевом уровне изоляции считывания (isolation level 0 reads). Если таблица содержит столбец-счетчик и необходимо создать неуникальный индекс для этой таблицы, то следует использовать опцию базы данных identity in nonunique index (счетчик в неуникальном индексе), что позволит автоматически включать столбец-счетчик в ключи табличных индексов и поэтому все они будут уникальными. Таким образом, эта опция делает логически неуникальные индексы внутренне уникальными, что позволяет использовать их в обновляемых курсорах на нулевом уровне изоляции считывания.

Можно также использовать курсор для таблиц без индексов, если эти таблицы не обновляются другими процессами, что приводит к изменению позиций строк. Например:

declare storinfo_crsr cursor
for select stor_id, stor_name, payterms
    from stores
    where state = 'CA'

Таблица stores, указанная в этом курсоре, вообще не содержит индексов. SQL Сервер допускает объявление курсора в таблице без уникальных индексов, но при обновлении или удалении из нее строк закрываются все курсоры в таких таблицах.

Создание обновляемых курсоров

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

Можно явно указать, является ли курсор обновляемым с помощью ключевых слов read only или update в операторе declare. Например, в следующем операторе определяется обновляемое результирующее множество для курсора pubs_crsr:

declare pubs_crsr cursor
for select pub_name, city, state
from publishers
for update of city, state

В этом примере результирующее множество будет включать все строки из таблицы publishers, но только поля city и state явно указаны как обновляемые.

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

опции distinct (различные);
предложения group by (группировка);
агрегирующих функций;
подзапросов;
оператора union (объединить);
предложения at isolation read uncommitted.

Нельзя указывать предложение for update, если оператор выбора курсора содержит одну из вышеперечисленных конструкций. SQL Сервер устанавливает курсор только для чтения, если предложение order by содержится в операторе выбора этого курсора. Дополнительная информация о курсорах содержится в главе «Курсоры» Справочного руководства SQL Сервера.

Если в предложении for update не указывается список столбцов, то все столбцы будут обновляемыми. Как было отмечено ранее при описании развертывания курсора, SQL Сервер пытается использовать уникальные индексы для обновляемых курсоров, когда развертывает базовую таблицу. При наличии курсора SQL Сервер рассматривает индекс, содержащий столбец-счетчик, как уникальный, даже если он и не объявлен таковым.

SQL Сервер позволяет указывать в списке столбцов предложения for update названия столбцов, которых нет в операторе выбора курсора.

В следующем примере SQL Сервер использует уникальный индекс в столбце pub_id таблицы publishers (несмотря на то, что этого столбца нет в определении курсора newpubs_crsr):

declare newpubs_crsr cursor
for select pub_name, city, state
from publishers
for update

Если предложение for update не указано, то SQL Сервер может выбрать любой уникальный индекс или, при его отсутствии, любую комбинацию индексов для развертывания таблицы. Однако, если явно указано предложение for update, то должен существовать уникальный индекс, необходимый для развертывания базовой таблицы. В противном случае будет выдано сообщение об ошибке.

В списке столбцов предложения for update следует указывать столбцы, в которых необходимо обновлять данные, и в этом списке не должно быть столбцов, включенных в уникальные индексы. Это позволяет SQL Серверу использовать уникальные индексы для развертки таблицы и позволяет избежать аномального обновления известного как Проблема привидений (Halloween Problem).

Эта проблема возникает, когда клиент обновляет поле строки результирующего множества курсора, которое влияет на порядок расположения строк базовой таблицы. Например, если SQL Сервер получает доступ к базовой таблице используя индекс, и ключ (значение) индекса обновляется клиентом, то измененная строка может переместиться и следовательно может быть снова считана через курсор. Это результат того, что обновляющий курсор лишь логически создает результирующее множество. На самом деле это множество является подмножеством базовой таблицы, на основе которой получен курсор.

Открытие курсоров

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

open название_курсора

После открытия курсор располагается перед первой строкой результирующего множества. Теперь можно использовать операцию fetch (загрузка) для считывания первой строки результирующего множества.

SQL Сервер не позволяет открывать курсор, если он уже открыт или еще не объявлен. Можно снова открыть ранее закрытый курсор, чтобы вернуть курсор в начало результирующего множества.

Считывание строк данных с помощью курсоров

После объявления и открытия курсора можно выбирать строки из результирующего множества с помощью команды fetch (загрузить, сдвинуть). Эта команда возвращает клиенту одну или несколько строк. Можно включить в эту команду Transact-SQL параметры или локальные переменные для сохранения возвращаемых данных.

Синтаксис оператора fetch

Оператор fetch имеет следующий синтаксис:

fetch название_курсора [into список_переменных]

Например, после объявления и открытия курсора authors_crsr можно считать первую строку результирующего множества следующим образом:

fetch authors_crsr

au_id        au_lname           au_fname
-----------  -----------------  ------------
341-22-1782  Smith              Meander

Каждый последующий оператор fetch выбирает следующую строку результирующего множества. Например:

fetch authors_crsr

au_id         au_lname        au_fname
------------  --------------  ---------------
527-72-3246    Greene         Morningstar

После прохода всех строк курсор будет указывать на последнюю строку результирующего множества. Если вновь попытаться выполнить команду fetch, то SQL Сервер выдаст предупреждающее сообщение о состоянии переменной sqlstatus (описанной далее), которая указывает на отсутствие данных. Позиция курсора при этом не изменится.

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

В конструкции into указываются переменные, в которых SQL Сервер должен сохранить возвращаемые данные. Список_переменных должен состоять из ранее объявленных Transact-SQL параметров или локальных переменных.

Например, после объявления переменных @name, @city и @state можно сохранить в них поля строки, возвращаемой через курсор pubs_crsr:

fetch pubs_crsr into @name, @city, @state

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

Проверка состояния курсора

SQL Сервер возвращает информацию о состоянии (статусе) курсора после каждого чтения (загрузки). Информацию о состоянии можно также получить через глобальную переменную @@sqlstatus. В следующей таблице перечислены возможные значения этой переменной и их смысл:

Таблица 16-1: Значения переменной @sqlstatus

+---------------------------+-----------------------------------+
| Величина                  | Смысл                             |
+---------------------------+-----------------------------------+
| 0                         | Указывает на успешное окончание   |
|                           | оператора fetch.                  |
+---------------------------+-----------------------------------+
| 1                         | Указывает на ошибочное завершение |
|                           | оператора fetch.                  |
+---------------------------+-----------------------------------+
| 2                         | Указывает, что в результирующем   |
|                           | множестве больше нет данных для   |
|                           | чтения. Это предупреждение        |
|                           | выдается, если курсор находится   |
|                           | на последней строке и клиент      |
|                           | выдает команду fetch.             |
+---------------------------+-----------------------------------+

Следующий оператор определяет статус переменной @@sqlstatus для текущего открытого курсора authors_crsr:

select @@sqlstatus

-------------------
       0

(Выбрана 1 строка)

Только оператор fetch может устанавливать переменную @@sqlstatus. Другие операторы не затрагивают эту переменную.

Проверка количества загруженных строк

У SQL Сервера имеется также глобальная переменная @@rowcount. Она позволяет увидеть количество строк результирующего множества, возвращенных клиенту операторами fetch. Другими словами, в ней запоминается общее количество строк, просмотренных через курсор до текущего момента времени.

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

В следующем примере определяется значение переменной @@rowcount для текущего открытого курсора authors_crsr:

select @@rowcount

-------------------
       1

(Выбрана 1 строка)

Получение нескольких строк одним оператором fetch

По умолчанию команда fetch позволяет получить одну строку данных за один раз. Пользователь может установить опцию cursor rows (курсорные строки), чтобы изменить число строк, возвращаемых одной командой fetch. Однако эта опция не влияет на операторы fetch, содержащие конструкцию into.

Команда установки этой опции имеет следующий вид:

set cursor rows число for название_курсора

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

Например, можно следующим образом изменить количество строк, возвращаемых через курсор authors_crsr:

set cursor rows 3 for authors_crsr

Теперь после каждого считывания оператор fetch будет возвращать три строки:

fetch authors_crsr

au_id        au_lname             au_fname
-----------  -------------------  ---------------
648-92-1872  Blotchet-Halls       Reginald
712-45-1867  del Castillo         Innes
722-51-5424  DeFrance             Michel

После считывания курсор будет расположен на последней переданной строке (в данном примере на авторе Michel DeFrance).

Передача нескольких строк за один раз особенно удобна для приложений клиента. Если пользователь считывает более одной строки за раз, то Открытый Клиент или Встроенный SQL (Open Client or Embedded SQL) автоматически буферизуют строки, переданные приложению клиента. Клиент по-прежнему имеет построчный доступ к данным, но при выполнении операторов fetch обращение к SQL Серверу происходит реже, что повышает производительность системы.

Обновление и удаление строк с помощью курсора

Если курсор является обновляемым, то через него можно обновлять и удалять строки. SQL Сервер анализирует оператор выбора, определяющий курсор, чтобы выяснить можно ли обновлять через этот курсор. Можно также явно указать на обновляющий курсор с помощью предложения for update в операторе объявления курсора declare cursor. Дополнительную информацию по этому поводу можно посмотреть в разделе "Создание обновляемых курсоров".

Удаление строк из результирующего множества курсора

С помощью конструкции where current of в операторе delete можно удалять строку, где находится курсор. Если строка удаляется из результирующего множества, то она также удаляется из соответствующей базовой таблицы. С помощью курсора за один раз можно удалить только одну строку.

Предложение delete... where current of имеет следующий синтаксис:

delete [from] [[база_данных.]владелец.]{название_таблицы | название_вьювера}
where current of название_курсора

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

Например, можно удалить строку, на которую указывает курсор authors_crsr с помощью следующего оператора:

delete from authors
where current of authors_crsr

Ключевое слово from здесь можно не указывать.

Замечание:
Нельзя удалять строки с помощью курсора, который определен через соединение, даже если он объявлен как обновляемый.

После удаление строки с помощью курсора SQL Сервер располагает курсор перед строкой, которая следует за удаленной строкой в результирующем множестве. Нужно по-прежнему использовать оператор fetch, чтобы получить доступ к следующей строке. Если была удалена последняя строка, то SQL Сервер располагает курсор за последней строкой результирующего множества.

Например, после удаления строки в предыдущем примере (которая соответствует Мишелю ДеФрансу) можно просчитать следующие три строки результирующего множества следующим образом:

fetch authors_crsr

au_id        au_lname          au_fname
-----------  ----------------  ---------------
807-91-6654  Panteley          Sylvia
899-46-2035  Ringer            Anne
998-72-3567  Ringer            Albert

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

Обновление строк результирующего множества курсора

Используя конструкцию where current of в операторе update, можно обновить содержимое строки, на которую указывает курсор. Каждое обновление результирующего множества курсора приводит к обновлению содержимого базовой таблицы, из которой получено множество курсора.

Оператор update... where current of имеет следующий синтаксис:

update [[база_данных.]владелец.]{название_таблицы | название_вьювера}
   set [[[база_данных.]владелец.]{название_таблицы | название_вьювера}]
       название_столбца1 = { выражение1 | NULL | (оператор_выбора)}
    [, название_столбца2 = { выражение2 | NULL | (оператор_выбора)}] ...
   where current of название_курсора

В предложении set указываются названия столбцов и их новые (обновляемые) значения. Если здесь указывается несколько столбцов, то они должны разделяться запятыми.

Название таблицы или вьювера, указанное в этом операторе, должно совпадать с названием таблицы или вьювера, указанным в предложении from оператора выбора, определяющего курсор. Если в предложении from указано несколько таблиц или вьюверов (в случае соединения), то можно указать только ту таблицу (вьювер), которая действительно обновляется.

Например, можно обновить строку, на которую указывает курсор pubs_crsr, следующим образом:

update publishers
set city = "Pasadena",
    state = "CA"
where current of pubs_crsr

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

SQL позволяет обновлять столбцы базовой таблицы, которые не были указаны в списке столбцов оператора выбора, определяющего курсор. Однако, если в предложении for update указывается список столбцов, то обновлять можно содержимое только этих столбцов.

Закрытие и удаление курсора

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

close название_курсора

Закрытие курсора не изменяет его определения. После этого можно вновь открыть курсор, тогда SQL Сервер создаст новое результирующее множество с помощью того же запроса. Например:

close authors_crsr
open authors_crsr

После этого можно считывать данные через курсор authors_crsr, начиная с начала результирующего множества. Все условия, связанные с этим курсором (такие как число строк считываемых за один раз) остаются в силе.

Например:

fetch authors_crsr

au_id        au_lname          au_fname
-----------  ----------------  ---------------
341-22-1782  Smith             Meander
527-72-3246  Greene            Morningstar
648-92-1872  Blotchet-Halls    Reginald

Если курсор больше не нужен, то его следует удалить (deallocate). Синтаксис оператора deallocate имеет следующий вид:

deallocate cursor название_курсора

Удаление курсора освобождает все ресурсы с ним связанные, включая название курсора. Нельзя вновь использовать название курсора до тех пор, пока курсор не удален. Если удаляется открытый курсор, SQL Сервер автоматически закрывает его. По окончанию соединения пользователя с сервером также закрываются и удаляются все курсоры.

Примеры использования курсора

Последующие примеры использования курсоров будут базироваться на следующем запросе:

select author = au_fname + " " + au_lname, au_id
from authors
order by au_lname

Результат этого запроса имеет следующий вид:

author                    au_id
------------------------  ----------------
Abraham Bennet            409-56-7008
Reginald Blotchet-Halls   648-92-1872
Cheryl Carson             238-95-7766
Michel DeFrance           722-51-5454
Ann Dull                  427-17-2319
Marjorie Green            213-46-8915
Morningstar Greene        527-72-3246
Burt Gringlesby           472-27-2349
Sheryl Hunter             846-92-7186
Livia Karsen              756-30-7391
Chastity Locksley         486-29-1786
Stearns MacFeather        724-80-9391
Heather McBadden          893-72-1158
Michael O'Leary          267-41-2394
Sylvia Panteley           807-91-6654
Anne Ringer               899-46-2035
Albert Ringer             998-72-3567
Meander Smith             341-22-1782
Dick Straight             274-80-9391
Dirk Stringer             724-08-9931
Johnson White             172-32-1176
Akiko Yokomoto            672-71-3249
Innes del Castillo        712-45-1867

В следующих пунктах показано как использовать курсор в этом запросе:

Сначала необходимо объявить курсор.

В операторе declare курсор определяется с помощью вышеприведенного оператора выбора:
```
declare newauthors_crsr cursor for
select author = au_fname + " " + au_lname, au_id
from authors
order by au_lname
```
После объявления курсор можно открыть:
```
open newauthors_crsr
```

Теперь можно считывать строки, используя курсор:

fetch newauthors_crsr

author                 au_id
--------------------   ---------------
Abraham Bennet         409-56-7008

Можно считывать несколько строк за один раз, установив число с помощью команды set:

set cursor rows 5 for newauthors_crsr
fetch newauthors_crsr

author                      au_id
-------------------------   ----------------
Reginald Blotchet-Halls     648-92-1872
Cheryl Carson               238-95-7766
Michel DeFrance             722-51-5454
Ann Dull                    427-17-2319
Marjorie Green              213-46-8915

Каждое последующее считывание будет сдвигать курсор еще на пять строк:

fetch newauthors_crsr

author                 au_id
---------------------  ------------
Morningstar Greene     527-72-3246
Burt Gringlesby        472-27-2349
Sheryl Hunter          846-92-7186
Livia Karsen           756-30-7391
Chastity Locksley      486-29-1786

После окончания работы с курсором его можно закрыть:
```
close newauthors_crsr
```
Закрытие курсора приводит к закрытию (releases) результирующего множества, но курсор остается определенным. Если его снова открыть командой open, то SQL Сервер снова выполняет запрос для формирования результирующего множества и устанавливает курсор перед первой строкой этого множества. По прежнему каждый оператор fetch будет считывать по пять строк.

Для полного удаления курсора следует выполнить команду deallocate:
```
deallocate cursor newauthors_crsr
```

Нельзя использовать название курсора до тех пор, пока курсор не удален командой deallocate:

Курсоры в сохраненных процедурах

Курсоры особенно полезны в сохраненных процедурах. С их помощью можно выполнить задание, требующее несколько запросов, всего одним запросом. Однако, все операции с курсором должны быть выполнены в одной процедуре. В сохраненной процедуре нельзя открывать, считывать или закрывать курсор, который не был объявлен в этой процедуре. Курсор не определен за пределами области действия (scope) сохраненной процедуры.

Например, следующая сохраненная процедура au_sales проверяет таблицу продаж, чтобы определить, продается ли у данного автора достаточно хорошо хотя бы одна книга:

create procedure au_sales (@author_id id)
as
/* declare local variables used for fetch */
declare @title_id tid
declare @title varchar(80)
declare @ytd_sales int
declare @msg varchar(120)

/* declare the cursor to get each book written by given author */
declare author_sales cursor for

select ta.title_id, t.title, t.total_sales
from titleauthor ta, titles t
where ta.title_id = t.title_id
  and ta.au_id = @author_id

open author_sales

fetch author_sales
       into @title_id, @title, @ytd_sales

if (@@sqlstatus = 2)
begin
   print "We do not sell books by this author."
   close author_sales
   return
end

/* if cursor result set is not empty, then process each row of
   information */
while (@@sqlstatus = 0)
begin
   if (@ytd_sales = NULL)
   begin
       select @msg = @title +
          " had no sales this year."
       print @msg
   end
   else if (@ytd_sales < 500)
   begin
       select @msg = @title +
           " had poor sales this year."
       print @msg
   end
   else if (@ytd_sales < 1000)
   begin
       select @msg = @title +
           " had mediocre sales this year."
       print @msg
   end
   else
   begin
       select @msg = @title +
           " had good sales this year."
       print @msg
   end
   fetch author_sales into @title_id, @title,
   @ytd_sales
end

/* if error occurred, call a designated handler */
if (@@sqlstatus = 1) exec error_handle
close author_sales
deallocate cursor author_sales
return

Дополнительную информацию о сохраненных процедурах можно получить в главе 14 "Использование сохраненных процедур".

Курсоры и блокировка

Методы блокировки при работе с курсором аналогичны обычным методам блокировки для SQL Сервера. Вообще говоря, операторы, считывающие данные (такие как select или readtext), используют разделяющую (shared) блокировку каждой страницы данных, чтобы предотвратить изменение данных со стороны неподтвержденных транзакций. Операторы обновления используют исключающую (exclusive) блокировку каждой страницы, которую они изменяют. Чтобы уменьшить вероятность тупиков (deadlocks) и улучшить производительность, SQL Сервер часто предваряет исключающую блокировку обновляющей блокировкой, которая указывает, что клиент собирается изменить данные на странице.

Для обновляющих курсоров SQL Сервер использует по умолчанию обновляющую блокировку при просмотре таблиц и вьюверов, указанных в предложении for update оператора declare cursor. Если предложение for update включено, но список таблиц пуст, то при обращении ко всем таблицам и вьюверам, указанным в предложении from оператора select, по умолчанию устанавливается обновляющая блокировка. Если предложение for update не указано, то при обращении ко всем таблицам и вьюверам устанавливается разделяющая блокировка. Чтобы использовалась разделяющая блокировка вместо обновляющей, необходимо добавить ключевое слово shared в предложении from. В частности, можно добавить слово shared к названию таблицы, для которой предпочтительна разделяющая блокировка.

Информация о блокировках SQL Сервера дается в Руководстве системного администратора SQL Сервера. Дополнительную информацию о курсорах и блокировках можно посмотреть в Справочном руководстве SQL Сервера.

Получение информации о курсорах

SQL Сервер предоставляет системную процедуру sp_cursorinfo, которая дает информацию о названии курсора, его текущем состоянии (таком как открыт или закрыт) и столбцах результирующего множества. В следующем примере дается информация о курсоре authors_crsr:

sp_cursorinfo 0, authors_crsr

Cursor name 'authors_crsr' is declared at nesting level '0'.
The cursor id is 327681
The cursor has been successfully opened 1 times
The cursor was compiled at isolation level 1.
The cursor is not open.
The cursor will remain open when a transaction is committed or rolled back.
The number of rows returned for each FETCH is 1.
The cursor is updatable.

There are 3 columns returned by this cursor.
The result columns are:

Name = 'au_id', Table = 'authors', Type = ID, Length = 11 (updatable)
Name = 'au_lname', Table = 'authors', Type = VARCHAR, Length = 40 (updatable)
Name = 'au_fname', Table = 'authors', Type = VARCHAR, Length = 20 (updatable)