Объявление курсора

Курсоры, хранимые процедуры и триггеры в SQL Server

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

Введение в курсоры

Как уже давно мог понять (или знать) читатель, характерным для реляционных баз данных является то, что обычно они оперируют множествами строк (таблицами). В SQL, кроме того, предусмотрены объекты, которые указывают на (результирующее) множество записей или определенную строку множества строк. Эти объекты называют курсорами (cursors) < $I[]курсор (cursor)>.

SQL Server поддерживает два различных типа курсоров: курсоры T-SQL и курсоры API (программного интерфейса приложения). API-курсоры создаются внутри приложения, в котором используются объекты ADO, OLE DB, DBLibrary или ODBC, и в данном разделе не рассматриваются.

В T-SQL имеется четыре типа курсоров со множеством характеристик. Основными из этих характеристик являются: возможность отражать изменения в исходных данных, осуществлять прокрутку во множестве строк и модифицировать множество строк.

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

Объявление курсора

В T-SQL для объявления курсоров предназначена инструкция DECLARE…CURSOR, а множество строк, на которые указывает курсор, определяется инструкцией SELECT. Синтаксис инструкции DECLARE…CURSOR < $I[] DECLARE…CURSOR > следующий[1]:

СИНТАКСИС

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [,... n ] ] ]

Здесь:

cursor_name — имя курсора.

LOCAL указывает на то, что область действия курсора является локальной для пакета (batch), хранимой процедуры или триггера, где создан курсор. На курсор можно ссылаться посредством локальной переменной в пакете, хранимой процедуре, триггере или посредством OUTPUT-параметра хранимой процедуры. Параметр OUTPUT используется для передачи локального курсора вызывающему пакету, хранимой процедуре или триггеру.

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

FORWARD_ONLY указывает на то, что перемещение по строкам курсора возможно только от первой к последней строке. Если FORWARD_ONLY используется без ключевых слов STATIC, KEYSET или DYNAMIC, курсор «работает» как DYNAMIC -курсор. Когда не определяется ни FORWARD_ONLY, ни SCROLL, FORWARD_ONLY является параметром по умолчанию, если только не указываются ключевые слова STATIC, KEYSET или DYNAMIC. Курсоры STATIC, KEYSET и DYNAMIC по умолчанию устанавливаются в SCROLL. В отличие от API-баз данных, таких как ODBC и ADO, FORWARD_ONLY поддерживается с помощью Transact-SQL-курсоров STATIC, KEYSET и DYNAMIC. FAST_FORWARD и FORWARD_ONLY являются взаимоисключающими: если один задается, другой не может быть задан.

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

KEYSET указывает на то, что, когда курсор открывается, число и порядок строк в курсоре являются фиксированными. Набор ключей, которые уникальным образом идентифицируют строки, встроен в таблицу в базе данных tempdb, известнуюкак keyset. Изменения неключевых значений в базовых таблицах, внесенные либо владельцем курсора, либо выполненные другими пользователями, являются видимыми, когда владелец прокручивает (scrolls around) курсор. Вставки, выполненные другими пользователями, не являются видимыми (вставки не могут выполняться посредством курсора Transact-SQL-сервера). Если строка удаляется, попытка выборки строки возвращает значение переменной @@FETCH_STATUS, равное -2. Обновление ключевых значений курсора извне подобно удалению старой строки, за которым следует вставка новой строки. Строка с новыми значениями невидима, и попытки выборки строки со старыми значениями возвращают значение переменной @@FETCH_STATUS, равное -2. Новые значения видимы, если обновление выполняется посредством курсора путем определения предложения WHERE CURRENT OF.

DYNAMIC определяет курсор, который отражает все изменения данных, внесенные в строки в его результирующем множестве, когда вы «прокручиваете» курсор. Значения данных, порядок и число строк могут изменяться в каждой выборке. Опция выборки ABSOLUTE не поддерживается динамическими курсорами.

FAST_FORWARD указывает курсор FORWARD_ONLY, READ_ONLY с разрешенными возможностями оптимизации производительности. FAST_FORWARD не может быть определен, если также определяются SCROLL или FOR_UPDATE. Опции FAST_FORWARD и FORWARD_ONLY являются взаимоисключающими.

READ_ONLY запрещает изменение данных курсора. На курсор не может выполняться ссылка в предложении (clause) WHERE CURRENT OF в операторе UPDATE или DELETE.

SCROLL_LOCKS указывает, что позиционированные обновления или удаления, выполненные посредством курсора, являются гарантированно успешными. SQL Server блокирует строки, когда они считываются в курсор, чтобы обеспечивалась их доступность для последующих модификаций. Нельзя использовать SCROLL_LOCKS вместе с FAST_FORWARD.

OPTIMISTIC позволяет выполнять обновления строк как внутри курсора, так и вне его. SQL Server не блокирует строки, когда они считываются в курсор. Поэтому, если строка была модифицирована после ее чтения курсором, попытка обновления или удаления (выполняемые курсором) обычно завершается неуспешно. Опция OPTIMISTIC не может определяться, если также определяется FAST_FORWARD.

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

select_statement — это обычная инструкция SELECT, которая определяет результирующий набор для курсора. Ключевые слова COMPUTE, COMPUTE BY, FOR BROWSE и INTO недопустимы внутри select_statement при объявлении курсора.

FOR UPDATE [ OF column_name [,... n ]] определяет столбцы в курсоре, доступные для обновления. При использовании OF column _ name [,... n ] можно модифицировать только перечисленные столбцы. Если FOR UPDATE используется без списка столбцов, модифицироваться могут все столбцы (если только не применена опция READ_ONLY).

В T-SQL можно объявлять переменные типа CURSOR. При этом оператор DECLARE…CURSOR не создает курсор: курсор создается при помощи оператора SET. Например,

DECLARE cursor_name CURSOR
LOCAL
FORWARD_ONLY
FOR SELECT МодельИзделия FROM Изделия WHERE КодОргПрод > 1

DECLARE @CursorVariable CURSOR

SET @CursorVariable =cursor_name

Для создания переменной типа CURSOR необязательно сначала объявлять курсор (идентификатор курсора). Можно объявить такую переменную, а затем назначить ей курсор без указания идентификатора курсора:

DECLARE @CursorVariable CURSOR

SET @CursorVariable CURSOR

LOCAL
FORWARD_ONLY
FOR SELECT МодельИзделия FROM Изделия WHERE КодОргПрод > 1