Використання курсорів та циклів у Transact-SQL. Курсори в процедурах, що зберігаються MySQL Курсори в sql server
Команда DECLARE CURSOR дозволяє витягувати записи з таблиці для маніпулювання. Це дозволяє проводити рядкову обробку замість традиційної обробки наборами даних, яку здійснює SQL.
У першому наближенні під час роботи з курсором використовуються такі кроки.
Курсор створюється командою DECLARE. Курсор відкривається командою OPEN.
Операції з курсором здійснюються за допомогою команди FETCH. Курсор закривається командою CLOSE.
У команді DECLARE CURSOR вказується інструкція SELECT. Кожен рядок, що повертається інструкцією SELECT, можна отримувати та обробляти індивідуально. У наступному прикладі для Oracle курсор оголошується у блоці оголошень разом із кількома іншими змінними. Після цього в наступному блоці BEGIN ... END курсор відкривається, по ньому проводиться вибірка, і курсор закривається.
CURSOR title_price_cursor IS SELECT title, price FROM titles
WHERE price IS NOT NULL; title_price_val title_price_cursor ROWTYPE; new_price NUMBER(10.2);
OPEN title_price_Cursor;
FETCH title_price_cur-sor INTO title_price_val;
new_price:= "title_price_val.price" * 1.25 INSERT INTO new_title_price VALUES
(title_price_val.title, new_price) CLOSE title_price_cursor; END;
Оскільки в цьому прикладі використовується PL/SQL, більшу частину коду ми в цій книзі не пояснюватимемо. Однак у блоці DECLARE ясно видно оголошення курсору. У блоці PL/SQL, що виконується, курсор ініціалізується командою OPEN, значення витягуються командою FETCH і, нарешті, курсор закривається командою CLOSE.
Інструкція SELECT- це основа курсору, так що гарною практикою є її ретельне тестування перед включенням до інструкції DECLARE CURSOR. Інструкція SELECT може працювати з базовою таблицею або поданням. Тому курсори «тільки для читання» можуть працювати з поновленнями. Інструкція SELECT може містити такі пропозиції, як ORDER BY, GROUP BY та HAVING, якщо ці пропозиції не оновлюють вихідну таблицю. Якщо курсор визначено як FOR UPDATE, рекомендується видаляти такі пропозиції з інструкції SELECT.
Локальні курсори часто використовуються як вихідні параметри процедур, що зберігаються. Тому в процедурі, що зберігається, можна визначити і заповнити курсор і передати його пакетному заданню, що викликало її, або процедурі, що зберігається.
У наступному простому прикладі для DB2 ми оголосимо курсор, який переглядає номери департаментів, назви департаментів та номери менеджерів у admin_group "ХО1".
DECLARE dept_cursor CURSOR
FOR SELECT dept_nbr, dept_name, mgr_nbr
WHERE admin_group="X01"
ORDER BY d'ept_name ASC, dept_nbr DESC, mgr_nbr DESC;
У наступному прикладі Microsoft SQL Server оголошується і відкривається курсор для таблиці publishers. Курсор відбирає з таблиці publishers перший запис, що відповідає інструкції SELECT, і вставляє її в іншу таблицю. Потім він переходить до наступного запису, потім до наступного - доти, доки всі записи не будуть оброблені. І нарешті, курсор закривається і вивільняє пам'ять (команда DEALLOCATE використовується лише у Microsoft SQL Server).
DECLARE @publisher_name VARCHAR(20)
DECLARE pub_cursor CURSOR FOR SELECT pub_name FROM publishers WHERE country "USA"
FETCH NEXT FROM pub_cursor INTO publisher_name
WHILE @s>FETCH_STATUS=0
INSERT INTO foreign_publishers VALUES(«j>publisher_name)
CLOSE pub_cursor DEALLOCATE pub_cursor
У цьому прикладі можна побачити, як курсор пересувається по набору записів. (Цей приклад покликаний лише продемонструвати цю ідею, оскільки насправді існує кращий спосібвирішення цього завдання, а саме інструкція INSERT, SELECT.)
застосовується до: SQL Server (починаючи з 2008) База даних SQL AzureСховище даних SQL AzureParallel Data Warehouse
Визначає такі атрибути серверного курсору мови Transact-SQL, як властивості перегляду та запит, який використовується для побудови результуючого набору, на якому працює курсор. Інструкція DECLARE CURSOR підтримує синтаксис стандарту ISO, так і синтаксис, що використовує набір розширень мови Transact-SQL.
ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR ( READ ONLY | UPDATE [ OF column_name [ ,...n ] ] ) ] ] [ ;] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ] FOR select_statement [ FOR UPDATE [ OF column_name [ ,...n ] ] ] [;]
cursor_name
cursor_name
INSENSITIVE
tempdb; таким чином, зміни базових таблиць не відображаються у повернутих вибірками цього курсору даних, і цей курсор не допускає зміни. При використанні синтаксису ISO, якщо не вказано параметр INSENSITIVE, зафіксовані оновлення та видалення, зроблені в базових таблицях, відображаються у наступних вибірках.
SCROLL
Вказує, що всі параметри вибірки (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) доступні. Якщо в інструкції DECLARE CURSOR стандарту ISO не вказано параметр SCROLL, підтримується лише параметр вибірки NEXT. Параметр SCROLL не може вказуватися разом із параметром FAST_FORWARD.
select_statement
Стандартна інструкція SELECT, яка визначає результуючий набір курсору. Ключові слова FOR BROWSE та INTO неприпустимі в select_statementоголошення курсору.
select_statementконфлікт із курсором запитаного типу.
READ ONLY
Оновлення ]
column_name [,
.. .n] вказано, лише перелічені стовпці дозволяють вносити зміни. Якщо інструкція UPDATE використовується без списку стовпців, оновлення може бути можливим для всіх стовпців.
cursor_name
Ім'я Transact-SQL певного серверного курсору. cursor_nameповинні відповідати правилам для ідентифікаторів.
LOCAL
Вказує, що курсор є локальним по відношенню до пакету, процедури, що зберігається, або тригеру, в якому він був створений. Ім'я курсора допустиме лише всередині цієї області. На курсор можуть посилатися локальні змінні пакети, процедури, що зберігаються, тригери або вихідний параметр збереженої процедури. Параметр OUTPUT використовується для передачі локального курсору пакету, що зберігається, процедурі або тригеру, який потім може привласнити параметр змінної курсору з метою подальшого звернення до курсора після завершення збереженої процедури. Курсор неявно звільняється після завершення виконання пакета, процедури, що зберігається, або тригера, за винятком випадку, коли курсор був переданий параметру OUTPUT. Якщо курсор був переданий параметру OUTPUT, то курсор звільняється при звільненні всіх змінних, що посилаються на нього, або при виході з області видимості.
GLOBAL
Вказує, що курсор є глобальним по відношенню до з'єднання. Ім'я курсора може використовуватися будь-якою процедурою, що зберігається, або пакетом, які виконуються в з'єднанні. Курсор неявно звільняється лише у разі розриву з'єднання.
FORWARD_ONLY
Вказує, що курсор може переглядатися лише від першого рядка до останнього. Підтримується лише параметр вибірки FETCH NEXT. Якщо параметр FORWARD_ONLY вказано без ключових слів STATIC, KEYSET або DYNAMIC, то курсор працює як курсор DYNAMIC. Якщо не вказано ні аргументу FORWARD_ONLY, ні аргументу SCROLL, за замовчуванням використовується аргумент FORWARD_ONLY, якщо немає ключових слів STATIC, KEYSET або DYNAMIC. Курсори STATIC, KEYSET та DYNAMIC мають значення за замовчуванням SCROLL. На відміну від таких інтерфейсів API баз даних, як ODBC та ADO, режим FORWARD_ONLY підтримується такими курсорами мови Transact-SQL: STATIC, KEYSET та DYNAMIC.
STATIC
Визначає курсор, який створює тимчасову копію даних для курсором. Всі запити до курсора звертаються до зазначеної тимчасової таблиці tempdb; таким чином, зміни базових таблиць не відображаються у повернутих вибірками цього курсору даних, і цей курсор не допускає зміни.
KEYSET
Вказує, що членство чи порядок рядків курсору незмінні під час його відкритті. Набір ключів, що однозначно визначають рядки, вбудований в таблицю в tempdbназивається ключів.
Зміни неключових значень у базових таблицях, зроблені власником курсору або зафіксовані іншими користувачами, відображаються під час перегляду курсору власником. Зміни, зроблені іншими користувачами, не відображаються (зміни не можуть бути зроблені за допомогою серверного курсору Transact-SQL). Якщо рядок видаляється, спроба вибірки рядків повертає @@FETCH_STATUS -2. Оновлення значень ключа з-за меж курсору аналогічно видаленню старого рядка з наступною вставкою нового рядка. Рядок з новими значеннями не видно і спроби вибірки рядка зі старими значеннями повертають @@FETCH_STATUS -2. Оновлення видно відразу, якщо вони зроблені через курсор за допомогою пропозиції WHERE CURRENT OF.
DYNAMIC
Визначає курсор, який відображає всі зміни даних, зроблені в рядках результуючого набору під час перегляду цього курсору. Значення даних, порядок та членство рядків у кожній вибірці можуть змінюватись. Параметр вибірки ABSOLUTE динамічними курсорами не підтримується.
FAST_FORWARD
Вказує курсор FORWARD_ONLY, READ_ONLY, для якого включено оптимізацію продуктивності. Параметр FAST_FORWARD не може вказуватися разом із параметрами SCROLL або FOR_UPDATE.
READ_ONLY
Запобігає змінам, зробленим через цей курсор. Пропозиція WHERE CURRENT OF не може мати посилання курсор в інструкції UPDATE або DELETE. Цей параметр має перевагу над встановленим за умовчанням можливістю оновлення курсору.
SCROLL_LOCKS
Вказує, що позиціоновані оновлення або видалення, які здійснюються за допомогою курсору, гарантовано будуть виконані успішно. SQL Server блокує рядки в міру їх зчитування курсором для забезпечення доступності цих рядків для подальших змін. Параметр SCROLL_LOCKS не може вказуватися разом із параметром FAST_FORWARD або STATIC.
OPTIMISTIC
Вказує, що позиціоновані оновлення або видалення, які здійснюються за допомогою курсору, не будуть виконані, якщо з моменту зчитування в курсор рядок було оновлено. SQL Server не блокує рядки в міру їхнього зчитування в курсор. Натомість використовуються порівняння timestampзначення стовпця або контрольних сумякщо в таблиці немає timestampстовпець, щоб визначити, чи змінювався рядок після зчитування в курсор. Якщо рядок було змінено, спроби позиціонованого оновлення або видалення будуть безрезультатними. Параметр OPTIMISTIC не може вказуватися разом із параметром FAST_FORWARD.
TYPE_WARNING
Вказує, що клієнту буде відправлено попередження, якщо курсор неявно буде перетворено з одного типу, що запитується, в інший.
select_statement
Стандартна інструкція SELECT, яка визначає результуючий набір курсору. Ключові слова COMPUTE, COMPUTE BY, FOR BROWSE та INTO неприпустимі в select_statementоголошення курсору.
SQL Serverнеявно перетворює курсор на інший тип, якщо пропозиції в select_statementконфлікт із курсором запитаного типу. Для отримання додаткових відомостей див. «Неявні перетворення курсору».
для оновлення ]
Визначає стовпці, що оновлюються в курсорі. If OF column_name [,
... n] надається лише перелічені стовпці дозволяють вносити зміни. Якщо інструкція UPDATE використовується без списку стовпців, то оновлення можливе для всіх стовпців, за винятком випадку, коли вказано параметр паралелізму READ_ONLY.
Інструкція DECLARE CURSOR визначає такі атрибути серверного курсору мови Transact-SQL, як властивості перегляду та запит, що використовується для побудови результуючого набору, на якому працює курсор. Інструкція OPEN заповнює результуючий набір, а оператор FETCH повертає рядок. Інструкція CLOSE очищає поточний набір, пов'язаний з курсором. Інструкція DEALLOCATE звільняє ресурси, які використовуються курсором.
Перша форма інструкції DECLARE CURSOR використовує синтаксис ISO для встановлення параметрів курсору. Друга форма інструкції DECLARE CURSOR використовує розширення мови Transact-SQL, що дозволяють визначати курсори за допомогою таких типів, як типи, що використовуються в курсорних функціях API баз даних, таких як ODBC і ADO.
Не можна змішувати дві ці форми. Якщо вказати SCROLL або без ОБЛІКУ ключові слова перед ключовим словом CURSOR, не можна використовувати ключові слова між курсором, а також для select_statementключові слова. При вказівці ключові слова між КУРСОРА, а також для select_statementключові слова не можна вказати SCROLL або INSENSITIVE перед ключовим словом CURSOR.
Якщо при використанні синтаксису мови Transact-SQL для інструкції DECLARE CURSOR не вказуються параметри READ_ONLY, OPTIMISTIC або SCROLL_LOCKS, то приймається наступне значення за промовчанням.
Якщо інструкція SELECT не підтримує оновлення (або недостатньо дозволів, або при доступі до віддалених таблиць, що не підтримують оновлення тощо), то курсору надається параметр READ_ONLY.
Курсори STATIC та FAST_FORWARD за замовчуванням мають значення READ_ONLY.
Курсори DYNAMIC та KEYSET за замовчуванням мають значення OPTIMISTIC.
Посилання на курсори можуть виконуватися лише іншими інструкціями мови Transact-SQL. Функції API баз даних не можуть посилатися на курсори. Наприклад, після оголошення курсору функції та методи OLE DB, ODBC або ADO не можуть посилатися на його ім'я. Рядки курсору не можуть бути вибрані за допомогою відповідних функцій та методів API; Для цього необхідно використовувати інструкції FETCH мови Transact-SQL.
Наведені нижче процедури, що зберігаються, можуть бути використані для визначення властивостей курсору після його оголошення.
Змінні можуть використовуватись як частина select_statement, У якому оголошено курсор. Значення змінних курсорів після його оголошення не змінюються.
За замовчуванням дозволи DECLARE CURSOR надаються всім користувачам, які мають роздільну здатність SELECT для використовуваних курсором уявлень, таблиць та стовпців.
Не можна використовувати курсори або тригери у таблиці з кластеризованим індексом columnstore. Це обмеження не застосовується до некластеризованих індексів; можна використовувати курсори та тригери у таблиці з некластеризованим індексом columnstore.
A. Використання простого курсору та синтаксису
Результуючий набір, створюваний при відкритті даного курсору, включає всі рядки і стовпці таблиці. Цей курсор можна оновлювати, всі оновлення та видалення представлені у вибірці для цього курсору. FETCH``NEXT доступна лише вибірка, оскільки SCROLL не було вказано параметр.
DECLARE vend_cursor CURSOR FOR SELECT * FROM Purchasing.Vendor OPEN vend_cursor FETCH NEXT FROM vend_cursor;
Б. Використання вкладених курсорів для виведення звіту
У прикладі вкладені курсори використовуються висновку складного звіту. Кожен постачальник оголошує внутрішній курсор.
SET NOCOUNT ON;
DECLARE @vendor_id int, @vendor_name nvarchar (50), @ message varchar (80), @product nvarchar (50); PRINT " -------- Vendor Products Report --------";
DECLARE vendor_cursor CURSOR FOR SELECT VendorID, Name FROM Purchasing.Vendor WHERE PreferredVendorStatus = 1 ORDER BY VendorID; OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @vendor_id, @vendor_name WHILE @@FETCH_STATUS = 0 BEGIN PRINT " " SELECT @message = "----- Products From Vendor: "+ @vendor_name PRINT @message -- Declare an inner cursor based
-- on vendor_id from the outer cursor. DECLARE product_cursor CURSOR FOR SELECT v.Name FROM Purchasing.ProductVendor pv, Production.Product v WHERE pv.ProductID = v.ProductID AND pv.VendorID = @vendor_id -- Variable value from the outer cursor OPEN product_cursor FETCH NEXT FROM product_cursor INTO @product IF @@FETCH_STATUS<>0 PRINT "<
Курсор - посилання контекстну область пам'яті. У деяких реалізаціях мови програмування SQL (Oracle, Microsoft SQL Server) - результуючий набір і пов'язаний з ним покажчик поточного запису, що отримується при виконанні запиту. б сказав, що курсор - це віртуальна таблиця, яка є альтернативним сховищем даних. При цьому курсор дозволяє звертатися до своїх даних, як до даних звичайного масиву.
Використовуються курсори в процедурах, що зберігаються. Досить теорії давайте розглянемо приклад:
У нас є база даних (база трохи не хороша, це одна з моїх лабораторних робіт, але наш викладач за базами даних наполягав на такій структурі)
/*дані про банк */
CREATE TABLE `bank` (
`BankName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "",
PRIMARY KEY (`BankId`))ENGINE=InnoDB
CHARACTER SET "utf8" COLLATE "utf8_bin";
/*дані про вклади */
CREATE TABLE `bankdistribution` (
`BankId` INTEGER (11) NOT NULL ,
`Persent` INTEGER (11) DEFAULT NULL ,
`ContributeAmount` DECIMAL (10,0) NOT NULL ,
`ClientId` INTEGER (11) NOT NULL ,
PRIMARY KEY (`BankId`, `ClientId`),
KEY `BankId` (`BankId`),
KEY `ClientId` (`ClientId`),
CONSTRAINT `bankdistribution_fk` FOREIGN KEY (`BankId`) REFERENCES `bank` (`BankId`),
CONSTRAINT `bankdistribution_fk1` FOREIGN KEY (`ClientId`) REFERENCES `client` (`ClientId`)
)ENGINE=InnoDB
/*дані про вкладників*/
CREATE TABLE `client` (
`ClientId` INTEGER (3) NOT NULL AUTO_INCREMENT,
`CreditCardId` BIGINT(10) NOT NULL ,
`Surname` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "",
`Name` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "",
`FirstName` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "",
`Phone` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "",
`Address` VARCHAR (50) COLLATE utf8_bin NOT NULL DEFAULT "",
`SafeId` INTEGER (5) NOT NULL ,
PRIMARY KEY (`ClientId`, `CreditCardId`),
KEY `ClientId` (`ClientId`))ENGINE=InnoDB
AUTO_INCREMENT=11 CHARACTER SET "utf8" COLLATE "utf8_bin"
Допустимо нам потрібно отримувати по черзі кожен банк і робити з ним якісь дії, допомогти в цьому нам міг би такий запит
Select `bank`.. Таким чином використовуючи LIMIT НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ,1 ми витягаємо в циклі з таблиці bank по черзі кожний запис і робимо з ним потрібні нам дії, при цьому збільшуючи значення НОМЕР_НУЖНОЙ_НАМ_ЗАПИСИ на 1. Тепер зробимо те ж саме
Begin
/* змінні куди ми отримуємо дані */
Declare vBankId integer ;
Declare vBankName VARCHAR (50);
Declare vAddress VARCHAR (50);
Declare vPhone VARCHAR (50);
/* змінна hadler - a*/
Declare done integer default 0;
/*Оголошення курсору*/
Declare BankCursor Cursor for Select `bank`.`BankId`,`bank`.`BankName`,`bank`.`Address`,`bank`.`Phone`, FROM `bank` where 1;
/*HANDLER призначення, якого пояснимо трохи нижче*/
DECLARE CONTINUE HANDLER FOR SQLSTATE "02000" SET done=1;
/* Відкриття курсору */
Open BankCursor;
/*витягуємо дані */
WHILE done = 0 DO
робимо потрібні нам дії
END WHILE;
/*закриття курсору */
Close BankCursor;
END;* Цей source code був highlighted with Source Code Highlighter.
Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
Message: No data - 0 rows виконані, вибрані, або процесовані
SQLSTATE: 02000 спрацьовує коли досягнуто кінець курсору, або коли select або update повертає порожній рядок.
Наступним рядком ми оголосили курсор DECLARE cursor_name CURSOR FOR select_statement;
Відкриваємо курсор Open cursor_name;
Далі поки не досягаємо кінець курсору (WHILE done = 0 DO) вилучаємо дані та обробляємо їх.
Перед виходом із процедури необхідно курсор закрити. Close cursor_name;
Начебто нічого складного. Але з SQLSTATE "02000" пов'язано багато підводних каменів.
WHILE done = 0 DO
FETCH BankCursor INTO vBankId, vBankName, vAddress, vPhone;
Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
робимо якісь дії
END WHILE;* Цей source code був highlighted with Source Code Highlighter.
Все добре і правильно з погляду синтаксису. Але з логічного погляду немає. Може статися так що вкладники не відкрили рахунків у якомусь банку, тоді для Select (ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1; спрацює SQLSTATE: 02000, мінлива done встановиться у 1, та цикл while закінчитися раніше ніж ми очікували. Цього можна уникнути вчинивши таким чином
WHILE done = 0 DO
FETCH BankCursor INTO vBankId, vBankName, vAddress, vPhone;
/* Витягнемо для банку суму будь-якого з його вкладів */
if (vContributeAmountSUM > 0) then
/* Витягнемо для банку суму будь-якого з його вкладів */
end if;
робимо якісь дії
END WHILE;* Цей source code був highlighted with Source Code Highlighter.
першим запитом ми перевірили чи є вклади (якщо їх немає то vContributeAmountSUM == 0) і тільки якщо такі є ми витягуємо дані.
Тепер допустимо нам потрібно вилікувати загальну суму на рахунках у різних банках у кожного клієнта
Declare ClientSummCursor Cursor for Select sum
Declare ClientSummCursor Cursor for Select sum (`bankdistribution`.`ContributeAmount`),`bankdistribution`.`ClientId` FROM `bankdistribution` Внутрішній Join client on (client.ClientId = bankdistribution.`ClientId`) where 1 group by`bank `ClientId`;Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId, vBankName, vAddress, vPhone;
/* Витягнемо для банку суму будь-якого з його вкладів */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* перевіримо чи дійсно є вклади в цьому банку */
if (vContributeAmountSUM > 0) then
/* Витягнемо для банку суму будь-якого з його вкладів */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if;
робимо якісь дії.
END WHILE;* Цей source code був highlighted with Source Code Highlighter.
Може виникнути та ж ситуація, коли дані в курсорі ClientSummCursor, закінчаться раніше ніж дані в BankCursor, спрацює SQLSTATE: 02000, змінна done встановиться в 1, і цикл while закінчитися раніше, ніж ми очікували. Цього можна уникнути вчинивши таким чином
Open ClientSummCursor;
WHILE done = 0 DO
FETCH BankCursor INTO vBankId, vBankName, vAddress, vPhone;
/* Витягнемо для банку суму будь-якого з його вкладів */
Select Сount(ContributeAmount) INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
/* перевіримо чи дійсно є вклади в цьому банку */
if (vContributeAmountSUM > 0) then
/* Витягнемо для банку суму будь-якого з його вкладів */
Select ContributeAmount INTO vContributeAmountSUM FROM bankdistribution where BankId = vBankId limit 1;
end if;
/* до вилучення даних з другого курсору запам'ятаємо стан sqlstate */
SET old_status = done;
/* Витягуємо потрібні нам дані */
FETCH ClientSummCursor INTO vSum,vClientId;
/* перевіряємо чи були вилучені дані, чи не працював sqlstate 0200 */
if (done = 0) then
робимо якісь дії.
end if;
/* перед закінченням while восттановим значення змінної done */
set done = old_status;
END WHILE;* Цей source code був highlighted with Source Code Highlighter.
Всім, хто дочитав до цього місця спасибі, сподіваюся це здасться комусь корисною.
Реалізація курсору у базі даних нагадує клас Java, що має набір даних та методи для їх обробки. При цьому SQL cursorвикористовує дані як типовий масив. Курсори можуть бути використані в тригерах, процедурах, що зберігаються, і функціях.
Відповідно до стандарту SQL під час роботи з курсорами виконуються такі основні действия:
- оголошення курсору;
- відкриття курсору з читанням даних;
- рядкова вибірка даних із курсору;
- зміна за допомогою курсору даних рядка;
- закриття курсору, після чого він стає недоступним;
- звільнення курсору, тобто. видалення курсору з пам'яті, оскільки його закриття необов'язково звільняє пам'ять, що асоціюється з ним.
У різних реалізаціях визначення курсоруможе мати деякі відмінності. Так, наприклад, іноді необхідно явним чином звільнити пам'ять, що виділяється для курсора. Після звільнення курсору асоційована з ним пам'ять також звільняється. При цьому стає можливим повторне використання курсору. В інших реалізаціях при закритті курсору звільнення пам'яті відбувається неявним чином.
В окремих випадках без застосування курсору не обійтись. Однак, по можливості, слід уникати використання курсору та працювати зі стандартними командами обробки даних: SELECT, UPDATE, INSERT, DELETE. Це пов'язано з тим, що курсори не дозволяють проводити операції зміни над усім обсягом даних і швидкість виконання операцій обробки даних за допомогою курсору помітно нижче, ніж у стандартних засобів SQL.
Якщо програма може змінити дані, завантажені в cursor, він називається модифицируемым. Говорячи про курсори, не слід забувати про ізольованість транзакцій. Один користувач змінює запис за допомогою курсору, в той час, як інший читає цей запис за допомогою власного курсору. Більше того, він може змінити той самий запис, що зумовлює необхідність дотримання цілісності даних.
Оголошення курсору, declare cursor
Курсори мають бути оголошені до їхнього використання. У стандарті SQL для створення курсору використовується наступний синтаксис:
Declare cursor_name cursor for select_statement ])]
У цьому виразі оголошується курсор declare cursor c ім'ям "cursor_name".
INSENSITIVEстворюється статичний курсор, який дозволяє вносити зміни. Крім того, не відображаються зміни, зроблені іншими користувачами. Якщо ключового слова INSENSITIVE немає, створюється динамічний курсор.
При використанні ключового слова SCROLLстворений курсор можна прокручувати у будь-якому напрямку, що дозволяє застосовувати будь-які команди вибірки. Якщо це аргумент опускається, то cursor виявиться послідовним, тобто. його перегляд буде можливим лише в одному напрямку – від початку до кінця.
Вираз select_statementвказує на конструкцію читання інформації типу select...from.... Воно не повинно містити оператора intoоскільки cursor має свій оператор fetchдля заповнення змінних даних курсору.
При вказівці аргументу FOR READ_ONLYбуде створено курсор "тільки для читання", і жодних модифікацій даних не дозволяється. Як курсор "тільки для читання" може бути оголошено динамічний курсор, що дозволить відображати зміни, зроблені іншим користувачем.
Створення курсору з аргументом FOR UPDATEдозволяє виконувати в курсорі зміну даних або у зазначених стовпцях, або, за відсутності аргументу OF ім'я_стовпця, у всіх стовпцях.
У підпрограмі можна оголосити кілька курсорів. Але кожен курсор повинен мати унікальне ім'я. Для відкриття курсору потрібно використовувати оператор open, який відкриває раніше оголошений курсор:
Відкриття курсору, cursor open
У SQL визначено наступний синтаксис відкриття курсору "cursor open"" :
Open cursor_name;
Вибір даних з курсору, cursor fetch
Синтаксис читання даних з курсору до деяких змінних має такий вигляд:
Fetch cursor_name в var_name [, var_name] ...;
Оператор fetchвибирає дані відкритого курсору змінні, розташовані після intoі переміщує курсор в наступну позицію.
Закриття курсору, cursor close
Оператор closeзакриває cursor. Якщо оператор явно не вказаний, курсор закривається автоматично при закритті відповідного програмного блоку.
Close cursor_name;
Після закриття курсор стає недоступним. При закритті знімаються всі блокування, встановлені під час роботи курсора. Закривати можна лише відкриті курсори. Закритий, але звільнений курсор може бути повторно відкритий. Не допускається закривати відкритий курсор.
Кожна СУБД має свої особливості використання курсора.
Особливості використання курсорів у Oracle
У PL/SQL є чотири курсорні атрибути %FOUND, %NOTFOUND, %ISOPENі %ROWCOUNT. Атрибути курсору оголошуються подібно до операторів %TYPE і %ROWTYPE, праворуч від імені курсору.
Атрибут %FOUND
Атрибут %NOTFOUND
Атрибут %NOTFOUND є повною протилежністю %FOUND.
Атрибут %ISOPEN
Атрибут %ISOPEN вказує лише на те, чи курсор відкритий чи ні.
Атрибут %ROWCOUNT
Атрибут %ROWCOUNTє числовим атрибутом, що повертає число рядків, лічених курсором на певний час.
Приклад SQL курсору в СУБД Oracle
Declare v_id managers.id %TYPE; v_name managers.name%TYPE; v_comm managers.comm%TYPE; crs cursor для select id, name, sum(comm) as comm from managers where data between "2014-11-01" and "2014-11-30" group by id, name; begin open crs; loop EXIT WHEN crs%NOTFOUND; FETCH crs до v_id, v_name, v_comm; insert в bonus(id, name, comm) values (crs.id, crs.name, crs.comm); end loop; commit; close crs; end;
Особливості використання курсорів у SQL сервері
Курсори, що використовуються в MSSQL, можуть бути послідовні та прокручуються. Послідовні дозволяють вибирати дані лише в одному напрямку – від початку до кінця. Курсори, що прокручуються, допускають переміщення в обох напрямках і дозволяють виконувати перехід до довільного рядка результуючого набору курсора.
SQL Server підтримує курсори статичні, динамічні, послідовні та керовані набором ключів.
У схемі зі статичним курсором інформація зберігається як моментального знімка за станом деякий час. Тому зміни, внесені до бази даних іншим користувачем, не видно. На час відкриття курсору сервер встановлює блокування на всі рядки, що включені в його повний результуючий набір. Статичний курсор не змінюється після створення і відображає той набір даних, який існував на момент його відкриття. Якщо інші користувачі змінять у вихідній таблиці дані, що включені в курсор, це ніяк не вплине на статичний курсор. У статичний курсор внести зміни неможливо, тому він завжди відкривається у режимі "тільки для читання".
Динамічний курсор вимагає додаткових мережних витрат та програмних ресурсів. У разі використання динамічних курсорів повна копія даних не створюється, а виконується вибірка з вихідних таблиць тільки при зверненні користувача до тих чи інших даних. На час вибірки сервер блокує рядки, а всі зміни, які користувач вносить у повний результуючий набір курсору, буде видно в курсорі. Однак після вибірки даних курсором внесені зміни іншим користувачем не позначаться в курсорі.
Курсор, керований набором ключів, за властивостями розташовується між статичним та динамічним. Записи ідентифікуються на момент вибірки, і таким чином відслідковуються зміни. Такий тип курсору корисний при реалізації прокручування назад. У цьому випадку додавання та видалення даних не видно, поки інформація не оновиться, а курсор вибирає нову версіюзаписи, якщо до неї було внесено зміни.
Статичні курсори найкраще використовуватиме систем обробки інформації, тобто. для систем звітності або для статистичних та аналітичних цілей. Статичний курсор краще справляється із вибіркою великої кількості даних. У системах електронних покупок або резервування об'єктів (місць, квитків) необхідне динамічне сприйняття інформації, що оновлюється, в міру внесення змін. У разі використовується динамічний курсор. У цих додатках обсяг переданих даних, зазвичай, невеликий, а доступом до них складає рівні окремих записів.
Послідовні курсори не дозволяють виконувати вибірку даних у зворотному напрямку лише від початку до кінця курсору. Послідовний курсор не зберігає набір всіх рядків із даними. Вони зчитуються з бази даних, як тільки виконується вибірка в курсорі, що дозволяє динамічно відображати всі зміни, що вносяться користувачами в базу даних за допомогою команд INSERT, UPDATE, DELETE. Курсор читає останній стан даних.
Оголошення курсору
Declare cursor_name cursor for SELECT_оператор ]]
При використанні ключового слова LOCALбуде створено локальний курсор, який видно тільки в межах блоку, тригера, процедури, що зберігається, або користувальницької функції. Ключове слово GLOBALвизначає глобальний курсор, який існує до закриття поточного з'єднання.
Оператор FORWARD_ONLYвизначає послідовний курсор, що дозволяє здійснювати вибірку даних лише у напрямку від першого рядка до останнього. При використанні оператора SCROLLстворюється курсор, що прокручується, який забезпечує звернення до даних у будь-якому порядку і в будь-якому напрямку.
Тип курсору визначають оператори:
- STATIC – створення статичного курсору;
- DYNAMIC – створення динамічного курсору;
- KEYSET – створення ключового курсору.
Якщо для курсору READ_ONLYвказати аргумент FAST_FORWARD, то створений курсор буде оптимізовано для швидкого доступудо даних. Цей аргумент не може бути використаний спільно з аргументами FORWARD_ONLYі OPTIMISTIC.
Якщо курсор створено із зазначенням оператора OPTIMISTIC, то забороняється зміна та видалення рядків, які були змінені після відкриття курсору.
При вказівці аргументу TYPE_WARNINGсервер буде інформувати про неявну зміну типу курсору, якщо він несумісний із запитом SELECT.
Вибір даних з курсору, fetch
Відразу після відкриття курсору можна отримати його вміст за допомогою наступної команди:
При використанні оператора FIRSTбуде повернено перший рядок результуючого набору курсору, який стає поточним рядком. При вказівці LASTбуде повернуто останній рядок курсору. Вона ж стає поточним рядком.
При вказівці оператора NEXTбуде повернуто рядок, що знаходиться в результуючому наборі відразу після поточного. Цей рядок стає поточним. За замовчуванням команда FETCHвикористовує саме цей спосіб вибірки рядків.
При вказівці оператора PRIORбуде повернуто рядок, що знаходиться перед поточним. Цей рядок стає поточним.
Оператор ABSOLUTE (номер_рядка | @змінна_номера_рядка)повертає рядок за його абсолютним порядковим номером у повному результуючому наборі курсору. Номер рядка можна встановити за допомогою константи або як ім'я змінної, в якій зберігається номер рядка. Змінна повинна мати цілий тип даних. Вказуються як позитивні, і негативні значення. При вказівці позитивного значення рядок відраховується з початку набору, негативного – від кінця. Вибраний рядок стає поточним. Якщо вказано нульове значення, рядок не повертається.
Аргумент RELATIVE (кільк_рядки | @змінна_кіл_рядка)повертає рядок, що перебуває зі зміщенням на вказану кількість рядків після поточного. Якщо вказати негативне значення числа рядків, то буде повернуто рядок, що знаходиться за вказану кількість рядків перед поточним. Якщо вказати нульове значення, повернеться поточний рядок. Повернутий рядок стає поточним.
Щоб відкрити глобальний курсор, перед його ім'ям потрібно вказати ключове слово GLOBAL. Ім'я курсора також може бути вказано за допомогою змінної.
У виразі INTO @ім'я_змінної [,...n]визначається список змінних, в яких будуть збережені відповідні значення стовпців рядка, що повертається. Порядок вказівки змінних має відповідати порядку стовпців у курсорі, а тип даних змінної – типу даних у стовпці курсору.
Зміна та видалення даних з використанням курсору
Для зміни даних за допомогою курсору необхідно виконати команду UPDATE у такому форматі:
За одну операцію можуть бути змінені значення кількох стовпців поточного рядка курсору, але вони повинні належати одній таблиці.
Для видалення даних за допомогою курсору використовується команда DELETE у такому форматі:
В результаті буде видалено рядок, що є поточним у курсорі.
Звільнення пам'яті, deallocate
Для видалення курсору з пам'яті використовується команда
Deallocate cursor_name;
Атрибут @@FETCH_STATUS
Для визначення наявності рядків у курсорі слід використовувати глобальну змінну @@FETCH_STATUS, яка набуває ненульового значення, якщо рядків у курсорі більше немає. Якщо ж набір рядків ще не вичерпано, то @@FETCH_STATUS дорівнює нулю.
Приклад курсора у SQL сервері
Declare @company varchar(50), @manager varchar(50), @message varchar(256); declare crs_clients орієнтовний місцевий офіс для select company, manager from customers where city = "Moscow" order by company, manager; print "Список клієнтів"; open crs_clients; fetch next from crs_clients в @company, @manager; while @@FETCH_STATUS = 0 begin select @message = "Компанія" + @company + "менеджер" + @manager; print @message; -- перехід до наступного запису fetch next from crs_clients в @company, @manager; end; close crs_clients; deallocate crs_clients;
Дається визначення курсору. Наводиться опис його типів та поведінки: статичні, динамічні, послідовні та ключові курсори. Описуються принципи управління курсором: створення та відкриття курсору, зчитування даних, закриття курсору. Наводяться приклади програмування курсору.Поняття курсору
Запит до реляційної бази даних зазвичай повертає кілька рядів (записів) даних, але програма за один раз обробляє лише один запис. Навіть якщо вона має справу одночасно з кількома рядами (наприклад, виводить дані у формі електронних таблиць), їх кількість, як і раніше, обмежена. Крім того, при модифікації, видаленні чи додаванні даних робочою одиницею є ряд. У цій ситуації першому плані виступає концепція курсора , й у контексті курсор – покажчик на ряд.
Курсор у SQL – це область пам'яті бази даних, яка призначена для зберігання останнього оператора SQL. Якщо поточний оператор – запит до бази даних, у пам'яті зберігається і рядок даних запиту, який називається поточним значенням, або поточним рядком курсору . Вказана область у пам'яті названа та доступна для прикладних програм.
Зазвичай курсори використовуються для вибору з бази даних деякого підмножини інформації, що зберігається в ній. У кожний момент часу прикладною програмою може бути перевірено один рядок курсору. Курсори часто застосовуються в операторів SQL, вбудованих у написані мовами процедурного типу прикладні програми. Деякі їх неявно створюються сервером бази даних, тоді як інші визначаються програмістами.
Відповідно до стандарту SQL під час роботи з курсорами можна виділити такі основні действия:
- створення або оголошення курсору;
- відкриття курсору, тобто. наповнення його даними, які зберігаються у багаторівневій пам'яті;
- вибірка з курсорута зміна за його допомогою рядків даних;
- закриття курсору, після чого він стає недоступним для програм користувача;
- звільнення курсору, тобто. видалення курсору як об'єкта, оскільки його закриття необов'язково звільняє пам'ять, що асоціюється з ним.
У різних реалізаціях визначення курсору може мати певні відмінності. Так, наприклад, іноді розробник повинен явним чином звільнити пам'ять, що виділяється для курсора. Після звільнення курсоруасоційована з ним пам'ять також звільняється. При цьому стає можливим повторне використання його імені. В інших реалізаціях при закриття курсорувизволення пам'яті відбувається неявним чином. Відразу після відновлення вона стає доступною для інших операцій: відкриття іншого курсоруі т.д.
У деяких випадках застосування курсору є неминучим. Однак по можливості цього слід уникати та працювати зі стандартними командами обробки даних: SELECT, UPDATE, INSERT, DELETE. Крім того, що курсори не дозволяють проводити операції зміни над усім обсягом даних, швидкість виконання операцій обробки даних за допомогою курсору помітно нижче, ніж у стандартних засобів SQL.
Реалізація курсорів у середовищі MS SQL Server
SQL Server підтримує три види курсорів:
- курсори SQL застосовуються в основному всередині тригерів, процедур, що зберігаються, і сценаріїв;
- курсори сервера діють на сервері та реалізують програмний інтерфейс додатків для ODBC, OLE DB, DB_Library;
- курсори клієнта реалізуються самому клієнті. Вони вибирають весь результуючий набір рядків із сервера та зберігають його локально, що дозволяє прискорити операції обробки даних за рахунок зниження втрат часу виконання мережевих операцій.
Різні типи розрахованих на багато користувачів додатків вимагають і різних типів організації паралельного доступу до даних. Деякі програми потребують негайного доступу до інформації про зміни в базі даних. Це притаманно систем резервування квитків. В інших випадках, наприклад, у системах статистичної звітності, важливою є стабільність даних, адже якщо вони постійно модифікуються, програми не зможуть ефективно відображати інформацію. Різним додаткам потрібні різні реалізації курсорів.
У середовищі SQL Server типи курсорів різняться за можливостями. Тип курсору визначається на стадії його створення і не може бути змінено. Деякі типи курсорів можуть виявляти зміни, зроблені іншими користувачами у рядках, включених до результуючого набору. Однак SQL Server відстежує зміни таких рядків тільки на стадії звернення до рядка і не дозволяє модифікувати зміни, коли рядок вже рахується.
Курсори поділяються на дві категорії: послідовніі прокручуються. Послідовнідозволяють вибирати дані лише в одному напрямку – від початку до кінця. Прокручуються ж курсоринадають більшу свободу дій - допускається переміщення в обох напрямках і перехід до довільного рядка результуючого набору курсору. Якщо програма здатна модифікувати дані, на які вказує курсор, він називається прокручується і модифікується. Говорячи про курсори, не слід забувати про ізольованість транзакцій. Коли один користувач модифікує запис, інший читає його за допомогою власного курсору, більше того, він може модифікувати той самий запис, що робить необхідним дотримання цілісності даних.
SQL Server підтримує статичні , динамічні курсори . послідовніта керовані набором ключів.
У схемі зі статичним курсоромінформація читається з бази даних один раз і зберігається у вигляді моментального знімка (станом на деякий момент часу), тому зміни, внесені до бази даних іншим користувачем, не видно. На час відкриття курсорусервер встановлює блокування на всі рядки, що включені в його повний результуючий набір. Статичний курсорне змінюється після створення та завжди відображає той набір даних, який існував на момент його відкриття.
Якщо інші користувачі змінять у вихідній таблиці дані, що включені в курсор, це ніяк не вплине на статичний курсор.
У статичний курсорвнести зміни неможливо, тому він завжди відкривається у режимі "тільки для читання".
Динамічний курсорпідтримує дані в "живому" стані, але це потребує витрат мережних та програмних ресурсів. При використанні динамічних курсорівне створюється повна копія вихідних даних, а виконується динамічна вибірка з вихідних таблиць лише за зверненні користувача до тих чи іншим даним. На час вибірки сервер блокує рядки, а всі зміни, які користувач вносить у повний результуючий набір курсору, будуть видні в курсорі. Однак якщо інший користувач вніс зміни вже після вибірки даних курсором, то вони не позначаться в курсорі.
Курсор, керований набором ключівзнаходиться посередині між цими крайнощами. Записи ідентифікуються на момент вибірки, і цим відслідковуються зміни. Такий тип курсору корисний при реалізації прокручування назад - тоді додавання та видалення рядів не видно, поки інформація не оновиться, а драйвер вибирає нову версію запису, якщо до неї були внесені зміни.
Послідовні курсорине дозволяють виконувати вибірку даних у зворотному напрямку. Користувач може вибирати рядки тільки від початку до кінця курсору. Послідовний курсорне зберігає набір всіх рядків. Вони зчитуються з бази даних, як тільки вибираються в курсорі, що дозволяє динамічно відображати всі зміни, що вносяться користувачами до бази даних за допомогою команд INSERT, UPDATE, DELETE. У курсорі видно останній стан даних.
Статичні курсоризабезпечують стабільний погляд на дані. Вони хороші для систем "складування" інформації: додатків для систем звітності або статистичних і аналітичних цілей. Крім того, статичний курсоркраще за інших справляється з вибіркою великої кількості даних. Навпаки, в системах електронних покупок або резервування квитків необхідне динамічне сприйняття інформації, що оновлюється, в міру внесення змін. У таких випадках використовується динамічний курсор. У цих додатках обсяг переданих даних, зазвичай, невеликий, а доступом до них складає рівні рядів (окремих записів). Груповий доступ зустрічається вкрай рідко.
Управління курсором у середовищі MS SQL Server
Управління курсоромреалізується шляхом виконання наступних команд:
- DECLARE – створення або оголошення курсору;
- OPEN – відкриття курсору, тобто. наповнення його даними;
- FETCH - вибірка з курсорута зміна рядків даних за допомогою курсору;
- CLOSE - закриття курсору;
- DEALLOCATE - звільнення курсору, тобто. видалення курсору як об'єкта.
Оголошення курсору
У стандарті SQL для створення курсору передбачено таку команду:
При використанні ключового слова INSENSITIVE буде створено статичний курсор. Зміни данихне дозволяється, крім того, не відображаються зміни, зроблені іншими користувачами. Якщо ключового слова INSENSITIVE немає, створюється динамічний курсор.
При вказівці ключового слова SCROLL створений курсор можна прокручувати у будь-якому напрямку, що дозволяє застосовувати будь-які команди вибірки . Якщо цей аргумент опускається, то курсор виявиться послідовним, тобто. його перегляд буде можливим лише в одному напрямку – від початку до кінця.
SELECT-оператор задає тіло запиту SELECT , з якого визначається результуючий набір рядків курсора .
При вказівці аргументу FOR READ_ONLY створюється курсор "тільки читання", і жодні модифікації даних забороняються. Він відрізняється від статичного, хоча останній також не дозволяє змінювати дані. Як курсор "тільки для читання" може бути оголошений динамічний курсор, що дозволить відображати зміни, зроблені іншим користувачем.
Створення курсору з аргументом FOR UPDATE дозволяє виконувати курсор зміна данихабо в зазначених стовпцях, або, за відсутності аргументу OF имя_столбца , у всіх стовпцях.
У середовищі MS SQL Server прийнято наступний синтаксис команди створення курсору:
<создание_курсора>::= DECLARE ім'я_курсора CURSOR FOR SELECT_оператор ]]
При використанні ключового слова LOCAL буде створено локальний курсор , який видно тільки в межах пакета, тригера, процедури, що зберігається, або користувальницької функції. Після завершення роботи пакета, тригера, процедури чи функції курсор неявно знищується. Щоб передати вміст курсору за межі конструкції, що його створила, необхідно привласнити його параметру аргумент OUTPUT .
Якщо вказано ключове слово GLOBAL, створюється глобальний курсор; вона існує до закриття поточного з'єднання.
При вказівці FORWARD_ONLY створюється послідовний курсор; вибірку даних можна здійснювати лише у напрямку від першого рядка до останнього.
При вказівці SCROLL створюється курсор, що прокручується; звертатися до даних можна у будь-якому порядку та у будь-якому напрямку.
При вказівці STATIC створюється статичний курсор.
При вказівці KEYSET створюється ключовий курсор.
При вказівці DYNAMIC створюється динамічний курсор.
Якщо для курсора READ_ONLY вказати аргумент FAST_FORWARD , то створений курсор буде оптимізовано для швидкого доступу до даних. Цей аргумент не може бути використаний спільно з аргументами FORWARD_ONLY та OPTIMISTIC.
У курсорі, створеному із зазначенням аргументу OPTIMISTIC, забороняється зміна та видалення рядків, які були змінені після відкриття курсору.
При вказанні аргументу TYPE_WARNING сервер інформуватиме користувача про неявну зміну типу курсору, якщо він несумісний із запитом SELECT.
Відкриття курсору
Для відкриття курсорута наповнення його даними із зазначеного під час створення курсору запиту SELECT використовується наступна команда:
Після відкриття курсорувідбувається виконання пов'язаного з ним оператора SELECT, вихідні дані якого зберігаються в багаторівневій пам'яті.
Вибірка даних із курсору
Відразу після відкриття курсоруможна вибрати його вміст (результат виконання відповідного запиту) за допомогою наступної команди:
При вказівці FIRST буде повернуто перший рядок повного результуючого набору курсору , яка стає поточним рядком.
При вказівці LAST повертається останній рядок курсора . Вона ж стає поточним рядком.
При вказівці NEXT повертається рядок, що знаходиться в повному результуючому наборі відразу після поточного. Тепер вона стає поточною. За замовчуванням FETCH використовує саме цей спосіб вибірки рядків.
Ключове слово PRIOR повертає рядок, що знаходиться перед поточним. Вона і стає поточною.
Аргумент ABSOLUTE (номер_рядка | @змінна_номера_рядка)повертає рядок за його абсолютним порядковим номером у повному результуючому наборі курсору. Номер рядка можна встановити за допомогою константи або як ім'я змінної, в якій зберігається номер рядка. Змінна повинна мати цілий тип даних. Вказуються як позитивні, і негативні значення. При вказівці позитивного значення рядок відраховується з початку набору, негативного – від кінця. Вибраний рядок стає поточним. Якщо вказано нульове значення, рядок не повертається.
Аргумент RELATIVE (кільк_рядки | @змінна_кіл_рядка)повертає рядок, що знаходиться через вказану кількість рядків після поточного. Якщо вказати негативне значення числа рядків, то буде повернуто рядок, що знаходиться за вказану кількість рядків перед поточним. Якщо вказати нульове значення, повернеться поточний рядок. Повернутий рядок стає поточним.
Щоб відкрити глобальний курсор, перед його ім'ям потрібно вказати ключове слово GLOBAL. Ім'я курсора також може бути вказано за допомогою змінної.
У конструкції INTO @ім'я_змінної [,...n]задається список змінних, в яких будуть збережені відповідні значення стовпців рядка, що повертається. Порядок вказівки змінних повинен відповідати порядку стовпців у курсорі, а тип даних змінної – типу даних у стовпці курсору. Якщо конструкція INTO не вказана, поведінка команди FETCH нагадуватиме поведінку команди SELECT – дані виводяться на екран.
Зміна та видалення даних
Для виконання змін за допомогою курсору необхідно виконати команду UPDATE у такому форматі:
За одну операцію можуть бути змінені кілька стовпців поточного рядка курсору, але вони повинні належати до однієї таблиці.
Для видалення даних за допомогою курсору використовується команда DELETE у такому форматі:
В результаті буде видалено рядок, встановлений в курсорі .
Закриття курсору
Після закриття курсор стає недоступним для користувачів програми. При закритті знімаються всі блокування, встановлені у його роботи. Закриття може застосовуватися лише до відкритих курсорів. Закритий, але не звільнений курсорможе бути повторно відкрито. Не допускається закривати невідкритий курсор.
Звільнення курсору
Закриття курсорунеобов'язково звільняє асоційовану із нею пам'ять. У деяких реалізаціях потрібно явно звільнити її за допомогою оператора DEALLOCATE. Після звільнення курсорузвільняється і пам'ять, у своїй стає можливим повторне використання імені курсора .
Для контролю досягнення кінця курсору рекомендується застосовувати функцію: @@FETCH_STATUS
Функція @@FETCH_STATUS повертає:
0 якщо вибірка завершилася успішно;
1, якщо вибірка завершилася невдало внаслідок спроби вибірки рядка, що знаходиться за межами курсору;
2 , якщо вибірка завершилася невдало внаслідок спроби звернення до віддаленого чи зміненого рядка.
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 BY Фірма, Прізвище OPEN klient_cursor SELECT @message="Клієнт" [email protected]+ " Фірма "+ @firm PRINT @message SELECT @message="Найменування товару Дата покупки Вартість" PRINT @message DECLARE tovar_cursor CURSOR FOR SELECT Товар.Назва, Угода.Дата, Товар.Ціна*Угода.Кількість AS Вартість FROM Товар INNER JOIN Угода ON Товар. КодТовара=Угода.КодТовара WHERE Угода.КодКлієнта [email protected] _kl OPEN tovar_cursor FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p IF @@FETCH_STATUS<>0 PRINT "Нема покупок" WHILE @@FETCH_STATUS=0 BEGIN SELECT @message=" " [email protected]+" "+ CAST(@d AS CHAR(12))+" "+ CAST(@p AS CHAR(6)) PRINT @message SET @ [email protected][email protected] FETCH NEXT FROM tovar_cursor INTO @nam, @d, @p END CLOSE tovar_cursor DEALLOCATE tovar_cursor SELECT @message="Загальна вартість" + CAST(@s AS CHAR(6)) PRINT @message -- перехід до наступного клієнта-- FETCH NEXT FROM klient_cursor INTO @id_kl, @firm, @fam END CLOSE klient_cursor DEALLOCATE klient_cursor Приклад 13.6. Курсор для виведення списку придбаних клієнтами з Москви товарів та їхньої загальної вартості.
Приклад 13.7.Розробити курсор, що прокручується, для клієнтів з Москви. Якщо номер телефону починається на 1, видалити клієнта з таким номером та в першому записі курсору замінити першу цифру у номері телефону на 4.
DECLARE @firm VARCHAR(50), @fam VARCHAR(50), @tel VARCHAR(8), @message VARCHAR(80) PRINT " Список клієнтів" DECLARE CURSOR ="Москва" ORDER BY Фірма, Прізвище FOR UPDATE OPEN klient_cursor FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel WHILE @@FETCH_STATUS=0 BEGIN SELECT @message="Клієнт " [email protected]+ "Фірма" [email protected]"Телефон" + @tel PRINT @message -- якщо номер телефону починається на 1, -- видалити клієнта з таким номером IF @tel LIKE '1%' DELETE Клієнт WHERE CURRENT OF klient_cursor ELSE -- перехід до наступного клієнта FETCH NEXT FROM klient_cursor INTO @firm, @fam, @tel END FETCH ABSOLUTE 1 FROM klient_cursor INTO @firm, @fam, @tel -- у першому записі замінити першу цифру в -- номері телефону на 4 UPDATE Клієнт SET Телефон='4' + RIGHT( @tel,LEN(@tel)-1)) WHERE CURRENT OF SELECT @message="Клієнт " [email protected]+" Фірма "+ @firm " Телефон "+ @tel PRINT @message CLOSE klient_cursor DEALLOCATE klient_cursor Приклад 13.7. Курсор, що прокручується, для клієнтів з Москви.
Приклад 13.8.Використання курсору як вихідного параметра процедури. Процедура повертає набір даних – перелік товарів.
Виклик процедури та виведення на друк даних із вихідного курсору здійснюється наступним чином:
DECLARE @my_cur CURSOR DECLARE @n VARCHAR(20) EXEC my_proc @ [email protected] _cur OUTPUT FETCH NEXT FROM @my_cur INTO @n SELECT @n WHILE (@@FETCH_STATUS=0) BEGIN FETCH NEXT FROM @my_cur INTO @n SELECT @n END CLOSE @my_cur DEALLOCATE @my_cur
