fETCh с -- I.jre-ieiMC. сде;:анчое с (юмоцью UPDATE, -е огражсется SELECT * FRCM #teno -- Но измеген'/ie действительно имело кесто CLOSE с DEALLOCATE с GO

DROP TA3LE #ie:np kl cl

1 NULI

kl Cl

2 NULL

3 NULI

4 NULL

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

Курсоры, состоящие из множества ключей (keyset)

Открытие KEYSET-курсора возвращает полностью прокручиваемое результирующее множество, с постоянным количеством членов и порядком. Как и в случае курсоров без возможности прокрутки (Forward Only) и статических курсоров, изменения данньгх, на которых гюстроен курсор (за исключением ключевых столбцов), отражаются при обрап1ении к иим; однако добавление новых записей пе отражается курсором. Как и в случае статического курсора, множество уникальных ключевых значений Bannceii курсора копируется в таблицу в tempdb (отсюда термин .\июжество ключей (keyset)), когда курсор открывается. Вот почему членство в курсоре фиксировано. Если таблица курсора не и.меет первичного или уникального ключа, все .чнтожество потенциальных ключей копируется в таблицу с множеством ключей. Поскольку из.менения ключевых столбцов не отражаются курсором, невозможность определить некоторый у1иткальный ключ для данных курсора приведет к тому, что он не будет отражать из.менщищ для любьа столбцов, входящих в потенциальный ключ. Вот при.мер такого курсора:

CREATE TABLE #temp (kl int identity PRIMARY KEY, Cl int NULL)

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #tenip DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE С CURSOR KEYSET

FOR SELECT kl, cl FROM #temp

OPEN с -- Множество ключей копируется в tempdb

UPDATE #temp

SET cl=2

WHERE к 1=1

INSERT #temp VALUES (3) -- это изменение не будет видино курсору (identity-столбец

опустить) FETCH с -- Изменение видимо

FETCH LAST FROM с -- A новая запись - нет

SELECT * FROM #temp

CLOSE с

DEALLOCATE с

DROP TABLE #temp kl cl

В этом при.мере после открытия курсора в его первую запись вносится изменение до считывания записи из курсора. Другая запись вставляется в таблицу. После того как процедура начинает получать записи из курсора, первое изменение показывается, а новая запись - нет. Это происходит потому, что количество членов в этом типе курсора не изменяется, после того как он открыт. Обратите внимание на включение в рабочую таблицу PRIMARY KEY-ограниче-ния. Без него изменения столбца с1 не были бы види.мы курсору, хотя курсор и имеет identity-столбец. Почему? Пото.му что нет гарантии, что identity-столбцы сами по себе будут уникальны. Вы всегда можете использовать SET IDENTHYJNSERT, чтобы добавить дублирующиеся identity-значения, или сбросить счетчик identity, чтобы сервер сдела.д это за вас. Чтобы гарантировать уникальность, необходимо применить PRIMARY- или UNIQUE KEY-ограничения. Без уникального ключа сервер копирует все по-]*енциалыняе ключи каждой записи во временную таблицу.

Правильное использование курсоров

Совет: задействуйте курсоры, когда это действительно необходимо. Это может показаться слишком категоричным, но я ду.маю, что большинство опытных разработчиков на Transact-SQL согласятся с те.м, что применение курсоров должно быть последним в вашем списке способов кодироват-ш. Вместо этого попытайтесь найти решение, которое осгювывается на возможрюстях Transact-SQL для работы с множествами данных. Для этого он был спроектирован, именно это он делает лучше всего. Хотя курсоры просты для новичков, слишком частое или иеправилыюе использование курсоров - основной источник проблем с производительностью в большинстве СУБД, включая SQL Server.

Это не означает, что использование курсоров - табу, и вы попадете в ад, если их задействуете. Если вы достаточно долго программируете на Transact-SQL,

рано или поздно вье сголкиетесь с Kypc()j)a.\h[. HeKorojibie тины разработки нуждаются в них. Как и во лиюгих других случаях, степень Baniero успеха в значительной мере зависит от вашего образа мышления. При.меияйте курсоры, когда это и.меет смысл, - иросто будьте вни.уштельиы, 4to6i)I пе начать неправильно их использовать.

