ЧК}* GLOBAL Со / FETCI- :а--у

CLOSE Global Оаг-у

CLOSE Ddcryi CEALLCCATF GLCbA; Da-r, CEAlLGCATE Vc-ry

white

stor-

638C BUI 032 5

Вы можете за/щть, будет ли SQL ServeT c(JЗдauaтl) тлобальиые курсоры, если ие указана область видимости, с помощью системной хранилюй процедуры sp dboption (слютрите следуюп1иГ1 раздел, Коп4)игурироваиие курсоров , для получения подробной ин4юрмацни).

OPEN

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

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

CREAfE TABLE #temp (kl int dentity PR:;-APY KE/, cl mt NULI) INSERT #temp DEFALIT VALUES INSERT #LeTp DEFAULT VALUES INSERT #te,Tip DEFAULT VALUES INSERT ftemp DEFAULl VALUES

DECLARE GlobalCt,nsor CURS04 5А 1С -- ()()ьязляе1- гпосальньм курсор GLOBAL

FOR SELEC kl, cl FRCM #teir,p

DECLARE localCunson CURSOR STATIC - Обьявляес иокально.п курсоо LOCAL

FOR SELECT kl, cl FRCM rftemp WhERE kl<4 -- Bo3Bpak;cei то;1ько три загмси OPEN GLOBAL GlODalCunscr

SELECT (?@CUR50R R0wS AS NumDe-OfGLOBAi CunsorRows OPEN LocalCunson

SELECT egCURSOR ROWS AS Nunoe-GfLOCALCursorRows CLCSE GLOBAL GlcbalCunson DEALLCCAIL GLOBAL GlobalC.rson

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

FETCH

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

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

SET NOCOUNT ON t

CREATE TABLE Icursortest (kl 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 с CURSOR SCROLL

FOR SELECT * FROM #cursortest

OPEN с

FETCH с -- Получаем первую запись FETCH ABSOLUTE 4 FROM с -- Получаем 4-ю запись FETCH RELATIVE -1 FROM с -- Получаем 3-ю запись FETCH LAST FROM с -- Получаем послеЛ - запись

FETCH FIRST FROM с -- Получаем первую ,кь

CLOSE с

CLOSE LocalCursor DEALLOCATE LocalCursor GO

DROP TABLE #temp

NumberOfGLOBALCursorRows

NumberOfLOCALCursorRows

DFAlLCCATL с GC

DROP TABLE #c.riortest

Kl 3 kl 10

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

SET N0C0LN1 ON

CREATE TABIE #cursortest (kl int identity)

INSERT #cunsontest ОЕ.АСЕТ VALUES INSERT #cursortest DEFAULT VALUES INSERT #cunsortest DEFAULT VALUES INSERT Icursortest DEFAULT VALUES INSERT #cursortest DEFAULT VALUES INSERT #curscrtest DEFAULT VALUES INSERT #cursortest DEFAULT VALUES INSERT #cuirsortest DEFAULT VALUES INSERT #cursortest DEFAULT VALUES INSERT #cursortest DEFAULl VALUES

DECLARE с CURSOR SCROLL

FOR SELECT * FRCM #cursortest

DECLARE Зк int

OPEN с

FETCH с INIC @k

WHILE (eiaFETChJlAlUS-O) BEGIN SELECT @k FETCH с INTO @k END

CLOSE с DEALLOCATE с GO

DROP TABLE cursortest

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

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

USE pubs

SET CURSOft CLOSE ON COMMIT OFF -- Ha тот случай, если было включено SET NOCOUNT ON

DECLARE с CURSOR SCROLL

FOR SELECT title id. qty FROM sales ORDER BY qty OPEN с

BEGIN TRAN -- Чтобы можно было отменить наши имзмеиения PRINT Before image FETCH с UPDATE sales SET qty=4

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

PRINT After image

FETCH RELATIVE 0 FROM с

ROLLBACK TRAN Отменяем UPDATE

CLOSE с

DEALLOCATE с

-52091 4

CLOSE

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

DEALLOCATE

После того как вы закончили работу с курсо1)ом, необходи%ю освобод1ггь его. Курсор занн.мает .место в ироцедурпо.м к;-лпе, которое можно использовать для других целей, если оно ва.м болыпе не нужно. Даже при том, что освобождение курсора автоматически зак[)ывает его, считается дур1гы.м тоном освобождение курсора без предварительного его закрытия с ио.мощью Ko.vnnubi CLOSE.

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

в донолнение к конфигурированию курсо[)ов с помощью onunii при обт>явлении Transact-SQL предоставляет ко.манды и огицш конфигурации, которые также .vro-гут изменять поведение курсоров. Процедуры sp configure и sp dboption, ко.манда SET могут быть использованы для ко11(})игурнрования того, как курсоры со.зда-ются и как они себя ведут после создания.

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

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

