Курсоры

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

Операции в реляционной базе данных выполняются над множеством строк. Набор строк, возвращаемый инструкцией SELECT, содержит все строки, которые удовлетворяют условиям, указанным в предложении WHERE инструкции. Такой полный набор строк, возвращаемых инструкцией, называется результирующим набором.

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

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

Пример:

Declare @i As int, @a As nchar(10)

select @i= col1, @a = col2 from Table_2

Where Col1 = 1

select @i, @a

Результат:

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

Пример:

Declare @i As int, @a As nchar(10)

select @i= col1, @a = col2 from Table_2

select @i, @a

Результат:

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

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

Курсор в SQL – это область в памяти базы данных, которая предназначена для хранения последнего оператора SQL. Указанная область в памяти поименована и доступна для прикладных программ.

Курсоры позволяют усовершенствовать обработку результатов:

• позиционируясь на отдельные строки результирующего набора;

• получая одну или несколько строк от текущей позиции в результирующем наборе;

• поддерживая изменение данных в строках в текущей позиции результирующего набора;

• поддерживая разные уровни видимости изменений, сделанных другими пользователями для данных, представленных в результирующем наборе;

• предоставляя инструкциям Transact-SQL в сценариях, хранимых процедурах и триггерах доступ к данным результирующего набора.

SQL определяет курсоры статические, динамические и управляемые набором ключей.

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

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

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

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

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

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

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

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

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

Курсоры делятся на две категории: последовательные и прокручиваемые.

Последовательные позволяют выбирать данные только в одном направлении – от начала к концу.

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

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

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

Microsoft SQL Server 2005 поддерживает два метода запроса курсоров:

• Transact-SQL.

Язык Transact-SQL поддерживает синтаксис использования курсоров по стандарту SQL-92.

• API-функции курсоров базы данных.

SQL Server поддерживает функциональность курсоров для следующих API-интерфейсов баз данных:

• ADO (Microsoft ActiveX Data Object);

• OLE DB;

• открытый интерфейс доступа к базам данных (ODBC).

В ODBC и ADO определены четыре типа курсора, поддерживаемые MicrosoftSQL Server 2005:

• статические курсоры;

• динамические курсоры;

• однонаправленные курсоры;

• курсоры, управляемые набором ключей.

В моделях курсоров API базы данных однонаправленный курсор рассматривается как отдельный тип курсора.

В SQL Server однонаправленность и прокрутка рассматриваются как параметры, которые могут быть применены к статическим, динамическим и управляемым набором ключей курсорам.

Оба этих способа (Transact-SQL и API-функции курсоров базы данных) никогда не должны использоваться в приложении одновременно.

Приложение, применяющее API-интерфейс для определения режима работы курсоров, не может затем выполнить инструкцию Transact-SQL DECLARE CURSOR для запроса нового курсора Transact-SQL. Инструкция DECLARE CURSOR может использоваться только в том случае, если все атрибуты API-курсоров будут установлены в значения по умолчанию.

Если не был запрошен ни Transact-SQL, ни API-курсор, SQL Server целиком возвращает по умолчанию результирующий набор приложению (это называется результирующим набором по умолчанию).

Для всех курсоров SQL Server используется одинаковый цикл обработки.

1. Создание или объявление курсора. Связать курсор с результирующим набором инструкции Transact-SQL и задать его характеристики (например, возможность обновления строк).

2. Выполнить инструкцию Transact-SQL для заполнения курсора. Открытие курсора, т.е. наполнение его данными, которые сохраняются в многоуровневой памяти.

3. Получить в курсор необходимые строки. Операция получения в курсор одной и более строк называется выборкой. Выполнение серии выборок для получения строк в прямом или обратном направлении называется прокруткой.

4. При необходимости выполнить операции изменения (обновления или удаления) строки в текущей позиции курсора.

5. Закрыть курсор (после чего он становится недоступным для пользовательских программ).

6. Освобождение курсора, т.е. удаление курсора как объекта, поскольку его закрытие необязательно освобождает ассоциированную с ним память

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

