Поделиться через

  • Статья

Курсоры языка Transact-SQL

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

Методика использования курсора языка Transact-SQL в хранимой процедуре или триггере такова:

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

  2. Воспользуйтесь инструкцией DECLARE CURSOR, чтобы связать курсор языка Transact-SQL с инструкцией SELECT. Инструкция DECLARE CURSOR определяет также характеристики курсора, например имя курсора и тип курсора (read-only или forward-only).

  3. Воспользуйтесь инструкцией OPEN для выполнения инструкции SELECT и заполнения курсора.

  4. Воспользуйтесь инструкцией FETCH INTO для выборки отдельных строк и перемещения данных для каждого столбца в указанную переменную. После этого другие инструкции языка Transact-SQL могут ссылаться на эти переменные для доступа к выбранным значениям данных. Курсоры языка Transact-SQL не поддерживают выборку группы строк.

  5. После завершения работы с курсором примените инструкцию CLOSE. Закрытие курсора освобождает некоторые ресурсы, например результирующий набор курсора и его блокировки на текущей строке, однако структура курсора будет доступна для обработки, если снова выполнить инструкцию OPEN. Поскольку курсор все еще существует на этом этапе, повторно использовать его имя не удастся. Инструкция DEALLOCATE полностью освобождает все ресурсы, выделенные курсору, в том числе имя курсора. После освобождения курсора его необходимо перестроить заново с помощью инструкции DECLARE.

Наблюдение за активностью курсора языка Transact-SQL

Для получения списка курсоров, видимых в текущем соединении, используется системная хранимая процедура sp_cursor_list, а для определения характеристик курсора используются процедуры sp_describe_cursor, sp_describe_cursor_columns и sp_describe_cursor_tables.

После открытия курсора функция @@CURSOR_ROWS или столбец cursor_rows, возвращенный процедурами sp_cursor_list или sp_describe_cursor, показывают количество строк в курсоре.

После выполнения каждой инструкции FETCH функция @@FETCH_STATUS обновляется, отражая состояние последней выборки. Сведения о состоянии имеются также в столбце fetch_status, возвращенном процедурой sp_describe_cursor. Функция @@FETCH_STATUS показывает такие состояния, как выборка за пределами первой или последней строк курсора. Функция @@FETCH_STATUS глобальна для соединения и обновляется после любой выборки в любом курсоре, открытом во время соединения. Если состояние нужно посмотреть позже, то перед выполнением следующей инструкции в пределах соединения необходимо сохранить значение функции @@FETCH_STATUS в пользовательской переменной. Даже если следующей инструкцией будет не FETCH, а INSERT, UPDATE или DELETE, сработает триггер, содержащий инструкции FETCH, что приведет к обновлению значения функции @@FETCH_STATUS. Столбец fetch_status, возвращенный процедурой sp_describe_cursor, относится только к указанному курсору и не зависит от инструкций FETCH, ссылающихся на другие курсоры. Однако процедура sp_describe_cursor зависит от инструкций FETCH, ссылающихся на тот же курсор, поэтому при ее использовании следует соблюдать осторожность.

После завершения инструкции FETCH курсор располагается на выбранной строке. Выбранная строка называется также текущей строкой. Если курсор не объявлен с параметром read-only, то текущую строку можно изменить с помощью инструкций UPDATE или DELETE с предложением WHERE CURRENT OF cursor_name.

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

Использование переменной типа cursor

Microsoft SQL Server поддерживает также переменные с типом данных cursor. Курсор может быть связан с переменной типа cursor двумя способами:

/* Use DECLARE @local_variable, DECLARE CURSOR and SET. */
DECLARE @MyVariable CURSOR;

DECLARE MyCursor CURSOR FOR
SELECT LastName FROM AdventureWorks2008R2.Person.Person;

SET @MyVariable = MyCursor;
GO
/* Use DECLARE @local_variable and SET */
DECLARE @MyVariable CURSOR;

SET @MyVariable = CURSOR SCROLL KEYSET FOR
SELECT LastName FROM AdventureWorks2008R2.Person.Person;
DEALLOCATE MyCursor;

После связи курсора с переменной типа cursor эта переменная может использоваться в инструкциях курсора языка Transact-SQL вместо имени курсора. Кроме того, выходным параметрам хранимой процедуры можно назначить тип данных cursor и связать их с курсором. Это позволяет управлять локальными курсорами из хранимых процедур.

Ссылки на курсоры языка Transact-SQL

На имена курсоров и переменные языка Transact-SQL можно ссылаться только из инструкций на языке Transact-SQL; на них нельзя сослаться из API-функций для OLE DB, ODBC и ADO. Например, при использовании инструкций DECLARE CURSOR и OPEN в отношении курсора языка Transact-SQL воспользоваться ODBC-функциями SQLFetch или SQLFetchScroll для выборки строки из курсора языка Transact-SQL не удастся. В приложениях, требующих обработки курсоров и использующих API-функции, вместо курсоров языка Transact-SQL используйте встроенные в интерфейс API для баз данных средства поддержки курсоров.

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

Курсоры языка Transact-SQL особенно эффективны при использовании в хранимых процедурах и триггерах. При таком использовании все компоненты собраны на сервере в единый план выполнения и отсутствует сетевой трафик, связанный с выборкой строк.

Курсоры языка Transact-SQL и параметры SET

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

Параметры, влияющие на план выполнения

ARITHABORT

NUMERIC_ROUNDABORT

FORCEPLAN

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_NULL_DFLT_OFF

ANSI_WARNINGS

ANSI_PADDING

ANSI_NULLS

CONCAT_NULL_YIELDS_NULL

DATEFIRST

DATEFORMAT

LANGUAGE

TEXTSIZE

Индексированные представления и вычисляемые столбцы

ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT (при уровне совместимости 80 или ниже)

CONCAT_NULL_YIELDS_NULL

QUOTED_IDENTIFIER

NUMERIC_ROUNDABORT

В SQL Server 2000 изменение параметров ANSI_NULLS и QUOTED_IDENTIFIER не вызывает ошибки, в отличие от других параметров.