DECLARE CURSOR (Transact-SQL)

Изменения: 5 декабря 2005 г.

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

Соглашения о синтаксисе в Transact-SQL

Синтаксис

SQL 92 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 ] ] ]
[;]

Аргументы

• cursor_name
  Имя определенного серверного курсора Transact-SQL. Аргумент cursor_name должен соответствовать правилам для имен идентификаторов. Дополнительные сведения о правилах для имен идентификаторам см. разделе Использование идентификаторов в качестве имен объектов.

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

• SCROLL
  Указывает, что доступны все параметры выборки (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE). Если в инструкции DECLARE CURSOR стандарта SQL-92 не указан параметр SCROLL, то поддерживается только параметр выборки NEXT. Параметр SCROLL не может указываться вместе с параметром FAST_FORWARD.

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

  Microsoft 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 базы данных. С целью обеспечения совместимости с более ранними версиями SQL Server, в которых все курсоры являются глобальными, в SQL Server версии 7.0 данный параметр равен FALSE. В будущих версиях SQL Server значение этого параметра по умолчанию может быть изменено. Дополнительные сведения см. в разделе «Настройка параметров базы данных».

• FORWARD_ONLY
  Указывает, что курсор может просматриваться только от первой строки к последней. Поддерживается только параметр выборки FETCH NEXT. Если параметр FORWARD_ONLY указан без ключевых слов STATIC, KEYSET или DYNAMIC, то курсор работает как курсор DYNAMIC. Если не указан ни один из параметров FORWARD_ONLY или SCROLL, а также не указано ни одно из ключевых слов STATIC, KEYSET или DYNAMIC, то по умолчанию задается параметр FORWARD_ONLY. Курсоры STATIC, KEYSET и DYNAMIC имеют значение по умолчанию SCROLL. В отличие от таких интерфейсов API баз данных, как ODBC и ADO, режим FORWARD_ONLY поддерживается следующими курсорами языка Transact-SQL: STATIC, KEYSET и DYNAMIC.

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

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

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

  Изменения неключевых значений в базовых таблицах, сделанные владельцем курсора или зафиксированные другими пользователями, отображаются при просмотре курсора владельцем. Изменения, сделанные другими пользователями, не отображаются (изменения не могут быть сделаны с помощью серверного курсора языка Transact-SQL). При попытке выборки строки, которая была удалена, параметру @@FETCH_STATUS присваивается значение -2. При проведении обновлений ключевых значений извне курсора устаревшие строки удаляются и на их место записываются новые. Строка новых значений имеет статус невидимой, а при попытке выборки строки с устаревшими значениями параметру @@FETCH_STATUS присваивается значение -2. Новые значения видны, если обновления в курсоре выполняются с указанием предложения WHERE CURRENT OF.

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

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

  Примечание.
  В SQL Server 2000 аргументы курсора FAST_FORWARD и FORWARD_ONLY взаимно исключают друг друга. При указании обоих аргументов возвращается ошибка. В SQL Server 2005 оба ключевых слова могут одновременно применяться в одной инструкции DECLARE CURSOR.

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

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

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

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

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

  Примечание.
  В объявлении курсора можно использовать подсказку запроса; однако если использовать предложение FOR UPDATE OF, то после него следует указать параметр OPTION (подсказка_запроса).

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

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

Замечания

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

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

Нельзя смешивать две эти формы. Если перед ключевым словом CURSOR указаны параметры SCROLL или INSENSITIVE, то между операторами CURSOR и FOR select_statement нельзя использовать какие-либо ключевые слова. Если между операторами CURSOR и FOR select_statement указаны любые ключевые слова, нельзя использовать параметры SCROLL или INSENSITIVE перед ключевым словом CURSOR.

Если при использовании синтаксиса языка Transact-SQL для инструкции DECLARE CURSOR не указываются параметры READ_ONLY, OPTIMISTIC или SCROLL_LOCKS, то принимается следующее значение по умолчанию.

• Если инструкция SELECT не поддерживает обновления (или недостаточно разрешений, или при доступе к удаленным таблицам, не поддерживающим обновление, и т.п.), то курсору присваивается параметр READ_ONLY.
• Курсоры STATIC и FAST_FORWARD по умолчанию имеют значение READ_ONLY.
• Курсоры DYNAMIC и KEYSET по умолчанию имеют значение OPTIMISTIC.

Ссылки на курсоры могут производиться только другими инструкциями языка Transact-SQL. API-функции баз данных не могут ссылаться на курсоры. Например, после объявления курсора функции и методы OLE DB, ODBC или ADO не могут ссылаться на его имя. Строки курсора не могут быть выбраны с помощью соответствующих функций и методов API; для этой цели необходимо использовать инструкции FETCH языка Transact-SQL.

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

Системные хранимые процедуры	Описание
sp_cursor_list	Возвращает список курсоров, доступных для соединения в настоящий момент времени, а также их атрибуты.
sp_describe_cursor	Описывает атрибуты курсора, например имеет ли он тип «forward-only» или «scrolling».
sp_describe_cursor_columns	Описывает атрибуты столбцов результирующего набора.
sp_describe_cursor_tables	Описывает базовые таблицы, к которым курсор получает доступ.

Переменные могут использоваться в качестве составных частей выражения select_statement, объявляющего курсор. Значения переменных курсора после его объявления не изменяются. В SQL Server версии 6.5 и ранее значения переменных обновляются при каждом открытии курсора.

Разрешения

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

Примеры

A. Использование простого курсора и синтаксис

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

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)

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия	Журнал
5 декабря 2005 г.	Новое содержимое: Добавлено примечание об использовании подсказки запроса в объявлении курсора.