Видалення та оновлення даних з використанням курсора

Курсори у прикладних програмах часто використовують для послідовного перегляду даних. Якщо курсор не пов'язаний з операцією групування, то фактично кожен рядок курсора суворо відповідає тільки одному рядку вихідної таблиці, і в цьому випадку курсор зручно використовувати для оперативного корегування даних. В стандарті визначені операції модифікації даних, пов'язаних з курсором. Операція видалення рядка, пов'язаного з поточним показчиком курсора, має наступний синтаксис:

DELETE FROM <ім'я_ таблиці> WHERE CURRENT OF <ім'я кypcopa>

Якщо вказаний в операторі курсор відткритий, встановлений на деякий рядок визначеної таблиці, то поточний рядок курсора видаляється, а він позиціонується перед наступним рядком. Таблиця, що вказана в розділі FROM оператора DELETE, повинна бути вказана в самому зовнішньому розділі FROM специфікації курсора.

Якщо необхідно прочитати наступний рядок курсора, то слід знову виконати оператор FETCH NEXT.

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

UPDATE <ім'я таблиці> SET <ім'я стовпця 1> = {<значення> | NULL}

[{<ім'я _стовпця_N> = {<значення> | NULL}}...]

WHERE CURRENT OF <ім'я курсора>

Одним оператором позиційного оновлення може бути замінено декілька значень стовпців рядка таблиці, що відповідає поточній позиції курсора. Після виконання операції модифікації позиція курсора не змінюється. Для того щоб можна було використовувати позиційні оператори видалення (DELETE) і модифікації (UPDATE), курсор повинен задовольняти відповідним вимогам. Згідно стандарту SQL1, це наступні вимоги:

• Запит, пов'язаний з курсором, повинен зчитувати дані з однієї вихідної таблиці, тобто в реченні FROM запита SELECT, пов'язаного з визначенням курсора (DECLARE CURSOR), повинна бути задана лише одна таблиця.

• В запиті не може бути присутнім параметр впорядкування ORDER BY. Для того щоб зберіглася взаємна однозначна відповідність рядків курсора і вихідної таблиці, курсор не повинен ідентифікувати впорядкований набір даних.

• В запиті не повинно бути присутнім ключове слово DISTINCT.

• Запит не повинен вміщувати операції групування, тобто в ньому не повинно бути присутнім речення GROUP BY або HAVING.

• Користувач, який бажає використати операції позиційного видалення або оновлення, повинен мати відповідні права на виконання даних операцій над базовою таблицею.

Використання курсора для операції оновлення значно ускладює роботу з подібним курсором з боку СКБД, тому операції, пов'язані з позиційною модифікацією, виконуються значно повільніше, ніж операції з курсорами, які використовуються тільки для читання. Саме тому рекомендується обов'язково вказувати в операторі визначення курсора речення READ ONLY, якщо ви не збираєтесь використовувати даний курсор для операцій модифікації. За замовчуванням, якщо немає додаткових вказівок, СКБД створює курсор з можливостями модифікації.

Курсори — зручний засіб для формування бізнес-логики додатків, але слід пам'ятати, що якщо ви відкриваєте курсор з можливістю модифікації, то СКБД блокує всі рядки базової таблиці, що увійшли до вашого курсора, та тим самим блокує роботу інших користувачів з даною таблицею. Для того щоб звести до мінімуму кількість необхідних блокувань, при роботі інтерактивних програм слід дотримуватися наступних правил:

• Необхідно робити транзакції якомога коротшими.

• Необхідно виконувати оператор завершення COMMIT після кожного запита і якомога скоріше після змін зроблених програмою,

• Необхідно уникати програм, в яких здійснюється інтенсивна взаємодія з користувачем або здійснюється перегляд дуже великої кількості рядків даних.

• Якщо можливо, то краще не використовувати курсори прокручування (SCROLL), тому що вони потребують блокування всіх вибраних рядків , пов'язаних з відкритим курсором.

• Використання простого послідовного курсора дозволить системі розблокувати поточний рядок, як тільки буде виконана операція FETCH, що мінімізує блокування інших користувачів, які працюють паралельно з вами і використовують ті ж самі таблиці.

• Якщо можливо, визначайте курсор як READ ONLY.

Проте, коли ми розглядали моделі «клієнт — сервер», застосовувані в БД, то визначили, що в розвинутих моделях серверів баз даних більша частина бізнес-логики клієнтського додатку виконується саме на сервері, а не на клієнті. Для цього використовуються спеціальні об'єкти, які називаються збережуваними процедурами і зберігаються в БД, як таблиці та інші базові об'єкти.

В зв'язу з цим фактом, курсори, використовувані в додатках, звичайно розподіляються на курсори сервера та курсори клієнта.

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

Курсори клієнта — це ті курсори, визначені в прикладних програмах, що виконуються на боці клієнта. Набір рядків, пов'язаний з даним курсором, пересилається на комп‘ютер клієнта і там оброблюється. Якщо з курсором пов'язаний великий набір даних, то операція пересилання набору рядків, пов'язаних з курсором, може зайняти значний час та значні ресурси мережі і клієнтського комп'ютера.

Звичайно, курсори сервера економічніші і швидше виконуються. Тому краще трансформувати логіку роботи додатка таким чином, щоб якомога частіше замість курсорів клієнта використовувати курсори сервера.