→ Курсори в процедурах MySQL, що зберігаються. Курсори у вбудованому SQL Приклад на збільшення комісійних

Курсори в процедурах MySQL, що зберігаються. Курсори у вбудованому SQL Приклад на збільшення комісійних

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

Відповідно до стандарту SQL під час роботи з курсорамиможна виділити такі основні дії:

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

SQL Server підтримує три виду курсорів:

  • курсори SQL застосовуються в основному всередині тригерів, збережених процедур та сценаріїв;
  • курсорисервери діють на сервері та реалізують програмний інтерфейс додатків для ODBC, OLE DB, DB_Library;
  • курсориклієнта реалізуються на клієнті. Вони вибирають весь результуючий набір рядків із сервера та зберігають його локально, що дозволяє прискорити операції обробки даних за рахунок зниження втрат часу виконання мережевих операцій.

Управління курсором у середовищі MS SQL Server

Управління курсоромреалізується шляхом виконання наступних команд:

  • DECLARE – створення або оголошення курсору ;
  • OPEN – відкриття курсору, тобто. наповнення його даними;
  • FETCH - вибірка з курсоруі змінарядків даних за допомогою курсору;
  • CLOSE - закриття курсору ;
  • DEALLOCATE - звільнення курсору, тобто. видалення курсору як об'єкта.

Оголошення курсору

У стандарті SQL для створення курсорупередбачена наступна команда:

<создание_курсора>::= DECLARE ім'я_курсора CURSOR FOR SELECT_оператор ])]

При використанні ключового слова 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 використовується наступна команда:

