Курсоры SQL Server

Применимо к: даSQL Server (все поддерживаемые версии) ДаБаза данных SQL Azure

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

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

  • позиционируясь на отдельные строки результирующего набора;

  • получая одну или несколько строк от текущей позиции в результирующем наборе;

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

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

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

Совет

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

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

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

курсоры Transact-SQL

Курсоры Transact-SQL основаны на синтаксисе DECLARE CURSOR и в основном используются в скриптах, хранимых процедурах и триггерах Transact-SQL. Transact-SQL реализуются на сервере и управляются инструкциями Transact-SQL , отправляемыми от клиента серверу. Они также могут содержаться в пакетах, хранимых процедурах или триггерах.

Серверные курсоры интерфейса прикладного программирования (API)

Курсоры API поддерживают функции курсоров API в OLE DB и ODBC. Курсоры API реализуются на сервере. Всякий раз, когда клиентское приложение вызывает функцию курсора API, поставщик OLE DB или драйвер ODBC для собственного клиента SQL Server передает требование на сервер для выполнения действия в отношении серверного курсора API.

Клиентские курсоры

Клиентские курсоры реализуются внутренне драйвером ODBC для собственного клиента SQL Server и библиотекой DLL, реализующей API-интерфейс ADO. Клиентские курсоры реализуются посредством кэширования всех строк результирующего набора на клиенте. Каждый раз, когда клиентское приложение вызывает функцию курсора API, драйвер ODBC для собственного клиента SQL Server или ADO DLL выполняет операцию курсора на строках результирующего набора, кэшированных на клиенте.

Типы курсоров

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 баз данных называют их курсорами моментальных снимков.

Keyset

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

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

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

Примечание

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

Запрос курсора

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

  • Transact-SQL

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

  • API-функции курсоров базы данных.

    SQL Server поддерживает функциональность курсоров для следующих API-интерфейсов баз данных:

    • ADO (Microsoft ActiveX Data Object);

    • OLE DB

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

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

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

Обработка курсоров

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

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

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

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

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

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

Режимы работы курсоров
Способы реализации курсоров

См. также:

DECLARE CURSOR (Transact-SQL)
Курсоры (Transact-SQL)
Функции работы с курсорами (Transact-SQL)
Хранимые процедуры курсора (Transact-SQL)
SET TRANSACTION ISOLATION LEVEL (Transact-SQL)