13. Лекція: Курсори: принципи роботи:
Мета: Дати визначення курсору. Привести опис його типів і поведінки: статичні, динамічні, послідовні і ключові курсори. Описати принципи управління курсором: створення і відкриття курсору, читання даних, закриття курсору. Навести приклади програмування курсору
ПЛАН
Оголошення курсору
Відкриття курсору
Вибірка даних з курсору
Зміна і видалення даних
Закриття курсору
Запит до реляційної бази даних звичайно повертає декілька рядів (записів) даних, але додаток за один раз обробляє лише один запис. Навіть якщо він має справу одночасно з декількома рядами (наприклад, виводить дані у формі електронних таблиць), їх кількість як і раніше обмежена. Крім того, при модифікації, видаленні або додаванні даних робочою одиницею є ряд. В цій ситуації на перший план виступає концепція курсору, і в такому контексті курсор – вказівник на ряд.
Курсор в SQL – це область в пам'яті бази даних, яка призначена для зберігання останнього оператора SQL. Якщо поточний оператор – запит до бази даних, в пам'яті зберігається і рядок даних запиту, званий поточним значенням, або поточним рядком курсору. Вказана область в пам'яті пойменована і доступна для прикладних програм.
Звичайно курсори використовуються для вибору з бази даних деякої підмножини збереженої в ній інформації. В кожний момент часу прикладною програмою може бути перевірено один рядок курсору. Курсори часто застосовуються в операторах SQL, вбудованих в написані на язиках процедурного типу прикладні програми. Деякі з них неявно створюються сервером бази даних, тоді як інші визначаються програмістами.
Відповідно до стандарту SQL при роботі з курсорами можна виділити наступні основні дії:
створення або оголошення курсору ;
відкриття курсору, тобто наповнення його даними, які зберігаються в багаторівневій пам'яті ;
вибірка з курсору і зміна з його допомогою рядків даних;
закриття курсору, після чого він стає неприступним для призначених для користувача програм;
звільнення курсору, тобто видалення курсору як об’єкту, оскільки його закриття необов’язково звільняє асоційовану з ним пам'ять.
В різних реалізаціях визначення курсору може мати деякі відмінності. Так, наприклад, іноді розробник повинен явним чином звільнити пам'ять, що виділяється для курсору. Після звільнення курсору асоційована з ним пам'ять також звільняється. При цьому стає можливим повторне використовування його імені. В інших реалізаціях при закритті курсору звільнення пам'яті відбувається неявним чином. Відразу після відновлення вона стає доступною для інших операцій: відкриття іншого курсору і т.д.
В деяких випадках вживання курсору неминуче. Проте по можливості цього слід уникати і працювати із стандартними командами обробки даних: SELECT, UPDATE, INSERT, DELETE. Крім того, що курсори не дозволяють проводити операції зміни над всім об’ємом даних, швидкість виконання операцій обробки даних за допомогою курсору помітно нижче, ніж у стандартних засобів SQL.
SQL Server підтримує три види курсорів:
курсори SQL застосовуються в основному усередині тригерів, збережених процедур і сценаріїв;
курсори серверу діють на сервері і реалізують програмний інтерфейс додатків для ODBC, OLE DB, DB_Library;
курсори клієнта реалізуються на самому клієнті. Вони вибирають весь результуючий набір рядків з серверу і зберігають його локально, що дозволяє прискорити операції обробки даних за рахунок зниження втрат часу на виконання мережних операцій.
Різні типи розрахованих на багато користувачів додатків вимагають і різних типів організації паралельного доступу до даних. Деяким додаткам необхідний негайний доступ до інформації про зміни в базі даних. Це характерно для систем резервування квитків. В інших випадках, наприклад, в системах статистичної звітності, важлива стабільність даних, адже якщо вони постійно модифікуються, програми не зможуть ефективно відображати інформацію. Різним додаткам потрібні різні реалізації курсорів.
В середовищі SQL Server типи курсорів розрізняються по можливостях, що надаються. Тип курсору визначається на стадії його створення і не може бути змінений. Деякі типи курсорів можуть знаходити зміни, зроблені іншими користувачами в рядках, включених в результуючий набір. Проте SQL Server відстежує зміни таких рядків тільки на стадії звернення до рядка і не дозволяє відстежувати зміни, коли рядок вже лічений.
Курсори діляться на дві категорії: послідовні і прокручувані.
Послідовні дозволяють вибирати дані тільки в одному напрямі – від початку до кінця. Прокручувані ж курсори надають велику свободу дій – допускається переміщення в обох напрямах і перехід до довільного рядка результуючого набору курсору. Якщо програма здатна модифікувати дані, на які указує курсор, він називається прокручуваним і модифікується. Кажучи про курсори, не слід забувати про ізольованість транзакцій. Коли один користувач модифікує запис, інший читає її за допомогою власного курсору, більш того, він може модифікувати той же запис, що робить необхідним дотримання цілісності даних.
SQL Server підтримує курсори статичні, динамічні, послідовні і керовані набором ключів.
В схемі із статичним курсором інформація читається з бази даних один раз і зберігається у вигляді моментального знімка (за станом на деякий момент часу), тому зміни, внесені в базу даних іншим користувачем, не видимі. На час відкриття курсору сервер встановлює блокування на всі рядки, включені в його повний результуючий набір. Статичний курсор не змінюється після створення і завжди відображає той набір даних, який існував на момент його відкриття.
Якщо інші користувачі змінять в початковій таблиці включені в курсор дані, це ніяк не вплине на статичний курсор.
В статичний курсор внести зміни неможливо, тому він завжди відкривається в режимі "тільки для читання".
Динамічний курсор підтримує дані в "живому" стані, але це вимагає витрат мережних і програмних ресурсів. При використовуванні динамічних курсорів не створюється повна копія початкових даних, а виконується динамічна вибірка з початкових таблиць тільки при зверненні користувача до тих або інших даних. На час вибірки сервер блокує рядки, а всі зміни, що вносяться користувачем в повний результуючий набір курсору, будуть видні в курсорі. Проте якщо інший користувач вніс зміни вже після вибірки даних курсором, то вони не відобразяться в курсорі.
Курсор, керований набором ключів, знаходиться посередині між цими крайнощами. Записи ідентифікуються на момент вибірки, і тим самим відстежуються зміни. Такий тип курсору корисний при реалізації прокрутки назад – тоді додавання і видалення рядів не видні, поки інформація не оновиться, а драйвер вибирає нову версію запису, якщо в неї були внесені зміни.
Послідовні курсори не дозволяють виконувати вибірку даних у зворотному напрямі. Користувач може вибирати рядки тільки від початку до кінця курсору . Послідовний курсор не зберігає набір всіх рядків. Вони прочитуються з бази даних, як тільки вибираються в курсорі, що дозволяє динамічно відбивати всі зміни, що вносяться користувачами в базу даних за допомогою команд INSERT, UPDATE, DELETE. В курсорі доступний самий останній стан даних.
Статичні курсори забезпечують стабільний погляд на дані. Вони застосовні для систем "складування" інформації: додатків для систем звітності або для статистичних і аналітичних цілей. Крім того, статичний курсор краще за інші справляється з вибіркою великої кількості даних. Навпаки, в системах електронних покупок або резервування квитків необхідне динамічне сприйняття інформації, що обновляється, у міру внесення змін. В таких випадках використовується динамічний курсор. В цих додатках об”єм передаваних даних, як правило, невеликий, а доступ до них здійснюється на рівні рядків (окремих записів). Груповий доступ зустрічається дуже рідко.
Управління курсором реалізується шляхом виконання наступних команд:
DECLARE – створення або оголошення курсору ;
OPEN – відкриття курсору, тобто наповнення його даними;
FETCH – вибірка з курсору і зміна рядків даних за допомогою курсору;
CLOSE – закриття курсору ;
DEALLOCATE – звільнення курсору, тобто видалення курсору як об”єкту.
Оголошення курсору
В стандарті SQL для створення курсору передбачена наступна команда:
<створення_курсору>::=
DECLARE ім”я_курсору [INSENSITIVE][SCROLL] CURSOR
FOR SELECT_оператор
[FOR { READ_ONLY | UPDATE [Ім”я_колонки[,...n]]}]
При використовуванні ключового слова INSENSITIVE буде створений статичний курсор. Зміни даних не дозволяються, крім того, не відображаються зміни, зроблені іншими користувачами. Якщо ключове слово INSENSITIVE відсутнє, створюється динамічний курсор.
При вказівці ключового слова SCROLL створений курсор можна прокручувати в будь-якому напрямі, що дозволяє застосовувати будь-які команди вибірки. Якщо цей аргумент опускається, то курсор виявиться послідовним, тобто його перегляд буде можливий тільки в одному напрямі – від початку до кінця.
SELECT-оператор задає тіло запиту SELECT, за допомогою якого визначається результуючий набір рядків курсору.
При вказівці аргументу FOR READ_ONLY створюється курсор "тільки для читання", і ніякі модифікації даних не дозволяються. Він відрізняється від статичного, хоча останній також не дозволяє міняти дані. Як курсор "тільки для читання" може бути оголошений динамічний курсор, що дозволить відображати зміни, зроблені іншим користувачем.
Створення курсору з аргументом FOR UPDATE дозволяє виконувати в курсорі зміну даних або у вказаних стовпцях, або, за відсутності аргументу ім”я_колонки, у всіх стовпцях.
В середовищі MS SQL Server прийнятий наступний синтаксис команди створення курсору:
<створення_курсору>::=
DECLARE ім”я_курсору CURSOR [LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR SELECT_оператор
[FOR UPDATE [Ім”я_колонки[,...n]]]
При використанні ключового слова LOCAL буде створений локальний курсор, який видний тільки в межах пакету, тригера, збереженої процедури або призначеної для користувача функції, що створив його. Після закінчення роботи пакету, тригера, процедури або функції курсор неявно знищується. Щоб передати вміст курсору за межі конструкції, що створила його, необхідно привласнити його параметру аргумент OUTPUT.
Якщо вказано ключове слово GLOBAL, створюється глобальний курсор ; він існує до закриття поточного з’єднання.
При вказівці FORWARD_ONLY створюється послідовний курсор ; вибірку даних можна здійснювати тільки в напрямі від першого рядка до останнього.
При вказівці SCROLL створюється прокручуваний курсор ; звертатися до даних можна у будь-якому порядку і в будь-якому напрямі.
При вказівці STATIC створюється статичний курсор.
При вказівці KEYSET створюється ключовий курсор.
При вказівці DYNAMIC створюється динамічний курсор.
Якщо для курсору READ_ONLY вказати аргумент FAST_FORWARD, то створений курсор буде оптимізований для швидкого доступу до даних. Цей аргумент не може бути використаний спільно з аргументами FORWARD_ONLY і OPTIMISTIC.
В курсорі, створеному з вказівкою аргументу OPTIMISTIC, забороняється зміна і видалення рядків, які були змінені після відкриття курсору.
При вказівці аргументу TYPE_WARNING сервер інформуватиме користувача про неявну зміну типу курсору, якщо він несумісний із запитом SELECT.
Відкриття курсору
Для відкриття курсору і наповнення його даними з вказаного при створенні курсору запиту SELECT використовується наступна команда:
OPEN {{[GLOBAL]ім”я_курсору }
|@ім”я_змінної_курсору}
Після відкриття курсору відбувається виконання пов”язаного з ним оператора SELECT, вихідні дані якого зберігаються в багаторівневій пам'яті.
Вибірка даних з курсору
Відразу після відкриття курсору можна вибрати його вміст (результат виконання відповідного запиту) за допомогою наступної команди:
FETCH [[NEXT | PRIOR | FIRST | LAST
| ABSOLUTE {номер_рядка
| @змінна_номера_рядка}
| RELATIVE {номер_рядка |
@змінна_номера_рядка}]
FROM ]{{[GLOBAL ]ім”я_курсору }|
@ім”я_змінної_курсору }
[INTO @ім”я_змінної [,...n]]
При вказівці FIRST буде повернений найперший рядок повного результуючого набору курсору, який стає поточним рядком.
При вказівці LAST повертається самий останній рядок курсору. Вона ж стає поточним рядком.
При вказівці NEXT повертається рядок, що знаходиться в повному результуючому наборі зразу ж після поточної. Тепер вона стає поточною. За умовчанням команда FETCH використовує саме цей спосіб вибірки рядків.
Ключове слово PRIOR повертає рядок, що знаходиться перед поточною. Вона і стає поточною.
Аргумент ABSOLUTE {номер_рядка | @змінна_номера_рядка} повертає рядок по його абсолютному порядковому номеру в повному результуючому наборі курсору. Номер рядка можна задати за допомогою константи або як ім”я змінної, в якій зберігається номер рядка. Змінна повинна мати цілочисельний тип даних. Вказуються як позитивні, так і негативні значення. При вказівці позитивного значення рядок відлічується від початку набору, негативного – від кінця. Вибраний рядок стає поточним. Якщо вказано нульове значення, рядок не повертається.
Аргумент RELATIVE {кільк_рядка | @змінна_кільк_рядка} повертає рядок, що знаходиться через вказану кількість рядків після поточного. Якщо вказати негативне значення числа рядків, то буде повернений рядок, що знаходиться за вказану кількість рядків перед поточним. При вказівці нульового значення повернеться поточний рядок. Повернений рядок стає поточним.
Щоб відкрити глобальний курсор, перед його ім”ям вимагається вказати ключове слово GLOBAL. Ім”я курсору також може бути вказано за допомогою змінної.
В конструкції INTO @ім”я_змінної [,...n] задається список змінних, в яких будуть збережені відповідні значення стовпців рядка, що повертається. Порядок вказівки змінних повинен відповідати порядку стовпців в курсорі, а тип даних змінної – типу даних в стовпці курсору. Якщо конструкція INTO не вказана, то поведінка команди FETCH нагадуватиме поведінку команди SELECT – дані виводяться на екран.
Зміна і видалення даних
Для виконання змін за допомогою курсору необхідно виконати команду UPDATE в наступному форматі:
UPDATE ім”я_таблицы SET {ім”я_колонки = { DEFAULT | NULL | вираз}}[,...n]
WHERE CURRENT {{[GLOBAL] ім”я_курсору} |@ім”я_змінної_курсору}
За одну операцію можуть бути змінено декілька стовпців (комірок) поточного рядка курсору, але всі вони повинні належати одній таблиці.
Для видалення даних за допомогою курсору використовується команда DELETE в наступному форматі:
DELETE ім”я_таблицы
WHERE CURRENT {{[GLOBAL] ім”я_курсору} |@ім”я_змінної_курсору}
В результаті буде видалений рядок, встановлений поточним в курсорі.
Закриття курсору
CLOSE {ім”я_курсору | @ім”я_змінної_курсору}
Після закриття курсор стає неприступним для користувачів програми. При закритті знімаються всі блокування, встановлені в процесі його роботи. Закриття може застосовуватися тільки до відкритих курсорів. Закритий, але не звільнений курсор може бути повторно відкритий. Не допускається закривати невідкритий курсор.
Звільнення курсору
Закриття курсору необов'язково звільняє асоційовану з ним пам'ять. В деяких реалізаціях потрібно явним чином звільнити її за допомогою оператора DEALLOCATE. Після звільнення курсору звільняється і пам'ять, при цьому стає можливим повторне використовування імені курсору.
DEALLOCATE { ім”я_курсору | @ім”я_змінної_курсору }
Для контролю досягнення кінця курсору рекомендується застосовувати функцію: @@FETCH_STATUS
Функція @@FETCH_STATUS повертає:
0, якщо вибірка завершилася успішно;
-1, якщо вибірка завершилася невдало внаслідок спроби вибірки рядка, що знаходиться за межами курсору ;
-2, якщо вибірка завершилася невдало унаслідок спроби звернення до видаленого або зміненого рядка.
DECLARE abc CURSOR SCROLL FOR
SELECT * FROM Клієнт
Приклад 13.1. Оголошення курсору.
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM Клієнт
Приклад 13.2. Використовування змінної для оголошення курсору.
DECLARE abc CURSOR GLOBAL SCROLL FOR
SELECT * FROM Клієнт
OPEN abc
Приклад 13.3. Оголошення і відкриття курсору.
DECLARE @MyCursor CURSOR
SET @MyCursor=abc
Приклад 13.4. Використовування змінної для переприсвоювання курсору.
Приклад 13.5. Розробити курсор для виведення списку фірм і клієнтів з Москви.
DECLARE @firm VARCHAR(50)
@fam VARCHAR(50)
@message VARCHAR(80)
PRINT « Список клієнтів”
DECLARE klient_cursor CURSOR LOCAL FOR
SELECT Фірма, Прізвище
FROM Клієнт
WHERE Місто=«Москва”
ORDER Фірма, Прізвище
OPEN klient_cursor
FETCH NEXT FROM klient_cursor INTO @firm @fam
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @message=«Клиент «+@fam+
« Фірма «+ @firm
PRINT @message
//-- перехід до наступного клієнта--
FETCH NEXT FROM klient_cursor
INTO @firm @fam
END
CLOSE klient_cursor
DEALLOCATE klient_cursor
Приклад 13.5. Курсор для виведення списку фірм і клієнтів з Москви.
Приклад 13.6. Розробити курсор для виведення списку придбаних клієнтами з Москви товарів і їх загальної вартості. В один курсор заносяться всі московські клієнти, потім для кожного рядка курсору, тобто для кожного клієнта, визначається і роздруковується інший курсор – його покупки. Підраховується загальна вартість покупок клієнта.
DECLARE @id_kl INT
@firm VARCHAR(50)
@fam VARCHAR(50)
@message VARCHAR(80)
@nam VARCHAR(50)
@d DATETIME
@p INT
@s INT
SET @s = 0
PRINT « Список покупок”
DECLARE klient_cursor CURSOR LOCAL FOR
SELECT КодКлієнта, Фірма, Прізвище
FROM Клієнт
WHERE Місто = «Москва”
ORDER Фірма, Прізвище
OPEN klient_cursor
FETCH NEXT FROM klient_cursor
INTO @id_kl @firm @fam
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @message=«Клиент”+@fam+ «Фірма”+ @firm
PRINT @message
SELECT @message=«Назва товару Дата покупки Вартість”
PRINT @message