Инструкция DECLARE CURSOR определяет такие атрибуты серверного курсора языка Transact-SQL, как свойства просмотра и запрос, используемый для построения результирующего набора, на котором работает курсор.

Инструкция OPEN заполняет результирующий набор.

Оператор FETCH возвращает из него строку (3 и 4 этапы).

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

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

Инструкция DECLARE CURSOR была расширена; таким образом, можно указать четыре типа для курсоров Transact-SQL. Эти курсоры различаются по способности обнаруживать изменения в результирующем наборе и по потребляемым ими ресурсам, например памяти и пространству в базе данных tempdb.

Синтаксис

SQL 92 Syntax для задания параметров работы курсора.

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FOR select_statement

[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

[;]

Transact-SQL Extended Syntax использует расширения языка Transact-SQL, позволяющие определять курсоры с помощью таких же типов, как типы, используемые в курсорных функциях API баз данных, таких как ODBC и ADO.

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR select_statement

[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

[;]

Нельзя смешивать две эти формы.

Аргументы:

cursor_name - имя определенного серверного курсора Transact-SQL.

SCROLL - указывает, что доступны все параметры выборки (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Параметр SCROLL не может указываться вместе с параметром FAST_FORWARD (расширенная форма).

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

select_statement - Стандартный оператор SELECT, который определяет результирующий набор курсора. Ключевые слова COMPUTE, COMPUTE BY и INTO нельзя использовать внутри выражения select_statement при объявлении курсора.

Microsoft SQL Server неявно преобразует курсор в другой тип, если предложения в инструкции select_statement нельзя использовать с курсором запрошенного типа.

READ ONLY - предотвращает изменения, сделанные через этот курсор. Предложение WHERE CURRENT OF в инструкции UPDATE или DELETE не может ссылаться на курсор. Этот параметр переопределяет установленную по умолчанию возможность обновления курсора.

LOCAL (расширенная форма) - Указывает, что область курсора локальна по отношению к пакету, хранимой процедуре или триггеру, в которых этот курсор был создан. Имя курсора допустимо только внутри этой области. На курсор могут ссылаться локальные переменные пакета, хранимые процедуры, триггеры или параметр OUTPUT хранимой процедуры.

GLOBAL (расширенная форма) - Указывает, что область курсора является глобальной по отношению к соединению. Имя курсора может использоваться любой хранимой процедурой или пакетом, которые выполняются соединением. Курсор неявно освобождается только в случае разрыва соединения.

FORWARD_ONLY (расширенная форма) - Указывает, что курсор может просматриваться только от первой строки к последней. Поддерживается только параметр выборки FETCH NEXT.

Если параметр FORWARD_ONLY указан без ключевых слов STATIC, KEYSET или DYNAMIC, то курсор работает как курсор DYNAMIC. Если не указан ни один из параметров FORWARD_ONLY или SCROLL, а также не указано ни одно из ключевых слов STATIC, KEYSET или DYNAMIC, то по умолчанию задается параметр FORWARD_ONLY.

Курсоры STATIC, KEYSET и DYNAMIC имеют значение по умолчанию SCROLL.

STATIC (расширенная форма) - Определяет курсор, который создает временную копию данных для использования курсором. Все запросы к курсору обращаются к указанной временной таблице в базе данных tempdb, поэтому изменения базовых таблиц не влияют на данные, возвращаемые выборками для данного курсора, а сам курсор не позволяет производить изменения.

KEYSET (расширенная форма) - Указывает, что членство или порядок строк в курсоре не изменяются после его открытия. Набор ключей, однозначно определяющих строки, встроен в таблицу в базе данных tempdb с именем keyset.

DYNAMIC (расширенная форма) - Определяет курсор, отображающий все изменения данных, сделанные в строках результирующего набора при просмотре этого курсора. Значения данных, порядок, а также членство строк в каждой выборке могут меняться. Параметр выборки ABSOLUTE динамическими курсорами не поддерживается (рассматривается ниже).

FAST_FORWARD (расширенная форма) - Указывает курсор FORWARD_ONLY, READ_ONLY, для которого включена оптимизация производительности. Параметр FAST_FORWARD не может указываться вместе с параметрами SCROLL или FOR_UPDATE.

SCROLL_LOCKS (расширенная форма) - Указывает, что позиционированные изменения или удаления, сделанные через этот курсор, будут гарантированно применены. Microsoft SQL Server блокирует строки при их считывании в курсор, что обеспечивает возможность их последующего изменения. Параметр SCROLL_LOCKS не может указываться вместе с параметром FAST_FORWARD или STATIC.

OPTIMISTIC (расширенная форма) - Указывает, что позиционированные изменения или удаления, сделанные через этот курсор, не будут успешными, если эти строки были изменены после считывания в курсор.

SQL Server не блокирует строки при их считывании в курсор. Вместо этого, чтобы определить, изменялась ли строка после считывания в курсор, выполняется сравнение значений столбца timestamp (или контрольных сумм, если в таблице нет столбца timestamp). Если строка была изменена, то ее позиционированное изменение или удаление невозможно. Параметр OPTIMISTIC не может указываться вместе с параметром FAST_FORWARD.

TYPE_WARNING (расширенная форма) - Указывает, что клиенту посылается предупреждающее сообщение при неявном преобразовании типа курсора из запрошенного типа в другой тип.

FOR UPDATE [OF column_name [,...n]] - Указывает столбцы курсора, которые можно изменять. Если указано предложение OF column_name [,...n], то изменения разрешаются только для перечисленных столбцов. Если инструкция UPDATE используется без списка столбцов, то обновление возможно для всех столбцов, за исключением случая, когда был указан параметр READ_ONLY.

Замечания:

Если в инструкции DECLARE CURSOR, выполненной согласно SQL-92, параметр SCROLL не указан, единственным поддерживаемым параметром инструкции FETCH является NEXT. Если в инструкции DECLARE CURSOR, выполненной согласно SQL-92, указан параметр SCROLL, поддерживаются все параметры инструкции FETCH.

При использовании расширений курсора Transact-SQL DECLARE применимы следующие правила:

• Если указан FORWARD_ONLY или FAST_FORWARD, единственным поддерживаемым параметром инструкции FETCH является NEXT.

• Если DYNAMIC, FORWARD_ONLY или FAST_FORWARD не указаны и указан один из параметров KEYSET, STATIC или SCROLL, поддерживаются все параметры инструкции FETCH.

• Курсоры DYNAMIC SCROLL поддерживают все параметры инструкции FETCH, за исключением параметра ABSOLUTE.

Примеры объявления курсоров (SQL 92 Syntax):

1. DECLARE MyCursor1 CURSOR FOR (select * from Authors) /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи до последней. Курсор является динамическим. Нет параметров INSENSITIVE и SCROLL */

2. DECLARE MyCursor1 INSENSITIVE CURSOR FOR (select * from Authors) /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи до последней. Курсор является статическим. Параметр INSENSITIVE */

3. DECLARE MyCursor1 SCROLL CURSOR FOR (select * from Authors)

4. /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно в любом направлении. Курсор является динамическим. Параметр SCROLL */

5. DECLARE MyCursor1 INSENSITIVE SCROLL CURSOR FOR (select * from Authors) /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно в любом направлении. Курсор является статическим.*/

6. DECLARE MyCursor1 CURSOR FOR (select * from Authors) FOR READ ONLY /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи до последней (нет параметра SCROL). Курсор является динамическим. Данные доступны только для чтения.*/

7. DECLARE MyCursor1 CURSOR FOR (select * from Authors) FOR UPDATE /*объявили курсор с названием MyCursor1, который содержит всю информацию об авторах, двигаться по нему можно только от первой записи до последней. Курсор является динамическим. Данные курсора можно менять.*/

Операторы для работы с курсором

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

Синтаксис оператора OPEN в обозначениях MS SQL Server:

OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

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

Пример:

DECLARE MyCursor1 CURSOR FOR (select * from Authors)

OPEN MyCursor1

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

Синтаксис оператора CLOSE в обозначениях MS SQL Server:

CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

Пример:

DECLARE MyCursor1 CURSOR FOR (select * from Authors)

OPEN MyCursor1

--здесь операторы работы с курсором

CLOSE MyCursor1

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

Синтаксис оператора DEALLOCATE в обозначениях MS SQL Server:

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

Пример:

DECLARE MyCursor1 CURSOR FOR (select * from Authors)

OPEN MyCursor1

--здесь операторы работы с курсором

CLOSE MyCursor1

DEALLOCATE MyCursor1

Работа с курсором:

FETCH – оператор движения по записям курсора и извлечения данных текущей записи в указанные переменные.

Синтаксис оператора FETCH в обозначениях MS SQL Server:

FETCH

          [ [ NEXT | PRIOR | FIRST | LAST

                    | ABSOLUTE { n | @nvar }

                    | RELATIVE { n | @nvar } ]

                               FROM

          ]

{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }

[ INTO @variable_name [ ,...n ] ]

NEXT - Возвращает строку результата сразу же за текущей строкой и перемещает указатель текущей строки на возвращенную строку. Если инструкция FETCH NEXT выполняет первую выборку в отношении курсора, она возвращает первую строку в результирующем наборе. NEXT является параметром по умолчанию выборки из курсора.

PRIOR - Возвращает строку результата, находящуюся непосредственно перед текущей строкой и перемещает указатель текущей строки на возвращенную строку. Если инструкция FETCH PRIOR выполняет первую выборку из курсора, не возвращается никакая строка и положение курсора остается перед первой строкой.

FIRST - Возвращает первую строку в курсоре и делает ее текущей.

LAST - Возвращает последнюю строку в курсоре, и делает ее текущей.

ABSOLUTE { n | @nvar} - Если n или @nvar имеют положительное значение, возвращает строку, стоящую дальше на n строк от передней границы курсора, и делает возвращенную строку новой текущей строкой. Если n или @nvar имеют отрицательное значение, возвращает строку, отстоящую на n строк от задней границы курсора, и делает возвращенную строку новой текущей строкой. Если n или @nvar равны 0, не возвращается никакая строка. n должно быть целым числом, а @nvar должна иметь тип данных smallint, tinyint или int.

RELATIVE { n | @nvar} - Если n или @nvar имеют положительное значение, возвращает строку, отстоящую на n строк вперед от текущей строки, и делает возвращенную строку новой текущей строкой. Если n или @nvar имеют отрицательное значение, возвращает строку, отстоящую на n строк назад от текущей строки, и делает возвращенную строку новой текущей строкой. Если n или @nvar равны 0, возвращает текущую строку. Если при первой выборке из курсора инструкция FETCH RELATIVE указывается с отрицательными или равными нулю параметрами n или @nvar, то никакая строка не возвращается. n должно быть целым числом, а @nvar должна иметь тип данных smallint, tinyint или int.

GLOBAL - Указывает, что параметр cursor_name ссылается на глобальный курсор.

cursor_name - Имя открытого курсора, из которого должна быть произведена выборка. Если под именем cursor_name существуют как глобальный, так и локальный курсоры, cursor_name устанавливается в качестве глобального курсора, если указывается GLOBAL, и в качестве локального курсора, если GLOBAL не задано.

@cursor_variable_name - Имя переменной курсора, ссылающейся на открытый курсор, из которого должна быть произведена выборка.

INTO @variable_name[ ,...n] - Позволяет поместить данные из столбцов выборки в локальные переменные. Каждая переменная из списка, слева направо, связывается с соответствующим столбцом в результирующем наборе курсора.

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

Разрешения на инструкцию FETCH по умолчанию предоставляются всем допустимым пользователям.

Для контроля достижения конца курсора рекомендуется применять функцию @@FETCH_STATUS. Функция @@FETCH_STATUS возвращает состояние последней инструкции FETCH.

Функция @@FETCH_STATUS возвращает:

0, если выборка завершилась успешно;

-1, если выборка завершилась неудачно вследствие попытки выборки строки, находящейся за пределами курсора;

-2, если выборка завершилась неудачно вследствие попытки обращения к удаленной или измененной строке.

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

DECLARE MyCursor1 SCROLL CURSOR FOR (select * from Authors)

DECLARE @i bigint, @s char(20), @d datetime

OPEN MyCursor1

FETCH FIRST FROM MyCursor1

INTO @i, @s, @d

PRINT @i

PRINT @s

PRINT @d

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM MyCursor1

INTO @i, @s, @d

PRINT @i

PRINT @s

PRINT @d

END

CLOSE MyCursor1

DEALLOCATE MyCursor1

Результат:

1

Толстой Л.Н.

Jun 5 1825 12:00AM

2

Куприн А.И.

Aug 26 1870 12:00AM

3

Пушкин А.С.

Jun 7 1799 12:00AM

4

Булгаков М.А.

May 3 1899 12:00AM

5

Олеша Ю.К.

Feb 19 1899 12:00AM

6

Катаев В.П.

Jan 16 1897 12:00AM

7

Иванов И.И.

Jan 16 1897 12:00AM

7

Иванов И.И.

Jan 16 1897 12:00AM –сохраняются последние значения переменных.

Измененный пример:

DECLARE MyCursor1 SCROLL CURSOR FOR (select * from Authors)

OPEN MyCursor1

FETCH FIRST FROM MyCursor1

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM MyCursor1 --отсутствует INTO!!!!!!!

END

CLOSE MyCursor1

DEALLOCATE MyCursor1

Результат:

Пример: объявляется простой курсор для строк таблицы Person.Contact, в которых фамилия начинается на букву B, и используется инструкция FETCH NEXT для перемещения по строкам. Инструкции FETCH возвращают значение для столбца, указываемого в DECLARE CURSOR в качестве результирующего набора, состоящего из одной строки. Ограничивается количество читаемых строк (10).

USE AdventureWorks

GO

DECLARE @n As Int

DECLARE contact_cursor CURSOR FOR

SELECT LastName FROM Person.Contact

WHERE LastName LIKE 'B%'

ORDER BY LastName

set @n=0

OPEN contact_cursor

FETCH NEXT FROM contact_cursor

WHILE @@FETCH_STATUS = 0 and @n<=10

BEGIN

FETCH NEXT FROM contact_cursor --возращает LastName

set @n=@n+1

END

CLOSE contact_cursor

DEALLOCATE contact_cursor

GO

Результат:

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

USE AdventureWorks

GO

DECLARE @LastName varchar(50), @FirstName varchar(50)

DECLARE contact_cursor CURSOR FOR --динамический, прокрутка только вперед

SELECT LastName, FirstName FROM Person.Contact

WHERE LastName LIKE 'B%'

ORDER BY LastName, FirstName

OPEN contact_cursor

FETCH NEXT FROM contact_cursor

INTO @LastName, @FirstName

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Contact Name: ' + @FirstName + ' ' + @LastName

FETCH NEXT FROM contact_cursor

INTO @LastName, @FirstName

END

CLOSE contact_cursor

DEALLOCATE contact_cursor

GO

Результат:

Пример: В этом примере создается курсор SCROLL, предоставляющий все возможности прокрутки при помощи параметров LAST, PRIOR, RELATIVE и ABSOLUTE.

USE AdventureWorks

GO

--Результирующий набор

SELECT Top (10) LastName, FirstName FROM Person.Contact

ORDER BY LastName, FirstName

DECLARE contact_cursor SCROLL CURSOR FOR –статический курсор

SELECT Top (10) LastName, FirstName FROM Person.Contact

ORDER BY LastName, FirstName

OPEN contact_cursor

-- Перейти к последней строке курсора

FETCH LAST FROM contact_cursor

-- Перейти к предыдущей от текущей (посмотрите результат для последней строки)

FETCH PRIOR FROM contact_cursor

-- Перейти ко второй строке курсора

FETCH ABSOLUTE 2 FROM contact_cursor

-- Перейти на три строки вперед от текущей

FETCH RELATIVE 3 FROM contact_cursor

-- Перейти на две строки назад от текущей

FETCH RELATIVE -2 FROM contact_cursor

CLOSE contact_cursor

DEALLOCATE contact_cursor

GO

Результат:

@@CURSOR_ROWS - Возвращает число выбранных строк, имеющихся в последнем открытом курсоре в данном подключении.

Типы возвращаемых данных integer.

Возвращаемое значение:

Возвращаемое значение	Описание
-1	Курсор является динамическим. Так как динамический курсор отражает все изменения, количество строк для курсора постоянно изменяется. Никогда не может быть точно определено, что все отмеченные строки были получены.
0	Ни один курсор не был открыт, не было строк для последнего открытого курсора или последний открытый курсор закрыт или освобожден.
n	Курсор полностью заполнен. Возвращенное значение (n) является общим количеством строк в курсоре.

Пример:

USE AdventureWorks

GO

DECLARE @I int, @n int

set @n=0

DECLARE contact_cursor2 CURSOR FOR –статический курсор

SELECT LastName FROM Person.Contact

WHERE LastName LIKE 'B%'

ORDER BY LastName

OPEN contact_cursor2

SET @I = @@CURSOR_ROWS

SELECT @I As 'Количество строк курсора'

FETCH NEXT FROM contact_cursor2

WHILE @@FETCH_STATUS = 0 And @n<5

BEGIN

set @n= @n + 1

SELECT @n

FETCH NEXT FROM contact_cursor2

END

CLOSE contact_cursor2

DEALLOCATE contact_cursor2

GO

Результат:

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

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

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

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

Существует два метода для выполнения позиционированных обновлений в курсоре:

• С помощью предложения Transact-SQL WHERE CURRENT OF для инструкции UPDATE или DELETE.

• С помощью функции или метода позиционированного обновления из API-интерфейса базы данных.

Выполнение позиционированных обновлений с использованием Transact-SQL.

Предложение Transact-SQL WHERE CURRENT OF обычно используется в хранимых процедурах, триггерах и сценариях Transact-SQL, когда изменение данных необходимо осуществлять на основе определенных строк в курсоре. Хранимой процедурой, триггером или сценарием выполняются следующие действия:

• Объявление и открытие курсора с помощью инструкций DECLARE и OPEN.

• Использование инструкции FETCH для позиционирования в строке курсора.

• Выполнение инструкции UPDATE или DELETE с использованием предложения WHERE CURRENT OF. При этом значение cursor_name из инструкции DECLARE используется в качестве значения cursor_name в предложении WHERE CURRENT OF.

Изменение и удаление данных. Для выполнения изменений с помощью курсора необходимо выполнить команду UPDATE в следующем формате:

UPDATE имя_таблицы –редактирование только в одной таблице

SET {имя_столбца={

DEFAULT | NULL | выражение}}[,...n]

WHERE CURRENT OF {{[GLOBAL] имя_курсора}

|@имя_переменной_курсора}

За одну операцию могут быть изменены несколько столбцов текущей строки курсора, но все они должны принадлежать одной таблице.

Пример: изменяется через курсор значение поля CreditRating в первой записи таблицы Purchasing.Vendor .

DECLARE vend_cursor1 CURSOR—динамический курсор

FOR SELECT * FROM Purchasing.Vendor

OPEN vend_cursor1

FETCH NEXT FROM vend_cursor1

UPDATE Purchasing.Vendor

SET CreditRating=2

WHERE CURRENT OF vend_cursor1

Пример:

CREATE TABLE dbo.Table1

(c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);

GO

CREATE TABLE dbo.Table2

(d1 int PRIMARY KEY NOT NULL, d2 int NOT NULL);

GO

INSERT INTO dbo.Table1 VALUES (1, 10);

INSERT INTO dbo.Table2 VALUES (1, 20);

INSERT INTO dbo.Table2 VALUES (2, 30);

GO

DECLARE abc CURSOR LOCAL FOR

SELECT c1, c2

FROM dbo.Table1;-- курсор объявлен для таблицы Table1

OPEN abc;

FETCH abc;-- находимся на первой строке

--изменяется первая строка Table1

UPDATE dbo.Table1 --изменяем первую строку

SET c2 = c2 + d2

FROM dbo.Table2 -- выборка d2 из таблицы Table2

WHERE CURRENT OF abc;

GO

SELECT c1, c2 FROM dbo.Table1;

GO

Реультат:

Для удаления данных посредством курсора используется команда DELETE в следующем формате:

DELETE [FROM] имя_таблицы

WHERE CURRENT OF {{[GLOBAL] имя_курсора}

|@имя_переменной_курсора}

В результате будет удалена строка, установленная текущей в курсоре.

Пример: Следующий пример удаляет одну строку из таблицы EmployeePayHistory с помощью курсора complex_cursor. Операция удаления затрагивает только одну строку, выбранную в данный момент курсором.

USE AdventureWorks;

GO

--вывод выбираемых строк

SELECT Top (10) a.EmployeeID

FROM HumanResources.EmployeePayHistory AS a

WHERE RateChangeDate <>

(SELECT MAX(RateChangeDate)

FROM HumanResources.EmployeePayHistory AS b

WHERE a.EmployeeID = b.EmployeeID) ;

--Объявление курсора по выведенным данным

DECLARE complex_cursor CURSOR FOR

SELECT Top (10)a.EmployeeID

FROM HumanResources.EmployeePayHistory AS a

WHERE RateChangeDate <>

(SELECT MAX(RateChangeDate)

FROM HumanResources.EmployeePayHistory AS b

WHERE a.EmployeeID = b.EmployeeID) ;

--открытие курсора

OPEN complex_cursor;

--выборка первой строки курсора

FETCH FROM complex_cursor;

--удаление текущей (первой строки) курсора и записи из таблицы

DELETE FROM HumanResources.EmployeePayHistory

WHERE CURRENT OF complex_cursor;

CLOSE complex_cursor;

DEALLOCATE complex_cursor;

GO

--вывод результата

SELECT Top (10) a.EmployeeID

FROM HumanResources.EmployeePayHistory AS a

WHERE RateChangeDate <>

(SELECT MAX(RateChangeDate)

FROM HumanResources.EmployeePayHistory AS b

WHERE a.EmployeeID = b.EmployeeID) ;

Результат:

В некоторых случаях применение курсора неизбежно. Однако по возможности этого следует избегать и работать со стандартными командами обработки данных: SELECT, UPDATE, INSERT, DELETE. Помимо того, что курсоры не позволяют проводить операции изменения над всем объемом данных, скорость выполнения операций обработки данных посредством курсора заметно ниже, чем у стандартных средств SQL.

Временные таблицы

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

SQL Server поддерживает три вида временных таблиц: локальные временные таблицы, глобальные временные таблицы и табличные переменные.

Локальные временные таблицы

Локальная временная таблица создается с помощью задания в ее имени знака решетки в качестве префикса, например #Т1. Все виды временных таблиц создаются в базе данных tempdb.

Временная локальная таблица видна только в сеансе, ее создавшем, на уровне ее создания и на всех внутренних уровнях стека вызова (внутренние процедуры, функции, триггеры и ди­намические пакеты). SQL Server автоматически уничтожает временную таблицу, когда уровень ее создания в стеке вызовов выходит за пределы области видимости.

Например, хранимая процедура Proс1 вызывает процедуру Ргос2, которая в свою очередь вызывает процедуру РгосЗ, в свою очередь вызывающую процедуру Ргос4. Ргос2 перед вызовом РгосЗ создает временную таблицу #Т1. Таблица #Т1 видима в процедурах Ргос2, РгосЗ и Ргос4, но не в процедуре Procl и автоматически уничтожается SQL Server, когда завершается процедура Ргос2.

Если временная таблица создается в рассчитанном на конкретную ситуацию пакете на самом внешнем уровне вложенности в сеансе (значение функции @@nestlevel равно 0), она видна также во всех подчиненных пакетах и автоматически уничтожается SQL Server, только когда сеанс создания таблицы отключается.

Как SQL Server предотвращает конфликты имен, если два сеанса создают локальные временные таблицы с одним и тем же именем? SQL Server на внутреннем уровне добавляет к имени таблицы суффикс, делающий имя уникальным в базе данных tempdb. Разработчику не нужно заботиться об этом — он ссылается на таблицу с помощью заданного им имени, без внутреннего суффикса, и только его сеанс получает доступ к этой таблице.

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

Другой сценарий - необходимость многократного доступа к результатам какой-либо затратной обработки.

Пример:

Нужно соединить таблицы Sales.Orders (Заказы) и Sales.OrderDetails (Сведения о заказе), подытожить объемы заказов в пределах года заказа и соединить два экземпляра итогов данных для сравнения каждого годового объема заказов с объемом предыдущего года.

Один вариант - применить табличные выражения, но табличные выражения виртуальны. Ресурсоемкая работа по просмотру всех данных, соединению таблиц Orders и OrderDetails и получению итоговых данных выполняется дважды.

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

IF OBJECT_ID('tempdb.dbo.#MyOrderTotalsByYear') IS NOT NULL

DROP TABLE dbo.#MyOrderTotalsByYear;

GO

SELECT

YEAR(O.orderdate) AS orderyear,

SUM(OD.qty) AS qty

INTO dbo.#MyOrderTotalsByYear-- временная таблица, не нужен оператор Declare.

FROM Sales.Orders AS O

JOIN Sales.OrderDetails AS OD

ON OD.orderid = O.orderid

GROUP BY YEAR(orderdate);--заполняем временную таблицу

SELECT orderyear, qty FROM dbo.#MyOrderTotalsByYear –вывод временнной таблицы

SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty

FROM dbo.#MyOrderTotalsByYear AS Cur

LEFT OUTER JOIN dbo.#MyOrderTotalsByYear AS Prv

ON Cur.orderyear = Prv.orderyear + 1;-- условие соединения, соединение для последовательных годов для одной временной таблицы

GO

Т.к. таблица dbo.#MyOrderTotalsByYear временная, то при обращении к ней из другого сеанса

SELECT orderyear, qty FROM dbo.#MyOrderTotalsByYear;

выдается ошибка: Msg 208, Level 16, State 0, Line 1 Invalid object name '#MyOrderTotalsByYear'.

Глобальные временные таблицы

Если создается глобальная временная таблица, она видна во всех других сеансах. Такие таблицы SQL Server уничтожает автоматически, когда отключается сеанс, создавший табли­цу, и на нее нет активных ссылок. Глобальная временная таблица создается добавлением к ее имени двух знаков решетки в качестве префикса, например ##Т1.

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

Табличные переменные

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

Табличные переменные физически присутствуют в виде таблиц в базе данных tempdb.

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

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

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

Рассмотренный выше пример с использованием табличной переменной:

DECLARE @MyOrderTotalsByYear As TABLE

(

orderyear INT NOT NULL PRIMARY KEY,

qty INT NOT NULL

);

INSERT INTO @MyOrderTotalsByYear(orderyear, qty)

SELECT

YEAR(O.orderdate) AS orderyear,

SUM(OD.qty) AS qty

FROM Sales.Orders AS O

JOIN Sales.OrderDetails AS OD

ON OD.orderid = O.orderid

GROUP BY YEAR(orderdate);

SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty

FROM @MyOrderTotalsByYear AS Cur

LEFT OUTER JOIN @MyOrderTotalsByYear AS Prv

ON Cur.orderyear = Prv.orderyear + 1;

GO

Результат:

В SQL Server 2008 добавлены табличные типы данных (TABLE).