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

Курсоры: доступ к отдельным строкам
Оператор выбора (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
В следующих пунктах показано как использовать курсор в этом запросе:
1.Сначала необходимо объявить курсор. В операторе declare курсор определяется с помощью вышеприведенного оператора выбора:
declare newauthors_crsr cursor for
select author = au_fname + " " + au_lname, au_id
from authors
order by au_lname
2. После объявления курсор можно открыть:
open newauthors_crsr
3. Теперь можно считывать строки, используя курсор:
fetch newauthors_crsr
author au_id
------------------------- ---------------
Abraham Bennet 409-56-7008
4. Можно считывать несколько строк за один раз, установив число с помощью команды 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
5. После окончания работы с курсором его можно закрыть:
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)