Курсор (бази даних)

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

1. Оголосити курсор, який визначає результатний набір.
2. Відкрити курсор для встановлення результатного набору.
3. Витягнути дані у необхідні локальні змінні з курсору, по одному рядку за раз.
4. Закрити курсор після завершення.

Для роботи з курсорами необхідно використовувати наступні інструкції SQL

У цьому розділі представлено способи, якими стандарт SQL:2003 визначає, як використовувати курсори в застосунках у вбудованому SQL. Не всі прив'язки застосунків для реляційних систем баз даних дотримуються цього стандарту, а деякі (наприклад, CLI або JDBC) використовують інший інтерфейс.

Програміст робить курсор відомим для СКБД за допомогою інструкції DECLARE … CURSOR і призначення курсору (обов'язкового) імені:

DECLARE «назва_курсору» CURSOR IS SELECT … FROM …

Перш ніж код може отримати доступ до даних, він повинен відкрити курсор за допомогою інструкції OPEN. Безпосередньо після успішного відкриття курсор розташовується перед першим рядком результатного набору.

OPEN «назва_курсору»

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

FETCH «назва_курсору» INTO …

Щойно застосунок обробив усі доступні рядки чи операція витягнення має позиціюватися на неіснуючому рядку (пор. прокручувані курсори нижче), СКБД повертає SQLSTATE «02000» (зазвичай супроводжується SQLCODE +100) для позначення кінця результатного набору.

Останній крок передбачає закриття курсору з використанням інструкції CLOSE:

CLOSE «назва_курсору»

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

Програмісти можуть оголошувати курсори прокручуваними чи ні. Прокручуваність позначає напрямок переміщення курсору.

З непрокручуваними (або лише вперед) курсорами можна FETCH кожен рядок щонайбільше один раз, а курсор автоматично переміщується до наступного рядка. Після витягнення останнього рядка та спроби наступного витягнення курсор розміщується після останнього рядка, що видає наступний код: SQLSTATE 02000 (SQLCODE +100).

Програма може позиціювати прокручуваний курсор будь-де в результатному наборі, використовуючи інструкцію SQL FETCH. Ключове слово SCROLL повинно бути вказано під час оголошення курсору. За замовчуванням NO SCROLL, хоча різні мовні зв'язування на кшталт JDBC можуть застосовувати різні значення за замовчуванням.

DECLARE «назва_курсору» «чутливість» SCROLL CURSOR FOR SELECT … FROM …

Цільову позицію для прокручуваного курсору можна вказати відносно (від поточної позиції курсору) чи абсолютно (від початку результатного набору).

FETCH [ NEXT | PRIOR | FIRST | LAST ] FROM «назва_курсору»
FETCH ABSOLUTE «n» FROM «назва_курсору»
FETCH RELATIVE «n» FROM «назва_курсору»;

Прокручувані курсори потенційно можуть кілька разів отримувати доступ до одного рядка в результатному наборі. Таким чином, модифікації даних (операції вставки, оновлення, видалення) з інших транзакцій можуть упливати на результат. Курсор може бути SENSITIVE або INSENSITIVE для таких модифікацій даних. Чутливий курсор підбирає модифікації даних, що впливають на результатний набір курсору, а нечутливий курсор — ні. Крім того, курсор може бути INSENSITIVE, у цьому випадку СКБД намагається якомога більше застосовувати чутливість.

Курсори зазвичай закриваються автоматично наприкінці транзакції, тобто коли відбувається COMMIT або ROLLBACK (або неявне припинення транзакції). Цю поведінку можна змінити, якщо курсор оголошений з використанням виразу WITH HOLD (за замовчуванням WITHOUT HOLD). Утримуваний курсор залишається відкритим після COMMIT і закривається на ROLLBACK (деякі СКБД відхиляються від цієї стандартної поведінки, а також зберігають утримувані курсори відкритими після ROLLBACK).

DECLARE назва_курсору CURSOR WITH HOLD FOR SELECT … FROM …

Коли відбувається COMMIT, утримуваний курсор позиціюється перед наступним рядком. Таким чином, інструкція позиційованого UPDATE чи DELETE буде успішною тільки після того, як операція FETCH відбулася спочатку в транзакції.

Варто зауважити, що JDBC визначає курсори утримуваними за замовчуванням. Це робиться тому, що JDBC також за замовчуванням активує автоматичну фіксацію.

Курсори можуть використовуватися не тільки для витягнення даних із СКБД у застосунок, а й для ідентифікації рядка в таблиці для оновлення або видалення. Стандарт SQL:2003 для цієї мети визначає інструкції SQL позиційованого оновлення та видалення. Такі інструкції не використовують регулярні вирази WHERE з предикатами. Натомість курсор ідентифікує рядок. Курсор повинен бути відкритим і вже позиційованим у рядку за допомогою інструкції FETCH.

UPDATE «назва_таблиці»
SET    …
WHERE  CURRENT OF «назва_курсору»

DELETE
FROM   «назва_таблиці»
WHERE  CURRENT OF «назва_курсору»

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

Використання курсорів у розподілених транзакціях (середовища X/Open XA), які контролюються з використанням монітору транзакцій, нічим не відрізняється від курсорів у нерозподілених транзакціях.

Проте, слід бути уважним при використанні утримуваних курсорів. З'єднання можуть використовуватися різними застосунками. Таким чином, щойно транзакцію було закінчено та зафіксовано, подальша транзакція (що працює в іншому застосунку) може успадковувати наявні утримувані курсори. Тому розробник застосунку повинен знати про цю ситуацію.

Мова XQuery дозволяє створювати курсори з використанням функції subsequence().

Формат:

let $displayed-sequence := subsequence($result, $start, $item-count)

Де $result — результат початкового XQuery, $start — номер елементу для запуску, а $item-count — кількість елементів, для повернення.

Це також можна зробити з використанням предикату:

let $displayed-sequence := $result[$start to $end]

Де $end — кінцева послідовність.

Повні приклади див. у Шаблон:Wikibooks-inline.

Наступна інформація може відрізнятися залежно від конкретної системи баз даних.

Витягнення рядка з курсору щоразу може призводити до round-trip мережі. Це використовує набагато більше пропускної здатності мережі, ніж зазвичай потрібно для виконання єдиної інструкції SQL на кшталт DELETE. Повторні англ. round trips мережі можуть сильно сповільнити операції з використанням курсору. Деякі СУБД намагаються зменшити цей ефект, використовуючи блокове витягнення. Блокове витягнення означає, що декілька рядків надсилаються разом від сервера до клієнта. Клієнт зберігає весь блок рядків у локальному буфері й отримує рядки звідти, доки цей буфер не вичерпається.

Курсори виділяють ресурси на сервері, як-от блокування, пакети, процеси та тимчасове зберігання. Наприклад, Microsoft SQL Server реалізує курсори створенням тимчасової таблиці та заповненням її результатним набором запиту. Якщо курсор не закрито належним чином (звільнено), ресурси не будуть звільнені до закриття самого сеансу (з'єднання) SQL. Це витрачання ресурсів на сервері може призвести до зниження продуктивності та відмов.

Таблиця EMPLOYEES

SQL> desc EMPLOYEES_DETAILS;
Назва                                     Порожнє? Тип
----------------------------------------- -------- --------------------

EMPLOYEE_ID                               NOT NULL NUMBER(6)
FIRST_NAME                                         VARCHAR2(20)
LAST_NAME                                 NOT NULL VARCHAR2(25)
EMAIL                                     NOT NULL VARCHAR2(30)
PHONE_NUMBER                                       VARCHAR2(20)
HIRE_DATE                                 NOT NULL DATE
JOB_ID                                    NOT NULL VARCHAR2(10)
SALARY                                             NUMBER(8,2)
COMMISSION_PCT                                     NUMBER(2,2)
MANAGER_ID                                         NUMBER(6)
DEPARTMENT_ID                                      NUMBER(4)

SAMPLE CURSOR KNOWN AS EE

CREATE OR REPLACE 
PROCEDURE EE AS
BEGIN

DECLARE
	v_employeeID EMPLOYEES_DETAILS.EMPLOYEE_ID%TYPE;
	v_FirstName EMPLOYEES_DETAILS.FIRST_NAME%TYPE;
	v_LASTName EMPLOYEES_DETAILS.LAST_NAME%TYPE;
	v_JOB_ID EMPLOYEES_DETAILS.JOB_ID%TYPE := 'IT_PROG';

Cursor c_EMPLOYEES_DETAILS IS
	SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
	FROM EMPLOYEES_DETAILS
	WHERE JOB_ID = 'v_JOB_ID';

BEGIN
	OPEN c_EMPLOYEES_DETAILS;

	LOOP
		FETCH c_EMPLOYEES_DETAILS INTO v_employeeID, v_FirstName, v_LASTName;

		DBMS_OUTPUT.put_line(v_employeeID);
		DBMS_OUTPUT.put_line(v_FirstName);
		DBMS_OUTPUT.put_line(v_LASTName);

		EXIT WHEN c_EMPLOYEES_DETAILS%NOTFOUND;
	END LOOP;

	CLOSE c_EMPLOYEES_DETAILS;
END;

END;

• Ітератор
• Проблеми SQL, які вимагають курсорів

• Date, Christopher J.. Database in Depth. O'Reilly & Associates. ISBN 0-596-10012-4.
• Connolly, Thomas M.; Begg, Carolyn E.. Database Systems. Addison-Wesley. ISBN 0-321-21025-5.
• Elmasri, Ramiz; Navathe, Shamkant B. Fundamentals of Database Systems. Addison-Wesley. ISBN 0-201-54263-3.
• Matthew, Neil; Stones, Richard. Beginning Databases with PostgreSQL: From Novice to Professional. Apress. ISBN 1-59059-478-9.
• Kyte, Thomas. Expert One-On-One: Oracle. Apress. ISBN 1-59059-525-4.
• Loney, Kevin. Oracle Database 10g: The Complete Reference. Oracle Press. ISBN 0-07-225351-7.

• Descriptions from Portland Pattern Repository
• Berkeley DB Reference Guide: Cursor operations. sleepycat.com (англійською).
• PostgreSQL cursors. PostgreSQL (англійською).
• MySQL's Cursor Documentation. dev.mysql.com (англійською).
• FirebirdSQL cursors documentation. Firebird (англійською).