Курсоры (SQL Server)

Применимо к:SQL ServerAzure SQL База данныхAzure SQL Управляемый экземплярSQL База данных в Microsoft Fabric

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

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

• Обеспечение возможности позиционирования на определённых строках набора результатов;

• Извлечение одной строки или блока строк из текущей позиции в наборе результатов.

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

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

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

Замечания

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

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

Реализации курсоров

SQL Server поддерживает три реализации курсоров.

Реализация курсора	Описание
Курсоры Transact-SQL	Курсоры Transact-SQL основаны на DECLARE CURSOR синтаксисе и используются главным образом в скриптах Transact-SQL, хранимых процедурах и триггерах. Курсоры Transact-SQL реализуются на сервере и управляются инструкциями Transact-SQL, отправляемые клиентом на сервер. Они также могут содержаться в пакетах, хранимых процедурах или триггерах.
Курсоры сервера интерфейса программирования приложений (API)	Курсоры API поддерживают функции курсоров API в OLE DB и ODBC. Курсоры API реализуются на сервере. Каждый раз, когда клиентское приложение вызывает функцию курсора API, поставщик OLE DB sql Server Native Client или драйвер ODBC передает запрос серверу для действия с курсором сервера API.
Клиентские курсоры	Клиентские курсоры реализуются внутренне драйвером ODBC собственного клиента SQL Server и библиотекой DLL, реализующей API ADO. Клиентские курсоры реализованы за счёт кэширования всех строк набора результатов на клиенте. Каждый раз, когда клиентское приложение вызывает функцию курсора API, драйвер ODBC SQL Server Native Client или библиотека DLL ADO выполняет операцию курсора над строками результирующего набора, кэшированными на клиенте.

Тип курсоров

SQL Server поддерживает четыре типа курсоров.

Курсоры могут использовать tempdb рабочие листы. Подобно операциям агрегирования или сортировки с выгрузкой на диск, они связаны с затратами на ввод-вывод и могут стать узким местом с точки зрения производительности. STATIC курсоры используют рабочие таблицы с момента своего появления. Дополнительные сведения см. в разделе «Рабочие таблицы» в руководстве по архитектуре обработки запросов.

Только вперёд

Однонаправленный курсор обозначается как FORWARD_ONLY и READ_ONLY и не поддерживает прокрутку. Их также называют курсорами firehose, и они поддерживают только последовательное получение строк с начала и до конца курсора. Строки не извлекаются из базы данных, пока они не будут извлечены. Эффекты всех операторов INSERT, UPDATE и DELETE, выполненных текущим пользователем или зафиксированных другими пользователями и влияющих на строки результирующего набора данных, видимы по мере выборки строк из курсора.

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

Хотя модели курсоров API баз данных считают однонаправленный курсор отдельным типом курсора, SQL Server так его не рассматривает. SQL Server поддерживает параметры «только вперёд» и «прокручиваемый», которые могут применяться к статическим, управляемым ключевым набором и динамическим курсорам. Курсоры Transact-SQL поддерживают только статические, управляемые набором ключей и динамические курсоры. Модели курсора API базы данных предполагают, что статические, управляемые набором ключей и динамические курсоры всегда могут быть прокручены. Если для атрибута или свойства курсора API базы данных задано значение "только для перемещения вперед", SQL Server реализует его как динамический курсор с перемещением только вперед.

Статический

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

Курсор не отражает изменения, внесенные в базу данных, которые влияют на членство результирующего набора или изменения значений в столбцах строк, составляющих результирующий набор. Статический курсор не отображает новые строки, вставляемые в базу данных после открытия курсора, даже если они соответствуют условиям поиска инструкции курсора SELECT . Если строки, составляющие результирующий набор, обновляются другими пользователями, новые значения данных не отображаются в статической курсоре. Статический курсор продолжает отображать строки, удаленные из базы данных после открытия курсора. Операции UPDATE, INSERT и DELETE не отображаются в статическом курсоре (до тех пор, пока курсор не будет закрыт и открыт повторно), не отображаются даже изменения, сделанные в том же соединении, в котором был открыт курсор.

Примечание.

Статические курсоры SQL Server всегда доступны только для чтения.

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

Дополнительные сведения см. в разделе «Рабочие таблицы» в руководстве по архитектуре обработки запросов. Дополнительные сведения о максимальном размере строки см. в разделе "Максимальная емкость" для SQL Server.

Transact-SQL использует термин нечувствительный для статических курсоров. Некоторые API баз данных обозначают их как курсоры снимков.

набор ключей

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

Динамический

Динамические курсоры — это противоположность статических курсоров. Динамические курсоры отражают все изменения строк в результирующем наборе при прокрутке курсора. Значения типа данных, порядок и членство строк в результирующем наборе могут меняться для каждой выборки. Все инструкции UPDATE, INSERT и DELETE, выполняемые пользователями, видимы посредством курсора. Обновления становятся видимыми немедленно, если они выполняются через курсор с использованием либо функции API, такой как SQLSetPos, либо предложения Transact-SQL WHERE CURRENT OF. Обновления, сделанные за пределами курсора, не отображаются до тех пор, пока они не будут зафиксированы, если уровень изоляции транзакций курсора не установлен для чтения без фиксации. Дополнительные сведения об уровнях изоляции см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Примечание.

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

Запросить курсор

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

• Transact-SQL

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

• Функции курсора интерфейса прикладного программирования (API) базы данных

  SQL Server поддерживает функциональные возможности курсоров этих API базы данных:

  • ADO (объект данных Microsoft ActiveX)

  • OLE DB

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

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

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

Процесс курсора

Курсоры Transact-SQL и курсоры API имеют другой синтаксис, но следующий общий процесс используется со всеми курсорами SQL Server:

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

2. Выполните инструкцию Transact-SQL, чтобы заполнить курсор.

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

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

5. Закрыть курсор.

Связанный контент

• Режимы работы курсоров
• Способы реализации курсоров
• ОПРЕДЕЛЕНИЕ КУРСОРА (Transact-SQL)
• Курсоры (Transact-SQL)
• Функции курсора (Transact-SQL)
• Хранимые процедуры для курсоров (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL (Transact-SQL)