DECLARE CURSOR (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

Синтаксис

Синтаксис ISO:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ_ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
[ ; ]

Расширенный синтаксис Transact-SQL:

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 Server. Аргумент cursor_name должен соответствовать требованиям, предъявляемым к идентификаторам.

INSENSITIVE

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

SCROLL

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

select_statement

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

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

READ_ONLY

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

UPDATE [ OF COLUMN_NAME [ ,...n ] ]

Определяет обновляемые столбцы в курсоре. Если OF <column_name> [, <... n> ] задано, только столбцы, перечисленные в списке, допускают изменения. Если инструкция UPDATE используется без списка столбцов, то обновление возможно для всех столбцов.

cursor_name

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

ЛОКАЛЬНО

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

Глобальные

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

Примечание.

Если не указан ни один из параметров GLOBAL или LOCAL, то значение по умолчанию управляется параметром default to local cursor базы данных.

FORWARD_ONLY

Указывает, что курсор может перемещаться только вперед и просматриваться от первой строки к последней. Поддерживается только параметр выборки FETCH NEXT. Все инструкции вставки, обновления и удаления, сделанные текущим пользователем (или зафиксированные другими пользователями), влияющие на строки в результирующем наборе, отображаются по мере получения строк. Так как курсор не может быть прокручен назад, однако изменения, внесенные в строки в базе данных после получения строки, не отображаются через курсор. Курсоры последовательного доступа по умолчанию являются динамическими. Это значит, что все изменения обнаруживаются в процессе обработки текущей строки. Благодаря этому курсор открывается быстрее, а в результирующем наборе отображаются изменения, внесенные в базовые таблицы. Хотя курсоры только для перенаправления не поддерживают обратную прокрутку, приложения могут вернуться в начало результирующий набор, закрывая и повторно открыв курсор.

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

STATIC

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

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

KEYSET

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

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

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

• Если строка удаляется, попытка получить строку возвращается @@FETCH_STATUS -2 из-за того, что удаленная строка отображается как пробел в результирующем наборе. В наборе ключей ключ для этой строки есть, но самой строки в результирующем наборе больше нет.

• Чтобы результаты вставки извне курсора (другими процессами) были видны, нужно закрыть курсор и открыть его заново. Результаты вставки внутри курсора видны в конце результирующего набора.

• Обновления значений ключа из-за границ курсора аналогично удалению старой строки с последующей вставкой новой строки. Строка с новыми значениями не отображается и пытается получить строку со старыми значениями @@FETCH_STATUS -2. Обновления видимы сразу, если они сделаны через курсор с помощью предложения WHERE CURRENT OF.

Примечание.

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

DYNAMIC

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

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

FAST_FORWARD

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

Примечание.

FAST_FORWARD и FORWARD_ONLY можно использовать в одной инструкции DECLARE CURSOR.

READ_ONLY

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

SCROLL_LOCKS

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

OPTIMISTIC

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

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

Если STATIC он указан вместе с OPTIMISTIC аргументом курсора, сочетание этих двух неявно преобразуется в эквивалент сочетания использования STATIC и READ_ONLY аргументов или STATIC FORWARD_ONLY аргументов.

TYPE_WARNING

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

Предупреждение не отправляется клиенту при использовании сочетания аргументов курсора OPTIMISTIC , STATIC а курсор неявно преобразуется в эквивалент или STATIC READ_ONLY STATIC FORWARD_ONLY курсор. Преобразование, превратимое READ_ONLY в FAST_FORWARD объект и READ_ONLY курсор с точки зрения клиентов.

select_statement

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

Примечание.

В объявлении курсора можно использовать подсказку запроса. Однако если вы также используете FOR UPDATE OF предложение, укажите OPTION (<query_hint>) после FOR UPDATE OF.

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 использует синтаксис 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 .

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

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

Переменные могут использоваться как часть select_statement , которая объявляет курсор. Значения переменной курсора не изменяются после объявления курсора.

Разрешения

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

Ограничения

Нельзя использовать курсоры или триггеры в таблице с кластеризованным индексом columnstore. Это ограничение не применяется к некластеризованным индексам columnstore. Курсоры и триггеры можно использовать в таблице с некластеризованным индексом columnstore.

Примеры

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

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

DECLARE vend_cursor CURSOR
    FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;

B. Использование вложенных курсоров для создания выходных данных отчета

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

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)