OPEN ((ім'я_курсора) |@ім'я_змінної_курсора)

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

Вибірка даних із курсору

Відразу після відкриття курсоруможна вибрати його вміст (результат виконання відповідного запиту) за допомогою наступної команди:

FETCH [ FROM ]((ім'я_курсора )| @ім'я_змінної_курсора ] ]

При вказівці FIRST буде повернуто перший рядок повного результуючого набору курсоруяка стає поточним рядком.

При вказівці LAST повертається останній рядок курсору. Вона ж стає поточним рядком.

При вказівці NEXT повертається рядок, що знаходиться в повному результуючому наборі відразу після поточного. Тепер вона стає поточною. За замовчуванням FETCH використовує саме цей спосіб вибіркирядків.

Ключове слово PRIOR повертає рядок, що знаходиться перед поточним. Вона і стає поточною.

Аргумент ABSOLUTE (номер_рядка | @змінна_номера_рядка) повертає рядок за його абсолютним порядковим номером у повному результуючому наборі курсору. Номер рядка можна встановити за допомогою константи або як ім'я змінної, в якій зберігається номер рядка. Змінна повинна мати цілий тип даних. Вказуються як позитивні, і негативні значення. При вказівці позитивного значення рядок відраховується з початку набору, негативного – від кінця. Вибраний рядок стає поточним. Якщо вказано нульове значення, рядок не повертається.

Аргумент RELATIVE (кільк_рядка | @змінна_кіл_рядка) повертає рядок, що знаходиться через вказану кількість рядків після поточного. Якщо вказати негативне значення числа рядків, то буде повернуто рядок, що знаходиться за вказану кількість рядків перед поточним. Якщо вказати нульове значення, повернеться поточний рядок. Повернутий рядок стає поточним.

Щоб відкрити глобальний курсор, перед ім'ям потрібно вказати ключове слово GLOBAL. Ім'я курсорутакож може бути зазначено за допомогою змінної.

У конструкції INTO @ім'я_змінної [,...n] задається список змінних, в яких будуть збережені відповідні значення стовпців рядка, що повертається. Порядок вказівки змінних повинен відповідати порядку стовпців у курсорі, а тип даних змінної – типу даних у стовпці курсору. Якщо конструкція INTO не вказана, поведінка команди FETCH нагадуватиме поведінку команди SELECT – дані виводяться на екран.


Курсор - посилання контекстну область пам'яті. У деяких реалізаціях мови програмування 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.

Всім, хто дочитав до цього місця спасибі, сподіваюся це здасться комусь корисною.

Явний курсор є командою SELECT , явно визначеною у розділі оголошень програми. При оголошенні явного курсору йому надається ім'я. Для команд INSERT, UPDATE, MERGE та DELETE явні курсори визначатися не можуть.

Визначивши команду SELECT як курсор, програміст отримує контроль над основними стадіями вибірки інформації з бази даних Oracle. Він визначає коли відкрити курсор (OPEN), коли вибрати з нього рядки (FETCH), скільки вибрати рядків і коли закрити курсор за допомогою команди CLOSE . Інформація про поточний стан курсору доступна через його атрибути. Саме висока деталізація контролю робить очевидні курсори безцінним інструментом для програміста.

Розглянемо приклад:

1 FUNCTION jealousy_level (2 NAME_IN IN friends.NAME%TYPE) RETURN NUMBER 3 AS 4 CURSOR jealousy_cur 5 IS 6 SELECT location FROM friends 7 WHERE NAME = UPPER (NAME_IN); 8 8 jealousy_rec jealousy_cur%ROWTYPE; 9 retval NUMBER; 10 BEGIN 11 OPEN jealousy_cur; 13 12 FETCH jealousy_cur INTO jealousy_rec; 15 13 IF jealousy_cur%FOUND 14 THEN 15 IF jealousy_rec.location = "PUERTO RICO" 16 THEN retval:= 10; 17 ELSIF jealousy_rec.location = "CHICAGO" 18 THEN retval:= 1; 19 END IF; 20 END IF; 24 21 CLOSE jealousy_cur; 26 22 RETURN retval; 23 EXCEPTION 24 WHEN OTHERS THEN 25 IF jealousy_cur%ISOPEN THEN 26 CLOSE jealousy_cur; 27 END IF; 28 END;

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

Оголошення явного курсору

Щоб отримати можливість використовувати явний курсор, його необхідно оголосити в розділі об'яв блоку PL/SQL або пакета:

CURSOR ім'я_курсора [ ([ параметр [, параметр ...]) ] [ RETURN специфікація_геЕігп ] IS команда_SELECT ];

Тут ім'я курсора - ім'я курсора, що оголошується; спеіфішція_те?іт - необов'язкова секція RETURN; KOMaHdaSELECT - будь-яка допустима SQL-команда SELECT. Курсору також можуть передаватися параметри (див. розділ «Параметри курсора»). Нарешті, після команди SELECT...FOR UPDATE можна встановити список стовпців для оновлення (також див. далі). Після оголошення курсор відкривається командою OPEN, а вибірка рядків із нього здійснюється командою FETCH.

Декілька прикладів оголошень явних курсорів.

  • Курсор без параметрів. Результатом набору рядків цього курсору є набір ідентифікаторів компаній, вибраних з усіх рядків таблиці:
CURSOR company_cur IS SELECT company_id FROM company;
  • Курсор із параметрами.Результуючий набір рядків цього курсору містить єдиний рядок з ім'ям компанії, що відповідає значенню переданого параметра:
CURSOR name_cur (company_id_in IN NUMBER) IS SELECT name FROM company WHERE company_id = company_id_in;
  • Курсор із пропозицією RETURN. Результуючий набір рядків цього курсору містить усі дані таблиці employee для підрозділу з ідентифікатором 10:
CURSOR emp_cur RETURN employees%ROWTYPE IS SELECT * FROM employees WHERE department_id = 10;

Ім'я курсору

Ім'я явного курсора має мати довжину до 30 символів і відповідати тим самим правилам, що й інші ідентифікатори PL/SQL. Ім'я курсора не є змінною – це ідентифікатор вказівника на запит. Ім'я курсора не присвоюється значення, його не можна застосовувати у виразах. Курсор використовується лише у командах OPEN, CLOSE та FETCH, а також для уточнення атрибута курсору.

Оголошення курсору в пакеті

Явні курсори оголошуються в розділі оголошень PL/SQL. Курсор може оголошуватися лише на рівні пакета, але з конкретної процедурі чи функції пакета. Приклад оголошення двох курсорів у пакеті:

PACKAGE book_info IS CURSOR titles_cur IS SELECT title FROM books; CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; END;

Перший курсор titles_cur повертає лише назви книг. Другий, books_cur , повертає всі рядки таблиці books, в яких назви книг відповідають шаблону, заданому як параметр курсора (наприклад, «Всі книги, що містять рядок "PL/SQL"»). Зверніть увагу: у другому курсорі використовується секція RETURN, яка оголошує структуру даних, що повертається командою FETCH.

У секції RETURN можуть бути зазначені будь-які з таких структур даних:

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

Кількість виразів у списку вибірки курсору повинна відповідати кількості стовпців запису имя_таблицы%ROWTYPE, Kypcop%ROWTYPE або тип запису. Типи даних елементів також мають бути сумісні. Наприклад, якщо другий елемент списку вибірки має тип NUMBER , другий стовпець запису в секції RETURN не може мати тип VARCHAR2 або BOOLEAN .

Перш ніж переходити до детального розгляду секції RETURN та її переваг, давайте спочатку розберемося, для чого взагалі може знадобитися оголошення курсорів у пакеті? Чому не оголосити явний курсор у тій програмі, в якій він використовується – у процедурі, функції чи анонімному блоці?

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

Також варто розглянути можливість створення функції, що повертає курсорну змінну на базі REF CURSOR. Викликаюча програма здійснює вибірку рядків через курсорну змінну. За додатковою інформацією звертайтесь до розділу «Курсорні змінні та REF CURSOR».

Оголошуючи курсори у пакетах для повторного використання, слід враховувати одну важливу обставину. Усі структури даних, зокрема курсори, оголошені на «рівні пакета» (не всередині конкретної функції чи процедури), зберігають свої значення протягом усього сеансу. Це означає, що пакетний курсор залишатиметься відкритим доти, доки ви явно не закриєте його, або до завершення сеансу. Курсори, оголошені у локальних блоках, автоматично закриваються після завершення цих блоків.

А тепер давайте розберемося із секцією RETURN. Об'ява курсора в пакеті має одну цікаву особливість: заголовок курсора може бути відокремлений від його тіла. Такий заголовок, що більше нагадує заголовок функції, містить інформацію, яка необхідна програмісту для роботи: ім'я курсора, його параметри та тип даних, що повертаються. Тілом курсору служить команда SELECT. Цей прийом продемонстровано в новій версії оголошення курсору books_cur у пакеті book_info:

PACKAGE book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE; END; PACKAGE BODY book_info IS CURSOR books_cur (title_filter_in IN books.title%TYPE) RETURN books%ROWTYPE IS SELECT * FROM books WHERE title LIKE title_filter_in; END;

Усі символи до ключового слова IS утворюють специфікацію, а після IS слідує тіло курсора. Поділ оголошення курсору може бути двом цілям.

  • Приховування інформації. Курсор у пакеті є «чорною скринькою». Це зручно для програмістів, тому що їм не потрібно писати або навіть бачити команду SELECT . Достатньо знати, які записи повертає цей курсор, у якому порядку та які стовпці вони містять. Програміст, який працює з пакетом, використовує курсор як будь-який інший готовий елемент.
  • Мінімум перекомпіляції. Якщо приховати визначення запиту в тілі пакета, зміни в команду SELECT можна буде вносити, не змінюючи заголовок курсору в специфікації пакета. Це дозволяє вдосконалювати, виправляти і повторно компілювати код без перекомпіляції специфікації пакета, завдяки чому програми, що залежать від цього пакета, не будуть позначені як недійсні і їх також не потрібно буде перекомпілювати.

Відкриття явного курсору

Використання курсору починається з визначення у розділі оголошень. Далі оголошений курсор потрібно відкрити. Синтаксис оператора OPEN дуже простий:

OPEN ім'я_курсора [(аргумент [, аргумент...])];

Тут ім'я курсора - це ім'я раніше оголошеного курсору, а аргумент - значення, що передається курсору, якщо він оголошений зі списком параметрів.

Oracle також підтримує синтаксис FOR при відкритті курсору, який використовується як для курсорних змінних (див. розділ "Курсорні змінні та REF CURSOR"), так і для вбудованого динамічного SQL.

Відкриваючи курсор, PL/SQL виконує запит, що міститься в ньому. Крім того, він ідентифікує активний набір даних - рядки всіх таблиць, що беруть участь у запиті, відповідні критерію WHERE та умові об'єднання. Команда OPEN не отримує дані - це завдання команди FETCH.

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

Більше того, якщо команда SELECT містить секцію FOR UPDATE , усі рядки, що ідентифікуються курсором, блокуються при його відкритті.

При спробі відкрити вже відкритий курсор PL/SQL видасть таке повідомлення про помилку:

ORA-06511: PL/SQL: cursor already open

Тому перед відкриттям курсору слід перевірити його стан за значенням атрибуту %isopen:

IF NOT company_cur%ISOPEN THEN OPEN company_cur; END IF;

Атрибути явних курсорів описуються нижче, у присвяченому ним розділі .

Якщо в програмі виконується цикл FOR з використанням курсору, цей курсор не потребує явного відкриття (вибір даних, закриття). Ядро PL/SQL робить це автоматично.

Вибірка даних із явного курсору

Команда SELECT створює віртуальну таблицю - набір рядків, що визначаються умовою WHERE зі стовпцями, що визначаються списком стовпців SELECT . Таким чином, курсор представляє цю таблицю у програмі PL/SQL. Основним призначенням курсору у програмах PL/SQL є вибірка рядків для обробки. Вибір рядків курсору виконується командою FETCH:

FETCH ім'я_курсора INTO запис_або_список_змінних;

Тут ім'я курсора - ім'я курсора, з якого вибирається запис, а запис або список змінних - структури даних PL/SQL , які копіюється наступний рядок активного набору записів. Дані можуть поміщатися в запис PL/SQL (оголошений з атрибутом %ROWTYPE або оголошенням TYPE) або змінні (змінні PL/SQL або змінні прив'язки - як, наприклад, елементи Oracle Forms).

Приклади явних курсорів

Наступні приклади показують різні методи вибірки даних.

  • Вибір даних з курсору в запис PL/SQL:
DECLARE CURSOR company_cur is SELECT...; company_rec company_cur%ROWTYPE; BEGIN OPEN company_cur; FETCH company_cur INTO company_rec;
  • Вибір даних з курсору в змінну:
FETCH new_balance_cur INTO new_balance_dollars;
  • Вибірка даних із курсору в рядок таблиці PL/SQL, змінну та змінну прив'язки Oracle Forms:
FETCH emp_name_cur INTO emp_name (1), hiredate, :dept.min_salary;

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

Вибірка після обробки останнього рядка

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

Як не дивно, у цьому випадку PL/SQL не ініціює виняток. Він нічого не робить. Оскільки вибирати більше нічого, значення змінних у секції INTO команди FETCH не змінюються. Інакше висловлюючись, команда FETCH не встановлює значення цих змінних рівними NULL .

Псевдоніми стовпців явного курсору

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

Псевдонім (alias) стовпця є альтернативним ім'ям, зазначеним у команді SELECT для стовпця або виразу. Задавши відповідні псевдоніми в SQL * Plus, можна вивести результати довільного запиту в легкочитаному вигляді. У таких ситуаціях псевдоніми є обов'язковими. З іншого боку, при використанні явних курсорів псевдоніми обчислюваних стовпців необхідні в таких випадках:

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

Розглянемо наступний запит. Команда SELECT вибирає назви всіх компаній, які замовляли товари протягом 2001 року, а також загальну суму замовлень (передбачається, що для поточного екземпляра бази даних за замовчуванням використовується маска форматування DD-MON-YYYY):

SELECT company_name, SUM (inv_amt) FROM company c, invoice i WHERE c.company_id = i.company_id AND i.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001";

При виконанні цієї команди SQL*Plus буде отримано наступний результат:

COMPANY_NAME SUM (INV_AMT)
ACME TURBO INC. 1000
WASHINGTON HAIR CO. 25.20

Як бачите, заголовок стовпця SUM (INV_AMT) погано підходить для звіту, але для простого перегляду даних він годиться. Тепер виконаємо той же запит у програмі PL/SQL з використанням явного курсору та додамо псевдонім стовпця:

DECLARE CURSOR comp_cur IS SELECT c.name, SUM (inv_amt) total_sales FROM company C, invoice I WHERE C.company_id = I.company_id AND I.invoice_date BETWEEN "01-JAN-2001" AND "31-DEC-2001"; comp_rec comp_cur%ROWTYPE; BEGIN OPEN comp_cur; FETCH comp_cur INTO comp_rec; END;

Без псевдоніма я не зможу послатися на стовпець у структурі запису comp_rec. За наявності псевдоніма з обчислюваним стовпцем можна працювати так само, як з будь-яким іншим стовпцем запиту:

IF comp. END IF;

При вибірці рядка в запис, оголошений з атрибутом %ROWTYPE , доступ до стовпця, що обчислюється, можна буде отримати тільки по імені - адже структура запису визначається структурою самого курсору.

Закриття явного курсору

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

Синтаксис команди CLOSE:

CLOSE ім'я_курсора;

Нижче наводиться кілька важливих порад та міркувань, пов'язаних із закриттям явних курсорів.

  • Якщо курсор оголошено та відкрито у процедурі, не забудьте його закрити після завершення роботи з ним; інакше у вашому коді з'явиться витік пам'яті. Теоретично курсор (як і будь-яка структура даних) повинен автоматично закриватися та знищуватись при виході з області дії. Як правило, при виході з процедури, функції або анонімного блоку PL/SQL справді закриває всі відкриті курсори. Але це процес пов'язані з певними витратами ресурсів, тому з міркувань ефективності PL/SQL іноді відкладає виявлення і закриття відкритих курсорів. Курсори типу REF CURSOR за визначенням не можуть бути закриті неявно. Єдине, в чому можна бути впевненим, так це в тому, що після завершення роботи «найзовнішнього» блоку PL/SQL, коли управління буде повернено SQL або іншій програмі, PL/SQL неявно закриє всі відкриті цим блоком або вкладеними блоками курсори, крім REF CURSOR. У статті "Cursor reuse in PL/SQL static SQL" з Oracle Technology Network наводиться докладний аналіз того, як і коли PL/SQL закриває курсори. Вкладені анонімні блоки - приклад ситуації, коли PL/SQL не здійснює неявне закриття курсорів. Цікава інформація з цієї теми наведена у статті Джонатана Генніка "Does PL/SQL Implicitly Close Cursors?".
  • Якщо курсор оголошений у пакеті на рівні пакета і відкритий у деякому блоці або програмі, він залишиться відкритим доти, доки ви його явно не закриєте, або до завершення сеансу. Тому, завершивши роботу з курсором пакетного рівня, його слід негайно закрити командою CLOSE (і до речі, те саме слід робити в розділі винятків):
BEGIN OPEN my_package.my_cursor; ... Працюємо з курсором CLOSE my_package.my_cursor; EXCEPTION WHEN OTHERS THEN IF mypackage.my_cursor%ISOPEN THEN CLOSE my_package.my_cursor; END IF; END;
  • Курсор можна закривати тільки в тому випадку, якщо він був відкритий раніше; інакше буде ініційовано виключення INVALID_CURS0R . Стан курсору перевіряється за допомогою атрибуту %ISOPEN:
IF company_cur%ISOPEN THEN CLOSE company_cur; END IF;
  • Якщо в програмі залишиться занадто багато відкритих курсорів, їх кількість може перевищити значення бази даних OPEN_CURSORS . Отримавши повідомлення про помилку, насамперед переконайтеся, що оголошені в пакетах курсори закриваються після того, як потреба в них відпаде.

Атрибути явних курсорів

Oracle підтримує чотири атрибути (%FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNTM) для отримання інформації про стан явного курсору. Посилання на атрибут має наступний синтаксис: курсор % атрибут

Тут курсор – ім'я оголошеного курсору.

Значення, що повертаються атрибутами явних курсорів, наведено у табл. 1.

Таблиця 1.Атрибути явних курсорів

Значення атрибутів курсорів до та після виконання різних операцій із ними вказані в табл. 2.

Працюючи з атрибутами явних курсорів, необхідно враховувати таке:

  • При спробі звернутися до атрибута %FOUND, %NOTFOUND або %ROWCOUNT до відкриття курсору або після його закриття Oracle ініціює виключення INVALID CURSOR (ORA-01001).
  • Якщо після першого виконання команди FETCH результуючий набір рядків виявиться порожнім, атрибути курсору повертають такі значення: %FOUND = FALSE , %NOTFOUND = TRUE та %ROWCOUNT = 0.
  • У разі використання BULK COLLECT атрибут %ROWCOUNT повертає кількість рядків, витягнутих у задані колекції.

Таблиця 2.Значення атрибутів курсорів

Операція %FOUND %NOTFOUND %ISOPEN %ROWCOUNT
До OPEN Виняток
ORA-01001		 Виняток
ORA-01001		 FALSE Виняток
ORA-01001
Після OPEN NULL NULL TRUE 0
До першої вибірки FETCH NULL NULL TRUE 0
Після першої вибірки
FETCH		 TRUE FALSE TRUE 1
Перед наступними
FETCH		 TRUE FALSE TRUE 1
Після наступних FETCH TRUE FALSE TRUE Залежить від даних
Перед останньою вибіркою FETCH TRUE FALSE TRUE Залежить від даних
Після останньої вибірки FETCH TRUE FALSE TRUE Залежить від даних
Перед CLOSE FALSE TRUE TRUE Залежить від даних
Після CLOSE Виняток Виняток FALSE Виняток

Використання всіх цих атрибутів показано в наступному прикладі:

Раніше в блогах вже неодноразово наводилися приклади використання параметрів процедур та функцій. Параметри - це засіб передачі в програмний модуль і з нього. При правильному використанні вони роблять модулі більш корисними та гнучкими.

PL/SQL дозволяє передавати параметри курсорам. Вони виконують самі функції, як і параметри програмних модулів, і навіть кілька додаткових.

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

Кількість параметрів курсору не обмежена. Під час виклику OPEN для курсору необхідно встановити значення всіх параметрів (крім параметрів, для яких визначено значення за замовчуванням).

У яких випадках курсору потрібні параметри? Загальне правило тут те саме, що і для процедур і функцій: якщо передбачається, що курсор буде використовуватися в різних місцях і з різними значеннями в розділі WHERE, для нього слід визначити параметр. Порівняємо курсори з параметром і без. Приклад курсора без параметрів:

CURSOR joke_cur IS SELECT name, category, last_used_date FROM Jokes;

У результуючий набір курсору включаються всі записи таблиці joke. Якщо нам потрібно лише деяке підмножина рядків, у запит включається секція WHERE:

CURSOR joke_cur IS SELECT name, category, last_used_date FROM jokes WHERE category = "HUSBAND";

Для виконання цього завдання ми не стали використовувати параметри, але вони й не потрібні. У цьому випадку курсор повертає всі рядки, що належать до конкретної категорії. Але що робити, якщо при кожному зверненні до цього курсора категорія змінюється?

Курсори з параметрами

Звичайно, ми не визначатимемо окремий курсор для кожної категорії - це зовсім не узгоджується з принципом розробки додатків, керованих даними. Нам потрібен лише один курсор, але такий, для якого можна було б змінювати категорію – і він все одно повертав би необхідну інформацію. І найкращим (хоча й не єдиним) рішенням цього завдання є визначення параметризованого курсору:

PROCEDURE explain_joke (main_category_in IN joke_category.category_id%TYPE) IS /* || Курсор зі списком параметрів || із єдиного рядкового параметра. */ CURSOR joke_cur (category_in IN VARCHAR2) IS SELECT name, category, last_used_date FROM Joke WHERE category = UPPER (category_in); joke_rec joke_cur%ROWTYPE; BEGIN /* Тепер під час відкриття курсору йому передається аргумент */ OPEN joke_cur (main_category_in); FETCH joke_cur INTO joke_rec;

Між іменем курсору та ключовим словом IS тепер міститься список параметрів. Жорстко закодоване значення HUSBAND у пропозиції WHERE замінено посиланням на параметр UPPER (category_in). При відкритті курсору можна буде встановити значення HUSBAND , husband або HuSbAnD - курсор все одно працюватиме. Назва категорії, для якої курсор повинен повернути рядки таблиці joke, задається в операторі OPEN (у дужках) у вигляді літералу, константи або виразу. У момент відкриття курсору проводиться розбір команди SELECT, а параметр пов'язується зі значенням. Потім визначається результуючий набір рядків – і курсор готовий до вибірки.

Відкриття курсору з параметрами

Новий курсор можна відкривати із зазначенням будь-якої категорії:

OPEN joke_cur (Jokes_pkg.category); OPEN joke_cur ("husband"); OPEN joke_cur ("politician"); OPEN joke_cur (Jokes_pkg.relation | | "-IN-LAW");

Параметри курсору найчастіше використовуються в умові WHERE, але посилатися на них можна і в інших місцях команди SELECT:

DECLARE CURSOR joke_cur (category_in IN ARCHAR2) IS SELECT name, category_in, last_used_date FROM joke WHERE category = UPPER (category_in);

Замість того, щоб зчитувати категорію з таблиці, ми просто підставляємо параметр category_in до списку вибірки. Результат залишається незмінним, тому що умова WHERE обмежує категорію вибірки значенням параметра.

Область дії параметра курсору

Область дії параметра курсору обмежується цим курсором. На курсор не можна посилатися за межами команди SELECT , пов'язаної з курсором. Нижче наведений фрагмент PL/SQL не компілюється, тому що ідентифікатор program_name не є локальною змінною в блоці. Це формальний параметр курсору, який визначено лише всередині курсору:

DECLARE CURSOR scariness_cur (program_name VARCHAR2) IS SELECT SUM (scary_level) total_scary_level FROM tales_from_the_crypt WHERE prog_name = program_name; BEGIN program_name:= "THE BREATHING MUMMY"; /* Неприпустиме посилання */ OPEN scariness_cur (program_name); .... CLOSE scariness_cur; END;

Режими параметра курсору

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

Значення параметрів за замовчуванням

Параметрам курсорів можуть надаватися значення за промовчанням. Приклад курсора зі значенням параметра за замовчуванням:

CURSOR emp_cur (emp_id_in NUMBER:= 0) IS SELECT employee_id, emp_name FROM employee WHERE employee_id = emp_id_in;

Оскільки для параметра emp_id_in визначено значення за промовчанням, у команді FETCH його значення можна не вказувати. У цьому випадку курсор поверне інформацію про співробітника із кодом 0.

Реалізація курсору у базі даних нагадує клас 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 in 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 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 "<>" WHILE @@FETCH_STATUS = 0 BEGIN SELECT @message = " " + @product PRINT @message FETCH NEXT FROM product_cursor INTO @product END CLOSE product_cursor DEALLOCATE product_cursor -- Get the next vendor. END CLOSE vendor_cursor; DEALLOCATE vendor_cursor;

 

 
Це цікаво: