Создание, применение и управление курсорами

 

 

Цель работы– изучение назначения и типов курсоров, синтаксиса и семантики команд языка Transact – SQL для создания и открытия курсоров, выборки данных из курсора и изменения строк таблиц с помощью курсоров, удаления данных, закрытия и освобождения курсоров, а также приобретения навыков их применения и управления с помощью команд и системных хранимых процедур SQL Server 2000.

 

Набор данных, имеющийся в таблице базы данных, называется полным набором строк таблицы (complete set of rows). Набор строк, возвращаемый команду SELECT, называется результирующим набором данных (result set). Он является частью полного набора, отфильтрованного горизонтально с помощью условий, заданных в разделе WHERE. Можно в результирующий набор не включать те или иные столбцы, применяя вертикальную фильтрацию.

Результирующие наборы могут содержать сотни тысяч строк, и клиентские приложения не всегда справляются с таким объемом данных. Для решения этой проблемы используются курсоры, которые представляют собой окна, налагаемые на результирующие набором данных и выделяющие требуемую часть данных. Перемещая созданный курсор, можно получить доступ ко всем результирующим данным. Таким образом, курсоры SQL Server 2000 представляют собой механизм обмена данными между сервером и клиентом, который минимизирует ресурсы клиентского приложения. Однако всегда, когда это возможно, следует избегать использования курсоров и применять команды SELECT, UPDATE, DELETE и INSERT.

MS SQL Server 2000 поддерживает три вида курсоров:

1. Курсоры Transact– SQL, которые применяются внутри триггеров, хранимых процедур и сценариев;

2. Курсоры сервера, которые действуют на сервере и реализуют программный интерфейс приложений для ODBC, OLE DB и DB_Library;

3. Курсоры клиента, которые реализуются на клиенте и выбирают весь результирующий набор для ускорения обработки данных.

Один курсор может базироваться на нескольких таблицах, расположенных в разных базах данных. Операция считывания определенных в курсоре данных называется выборкой (fetch). Если за одну операцию курсор позволяет выбрать несколько строк таблицы, то такой курсор называется блочным. По способу просмотра данных курсоры бывают последовательные (forward only), которые обеспечивают просмотр строк только в одном направлении – от начала к концу, и прокручиваемые, которые допускают просмотр в обоих направлениях и переход к произвольной строке.

По представляемым возможностям курсоры делятся на четыре типа: статические, динамические, последовательные и ключевые. Тип курсора определяется на стадии его создания и не может быть изменен.

Статический курсор (static cursor) называют также курсорами моментального снимка (snapshot cursor). При открытии такого курсора сервер выбирает все данные, соответствующие заданным критериям, и сохраняет результирующий набор строк в системной базе данных tempdb без изменения, если даже исходные строки и изменяются. Поэтому статический курсор всегда открывается в режиме “только для чтения”.

Динамические курсоры (dynamic cursor) противоположны статическим. При их использовании не создается полная копия исходных данных, а выполняется динамическая выборка данным из исходных таблиц только при обращении пользователя к тем или иным данным, при этом на время выборки соответствующие строки блокируются сервером. После выборки строк исходные строки могут изменяться пользователями, но эти изменения уже не отражаются в выбранных строках. С другой стороны, изменения в выбранных строках не будут видны другим пользователям, пока они не будут подтверждены (committed).

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

Курсоры, зависящие от набора ключей (keyset-driven cursor), или ключевые курсоры, построены на основе уникальных идентификаторов. Множество всех уникальных идентификаторов (ключей) строк таблиц базы данных называется набором ключей. Сервер блокирует строки исходных таблиц только на время составления таблицы ключей.

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

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

Формат команды языка Transact – SQL для создания курсора следующий:

 

DECLARE Имя курсора CURSOR [LOCAL/GLOBAL] [FORWARD_ONLY\SCROLL] [STATIC\KEYSET\DINAMIC\FAST_FORWARD] [READ_ONLY\SCROLL_LOCKS\OPTIMISTIC] [TYPE_WARNING]

FOR select_statement

[FOR UPDATE [OF column_name [,…n]]].

Для открытия курсора используется команда

OPEN {{[GLOBAL] Имя курсора}\Имя переменной}.

 

Для выборки данных необходимо применять команду

FETCH [[NEXT\PRIOR\FIRST\LAST\ABSOLUTE {n\@nvar}\ RELATIVE {n\@nvar}

] FROM

]

{{[GLOBAL] Имя курсора}\Имя переменной} []INTO @ Имя переменной [,…n]]

 

Команды UPDATE, DELETE, CLOSEи DEALLOCATEпозволят соответственно произвести изменение данных, удаление данных, закрытие и освобождение курсора.

 

Задание 1. Создать курсор curs для таблицы authors базы данных Pubs, выполнил следующие действия:

 

1. Создание курсора: DECLARE curs cursor

GLOBAL SCROLL KEYSET TYPE_WARNING FOR SELECT au_lname, au_fname, phone, title,

price, advance, sales = ytd_sales

FROM titleauthor INNER JOIN authors

ON titleauthor. au_id = authors. au_id

INNER JOIN titles

ON titleauthor. title_id = titles. titles_id

WHERE authors. state <> ‘CA’ FOR UPDATE.

 

2. Открытие курсора: OPEN curs

 

3. Выборка данных:

DECLARE @@Str1 char (5),

@@VFName varchar (20),

@@VLName varchar (40),

@@VPhone char (12),

@@ VTitle varchar (80),

@@VPrice money,

@@VAdrance money,

@@VSales int,

@@Count1 timyint,

@@Var1 money

SET @@Count = 1

SET @@Var1 = 0

WHILE @@Count1 <@@CURSOR_ROWS BEGIN

IF @@Count = 1

FETCH ABSOLUTE 1 FROM CURS INTO @@VFName,

@@VLName, @@VPhone, @@VTitle, @@VPrice,

@@VAdrance, @@VSales

ELSE

FETCH curs INTO @@VFName,

@@VLName, @@VPhone, @@VTitle, @@VPrice,

@@VAdrance, @@VSales

SET @@Count1 = @@Count1+1

SET @@Var1 = @@Var1+@@Vprice*@@VSales-@@VAdrance

END

SELECT ‘Итого прибыли:’, @@Var1

 

4. Закрытие и освобождение курсора: CLOSE curs

DEALLOCATE curs.