Модификация данных с помощью курсора

До сих пор в настоящей главе автор старался не касаться напрямую темы модификации данных непосредственно с помощью курсора. Теперь мы полностью подготовлены к усвоению этой темы, поэтому рассмотрим, как осуществляется обновление и удаление строк в пределах курсора.

Прежде всего необходимо отметить, что в операциях с курсорами затрагиваются от дельные строки, а не данные, представленные в виде множеств, поэтому требуется специальный синтаксис, позволяющий передать СУБД SQL Server указание на то, какая строка должна бьггь обновлена. К счастью, этот синтаксис довольно простой, к тому же в этой книге уже было показано, как выполняются операции UPDATE и DELETE.

В конечном итоге происходит обновление или удаление данньгх в таблице, которая является основополагающей по отношению к курсору. Выполнение этих операций является таким же простым, как и выполнение аналогичных операций с помощью операторов UPDATE и DELETE, которые неоднократно рассматривались в настоящей книге, но требует применения конструкции WHERE, позволяющей указать на строку курсора. Для этого достаточно добавить небольшую дополнительную конструкцию в оператор DELETE или UPDATE: WHERE CURRENT OF <cursor name>

Очевидно, что это не представляет никакой сложности. Исключительно ради эксперимента воспользуемся этой конструкцией и реализуем курсор с учетом указанного синтаксиса: USE AdventureWorks

/* Создается таблица, с которой будут проводиться эксперименты в этот раз */

SELECT SalesOrderlD, CustomerlD

INTO CursorTable

FROM Sales.SalesOrderHeader

WHERE SalesOrderlD BETWEEN 43661 AND 43665

-- Создание уникального индекса в форме первичного ключа ALTER TABLE CursorTable

ADD CONSTRAINT PKCursor

PRIMARY KEY (SalesOrderlD)

/* При выполнении оператора SELECT INTO было автоматически сформировано значение

** IDENTITY, но решено использовать значение SalesOrderlD, сформированное

** в программе, поэтому необходимо ввести в действие параметр IDENTITY INSERT,

** чтобы можно было переопределить идентификационное значение

SET IDENTITY INSERT CursorTable ON

-- Объявление курсора DECLARE CursorTest CURSOR

SCROLL -- Параметр, позволяющий выполнять прокрутку назад и проверять

-- результаты внесения изменений

KEYSET FOR

SELECT SalesOrderlD, CustomerlD FROM CursorTable

-- Объявления двух вспомогательных переменных DECLARE ©SalesOrderlD int

DECLARE ©CustomerlD varchar(5)

-- Открытие курсора и получение первой строки

OPEN CursorTest

FETCH NEXT FROM CursorTest INTO ©SalesOrderlD, ©CustomerlD

-- Обработка данных в цикле WHILE ©@FETCH STATUS=0 BEGIN

IF (©SalesOrderlD % 2 = 0) -- Четное число, поэтому производится

-- обновление данных

BEGIN

-- Внесение изменения. Но на этот раз для этой цели используется курсор UPDATE CursorTable

SET CustomerlD = -99999

WHERE CURRENT OF CursorTest

ELSE -- Это число должно быть нечетным, поэтому

-- производится удаление данных

BEGIN

-- Удаление строки для ознакомления с тем, как осуществляется -- эта операция DELETE CursorTable

WHERE CURRENT OF CursorTest

FETCH NEXT FROM CursorTest INTO ©SalesOrderlD, ©CustomerlD

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

FETCH FIRST FROM CursorTest INTO ©SalesOrderlD, ©CustomerlD

-- Повторная обработка в цикле WHILE @@FETCH STATUS != -1 BEGIN

IF @@FETCH STATUS = -2 BEGIN

PRINT MISSING! It probably was deleted.

ELSE

BEGIN

PRINT CAST(©SalesOrderlD AS varchar) + + CAST(©CustomerlD AS varchar)

FETCH NEXT FROM CursorTest INTO ©SalesOrderlD, ©CustomerlD

-- Удаление ненужных объектов CLOSE CursorTest

DEALLOCATE CursorTest

DROP TABLE CursorTable

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

В данном примере снова используется операция деления по модулю (%), которая уже рассматривалась в данной книге. Следует учитывать, что эта операция не возвращает ничего, кроме остатка от деления. Таким образом, если остаток от деления некоторого числа на 2 равен нулю, это означает, что рассматриваемое число- четное.

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

(5 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

MISSING! It probably was deleted. 43662 *

MISSING! It probably was deleted. 43664 *

MISSING! It probably was deleted.

В выводе этого сценария обнаруживается несколько строк 1 row (s) affected , которые представляют собой возвращаемые сообщения, касающиеся любой строки, которая была затронута действием операторов UPDATE и DELETE. После ознакомления с выводом, соответствующим последнему варианту результирующего набора, можно сразу же обнаружить, что были удалены все нечетные идентификаторы (согласно указаниям, приведенным в коде) и обновлены четные идентификаторы путем замены новыми значениями CustomerlD.

При этом мы не прибегали к каким-либо остроумным программистским приемам; достаточно было применить конструкцию WHERE с ключевым словом CURRENT.

Резюме

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

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

Но несмотря на сказанное, при любой возможности следует избегать использования курсоров. Для обеспечения функционирования курсоров затрачивается очень много ресурсов, и применение операций с курсорами вместо реляционных операций почти всегда приводит к снижению производительности больше чем в два раза. Для МНОГР1Х программистов (особенно если они прошли подготовку в среде мэйнфреймов или базы данных dBase) подход, предусматривающий построчную обработку данных, является наиболее привычным. Но в среде реляционных бах данных более приемлемыми являются операции, предназначенные для манипулирования строками на уровне множеств, т.е. реляционные операции. Поэтому курсоры следует использовать лишь в тех обстоятельствах, когда без них невозможно обойтись.