Курсоры с блокировкой строк таблицы

9.3.1. Предложение SELECT … FOR UPDATE

Используется для упреждающей блокировки набора строк, подпадающего под действие SELECT, при выполнении OPEN для курсора. Блокировка будет снята ближайшим оператором COMMIT или ROLLBACK.

 

Синтаксис:

 

SELECT … FROM … FOR UPDATE [OF список_имен_столбцов ] [NOWAIT];

 

Если указан список_имен_столбцов, то блокировка будет касаться только строк, на которые этот список распространяется. Пример:

 

DECLARE

           CURSOR clerks_in_new_york IS

SELECT empno, sal, comm

FROM emp, dept

WHERE emp.deptno = dept.deptno

           AND job = 'CLERK'

           AND loc = 'NEW YORK'

FOR UPDATE OF emp.sal;

BEGIN

           …

 

Здесь при открытии (OPEN) курсора будут заблокированы только строки таблицы emp, так как только ее столбцы фигурируют во конструкции FOR UPDATE.

 

 

Возможность изменять строки, выбираемые курсором

Если объявлен курсор с конструкцией FOR UPDATE, то к нему можно применять операторы UPDATE и DELETE со специальным указанием во фразе WHERE:

 

[UPDATE | DELETE] … WHERE CURRENT OF имя _курсора;

 

В этом случае модифицироваться будут строки, охваченные курсором с FOR UPDATE, так что дополнительного повторения UPDATE/DELETE … WHERE … не потребуется.

 

Упражнение. Выполнить следующий код и объяснить его работу:

 

SAVEPOINT start_perestroika;

 

DECLARE

CURSOR emp_cur IS

SELECT empno, sal, job, deptno

FROM emp

WHERE job = 'CLERK' OR deptno = 10

FOR UPDATE;

BEGIN

FOR emp_rec IN emp_cur LOOP

CASE

WHEN emp_rec.job = 'CLERK' THEN

DELETE FROM emp WHERE CURRENT OF emp_cur;

WHEN emp_rec.deptno = 10 THEN

UPDATE emp SET sal = sal * 10 WHERE CURRENT OF emp_cur;

ELSE NULL;

END CASE;

END LOOP;

END;

/

 

SELECT * FROM emp ORDER BY deptno, job;

 

ROLLBACK TO SAVEPOINT start_perestroika;

 

SELECT * FROM emp ORDER BY deptno, job;

 

Конструкция WHERE CURRENT OF позволяет вносить изменения только в одну базовую таблицу предложения SELECT. Если SELECT … FOR UPDATE блокирует строки сразу нескольких таблиц, конструкция WHERE CURRENT OF работать не будет.

 

 

Ссылки на курсор

Ссылка на курсор – это именованная структура данных ссылающаяся на курсор, ссылающийся, в свою очередь, на массив выбираемых курсором данных. Ее можно использовать начиная с версии Oracle 7.2 для более простого извлечения данных-результата или для маскировки незначительных изменений в SQL-запросе. Синтаксис объявления типа следующий:

 

TYPE имя_типа_ссылки_на_курсор IS REF CURSOR [RETURN тип_записи ];

 

Если конструкция RETURN присутствует, ссылка на курсор называется строгой; если нет – нестрогой. Нестрогая может ссылаться на любой запрос, а строгая – только тот, что возвращает результат указанного типа.

 

Пример описания обоих типов ссылки на курсор:

 

DECLARE

TYPE any_curtype IS REF CURSOR;

generic_curvar any_curtype;

TYPE company_curtype IS REF CURSOR RETURN dept%ROWTYPE;

company_cur company_curtype;

 

BEGIN

           …

 

Открытие курсора с помощью переменной-ссылки на курсор:

 

OPEN ссылка_на_курсор FOR предложение_SELECT;

 

Команды FETCH и CLOSE используются как обычно.

 

Для удобства программирования поддерживается «системный» тип SYS_REFCURSOR нестрогой ссылки на курсор:

 

DECLARE

generic_cursor SYS_REFCURSOR;

someemployee emp.ename%TYPE;

