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

У Вікіпедії є статті про інші значення цього терміна: Курсор.

В інформатиці курсор бази даних — керувальна структура, яка забезпечує обхід записів у базі даних. Курсори полегшують подальшу обробку в поєднанні з обходом, як-от отримання, додавання та видалення записів бази даних. Характеристика обходу курсору бази даних наближує курсори до поняття ітератора в мовах програмування.

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

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

Курсор можна розглядати як указівник на один рядок у наборі. Курсор може посилатися лише на один рядок за раз, але за необхідності може переміщуватися до інших рядків результатного набору.

Використання

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

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

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

У цьому розділі представлено способи, якими стандарт SQL:2003 визначає, як використовувати курсори в застосунках у вбудованому SQL. Не всі прив'язки застосунків для реляційних систем баз даних дотримуються цього стандарту, а деякі (наприклад, CLI[en] або 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, у цьому випадку СКБД намагається якомога більше застосовувати чутливість.

WITH HOLD

Курсори зазвичай закриваються автоматично наприкінці транзакції, тобто коли відбувається 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

Мова 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;

Див. також

Джерела

  • Date, Christopher J. (2005). Database in Depth. O'Reilly & Associates. ISBN 0-596-10012-4.
  • Connolly, Thomas M.; Begg, Carolyn E. (2002). Database Systems. Addison-Wesley. ISBN 0-321-21025-5.
  • Elmasri, Ramiz; Navathe, Shamkant B. (1994). Fundamentals of Database Systems. Addison-Wesley. ISBN 0-201-54263-3.
  • Matthew, Neil; Stones, Richard (2001). 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.

Посилання