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

 

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

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

 

·           Дается общий обзор курсоров;

·           Описывается, как объявить и открыть курсор;

·           Объясняется, как получить данные с помощью курсора;

·           Объясняется, как обновить или удалить данные с помощью курсора;

·           Описывается, как закрыть курсор и освободить занимаемую им память;

·           Даются примеры использования курсоров;

·           Описывается,как нейтрализовать воздействие (locking affects) курсоров;

·           Описывается, как получить информацию о курсорах.

 

 

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

 

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

·           Позиции курсора - указателя на одну из строк результирующего множества.

 

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

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

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

 

·           Закрытом - В этом случае не существует результирующего множества, поэтому нельзя считывать из него информацию. Первоначально курсор находится в этом состоянии. Чтобы использовать курсор, его необходимо явно открыть. После окончания работы курсор необходимо явно закрыть. 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 название_курсора [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 позволяет получить одну строку данных за один раз. Пользователь может установить опцию 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)

 

Дополнительная информация о процедуре sp_cursorinfo дается в Справочном руководстве SQL Сервера.

 

[ << Назад ] [ Содержание ] [ Далее >> ]