-- Включаем допслнительнь.е опции. чтоСы можно Оы1,о /.зкенть cursor threshold EXEC sp configure show advanceo CDtiors.l RECONFIGURE WIIH OVERRIDE

rS2C9: 3

Aler iirioye iule 1d Qty

USE northwind

DECLARE с CURSOR STATIC -- Заставляем записи копироваться в tempdb EOR SELECT OrderlD. ProductID EROM [Order Details] DECLARE (astart datetime SET iastart=getdate()

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

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

SELECT DATEDIFF(ms.(astart.getdateC)) AS [Milliseconds elapsed for Synchronous cursor] SELECT @@CURSOR ROWS AS [Number of rows in Synchronous cursor] CLOSE с

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

EXEC sp configure cursor threshold. 1000 -- Асинхронно для курсоров.

в которых > 1000 записей RECONFIGURE WITH OVERRIDE PRINT CHAR(13) -- Для красивого вывода SET (astart=getdate()

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

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

SELECT DATEDIFFCms.start.getdateO) AS [Milliseconds elapsed for Asynchronous cursor]

SELECT @iacURSOR ROWS AS [Number of rows in Asynchronous cursor]

CLOSE с

DEALLOCATE с

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

♦

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

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. Вот пример:

CREATt TABLE #tei!ip (kl int lOentUy PRIMARY КЕ/, cl mt NULL)

INSERl fftetip DEFAULT V.AIUES

INSERT #teTip DEFAULT V.ALUES

INSERT #teiip DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE с CURSOR DYNAMIC

FOR SELECI kl, cl EROM #temp

OPEN с

SET CURSOR CLOSE ON COMMIT ON

BEGIN TRAN

UPDATE #temp

SET cl=2

WHERE kl=l

COMMIT TRAN

-- Эти FETCHH буду! г.еуцд-.л:. ia< нек курсор занры' командой COMMIT FEICH с

FETCH LASl FROM с

- Этот CLOSE буде; неударен, так курсор закрыт командой СОММИ CLOSE с DEALLOCATE с GO

DROP TABLE #te,iip

SET CURSOR CLOSE ON COMMIT OEF

Server: Msg 16917. Level 16, State 2, Line С Cursor IS not open.

Server; Msg 16917, Level 16, State 2, Lme 26 Cursor IS not open,

Ser-ver; Msg 16917, Level 16, State 1, Line 29 Cursor is not open.

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

USE pubs

SET CURS0R CL0SE ON CCMMI! ON BEGIN IRAN

DECLARE с CURSOR DYNAMIC FOR SELEC! qty FROM sales OPEN с FETCH с

UPDATE sales SET qty=qty+l WHERE CURRENT OF с ROLLBACK TRAN

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

FETCH LAST FROM с

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

SET CURSOR CLOSE ON COMMIT OFF

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 с CURSOR DYNAMIC

FOR SELECT qty FROM sales

OPEN с

FETCH с

UPDATE sales

SET qty=qty+l

WHERE CURRENT OF с

ROLLBACK TRAN ,

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

ROLLBACK FETCH с

FETCH LAST FROM с

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

ROLLBACK CLOSE с DEALLOCATE С

qty 30

Несмотря на lor факт, что тран.закцня иы.ма от.меиеиа, в то время как наш ди-1К1.МИЧССКЛЙ курсор был открыт, это не повлияло на курсор. Это противоречит поведению серве])а, оиисаиио.М} в доку.ментации.

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

SQi- Server по умолчанию совдает глобачьные курсоры для сов.\1естн.\юсти с пре-дьаущи.чи! версия.ми сервера, не пoл,epживaвuпlн^ локачьные курсоры. Если вам пеобходн.мр изменить ситуацто, установите огщию базы данных default to local cursor database в true с похгощью sp dboption.

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

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

use pubs

SET CuRS0R CE0SE ON COMMIT OFF

SET NOCOUN ON

DECLARE С CURSOR OYNAHIC

FOR SELECT * EROM sales

OPEN с

FETCH с

BEGIN TRAN -- Начинаег транзакцию, чтоСь, можно Ыпо отменить наши изменения -- Позиционный UPDATE

UPDATE sales SET qty=qty+l WHERE CURRENT OF с FETCH RELATIVE 0 FROM с FETCH с

-- Позиционный DELETE DELETE sales WHERE CURRENT OF с SELECT * FRCM sales WHERE qty-.3 ROLLBACK TRAN -- Отменяем наши изменения

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

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

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

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

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

CREATE PROC 1 istsales cur (atitle id tid. (asalescursor cursor varying OUT

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

FOR SELECT * FROM sales WHERE title id LIKE iatitle id DECLARE lasc cursor -- Локальная курсорная переменная SET iasc=c -- Теперь у нас есть две ссылки на курсор OPEN с FETCH lasc

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

RETURN О

stor id ord num ord date qty payterms title id

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