Итак, мы выполнили обработку нескольких строк и убедились в том, что применяемая хранимая процедура является работоспособной; после этого мы можем выйти из хранимой процедуры (еще раз напомним, что курсор пока не был закрыт или освобожден). Затем обратимся к курсору из кода, выходящего за пределы хранимой процедуры: EXEC SpCursorScope

DECLARE ©Counter int,

©OrderlD int,

©CustomerlD int

SET ®Counter=6

WHILE (©Counter<=10) AND (©©FETCH STATUS=0) BEGIN

PRINT Row + CAST(©Counter AS varchar) + has a SalesOrderlD of + CAST(©OrderlD AS varchar) + and a CustomerlD of + CAST(©CustomerlD AS varchar)

SELECT ©Counter = ©Counter + 1

FETCH NEXT FROM CursorTest INTO ©OrderlD, ©CustomerlD

CLOSE CursorTest DEALLOCATE CursorTest

Приступим к анализу того, что здесь происходит.

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

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

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

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

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

Row 1 has а SalesOrderlD of 43659 and a CustomerlD of 676

Row 2 has a SalesOrderlD of 43660 and a CustomerlD of 117

Row 3 has a SalesOrderlD of 43661 and a CustomerlD of 442

Row 4 has a SalesOrderlD of 43662 and a CustomerlD of 227

Row 5 has a SalesOrderlD of 43663 and a CustomerlD of 510

Row б has а SalesOrderlD of 43664 and a CustomerlD of 3 97

Row 7 has a SalesOrderlD of 43665 and a CustomerlD of 146 Row 8 has a SalesOrderlD of 43666 and a CustomerlD of 511 Row 9 has a SalesOrderlD of 43667 and a CustomerlD of 646 Row 10 has a SalesOrderlD of 43668 and a CustomerlD of 514

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

Теперь рассмотрим, что произойдет, если для хранимой процедуры будет назначена локальная область определения:

USE AdventureWorks GO

ALTER PROCEDURE spCursorScope AS

DECLARE ©Counter int,

©OrderlD int,

©CustomerlD int

DECLARE CursorTest CURSOR

LOCAL

SELECT SalesOrderlD, CustomerlD FROM Sales.SalesOrderHeader

SELECT ©Counter = 1 OPEN CursorTest

FETCH NEXT FROM CursorTest INTO ©OrderlD, ©CustomerlD

PRINT Row + CAST(©Counter AS varchar) + has a SalesOrderlD of +

CAST (©OrderlD AS varchar) + and a CustomerlD of + CAST (©CustomerlD AS varchar)

WHILE (©Counter<=5) AND (©©FETCH STATUS=0) BEGIN

SELECT ©Counter = ©Counter + 1

FETCH NEXT FROM CursorTest INTO ©OrderlD, ©CustomerlD

PRINT Row + CAST(©Counter AS varchar) + has a SalesOrderlD of +

CAST (©OrderlD AS varchar) + and a CustomerlD of + CAST (©CustomerlD AS varchar)

Ha первый взгляд может показаться, что такое изменение является незначительным, но после повторного вызова сценария на выполнение обнаруживаются весьма существенные различия:

Row 1 has а SalesOrderlD of 43659 and a CustomerlD of 676 Row 2 has a SalesOrderlD of 43660 and a CustomerlD of 117 Row 3 has a SalesOrderlD of 43661 and a CustomerlD of 442 Row 4 has a SalesOrderlD of 43662 and a CustomerlD of 227 Row 5 has a SalesOrderlD of 43663 and a CustomerlD of 510 Row 6 has a SalesOrderlD of 43664 and a CustomerlD of 3 97

Msg 16916, Level 16, State 1, Line 13

A cursor with the name CursorTest does not exist.

Msg 16916, Level 16, State 1, Line 17

A cursor with the name CursorTest does not exist.

Msg 16916, Level 16, State 1, Line 18

A cursor with the name CursorTest does not exist.

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

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

Обеспечение прокрутки

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

Курсор FORWARD ONLY

Курсор FORWARD ONLY называется однонаправленным и полностью соответствует своем) названию. Указанный метод прокрутки применяется по умолчанию, а поскольку в приведенных выше примерах ничего не бьто сказано о том, какова последовательность обработки строк в применяемьгх курсорах, то вполне очевидно, что до сих пор в данной главе мы рассматривали только однонаправленные курсоры. Если используется однонаправленный курсор, то единственным допустимым оператором перемещения в курсоре является FETCH NEXT. Это означает, что необходимо выполнить все предусмотренные действия с каждой строкой и только после этого переходить к следующей, поскольку вслед за таким перемещением возврат к предыдущей строке становится возможным лишь путем закрытия и повторного открытия курсора.

Курсор SCROLLABLE

Курсор SCROLLABLE (прокручиваемый) также полностью соответствует своему названию. Он допускает прокрутку содержащихся в нем строк назад и вперед по мере необходимости. Если используется один из API-интерфейсов (ODBC, OLE DB, DB-Lib), то, в зависимости от объектной модели, с которой приходится сталкиваться, часто допускается перемещение непосредственно к определенной строке. А при использовании моделей доступа ADO и ADO.NET предоставляется также возможность легко пересортировать данные и ввести дополнительные фильтры.

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