BEGIN

OPEN generic_cursor FOR ' SELECT ename FROM emp ';

FETCH generic_cursor INTO someemployee;

DBMS_OUTPUT.PUT_LINE('Some employee: ' || someemployee);

CLOSE generic_cursor;

END;

/

 

Предложения TYPE здесь не понадобилось. Более полные примеры использования приводятся выше («Встроенный динамический SQL») и ниже по тексту.

 

Ограничения на использование ссылки на курсор:

 

- ссылки на курсор не могут объявляться как переменные пакета PL/SQL, и их нельзя передавать через переменные пакета

- ссылку на курсор нельзя связывать с блокирующим предложением SELECT … FOR UPDATE (в версии 10 уже можно)

- ссылкам на курсор нельзя присваивать значение NULL (в версии 10 уже можно) и их нельзя сравнивать друг с другом (но их можно присваивать друг другу)

- ссылки на курсор нельзя хранить в столбцах таблиц и в элементах коллекции

- ссылки на курсор нельзя передавать от сервера к серверу с помощью RPC

- ссылки на курсор нельзя использовать с пакетом DBMS_SQL

 

 

Примеры техники использования

Ссылка на курсор позволяет открыть курсор процедурой на сервере и передать с помощью ссылки в клиентской программе возможность работать с курсором. Ниже эта техника иллюстрируется в простом и в более реальным вариантах.

 

 

9.4.1.1. Простой пример

Выдадим в SQL*Plus:

 

VARIABLE refcur REFCURSOR

 

DECLARE

TYPE rct IS REF CURSOR;

somename VARCHAR2(20);

 

PROCEDURE getcur1 (rc OUT rct) IS BEGIN OPEN rc FOR ' SELECT ename FROM emp'; END;

PROCEDURE getcur2 (rc OUT rct) IS BEGIN OPEN rc FOR ' SELECT dname FROM dept'; END;

PROCEDURE getcur3 (rc OUT rct, num IN NUMBER) IS

BEGIN OPEN rc FOR ' SELECT dname FROM dept WHERE deptno = ' || TO_CHAR (num); END;

PROCEDURE fetchandclose (rc IN rct) IS

BEGIN

LOOP

FETCH rc INTO somename;

EXIT WHEN rc%NOTFOUND;

DBMS_OUTPUT.PUT_LINE (somename);

END LOOP;

CLOSE rc;

END;

 

BEGIN

getcur1 (:refcur); fetchandclose (:refcur);

 

getcur2 (:refcur); fetchandclose (:refcur);

 

getcur3 (:refcur, 30); fetchandclose (:refcur);

END;

/

 

 

Пример более реальный

В реальной программе описание типа (RCT) из примера выше чаще всего будет вынесено в пакет, а переменная привязки (REFCUR) и программа обработки курсора (FETCHANDCLOSE) будут вынесены в клиентскую программу, написанную, например, на Java, C или на Object Pascal, в то время как программы вычисления курсора (GETCUR1, GETCUR2, GETCUR3) останутся на сервере. Вот пример, как это можно организовать.

 

Создадим обобщенный пакет:

 

CREATE OR REPLACE PACKAGE generic_ref_cursor AS

TYPE refcur IS REF CURSOR;

PROCEDURE get_ref_cursor (sqlselect IN VARCHAR2, rc OUT refcur);

END;

/

 

CREATE OR REPLACE PACKAGE BODY generic_ref_cursor AS

PROCEDURE get_ref_cursor (sqlselect IN VARCHAR2, rc OUT refcur) AS

BEGIN

OPEN rc FOR sqlselect;

END;

END;

/

 

Возможный вариант использования пакета в PL/SQL и SQL*Plus:

 

SET SERVEROUTPUT ON

VARIABLE refcur REFCURSOR

 

DECLARE

PROCEDURE fetchandclose (rc IN generic_ref_cursor.refcur) IS

somename VARCHAR2(20);

BEGIN

LOOP

FETCH rc INTO somename;

EXIT WHEN rc%NOTFOUND;

DBMS_OUTPUT.PUT_LINE (somename);

END LOOP;

CLOSE rc;

END;

 

BEGIN

generic_ref_cursor.get_ref_cursor ('SELECT ename FROM emp',:refcur);

fetchandclose (:refcur);

 

generic_ref_cursor.get_ref_cursor ('SELECT dname FROM dept',:refcur);

fetchandclose (:refcur);

 

generic_ref_cursor.get_ref_cursor ('SELECT ename, sal FROM emp',:refcur);

-- ... а результат выдадим в SQL * Plus:

END;

/

 

PRINT refcur

 

BEGIN

generic_ref_cursor.get_ref_cursor ('SELECT * FROM emp',:refcur);

END;

/

 

PRINT refcur

 

Возможный вариант использования этой же техники в клиентской программе на Java:

 

import java.sql.CallableStatement;

import java.sql.ResultSet;

import oracle.jdbc.driver.OracleCallableStatement;

import oracle.jdbc.driver.OracleTypes;

...

...

 

CallableStatement cst;

OracleCallableStatement ocst;

ResultSet rs;

 

cst = cn.prepareCall

   (" BEGIN generic_ref_cursor.get_ref_cursor (?,?); END; ");

cst.setString (1, " SELECT sal FROM emp ");

cst.registerOutParameter (2, OracleTypes.CURSOR);

 

cst.execute ();

 

ocst = (OracleCallableStatement) cst;

 

rs = ocst. getCursor (2);

 

while (rs.next ()) { System.out.println (rs.getInt (1)); }

 

...

 

 

10. Несколько примеров использования циклов и курсоров

Ниже приводится несколько типичных примеров разных использования курсоров и циклов разными способами.

 

Пример «низкоуровневого» использования:

 

DECLARE

CURSOR dept_cur IS SELECT dname FROM dept;

dname_var dept.dname%TYPE;

BEGIN

OPEN dept_cur;

LOOP

FETCH dept_cur INTO dname_var;

EXIT WHEN dept_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE (dname_var);

END LOOP;

CLOSE dept_cur;

END;

/

 

Аналогичный курсор типа «строка»:

 

DECLARE

CURSOR dept_cur IS SELECT * FROM dept;

dept_rec dept_cur%ROWTYPE;

BEGIN

OPEN dept_cur;

LOOP

FETCH dept_cur INTO dept_rec;

EXIT WHEN dept_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE (dept_rec.dname);

END LOOP;

CLOSE dept_cur;

END;

/

 

Пример перебора результата SQL-запроса более высокого уровня:

 

BEGIN

FOR department IN (SELECT * FROM dept) LOOP

DBMS_OUTPUT.PUT_LINE (department.dname);

END LOOP;

END;

/

 

То же самое с помощью явного курсора. Обратите внимание, что в этом случае появляется возможность обратиться к атрибуту курсора:

 

DECLARE

CURSOR departments IS SELECT * FROM dept;

BEGIN

FOR department IN departments LOOP

DBMS_OUTPUT.PUT_LINE (departments%ROWCOUNT || department.dname);

END LOOP;

END;

/

 

Пример перебора для курсора с параметром:

 

DECLARE

CURSOR coworkers (dnumber IN NUMBER) IS

SELECT ename FROM emp WHERE deptno = dnumber;

BEGIN

FOR employee IN coworkers (20) LOOP

DBMS_OUTPUT.PUT_LINE (employee.ename);

END LOOP;

END;

/

 

То же самое, но параметр с умолчанием:

 

DECLARE

CURSOR coworkers (dnumber IN NUMBER := 10) IS

SELECT ename FROM emp WHERE deptno = dnumber;

BEGIN

FOR employee IN coworkers (20) LOOP

DBMS_OUTPUT.PUT_LINE (employee.ename);

END LOOP;

 

FOR employee IN coworkers () LOOP -- скобки можно и опустить

DBMS_OUTPUT.PUT_LINE (employee.ename);

END LOOP;

END;

/

 

Пример перебора по ссылке на курсор см. выше («Встроенный динамический SQL»).