Курсоры (SQL Server)
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure
Операции в реляционной базе данных выполняются над множеством строк. Например, набор строк, возвращаемый инструкцией SELECT, содержит все строки, которые удовлетворяют условиям, указанным в предложении WHERE инструкции. Этот полный набор строк, возвращаемых инструкцией, называется результирующий набор. Приложения, особенно интерактивные онлайн-приложения, не всегда могут эффективно работать со всем результирующий набор в виде единицы. Им нужен механизм, позволяющий обрабатывать одну строку или небольшое их число за один раз. Курсоры являются расширением результирующих наборов, которые предоставляют такой механизм.
Курсоры позволяют усовершенствовать обработку результатов:
позиционируясь на отдельные строки результирующего набора;
получая одну или несколько строк от текущей позиции в результирующем наборе;
поддерживая изменение данных в строках в текущей позиции результирующего набора;
поддерживая разные уровни видимости изменений, сделанных другими пользователями для данных, представленных в результирующем наборе;
Предоставление инструкций Transact-SQL в скриптах, хранимых процедурах и активирует доступ к данным в результирующем наборе.
Замечания
В некоторых сценариях, если в таблице есть первичный ключ, WHILE цикл можно использовать вместо курсора, не вызывая накладные расходы курсора.
Однако существуют сценарии, в которых курсоры не только не могут быть неизбежными, они на самом деле необходимы. В этом случае, если нет необходимости обновлять таблицы на основе курсора, используйте курсоры firehose , что означает быстрый и доступный только для чтения курсоры.
Реализации курсоров
SQL Server поддерживает три реализации курсоров.
| Реализация курсора | Description |
|---|---|
| Курсоры 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 или библиотеку 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 базы данных определяют их как курсоры моментальных снимков .
Keyset
Членство и порядок строк в курсоре, управляемом набором ключей, являются фиксированными при открытии курсора. Курсоры, управляемые набором ключей, управляются набором уникальных идентификаторов или ключей, известных как набор ключей. Ключи создаются из набора столбцов, который уникально идентифицирует строки результирующего набора. Набор ключей — это набор ключевых значений всех строк, попадающих под действие инструкции 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:
Свяжите курсор с результирующий набор инструкции Transact-SQL и определите характеристики курсора, например, можно ли обновлять строки курсора.
Выполните инструкцию Transact-SQL, чтобы заполнить курсор.
Получить в курсор необходимые строки. Операция получения в курсор одной и более строк называется выборкой. Выполнение серии выборок для получения строк в прямом или обратном направлении называется прокруткой.
При необходимости выполнить операции изменения (обновления или удаления) строки в текущей позиции курсора.
Закрыть курсор.