T-SQL синтаксис курсоров

Tsql теория > T-SQL синтаксис курсоров
04.04.2013 17:19:08


Наиболее часто встречающиеся слова в статье:

[DEFAULT] [-----------] [помощью] [DECLARE] [курсоры] [курсора] [cursortest] [DEALLOCATE] [курсоров] [title_id]


Статья:

T-SQL синтаксис курсоров


С курсорами связано множество команд и функций, они приведены в табл. 13.2. В следующем разделе мы рассмотрим эти команды подробнее.

Таблица 13.2. Синтаксис Transact-SQL для работы с курсорами

Команда или функция

Предназначение

DECLARE CURSOR

Объявляет курсор

OPEN

Открывает курсор, чтобы можно было получать из него данные

FETCH

Выбирает одну запись из курсора

CLOSE

Закрывает курсор, оставляя внутренние структуры, связанные с ним

DEALLOCATE

Освобождает внутренние структуры курсора

@@CURSOR_ROWS

Возвращает количество записей в курсоре

@@FETCH_STATUS

Определяет, была ли удачна или неудачна последняя команда FETCH

CURSOR_STATUS()

Возвращает информацию о статусе курсора или курсорной переменной

DECLARE CURSOR


DECLARE CURSOR объявляет курсор. Есть две основные версии команды DECLARE CURSOR — совместимый с ANSI /ISO SQL 92 синтаксис и расширенный синтаксис Transact-SQL. Синтаксис ANSI /ISO выглядит так:

DECLARE name [INSENSITIVE][SCROLL] CURSOR

FOR select

[FOR {READ ONLY | UPDATE [OF column [,...n]]}]

А расширенный синтаксис Transact-SQL так:

DECLARE name CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

[TYPE_WARNING]

FOR select

[FOR {READ ONLY | UPDATE [OF column [,...n]]}]

Компонента select команды — это обыкновенный оператор SELECT, который определяет, какие записи возвращает курсор. В нем нельзя использовать ключевые слова COMPUTE [BY], FOR BROWSE или INTO. Компонента select влияет на то, будет ли курсор открыт только для чтения. Например, если вы включите предложение FOR UPDATE, но укажете select, которая по существу запрещает изменения (например, включает GROUP BY или DISTINCT), ваш курсор будет неявно преобразован в курсор только для чтения (или статический). Сервер преобразует курсоры к статическим, не обновляемым по своей сути. Этот тип автоматического преобразования известен как неявное преобразование курсоров (implicit cursor conversions). Существует несколько критериев, которые влияют на неявное преобразование курсоров; за более подробной информацией обратитесь к Books Online. Для возможности изменения курсора вы не обязаны указывать FOR UPDATE явно, если сам по себе запрос SELECT является изменяемым. И еще раз, если не указано иначе, то будет ли курсор изменяемым, определяется характеристиками оператора SELECT. Вот пример:

CREATE TABLE #temp (k1 int identity, c1 int NULL)

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE c CURSOR

FOR SELECT k1, c1 FROM #temp

OPEN c

FETCH c

UPDATE #temp

SET c1=2

WHERE CURRENT OF c

SELECT * FROM #temp

CLOSE c

DEALLOCATE c

GO

DROP TABLE #temp

k1 c1

----------- -----------

1 NULL

k1 c1

----------- -----------

1 2

2 NULL

3 NULL

4 NULL

Даже притом, что курсор не объявлен как изменяемый, он является изменяемым на основании того факта, что его оператор SELECT изменяемый — то есть сервер может преобразовать изменение курсора в изменение соответствующей записи таблицы. Если вы укажете предложение FOR UPDATE и включите список столбцов, то столбцы, которые вы изменяете, должны быть указаны в этом списке. Если вы попытаетесь изменить столбец, которого нет в списке с помощью предложения WHERE CURRENT OF оператора UPDATE, SQL Server отклонит изменения и сгенерирует сообщение об ошибке. Вот пример:

CREATE TABLE #temp (k1 int identity, c1 int NULL, c2 int NULL)

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE c CURSOR

FOR SELECT k1, c1, c2 FROM #temp

FOR UPDATE OF c1

OPEN c

FETCH c

-- Плохой T-SQL -- Этот UPDATE пытается изменить столбец, которого нет в списке FOR UPDATE OF

UPDATE #temp

SET c2=2

WHERE CURRENT OF c

