Удаление и обновление данных с использованием курсора

Оператор закрытия курсора

Оператор закрытия курсора имеет простои синтаксис, он выглядит следующим образом

CLOSE <имя_курсора>

Оператор закрытия курсора закрывает временную таблицу, созданную опера­тором открытия курсора, и прекращает доступ прикладной программы к этому объекту Единственным параметром оператора закрытия является имя курсора

Оператор закрытия может быть выполнен в любой момент после оператора от­крытия курсора.

В некоторых коммерческих СУБД кроме оператора закрытия курсора использу­ется еще оператор деактивации (уничтожения) курсора. Например, в MS SQL Server 7.0 наряду с оператором закрытия курсора используется оператор

DEALLOCATE <имя_курсора>

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

При выполнении оператора DEALLOCATE SQL Server освобождает разделяемую па­мять, используемую командой описания курсора DECLARE. После выполнения эюй команды невозможно выполнение команды OPEN для- данного курсора.

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

Если указанный в операторе курсор открыт и установлен на некоторую строку, и курсор определяет изменяемую таблицу, то текущая строка курсора удаляет­ся, а он позиционируется перед следующей строкой. Таблица, указанная в раз­деле FROM оператора DELETE, должна быть таблицей, указанной в самом внешнем разделе FROM спецификации курсора.

Если нам необходимо прочитать следующую строку куроера, то надо снова вы­полнить оператор FETCH NEXT.

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

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

Для того чтобы можно было применять позиционные операторы удаления (DELETE) и модификации (UPDATE), курсор должен удовлетворять определенным требова­ниям. Согласно стандарту SQL1, это следующие требования:

* Запрос, связанный с курсором, должен считывать данные из одной исходной таблицы, то есть в предложении FROM запроса SELECT, связанного с определе­нием курсора (DECLARE CURSOR), должна быть задана только одна таблица

* В запросе не может присутствовать параметр упорядочения ORDER BY Для того чтобы сохранялось взаимно однозначное соответствие строк курсора и ис­ходной таблицы, курсор не должен идентифицировать упорядоченный набор данных

* В запросе не должно присутствовать ключевое слово DISTINCT

* Запрос не должен содержать операции группировки, то есть в нем не должно присутствовать предложение GROUP BY или HAVING

* Пользователь, который хочет применить операции позиционною удаления или обновления, должен иметь соответствующие права на выполнение дан­ных операции над базовой таблицей (О правах и привилегиях пользователя мы поговорим в главе 13)

Использование курсора для операции обновления значительно усложняет работу с подобным курсором со стороны СУБД, поэтому операции, связанные с пози­ционной модификацией, выполняются гораздо медленнее, чем операции с кур­сорами, которые используются только для чтения Именно поэтому рекоменду­ется обязательно указывать в операторе определения курсора предложение READ ONLY, если вы не собираетесь использовать данный курсор для операций моди­фикации По умолчанию, если нет дополнительных указании, СУБД создает кур­сор с возможностью модификации

Курсоры — удобное средство для формирования бизнес-логики приложений, но следует помнить, что если вы открываете курсор с возможностью модификации, то СУБД блокирует все строки базовой таблицы, вошедшие в ваш курсор, и тем самым блокируется работа других пользователей с данной таблицей

Чтобы свести к минимуму количество требуемых блокировок, при работе интер­активных программ следует придерживаться следующих правил

* Необходимо делать транзакции как можно короче

* Необходимо выполнять оператор завершения COMMIT после каждого запроса и как можно скорее после изменений, сделанных программой

* Необходимо избегать программ, в которых осуществляется интенсивное взаи­модействие с пользователем или осуществляется просмотр очень большого количества строк данных

* Если возможно, то лучше не применять прокручиваемые курсоры (SCROLL), потому что они требуют блокирования всех строк выборки, связанных с от­крытым курсором

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

* Если возможно, определяйте курсор как READ ONLY

* Однако когда мы рассматривали модели «клиент—сервер», применяемые в БД, то определили, что в развитых моделях серверов баз данных большая часть бизнес-логики клиентского приложения выполняется именно на сервере, а не на клиенте. Для этого используются специальные объекты, которые называют­ся хранимыми процедурами и хранятся в БД, как таблицы и другие базовые объекты.

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

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

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

Дата добавления: 2015-05-09