DECLARE CURSOR (Transact-SQL)
Применимо к:база данных
SQL Server Azure SQL Управляемый экземпляр SQL Azure
Определяет такие атрибуты серверного курсора языка Transact-SQL, как свойства просмотра и запрос для построения результирующего набора, по которому работает курсор. DECLARE CURSOR
поддерживает как синтаксис стандарта ISO, так и синтаксис с набором расширений Transact-SQL.
Соглашения о синтаксисе Transact-SQL
Синтаксис
ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
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 ] ] ]
[;]
Примечание
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
cursor_name
Имя определенного серверного курсора Transact-SQL. Аргумент cursor_name должен соответствовать требованиям, предъявляемым к идентификаторам.
INSENSITIVE
Определяет курсор, который создает временную копию данных для использования курсором. Все запросы к курсору обращаются к указанной временной таблице в базе данных tempdb, поэтому изменения базовых таблиц не влияют на данные, возвращаемые выборками для данного курсора, а сам курсор не позволяет производить изменения. Если при использовании синтаксиса ISO не указан параметр INSENSITIVE
, зафиксированные обновления и удаления, сделанные в базовых таблицах, отображаются в последующих выборках.
SCROLL
Указывает, что доступны все параметры выборки (FIRST
, LAST
, PRIOR
, NEXT
, RELATIVE
, ABSOLUTE
). Если в инструкции DECLARE CURSOR
стандарта ISO не указан параметр SCROLL
, то поддерживается только параметр выборки NEXT
. Если указан аргумент FAST_FORWARD
, задать SCROLL
невозможно. Если параметр SCROLL
не указан, для выборки доступен только механизм NEXT
, и курсор становится FORWARD_ONLY
.
select_statement
Стандартная инструкция SELECT
, которая определяет результирующий набор курсора. Ключевые слова FOR BROWSE
и INTO
недопустимы в аргументе select_statement, входящем в объявление курсора.
SQL Server неявным образом преобразует курсор в другой тип, если предложения в аргументе select_statement вызывают конфликт с функциями курсора запрошенного типа.
READ ONLY
Предотвращает изменения, сделанные через этот курсор. На курсов нельзя ссылаться в приложении WHERE CURRENT OF
в инструкции UPDATE
или DELETE
. Этот параметр имеет преимущество над установленной по умолчанию возможностью обновления курсора.
UPDATE [OF column_name [,...n]]
Определяет обновляемые столбцы в курсоре. Если указан аргумент OF <column_name> [, <... n>], вносить изменения можно только в перечисленные столбцы. Если инструкция UPDATE
используется без списка столбцов, то обновление возможно для всех столбцов.
cursor_name
Имя определенного серверного курсора Transact-SQL. Аргумент cursor_name должен соответствовать требованиям, предъявляемым к идентификаторам.
LOCAL
Указывает, что курсор является локальным по отношению к пакету, хранимой процедуре или триггеру, в котором он был создан. Имя курсора допустимо только внутри этой области. На курсор могут ссылаться локальные переменные пакета, хранимые процедуры, триггеры или выходной параметр OUTPUT
хранимой процедуры. Параметр OUTPUT
используется для передачи локального курсора вызывающему его пакету, хранимой процедуре или триггеру, который затем может присвоить параметр переменной курсора с целью последующего обращения к курсору после завершения хранимой процедуры. Курсор неявно освобождается после завершения выполнения пакета, хранимой процедуры или триггера, за исключением случая, когда курсор был передан параметру OUTPUT
. Если курсор был передан параметру OUTPUT
, то курсор освобождается при освобождении всех ссылающихся на него переменных или при выходе из области видимости.
GLOBAL
Указывает, что курсор является глобальным по отношению к соединению. Имя курсора может использоваться любой хранимой процедурой или пакетом, которые выполняются в соединении. Курсор неявно освобождается только в случае разрыва соединения.
Примечание
Если не указан ни один из параметров GLOBAL
или LOCAL
, то значение по умолчанию управляется параметром default to local cursor базы данных.
FORWARD_ONLY
Указывает, что курсор может перемещаться только вперед и просматриваться от первой строки к последней. Поддерживается только параметр выборки FETCH NEXT
. Результаты всех инструкций вставки, обновления и удаления, выполненных текущим пользователем (или зафиксированных другими пользователями) и влияющих на строки результирующего набора, отображаются по мере получения строк. Так как курсор нельзя прокручивать назад, изменения, внесенные в строки базы данных после их извлечения, не видны через курсор. Курсоры последовательного доступа по умолчанию являются динамическими. Это значит, что все изменения обнаруживаются в процессе обработки текущей строки. Благодаря этому курсор открывается быстрее, а в результирующем наборе отображаются изменения, внесенные в базовые таблицы. Хотя курсоры последовательного доступа не поддерживают обратную прокрутку, приложения могут возвращаться в начало результирующего набора, закрывая и повторно открывая курсор.
Если параметр FORWARD_ONLY
указан без ключевых слов STATIC
, KEYSET
или DYNAMIC
, курсор работает как динамический. Если не указан ни один из параметров FORWARD_ONLY
или SCROLL
, по умолчанию используется FORWARD_ONLY
, пока не будут заданы ключевые слова STATIC
, KEYSET
или DYNAMIC
. Курсоры STATIC
, KEYSET
и DYNAMIC
по умолчанию получают значение SCROLL
. В отличие от ODBC, ADO и других API-интерфейсов базы данных, для курсоров Transact-SQL STATIC
, KEYSET
и DYNAMIC
поддерживается FORWARD_ONLY
.
STATIC
Указывает, что курсор всегда отображает результирующий набор в том виде, который он имел на момент первого открытия курсора, и создает временную копию данных, предназначенную для использования курсором. Все запросы к курсору обращаются к этой временной таблице в базе данных tempdb. Поэтому результаты инструкций вставки, обновления и удаления данных в базовых таблицах не влияют на данные, возвращаемые запросами на извлечение к курсору, и курсор не обнаруживает изменения, внесенные в членство, порядок или значения результирующего набора после открытия курсора. Статические курсоры могут обнаруживать результаты собственных инструкций обновления, удаления и вставки, хотя это и не обязательно.
Например, предположим, что статический курсор извлекает строку, а другое приложение затем обновляет ее. Если приложение извлекает строку из статического курсора, оно получает значения без изменений, внесенных другим приложением. Поддерживаются все типы прокрутки.
KEYSET
Указывает, что членство или порядок строк в курсоре неизменны при его открытии. Набор ключей, однозначно определяющих строки, встроен в таблицу в базе данных tempdb с именем keyset. Возможности этого курсора по обнаружению изменений с одной стороны похожи на возможности статического курсора, а с другой — динамического. Так же как статический курсор, он не всегда обнаруживает изменения, внесенные в членство и порядок элементов результирующего набора. Так же как динамический курсор, он обнаруживает изменения, внесенные в значения строк результирующего набора.
Такие курсоры управляются с помощью набора уникальных идентификаторов — ключей. Ключи создаются из набора столбцов, который уникально идентифицирует строки результирующего набора. Набор ключей — это набор ключевых значений всех строк, возвращаемых инструкцией запроса. При использовании управляемых наборами ключей курсоров ключ создается для каждой строки курсора и сохраняется на клиентском компьютере или на сервере. При обращении к строке сохраненный ключ используется для получения текущих значений данных из источника данных. В курсоре, управляемом набором ключей, членство в результирующем наборе становится фиксированным, когда набор ключей полностью заполняется. Поэтому результаты операций добавления и обновления, влияющих на членство, не включаются в результирующий набор до повторного открытия.
Изменения в значениях данных (внесенные владельцем набора ключей или другими процессами) видны при прокрутке результирующего набора пользователем.
- Если строка удаляется, при попытке выбрать ее возвращается
@@FETCH_STATUS
со значением –2, так как удаленная строка отображается как пробел в результирующем наборе. В наборе ключей ключ для этой строки есть, но самой строки в результирующем наборе больше нет. - Чтобы результаты вставки извне курсора (другими процессами) были видны, нужно закрыть курсор и открыть его заново. Результаты вставки внутри курсора видны в конце результирующего набора.
- Обновления значений ключа из-за границ курсора аналогично удалению старой строки с последующей вставкой новой строки. Строка с новыми значениями невидима, и при попытке извлечь строку со старыми значениями функция
@@FETCH_STATUS
возвращает значение –2. Обновления видимы сразу, если они сделаны через курсор с помощью предложенияWHERE CURRENT OF
.
Примечание
Если запрос ссылается хотя бы на одну таблицу, не имеющую уникального индекса, курсор keyset преобразуется в статический курсор.
DYNAMIC
Определяет курсор, который отображает все изменения данных, сделанные в строках результирующего набора, при просмотре этого курсора и извлечении новой записи независимо от того, были ли изменения произведены внутри курсора или извне другими пользователями. Поэтому посредством такого курсора видны результаты всех инструкций вставки, обновления и удаления, выполненных всеми пользователями. Значения данных, порядок и членство строк в каждой выборке могут меняться. Параметр выборки ABSOLUTE
динамическими курсорами не поддерживается. Обновления, сделанные вне курсора, не видны до момента фиксации (если только уровень изоляции транзакций с курсорами не имеет значение UNCOMMITTED
).
Например, предположим, что динамический курсор извлекает две строки, а другое приложение затем обновляет одну из них и удаляет другую. Если после этого динамический курсор снова извлекает эти две строки, он не найдет удаленную строку, а для обновленной строки отобразит новые значения.
FAST_FORWARD
Указывает курсор FORWARD_ONLY
, READ_ONLY
, для которого включена оптимизация производительности. Если указан SCROLL
или FOR_UPDATE
, задать FAST_FORWARD
невозможно. Курсоры такого типа не допускают изменений данных внутри курсора.
Примечание
FAST_FORWARD
и FORWARD_ONLY
можно использовать в одной инструкции DECLARE CURSOR
.
READ_ONLY
Предотвращает изменения, сделанные через этот курсор. На курсов нельзя ссылаться в приложении WHERE CURRENT OF
в инструкции UPDATE
или DELETE
. Этот параметр имеет преимущество над установленной по умолчанию возможностью обновления курсора.
SCROLL_LOCKS
Указывает, что позиционированные обновления или удаления, осуществляемые с помощью курсора, гарантированно будут выполнены успешно. SQL Server блокирует строки по мере их считывания в курсор для обеспечения доступности этих строк для последующих изменений. Если указан FAST_FORWARD
или STATIC
, задать SCROLL_LOCKS
невозможно.
OPTIMISTIC
Указывает, что позиционированные обновления или удаления, осуществляемые с помощью курсора, не будут выполнены, если с момента считывания в курсор строка была обновлена. SQL Server не блокирует строки по мере их считывания в курсор. Вместо этого используются сравнения значений столбца timestamp или значений контрольных сумм, если в таблице нет столбца timestamp, для определения факта изменения строки после ее считывания в курсор. Если строка была изменена, то попытки позиционированного обновления или удаления будут безрезультатными. Если указан аргумент FAST_FORWARD
, задать OPTIMISTIC
невозможно.
TYPE_WARNING
Указывает, что клиенту будет отправлено предупреждение, если курсор неявно будет преобразован из одного запрашиваемого типа в другой.
select_statement
Стандартная инструкция SELECT, которая определяет результирующий набор курсора. Ключевые слова COMPUTE
, COMPUTE BY
, FOR BROWSE
и INTO
недопустимы в аргументе select_statement, входящем в объявление курсора.
Примечание
В объявлении курсора можно использовать указание запроса, но если используется предложение FOR UPDATE OF
, то после FOR UPDATE OF
следует указать параметр OPTION (<query_hint>)
.
SQL Server неявным образом преобразует курсор в другой тип, если предложения в аргументе select_statement вызывают конфликт с функциями курсора запрошенного типа. Дополнительные сведения см. в разделе "Неявные преобразования курсора".
FOR UPDATE [OF column_name [,...n]]
Определяет обновляемые столбцы в курсоре. Если OF <column_name> [, <... n>]
определено, только перечисленные столбцы позволяют вносить изменения. Если инструкция UPDATE
используется без списка столбцов, то обновление возможно для всех столбцов, за исключением случая, когда был указан параметр параллелизма READ_ONLY
.
Remarks
DECLARE CURSOR
определяет такие атрибуты серверного курсора языка Transact-SQL, как свойства просмотра и запрос для построения результирующего набора, по которому работает курсор. Инструкция OPEN
заполняет результирующий набор, а оператор FETCH
возвращает из него строку. Инструкция CLOSE
очищает текущий результирующий набор, связанный с курсором. Инструкция DEALLOCATE
освобождает ресурсы, используемые курсором.
Первая форма инструкции DECLARE CURSOR
использует синтаксис ISO для задания параметров работы курсора. Вторая форма инструкции DECLARE CURSOR
использует расширения языка Transact-SQL, позволяющие определять курсоры с помощью таких же типов, как и в курсорных функциях API баз данных ODBC или ADO.
Нельзя смешивать две эти формы. Если вы определяете ключевые слова SCROLL
или INSENSITIVE
до ключевого слова CURSOR
, вы не сможете использовать никакие ключевые слова между ключевыми словами CURSOR
и FOR <select_statement>
. Если вы указываете ключевые слова между ключевыми словами CURSOR
и FOR <select_statement>
, вы не сможете задать SCROLL
или INSENSITIVE
перед ключевым словом CURSOR
.
Если в DECLARE CURSOR
используется синтаксис Transact-SQL и не указано READ_ONLY
, OPTIMISTIC
или SCROLL_LOCKS
, по умолчанию используется следующий вариант:
Если инструкция
SELECT
не поддерживает обновления (или недостаточно разрешений, или при доступе к удаленным таблицам, не поддерживающим обновление, и т. п.), то курсору присваивается параметрREAD_ONLY
.Курсоры
STATIC
иFAST_FORWARD
по умолчанию получают значениеREAD_ONLY
.Курсоры
DYNAMIC
иKEYSET
по умолчанию получают значениеOPTIMISTIC
.
Ссылки на имена курсоров могут использоваться только в других инструкциях Transact-SQL. Функции API баз данных не могут ссылаться на курсоры. Например, после объявления курсора функции и методы OLE DB, ODBC или ADO не могут ссылаться на его имя. Строки курсора нельзя получить с помощью соответствующих функций и методов API; для этой цели необходимо использовать инструкции Transact-SQL FETCH.
Приведенные ниже хранимые процедуры могут быть использованы для определения свойств курсора после его объявления.
Системные хранимые процедуры | Описание |
---|---|
sp_cursor_list | Возвращает список курсоров, доступных для соединения в настоящий момент времени, а также их атрибуты. |
sp_describe_cursor | Описывает атрибуты курсора, например имеет ли он тип "forward-only" или "scrolling". |
sp_describe_cursor_columns | Описывает атрибуты столбцов результирующего набора. |
sp_describe_cursor_tables | Описывает базовые таблицы, к которым курсор получает доступ. |
Переменные могут использоваться в качестве составных частей выражения select_statement, объявляющего курсор. Значения переменных курсора после его объявления не изменяются.
Разрешения
По умолчанию разрешения DECLARE CURSOR
предоставляются всем пользователям, имеющим разрешения SELECT
для используемых курсором представлений, таблиц и столбцов.
Ограничения
Нельзя использовать курсоры или триггеры в таблице с кластеризованным индексом columnstore. Это ограничение не применяется к некластеризованным индексам columnstore. Курсоры и триггеры можно использовать в таблице с некластеризованным индексом columnstore.
Примеры
A. Использование простого курсора и синтаксиса
Результирующий набор, создаваемый при открытии данного курсора, включает в себя все строки и столбцы таблицы. Этот курсор можно обновлять, все обновления и удаления представлены в выборке для этого курсора. FETCH NEXT
является единственно доступной выборкой, так как параметр SCROLL
не был определен.
DECLARE vend_cursor CURSOR
FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;
Б. Использование вложенных курсоров для вывода отчета
В следующем примере вложенные курсоры используются для вывода сложного отчета. Для каждого поставщика объявляется внутренний курсор.
SET NOCOUNT ON;
DECLARE @vendor_id INT, @vendor_name NVARCHAR(50),
@message VARCHAR(80), @product NVARCHAR(50);
PRINT '-------- Vendor Products Report --------';
DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From Vendor: ' +
@vendor_name
PRINT @message
-- Declare an inner cursor based
-- on vendor_id from the outer cursor.
DECLARE product_cursor CURSOR FOR
SELECT v.Name
FROM Purchasing.ProductVendor pv, Production.Product v
WHERE pv.ProductID = v.ProductID AND
pv.VendorID = @vendor_id -- Variable value from the outer cursor
OPEN product_cursor
FETCH NEXT FROM product_cursor INTO @product
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = ' ' + @product
PRINT @message
FETCH NEXT FROM product_cursor INTO @product
END
CLOSE product_cursor
DEALLOCATE product_cursor
-- Get the next vendor.
FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;
См. также:
@@FETCH_STATUS (Transact-SQL)
CLOSE (Transact-SQL)
Курсоры (Transact-SQL)
DEALLOCATE (Transact-SQL)
FETCH (Transact-SQL)
SELECT (Transact-SQL)
sp_configure (Transact-SQL)
Обратная связь
Отправить и просмотреть отзыв по