k1 c1 c2

---------- ------------ ---------------

1 NULL NULL

Server: Msg 16932, Level 16, State 1, Line 18

The cursor has a FOR UPDATE list and the requested column to be updated is not

in this list.

The statement has been terminated.

Если select ссылается на переменную, переменная вычисляется, когда курсор объявляется, а не когда открывается. Это существенно, так как вы должны присваивать значения переменным до объявления курсора, который их использует. Вы не можете сначала объявить курсор, затем присвоить значение переменной, от которой он зависит, и рассчитывать, что курсор будет работать правильно. Вот пример:

-- В случае, если курсор остался от предыдущего примера

DEALLOCATE c

DROP TABLE #temp

GO

CREATE TABLE #temp (k1 int identity, c1 int NULL)

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE @k1 int

DECLARE c CURSOR

FOR SELECT k1, c1 FROM #temp WHERE k1<@k1 -- Не будет работать -- @k1 здесь равно NULL

SET @k1=3 -- Это надо переместить перед DECLARE CURSOR

OPEN c

FETCH c

UPDATE #temp

SET c1=2

WHERE CURRENT OF c

SELECT * FROM #temp

CLOSE c

DEALLOCATE c

GO

DROP TABLE #temp

k1 c1

----------- -----------

Server: Msg 16930, Level 16, State 1, Line 18

The requested row is not in the fetch buffer.

The statement has been terminated.

k1 c1

----------- -----------

1 NULL

2 NULL

3 NULL

4 NULL

Глобальные и локальные курсоры


Глобальные курсоры видимы вне пакета, хранимой процедуры или триггера, создавшего его, и существуют до тех пор, пока явно не будут освобождены или пока соединение, создавшее его. Локальный курсор видим только программному модулю, который его создал, если только курсор не возвращен с помощью выходного параметра. Локальные курсоры неявно освобождаются, когда выходят из области видимости. Для совместимости с предыдущими версиями, SQL Server по умолчанию создает глобальные курсоры, но вы можете отменить поведение по умолчанию, явно указав ключевое слово GLOBAL или LOCAL, когда объявляете курсор. Заметьте, что вы можете иметь глобальные и локальные курсоры с одинаковыми именами, хотя это довольно сомнительная практика кодирования. Например, этот код выполняется без ошибки:

DECLARE Darryl CURSOR -- My brother Darryl

LOCAL

FOR SELECT stor_id, title_id, qty FROM sales

DECLARE Darryl CURSOR -- My other brother Darryl

GLOBAL

FOR SELECT au_lname, au_fname FROM authors

OPEN GLOBAL Darryl

OPEN Darryl

FETCH GLOBAL Darryl

FETCH Darryl

CLOSE GLOBAL Darryl

CLOSE Darryl

DEALLOCATE GLOBAL Darryl

DEALLOCATE Darryl

au_lname au_fname

---------------------------------------- --------------------

White Johnson

stor_id title_id qty

---------- ------------ ---------

6380 BU1032 5

Мы можете изменить, будет ли SQL Server создавать глобальные курсоры, если не указана область видимости, с помощью системной хранимой процедуры sp_dboption (смотрите следующий раздел «Конфигурирование курсоров» за более подробной информацией).

OPEN


OPEN делает записи курсора доступными с помощью FETCH. Если курсор INSENSITIVE или STATIC, OPEN копирует все результирующее множество во временную таблицу. Если это KEYSET-курсор, OPEN копирует множество уникальных значений (или все множество потенциальных ключей, если не существует уникального ключа) во временную таблицу. В OPEN можно указать область видимости курсора, если включить опциональное ключевое слово GLOBAL. Если существуют и локальный и глобальный курсор с одинаковым именем (вы должны по возможности избегать этого), применяйте GLOBAL, чтобы указать курсор, который вы хотите открыть. (Опция базы данных default to local cursor определяет, получите ли вы глобальный или локальный курсор, когда ни то, ни другое явно не указано. Для более подробной информации смотрите следующий раздел, посвященный конфигурированию курсоров).

Используйте автоматическую переменную @@CURSOR_ROWS, чтобы определить, сколько записей в курсоре. Вот простой пример OPEN:

CREATE TABLE #temp (k1 int identity PRIMARY KEY, c1 int NULL)

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE GlobalCursor CURSOR STATIC -- Объявляем глобальный курсор