При.меры правильного применения курсоров - дина.\ншеские запросы, операции, ориентированные на записи, и прокручивае.\[ые форхил. Ди1шмические запросы создают и выполняют код Tran.sact-SQL ио ходу работы. Операции, ориентированные на записи, - это процедуры, состоящие из .чиюгих операторов, которые СЛИШКО.м сложны или KOTOj)bie нельзя осуществить с по.чющью одного оператора, напри.мер такого, как SELECT или UPDATE. Прокручиваемые фор.мы обычно предоставляют пользователям воз.\южность перемещаться по результирующему множеству. Прокручиваемые курсоры позволяют разработчику реализовать эту воз.можность настолько просто, насколько это возможно.

Динамические запросы

Курсоры удобно использовать с динамическн.ми запроса.ми, так как они гюзволяют создавать выполняемые Transact-SQL па основании результирующего .множества. Напри.мер, иредстави.м, что нам необходимо создать иерекрестную (сводную) таблицу по ряду значений. Предноложркм, что у нас есть три столбца - ключ, подключ и са.мо значение столбца. Мы хотим получить иерекрестную таблицу с ключами на оси х подключахпг на оси у, и значениями на их пересечении. Каждый ключ .может иметь разлгшное количество подключей!, а эти подключи могут быть, а .могут и пе быть последовательиы.чиг Вот подход, который использует курсор для создания дина.мического Transact-SQL для получения перекрестной таблицы:

CREATE TABLE #senes (keyl int. кеу2 int.

valuel decimal(6.2) DEFAULT (

(CASE (CAST(RAND() + .5 AS int)*-l) WHEN 0 THEN I ElSE -i END)(CONVER[( int.

RANDO * 100000) % 10000)*RAND()

DECLARE s CURSOR FOR

SELECT DISTINCT кеу2 FROM #ser1es ORDER BY кеу2

DECLARE iakey2 int. @key2str varchar(lO). @sql varchar(8000)

OPEN s

FETCH s INTO @кеу2 SET @sql =

WHILE (№FETCH STATUS=0) BEGIN SET @key2str=CAST(@key2 AS varchar)

SET iasql=iasql + .SUM(CASE WHEN key2=+(akey2str+ THEN valuel ELSE NULL END)

[+@key2str+]

FETCH s INTO @кеу2

SET iasql = SELECT keyr+?sql + FROM #series GROUP BY кеуГ

EXEC(@sql)

CLOSE s

DEALLOCATE s

OROP TABLE #series

keyl 1 2 3 4 5 6 7

1 212,74 -1608.59 1825.29 690.48 1863,44 5302.54 NULL

2 -7531.42 1848.63 -3746.60 -54.37 -2263.63 -1013.01 5453.57

3 126.13 -10.41 205.35 NULL NULL NULL NULL

Чтобы лучше понять, как это работает, давайте изучим динамический запрос. Вот как выглядит @sql прямо перед выполнением:

SELECT keyl.SUM(CASE WHEN кеу2=1 THEN valuel ELSE NULL END) [1]. SUMCCASE WHEN кеу2=2 THEN valuel ELSE NULL END) [2]. SUMCCASE WHEN key2=3 THEN valuel ELSE NULL END) [3]. SUMCCASE WHEN кеу2=4 THEN valuel ELSE NULL END) [4]. SUM(CASE WHEN кеу2=5 THEN valuel ELSE NULL END) [5]. SUMCCASE WHEN кеу2=6 THEN valuel ELSE NULL END) [6]. SUM(CASE WHEN кеу2-7 THEN valuel ELSE NULL END) [7] FROM #series GROUP BY keyl

Курсор возвращает запись дяя каждого уникального значения подкл1рча в последовательности. Независимо от ключа, который его содержит, если подключ появляется в таблице, SELECT DISTINCT курсора вернет его экземпляр. Оператор CASE, который создается для каждого столбца перекрестной таблицы, возвращает столбец valuel, когда подключ соответствует столбцу, и NULL в другом случае. GROUP BY сворачивает записи, возвращенные запросом, так что каждый ключ появляется только один раз. Чтобы лучше это понять, давайте посмотрим на перекрестную таблицу без GROUP BY:

keyl 1 2 3 4 5 6 7

В соответствии с характеристикой иервонача-и.ных данных только один иод-ключ в каждой заниси имеет значение. Остальные столбцы устанавливаются в NULL соответствующи.\и1 САЗЕ-выраженияхнт. Предложение GROUP BY свертывает эти NULL, создавая итоговую сводную таблицу, так что каждое значение последовательности появляется в соответствующем столбце подключа.

Операции, ориентированные на записи

Другое правильное ири.менение курсоров - это операции, ориентированные на записи. Это такие операции, которые нельзя осуществить с ио.мощью ед1И1Ст-венного оператора (напри.мер, SELECT). Некоторые их характеристики требуют больше воз.\южностей или гибкости, чем может предоставить решение из одного оператора. Вот при.мер операции, ориентированной на записи, показывающей исходный код триггеров, присоединенных к каждой таблице в базе данных: USE pubs

DECLARE objects CURSOR FOR

SELECT name, deltnig, instnig, updtnig

FROM sysobjects WHERE type-U AND deltnig+instnig+updtrig>0

DECLARE @objname sysname, (Pdeltrig int, Plnstnig int, @updtnig int, ?deltnigname sysname, @instnigname sysname, @updtngname sysname

OPEN objects

FETCH objects INTO @objname, @deltrig, @instrig, (aupdtnig WHILE (@(aFETCH STATUS=0) BEGIN

PRINT Triggers for object: -f@objname SELECT @deltrigname=OBJECT NAME((adeltnig), @instrigname=CBJECT NAME(@instnig),

@updtrigname-CBJECT NAME((aupdtmg) IF @deltmgname IS NOT NULL BEGIN

PRINT Table: +@objname+ Delete Triggen: 4deitmgname EXEC sp helptext (Pdeltrigname

IF @instrigname IS NOT NULL BEGIN

PRINT Table: f@objname+ InsenL Inggen: +Cinstrigname EXEC sp helptext @instrigname

IF (aupdtmgname IS NOT NULL BEGIN

PRINT Table: 4@objname-b Update Triggen- -b@updtnigname

EXEC sp helptext @updtrigname

FETCH objects INTO (Jobjname, @deltnig, (Pmstng, §updtrig

CLOSE objects DEALLOCATE objects

Triggers for object; employee

Table: employee Insert Trigger-. employee 1nsupd

Text

CREATE TRIGGER employee 1nsupd

ON employee

FOR insert. UPDATE

--Выбираем из таблицы jobs диапазоны уровней для данного типа job-a. declare @mln lvl tinyint.

§max lvl tinyint,

@emp lvl tinyint.

@jobJd smallint select @m1n lvl = min lvl.

?maxjvl = maxjvl,

@empjvl = i.jobjvl,

@job id = i. job id from,employee e. jobs j. inserted i where e.emp id = i.emp 1d AND i.job id = j,job 1d IF (@job id 1) and (?empjvl <> 10) begin

raiserror (Job id 1 expects the default level of 10..16.1) ROLLBACK TRANSACTION

end ELSE

IF NOT (@emp lvl BETWEEN laminjvl AND @max lvl) begin

raiserror (The level for job id:M should be between M and W..

16. 1. @job 1d. laminjvl. @maxjvl) ROtLBACK TRANSACTION

Table: employee Update Trigger: employee insupd Text

CREATE TRIGGER employeejnsupd ,

ON employee

FOR insert, UPDATE

-- Выбираем из таблицы jobs диапазоны уровней для данного типа job-a. declare iamin lvl tinyint.

@max lvl tinyint,

@emp lvl tinyint.

@job id smallint select @m1n lvl = min lvl.

@max lvl = max lvl,

@emp lvl = i. job lvl.

@job id = 1 .job id from employee e. jobs j. Inserted 1 where e.emp id = i.emp id AND i.job id - j.job id IF (@job id = 1) and ((aempjvl <> 10) begin

raiserror (Job id 1 expects the default level of 10..16.1) ROLLBACK TRANSACTION

end ELSE

IF NOT (@emp Ivl BETWEEN ?m1n Ivl AND ?maxjvl)

begin

rdSerror ( ihe eve; fc- jot. jc.sa b.culc ce tetcer xa anc ?d. .

16. 1. @job id. @niin ;vi, (Эта/ Ivl) ROLLBACK TRANSACTION

Конечно, мы могли бы иснользоиать нря.мон 3airpoc к таблице syscomments и соедшиггь его с таблицей sysobjects, чтобы получить гу же самую информащно, однако результирующее лиюжество ие бы.;ю бы отформатировано должным образом. Проходя ио таблице но одноГ| записи за раз, мы 1\юже.м отфор.матировать выходную 1тформац1ио для каждо!! таблгпгы и ее триггеров так, как захопьм.

Формы с возможностью прокрутки

Использовать или пет курсор для ([)ор.м с воз.\южностью прокрутки, зависит в значтгтельной стеиени от того, как .много данных может потребоваться форме. Поскольку курсоры Transact-SQL находятся на сервере н возвращают только получаемые записи, они ,moi7t сэкономить много времени и ресурсов при работе с большими результируюицьми множествами. Ва.м не понадобится возвращать 100000 записей по сети клпеитско.му приложению. Вместе с те.м нет необходи.мости применять курсоры с небольшилнг результирующими .множества.ми. Использовать курсор для прокручиваемой фор.мы или нет, зависит также от того, будет ли фор.\ш oбиoвляe.юii и нужно ли немедлеи1го показывать изменения, внесенные другими [юльзователя\П1. Если форма предназначена только для чтения и нет иеобходп.мостн показывать из.менения, внесенные другими пользс;-вателя.ми, .можно избежать при.меиепия курсора.

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

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

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

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]]}]

A расширенный синтаксис Transact-SQL вот так:

DECLARE name CURSOR

[LOCAL 1 GLOBAL]

[FORWARD ONLY SCROLL]

[STATIC I KEYSET DYNAMIC FASTJORWARD]

[READONLY SCROLL LOCKS OPTIMISTIC]

[TYPE WARNING]

FOR select

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

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

тора SELECT. Вот пример:

CREATE TABLE #temp (kl int identity, cl int NULL)

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE с CURSOR

FOR SELECT kl. cl FROM #temp

OPEN с FETCH С UPDATE #temp SET cl=2

WHERE CURRENT OF с

SELECT * FROM #temp CLOSE С DEALLOCATE с GO

DROP TABLE #temp

U cl

! NLLL

к] cl

2 HULL

3 null

4 NULL

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

CREATE TABLE #temp (kl int identity, cl int NULL. c2 int NULL)

INSERT #temp DEFAUlT VALUES

INSERl #tenip DEFAULT VALUES

INSERT #te.iip DEFAULT VALUES

INSERT #tenip DEFAULT VALUES

DECLARE с CURSOR

FOR SELECI kl. cl, c2 FROM #tenip FOR UPDATE OF cl OPEN с FETCH с

-- Плохой Transact-SQL -- 3ioi UPDAIE пыгаеюя изменшь столббм. коюрого нет в списке

FOR UPDAIE OF UPDATE #tenip SET c2-2

WHERE CURRENT OF с

kl cl 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 ссылается па пере.менную, пере.менная вычисляется, когда курсор объявляется, а не когда открывается. Это существенно, так как вы должны присваивать значения пере.меиным до объявления курсора, который их использует. Вы не можете сначала объявить курсор, зате.м присвоить значение нере.менной, от которой он зависит, и рассчитывать, что курсор будет работать правильно. Вот при.мер:

-- В случае, если курсор остался or предыдущего примера DEALLOCATE с DROP TABLE #temo GO

CREATE TABLE #temp (kl int Identity, cl int NULL)

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE m int

DECLARE С CURSOR

FOR SELECT kl. Cl FROM #temp WHERE kl<m -- He будет работать -- №1 здесь равно NULL

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

OPEN с

FETCH с

UPDATE #temp

SET Cl=2

WHERE CURRENT OF с SELECT * FROM #temp CLOSE с DEALLOCATE С GO

DROP TABLE #temp kl cl

Server: Msg 16930. Level 16. State 1. Line 18 The requested row is not in the fetch buffer. The statement has been terminated.

kl cl

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 aujname. au fname FROM authors OPEN GLOBAL Darryl OPEN Darryl