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 ] ] ]
[ ; ]
Примечание.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
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, KEYSETили DYNAMIC указаны. Курсоры 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 также задано значение.
TYPE_WARNING
Указывает, что клиенту будет отправлено предупреждение, если курсор неявно будет преобразован из одного запрашиваемого типа в другой.
select_statement
Стандартная SELECT инструкция, определяющая результирующий набор курсора. Ключевое слово COMPUTE, FOR BROWSECOMPUTE BYи 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 (Transact-SQL) | Возвращает список курсоров, доступных для соединения в настоящий момент времени, а также их атрибуты. |
| sp_describe_cursor (Transact-SQL) | Описывает атрибуты курсора, например, только для переадресации или прокрутки курсора. |
| sp_describe_cursor_columns (Transact-SQL) | Описывает атрибуты столбцов результирующего набора. |
| sp_describe_cursor_tables (Transact-SQL) | Описывает базовые таблицы, к которым курсор получает доступ. |
Переменные могут использоваться как часть 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;
Связанный контент
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по