GLOBAL

FOR SELECT k1, c1 FROM #temp

DECLARE LocalCursor CURSOR STATIC -- Объявляем локальный курсор

LOCAL

FOR SELECT k1, c1 FROM #temp WHERE k1<4 -- Возвращает только три записи

OPEN GLOBAL GlobalCursor

SELECT @@CURSOR_ROWS AS NumberOfGLOBALCursorRows

OPEN LocalCursor

SELECT @@CURSOR_ROWS AS NumberOfLOCALCursorRows

CLOSE GLOBAL GlobalCursor

DEALLOCATE GLOBAL GlobalCursor

CLOSE LocalCursor

DEALLOCATE LocalCursor

GO

DROP TABLE #temp

NumberOfGLOBALCursorRows

------------------------

4

NumberOfLOCALCursorRows

-----------------------

3

Для динамических курсоров @@CURSOR_ROWS возвращает –1, так как добавление новых записей может в любое время изменить количество записей, возвращенных курсором. Если курсор заполняется асинхронно, (смотрите раздел «Конфигурирование курсоров»), @@CURSOR_ROWS возвращает отрицательное значение, абсолютное значение которого показывает, сколько записей в настоящий момент в курсоре.

FETCH


FETCH — способ, с помощью которого вы получаете данные из курсора. Можете считать его специальным оператором SELECT, который возвращает только одну запись из предопределенного результирующего множества. Обычно FETCH вызывается в цикле, который использует @@FETCH_STATUS в качестве контролирующей переменной, каждый удачный вызов FETCH возвращает следующую запись курсора.

Курсоры с возможностью прокрутки (DYNAMIC, STATIC и KEYSET-курсоры, или те, которые объявлены с опцией SCROLL) позволяют FETCH получать не только следующие записи курсора. В дополнение к получению следующей записи прокручиваемые курсоры позволяют с помощью FETCH получить предыдущую запись, первую запись, последнюю запись, запись по ее номеру, и запись относительно текущей. Вот простой пример:

SET NOCOUNT ON

CREATE TABLE #cursortest (k1 int identity)

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

DECLARE c CURSOR SCROLL

FOR SELECT * FROM #cursortest

OPEN c

FETCH c -- Получаем первую запись

FETCH ABSOLUTE 4 FROM c -- Получаем 4-ю запись

FETCH RELATIVE -1 FROM c – Получаем 3-ю запись

FETCH LAST FROM c -- Получаем последнюю запись

FETCH FIRST FROM c -- Получаем первую запись

CLOSE c

DEALLOCATE c

GO

DROP TABLE #cursortest

k1

-----------

1

k1

-----------

4

k1

-----------

3

k1

-----------

10

k1

-----------

1

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

SET NOCOUNT ON

CREATE TABLE #cursortest (k1 int identity)

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

INSERT #cursortest DEFAULT VALUES

DECLARE c CURSOR SCROLL

FOR SELECT * FROM #cursortest

DECLARE @k int

OPEN c

FETCH c INTO @k

WHILE (@@FETCH_STATUS=0) BEGIN

SELECT @k

FETCH c INTO @k

END

CLOSE c

DEALLOCATE c

GO

DROP TABLE #cursortest

-----------

1

-----------

2

-----------

3

-----------

4

-----------

5

-----------

6

-----------

7

-----------

8

-----------

9

-----------

10

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

FETCH RELATIVE 0 можно использовать для обновления текущей записи. Это позволяет учитывать изменения текущей записи при прохождении курсора. Вот пример:

USE pubs

SET CURSOR_CLOSE_ON_COMMIT OFF -- На тот случай, если было включено

SET NOCOUNT ON

DECLARE c CURSOR SCROLL

FOR SELECT title_id, qty FROM sales ORDER BY qty

OPEN c

BEGIN TRAN -- Чтобы можно было отменить наши имзменения

PRINT 'Before image'

FETCH c

UPDATE sales

SET qty=4

WHERE qty=3 -- Мы знаем, что этому соответствует только одна запись, первая

PRINT 'After image'

FETCH RELATIVE 0 FROM c

ROLLBACK TRAN -- Отменяем UPDATE

CLOSE c

DEALLOCATE c

Before image

title_id qty

-------- ------

PS2091 3

After image

title_id qty

-------- ------

PS2091 4

CLOSE


CLOSE освобождает текущее результирующее множество курсора, и снимает любые блокировки, наложенные курсором. (До версии 7.0, SQL Server оставлял все блокировки до окончания транзакции, включая блокировки курсоров. Начиная с версии 7.0, блокировки курсоров обрабатываются независимо от других типов блокировок). Структуры данных курсора остаются нетронутыми, так что если понадобится, курсор можно открыть снова. Для закрытия глобального курсора указывайте ключевое слово GLOBAL.

DEALLOCATE


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

Конфигурирование курсоров


В дополнение к конфигурированию курсоров с помощью опций при объявлении, Transact-SQL предоставляет команды и опции конфигурации, которые также могут изменять поведение курсоров. Процедуры sp_configure и sp_dboption, команда SET могут быть использованы для конфигурирования того, как курсоры создаются и как они себя ведут, после того как созданы.

Асинхронные курсоры


По умолчанию, SQL Server генерирует все наборы ключевых значений синхронно — то есть вызов OPEN не закончится, пока результирующее множество курсора не будет полностью создано. Это может быть неоптимально для больших множеств, и вы можете изменить это поведение с помощью опции конфигурации sp_configure ‘cursor threshold’ (cursor threshold является дополнительной опцией; включите дополнительные опции с помощью sp_configure ‘show advanced options’, чтобы получить к ней доступ). Вот пример, который показывает отличия использования асинхронного курсора:

-- Включаем дополнительные опции, чтобы можно было изменить 'cursor threshold'

EXEC sp_configure 'show advanced options',1

RECONFIGURE WITH OVERRIDE

USE northwind

DECLARE c CURSOR STATIC -- Заставляем записи копироваться в tempdb

FOR SELECT OrderID, ProductID FROM [Order Details]

DECLARE @start datetime

SET @start=getdate()

-- Сначала попробуем с помощью синхронного курсора

OPEN c

PRINT CHAR(13) -- Для красивого вывода

SELECT DATEDIFF(ms,@start,getdate()) AS [Milliseconds elapsed for Synchronous cursor]

SELECT @@CURSOR_ROWS AS [Number of rows in Synchronous cursor]

CLOSE c

-- Теперь изменим 'cursor threshold', чтобы заставить сервер использовать асинхронные курсоры

EXEC sp_configure 'cursor threshold', 1000 -- Асинхронно для курсоров, в которых > 1000 записей

RECONFIGURE WITH OVERRIDE

PRINT CHAR(13) -- Для красивого вывода

SET @start=getdate()

OPEN c -- Открываем асинхронный курсор, так как в таблице больше 1000 записей

-- OPEN возвращается немедленно, так как курсор заполняется асинхронно

SELECT DATEDIFF(ms,@start,getdate()) AS [Milliseconds elapsed for Asynchronous cursor]

SELECT @@CURSOR_ROWS AS [Number of rows in Asynchronous cursor]

CLOSE c

DEALLOCATE c

GO

EXEC sp_configure 'cursor threshold', -1 -- Возвращаем синхронные курсоры

RECONFIGURE WITH OVERRIDE

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option changed. Run the RECONFIGURE statement to install.

Milliseconds elapsed for Synchronous cursor

-------------------------------------------

70

Number of rows in Synchronous cursor

------------------------------------

2155

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option changed. Run the RECONFIGURE statement to install.

Milliseconds elapsed for Asynchronous cursor

--------------------------------------------

0

Number of rows in Asynchronous cursor

-------------------------------------

-1

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Configuration option changed. Run the RECONFIGURE statement to install.

ANSI/ISO автоматическое закрытие курсоров


Спецификация ANSI/ISO SQL-92 определяет, что курсоры должны автоматически закрываться при фиксации транзакции. В этом нет большого смысла для приложений, в которых курсоры используются очень часто (те, которые задействуют прокручиваемые формы, например), так что в этом смысле SQL Server не соответствует стандарту. По умолчанию, курсоры SQL Server остаются открытыми, пока не будут явно закрыты, или, пока соединение, создавшее их, не отсоединится. Чтобы заставить SQL Server закрывать курсоры при фиксации транзакции, используйте команду SET CURSOR_CLOSE_ON_COMMIT. Вот пример:

CREATE TABLE #temp (k1 int identity PRIMARY KEY, c1 int NULL)

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE c CURSOR DYNAMIC

FOR SELECT k1, c1 FROM #temp

OPEN c

SET CURSOR_CLOSE_ON_COMMIT ON

BEGIN TRAN

UPDATE #temp

SET c1=2

WHERE k1=1

COMMIT TRAN

-- Эти FETCH'и будут неудачны, так как курсор закрыт командой COMMIT

FETCH c

FETCH LAST FROM c

-- Этот CLOSE будет неудачен, так курсор закрыт командой COMMIT

CLOSE c

DEALLOCATE c

GO

DROP TABLE #temp

SET CURSOR_CLOSE_ON_COMMIT OFF

Server: Msg 16917, Level 16, State 2, Line 0

Cursor is not open.

Server: Msg 16917, Level 16, State 2, Line 26

Cursor is not open.

Server: Msg 16917, Level 16, State 1, Line 29

Cursor is not open.

Вопреки Books Online, откат транзакции не закрывает изменяемые курсоры, когда CLOSE_CURSOR_ON_COMMIT отключена. Фактическое поведение ROLLBACK значительно отличается от описанного в документации и больше соответствует тому, которое происходит при фиксации транзакции. В общем, ROLLBACK не закрывает курсоры, если только не была включена опция CLOSE_CURSOR_ON_COMMIT. Вот пример:

USE pubs

SET CURSOR_CLOSE_ON_COMMIT ON

BEGIN TRAN

DECLARE c CURSOR DYNAMIC

FOR SELECT qty FROM sales

OPEN c

FETCH c

UPDATE sales

SET qty=qty+1

WHERE CURRENT OF c

ROLLBACK TRAN

-- Эти команды FETCH будут неудачны, так как курсор был закрыт командой ROLLBACK

FETCH c

FETCH LAST FROM c

-- Эта команда CLOSE будет неудачна, так как курсор был закрыт командой ROLLBACK

CLOSE c

DEALLOCATE c

GO

SET CURSOR_CLOSE_ON_COMMIT OFF

qty

------

5

Server: Msg 16917, Level 16, State 2, Line 21

Cursor is not open.

Server: Msg 16917, Level 16, State 2, Line 22

Cursor is not open.

Server: Msg 16917, Level 16, State 1, Line 25

Cursor is not open.

Теперь давайте отключим CURSOR_CLOSE_ON_COMMIT и снова выполним запрос:

SET CURSOR_CLOSE_ON_COMMIT OFF

BEGIN TRAN

DECLARE c CURSOR DYNAMIC

FOR SELECT qty FROM sales

OPEN c

FETCH c

UPDATE sales

SET qty=qty+1

WHERE CURRENT OF c

ROLLBACK TRAN

-- Эти команды FETCH выполнятся, так как курсор был оставлен открытым несмотря на ROLLBACK

FETCH c

FETCH LAST FROM c

-- Эта команда CLOSE выполнится, поскольку курсор был оставлен открытым, несмотря на ROLLBACK

CLOSE c

DEALLOCATE c

qty

------

5

qty

------

3

qty

------

30

Несмотря на тот факт, что транзакция былаотменена, в то время как наш динамический курсор был открыт, это не повлияло на курсор. Это противоречит поведению сервера, описанному в документации.

Конфигурирование создания по умолчанию глобальных или локальных курсоров


SQL Server по умолчанию создает глобальные курсоры. Это делается для совместимости с предыдущими версиями сервера, которые не поддерживали локальные курсоры. Если вам необходимо это изменить, установите опцию базы данных default to local cursor database в true с помощью sp_dboption.

Модифицируемые курсоры


Предложения WHERE CURRENT OF команд UPDATE и DELETE позволяют модифицировать и удалять записи с помощью курсора. Модификация или удаление с помощью курсора известны как позиционная модификация. Вот пример:

USE pubs

SET CURSOR_CLOSE_ON_COMMIT OFF

SET NOCOUNT ON

DECLARE C CURSOR DYNAMIC

FOR SELECT * FROM sales

OPEN c

FETCH c

BEGIN TRAN -- Начинает транзакцию, чтобы можно было отменить наши изменения

-- Позиционный UPDATE

UPDATE sales SET qty=qty+1 WHERE CURRENT OF c

FETCH RELATIVE 0 FROM c

FETCH c

-- Позиционный DELETE

DELETE sales WHERE CURRENT OF c

SELECT * FROM sales WHERE qty=3

