Шпаргалка по "Информатике"

Название курсора должно быть уникально в области его действия. В различных областях названия курсоров могут совпадать. Курсор определенный для одной области недоступен из других областей (диапазонов) действия. Однако, 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. Дополнительную информацию по этому поводу можно посмотреть в разделе "Создание обновляемых курсоров".