ТемаViii. Создание и использование курсоров

1. Понятие курсора

Курсор представляет собой результирующий набор дан­ных, сформированный SQL Server, в котором можно выполнять операции над отдельными строками. Этим он отличается от стан­дартных результирующих наборов, в которых операции выпол­няются сразу над всеми строками. Благодаря курсорам можно по­строчно обрабатывать данные, содержащиеся в некоторой таб­лице. С помощью курсоров SQL Server можно перемещаться по результирующему набору в обоих направлениях.

Курсоры можно использовать:

• в блоке операторов управления выполнением про­граммы;

• внутри хранимой процедуры;

• внутри триггера.

Каждый курсор должен иметь, по крайне мере, четыре компо­нента, которые должны следовать в таком порядке:

• объявление курсора (оператор DECLARE);

• открытие курсора(оператор OPEN);

• выборка данных из курсора (оператор FETCH);

• закрытие и освобождение курсора (операторы CLOSE и DEALLOCATE).

Оператор DECLARE содержит определённое пользовате­лем имя, применяемое для ссылки на результирующий набор данных, а также оператор SQL SELEСT, который генерирует этот результирующий набор. Оператор DECLARE можно представить в виде временной таблицы, которая содержит указатель на реаль­ный источник данных.

Синтаксис объявления курсора:

DECLARE имя_курсора [INSENSI­TIVE][SCROLL]CURSOR

FOR оператор_SELECT

[FOR{READ ONLY I UPDATE[OF список_столбцов]}]

Расширенный синтаксис оператора объявления курсора( принят в SQL Server 2000):

DECLARE имя_курсора CURSOR

[LOCAL | GLOBAL]

[FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | FAST_FORWARD | DYNAMIC]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]

FOR оператор_ SELECT

[FOR{READ ONLY | UPDATE[OF список_столбцов]}]

Синтаксические элементы первого варианта оператора объяв­ления курсора:

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

SCROLL – разрешает использование следующих FETCH- команд: PRIOR, FIRST, LAST, ABSOLUTEn и RELATIVEn.

Оператор_SELECT – это оператор SQL SELECT. При ис­пользовании следующих директив оператора SQL курсор объ­является как INSENSITIVE: DISTINCT, UNION, GROUP BY и(или) HAVING.

READ ONLY - запрещает обновления курсора.

UPDATE[OF список_столбцов] - разрешает обновление курсора. Необязательная директива [OF список_столбцов] опре­деляет, какие столбцы в курсоре можно обновлять.

Синтаксические элементы второго варианта оператора объяв­ления курсора:

FORWARD_ONLY – указывает, что для навигации кур­сора будет использоваться только команда FETCH NEXT. Эта опция используется для минимизации ресурсов и блокировок курсора.

STATIC (статистический курсор) – модификации кур­сора запрещены и изменения, внесённые в базовые таблицы по­сле открытия курсора, не будут отражены в курсоре. Эта опция используется, если курсор предназначен только для чтения, а также для минимизации ресурсов и блокировок курсора.

KEYSET – указывает на фиксированный порядок членов курсора. В курсоре при этом используются только ключи базовых таблиц. KEYSET-курсор переходит в STATIC-курсор, если ба­зовая таблица не содержит уникальных индексов или первичных ключей.

DINAMIC (динамический курсор) – изменения, внесён­ные в базовую таблицу, после открытия курсора, будут отражены в курсоре. Эту опцию используют для достижения согласованнности данных. Эта опция требует значительных затрат ресурсов по сравнению с KEYSET и STATIC.

FAST_FORWARD - определяет курсор как FOR­WARD_ONLY и READ_ONLY, используется для оптимизации и достижения максимальной производительности.

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

OPTIMISTIC – при чтении данных в курсор они не бло­кируются. Используют при одновременном доступе к данным курсора нескольких пользователей.

Пример:

1) стандартный курсор:

DECLARE pub_crsr CURSOR

FOR

SELECT pub_id, pub_name

FROM Publishers

2) курсор, предназначенный только для чтения:

DECLARE pub_crsr CURSOR

FOR

SELECT pub_id, pub_name

FROM Publishers

FOR READ ONLY

3) курсор, допускающий обновления:

DECLARE pub_crsr CURSOR

FOR

SELECT pub_id, pub_name

FROM Publishers

FOR UPDATE