ROLLBACK TRAN -- Отменяем наши изменения

SELECT * FROM sales WHERE qty=3 -- Удаленные записи восстанавливаются

CLOSE c

DEALLOCATE c

stor_id ord_num ord_date qty payterms title_id

-------- ----------- --------------------------------- ----- ------------- -----------

6380 6871 1994-09-14 00:00:00.000 5 Net 60 BU1032

stor_id ord_num ord_date qty payterms title_id

-------- ----------- --------------------------------- ----- ------------- -----------

6380 6871 1994-09-14 00:00:00.000 6 Net 60 BU1032

stor_id ord_num ord_date qty payterms title_id

-------- ----------- --------------------------------- ----- ------------- -----------

6380 722a 1994-09-13 00:00:00.000 3 Net 60 PS2091

stor_id ord_num ord_date qty payterms title_id

-------- ----------- --------------------------------- ----- ------------- -----------

stor_id ord_num ord_date qty payterms title_id

-------- ----------- --------------------------------- ----- ------------- -----------

6380 722a 1994-09-13 00:00:00.000 3 Net 60 PS2091

Курсорные переменные


Transact-SQL позволяет определять переменные, которые содержат указатели на курсоры, с помощью типа данных cursor. В командах OPEN, FETCH, CLOSE и DEALLOCATE можно использовать курсорные переменные, также как имена курсоров. Вы можете создавать переменные в хранимых процедурах, в которых заданы описания курсоров, и возвращать курсоры, созданные в хранимой процедуре, с помощью выходных параметров. Несколько процедур самого SQL Server используют эту возможность, чтобы возвращать результаты эффективным модульным способом (например, sp_cursor_list, sp_describe_cursor, sp_fulltext_tables_cursor). Заметьте, что вы не можете передавать курсор в процедуру с помощью входного параметра — вы можете только возвращать курсоры с помощью выходных параметров. Также нельзя определять столбцы таблицы курсорного типа — разрешены только переменные — также вы не можете присваивать значение курсорной переменной с помощью оператора SELECT (как в случае скалярных переменных) — для этого вы должны задействовать SET.

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

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

CREATE PROC listsales_cur @title_id tid, @salescursor cursor varying OUT

AS

-- Объявляем локальный курсор, чтобы он был автоматически особожден

-- когда выйдет из области видимости

DECLARE c CURSOR DYNAMIC

LOCAL

FOR SELECT * FROM sales WHERE title_id LIKE @title_id

DECLARE @sc cursor -- Локальная курсорная переменная

SET @sc=c -- Теперь у нас есть две ссылки на курсор

OPEN c

FETCH @sc

SET @salescursor=@sc -- Возвращаем курсор с помощью выходного параметра

RETURN 0

GO

SET NOCOUNT ON

-- Объявляем локальную курсорную переменную для получения выходного параметра

DECLARE @mycursor cursor

EXEC listsales_cur 'BU1032', @mycursor OUT -- Вызываем процедуру

-- Убедимся, что курсор открыт и в нем есть по крайне мере одна запись

IF (CURSOR_STATUS('variable','@mycursor')=1) BEGIN

FETCH @mycursor

WHILE (@@FETCH_STATUS=0) BEGIN

FETCH @mycursor

END

END

CLOSE @mycursor

DEALLOCATE @mycursor

stor_id ord_num ord_date qty payterms title_id

-------- -------------- ---------------------------------- ----- --------------- -----------

6380 6871 1994-09-14 00:00:00.000 5 Net 60 BU1032

stor_id ord_num ord_date qty payterms title_id

-------- -------------- --------------------------------- ----- ---------------- -------

8042 423LL930 1994-09-14 00:00:00.000 10 ON invoice BU1032

stor_id ord_num ord_date qty payterms title_id

-------- ------------ --------------------------------- ----- ------------- -----------

8042 QA879.1 1999-06-24 19:13:26.230 30 Net 30 BU1032

stor_id ord_num ord_date qty payterms title_id

-------- ----------- --------------------------------- ----- ------------- -----------

Обратите внимание на то, как этот код ссылается на курсор с помощью трех различных переменных, а также имени курсора. Для каждой команды, за исключением DEALLOCATE, ссылка на курсор с помощью курсорной переменной — аналог ссылки на курсор по имени. Если вы открываете курсор с помощью команды OPEN, независимо от того, ссылаетесь вы на курсор по имени или с помощью курсорной переменной, курсор будет открыт, и вы можете выбирать из него записи с помощью команды FETCH, используя любую переменную, которая ссылается на него. DEALLOCATE отличается в том смысле, что эта команда на самом деле не освобождает курсор, если только это не последняя ссылка на него. Это, однако, действительно предотвращает доступ к нему с помощью указанного идентификатора. Так что если у вас есть курсор с именем foo и курсорная переменная с названием foovar, которой было присвоено значение foo, освобождение foo лишь запретит доступ к курсору с помощью foo — foovar останется неизменной.

Хранимые процедуры для работы с курсорами


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

Таблица 13.3. Хранимые процедуры, связанные с триггерами

Процедура

Выполняемые функции

sp_cursor_list

Возвращает список курсоров, открытых соединением, а также их атрибуты

sp_describe_cursor

Возвращает атрибуты отдельного курсора

sp_describe_cursor_columns

Возвращает столбцы курсора (и их атрибуты)

sp_describe_cursor_tables

Возвращает список таблиц, использованных в курсоре

Оптимизация производительности курсоров


Лучший способ оптимизации курсоров — вообще не использовать их, когда это возможно. Как я уже говорил, SQL Server лучше работает с множествами данных, чем с отдельными записями. Он представляет собой реляционную СУБД, а работа с отдельными записями никогда не была сильно стороной реляционных СУБД. Хотя иногда без курсоров не обойтись, так что вот несколько советов по их оптимизации:

  • Не используйте статические/нечувствительные курсоры без необходимости. При открытии статического курсора все его записи копируются во временную таблицу. Вот почему они нечувствительны к изменениям — он на самом деле ссылается на копию таблицы в tempdb. Естественно, чем больше результирующее множество, тем более вероятно, что использование статического курсора вызовет проблемы с ресурсами в tempdb.

  • Используйте keyset-курсоры, если только они вам действительно необходимы. Как и в случае статических курсоров, открытие keyset-курсора создает временную таблицу. Хотя эта таблица содержит только значения ключей основной таблицы (если, конечно, существует уникальный ключ), она может все равно иметь существенный размер при работе с большими результирующими множествами.

  • Используйте опцию FAST_FORWARD вместо FORWARD_ONLY при работе с однонаправленными множествами только для чтения. При использовании FAST_FORWARD объявляется FORWARD_ONLY, READ_ONLY курсор с некоторыми внутренними оптимизациями производительности.

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

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

  • Будьте осторожны с модифицируемыми динамическими курсорами, особенно с теми, которые созданы по таблицам с неуникальными кластерными индексами, так как они могут вызвать «проблему Хэллоуина» —повторные, ошибочные обновления одной и той же строки или строк. Так как SQL Server внутренне делает неуникальные ключи кластерных индексов уникальными, добавляя к ним порядковый номер, возможно, что вы измените значение ключа записи на такое, которое уже существует и заставите сервер добавить суффикс, из-за которого запись переместится дальше в результирующем множестве. При выборе оставшегося результирующего множества вы опять наткнетесь на эту запись, и процесс повторится, что приведет к бесконечному циклу. Вот пример, иллюстрирующий данную проблему:

-- Этот код создает курсор, который представляет Проблему Хэллоуина.

-- Не запускайте его, если только вам не нравятся бесконечные циклы.

SET NOCOUNT ON

CREATE TABLE #temp (k1 int identity, c1 int NULL)

CREATE CLUSTERED INDEX c1 ON #temp(c1)

INSERT #temp VALUES (8)

INSERT #temp VALUES (6)

INSERT #temp VALUES (7)

INSERT #temp VALUES (5)

INSERT #temp VALUES (3)

INSERT #temp VALUES (0)

INSERT #temp VALUES (9)

DECLARE c CURSOR DYNAMIC

FOR SELECT k1, c1 FROM #temp

OPEN c

FETCH c

WHILE (@@FETCH_STATUS=0) BEGIN

UPDATE #temp

SET c1=c1+1

WHERE CURRENT OF c

FETCH c

SELECT * FROM #temp ORDER BY k1

END

CLOSE c

DEALLOCATE c

GO

DROP TABLE #temp

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

Заключение


Курсоры —о не лучший способ решения большинства задач, они могут вызывать серьезные проблемы с производительностью, если их неправильно использовать.