Место PL/SQL в архитектуре Oracle

Введение в PL/SQL

Материалы учебного курса

 

 

Владимир Викторович Пржиялковский

[email protected]

www.ccas.ru/prz/

 

29 декабря 2005 г.

Москва

 

Введение в PL/SQL___ 1

1. Основные понятия 4

2. Основные типы и структуры данных_ 6

3. Выражения 10

4. Основные управляющие структуры_ 11

5. Подпрограммы_ 15

6. Взаимодействие с базой данных: статический SQL_ 17

7. Регулирование изменений в БД_ 19

8. Встроенный динамический SQL_ 23

9. Использование курсоров_ 25

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

11. Обработка исключительных ситуаций_ 35

12. Хранимые процедуры и функции_ 40

13. Триггерные процедуры_ 47

14. Пакеты в PL/SQL_ 54

15. Вызов функций PL/SQL в предложениях SQL_ 59

16. Более сложные типы данных: коллекции_ 62

17. Отладка процедур в PL/SQL_ 78

18. Системы программирования для PL/SQL_ 81

19. Системные пакеты PL/SQL_ 82

Справочная часть_ 97

20. Атрибуты триггерных процедур уровня схемы БД и событий в СУБД_ 97

Страница для заметок_ 101

 

Основные понятия

Место PL/SQL в архитектуре Oracle

 

Предоставляемый PL/SQL-машине код представлен в промежуточном виде и фактически интерпретируется. Начиная с версии СУБД 9, однако, появиласть возможность транслировать программы на PL/SQL в машинный код.

 

 

Общая структура программы на PL/SQL

Блок в PL/SQL.

 

 

Именованый и неименованый (анонимный – при отсутствии заголовка) блок.

 

Раздел выполнения может наряду с программным кодом содержать произвольное число других (вложенных) блоков PL/SQL.

 

Пример блока PL/SQL (анонимного) в SQL*Plus:

 

SET SERVEROUTPUT ON

 

DECLARE

var VARCHAR2(20);

var1 var%TYPE;

BEGIN

/*

комментарий

*/          -- строчный комментарий

NULL;                     -- пустой оператор, ничего не делать

var1:= 'Hello world';      -- оператор присвоенния

DBMS_OUTPUT.PUT_LINE (var1); -- обращение к процедуре

BEGIN                     -- вложеный блок – тоже оператор

DBMS_OUTPUT.PUT_LINE ('Hello once more');

END;                      -- конец вложеного блока

END;

/

 

 

Основные типы и структуры данных

Набор типов переменных в PL/SQL отличен от набора типов в Oracle SQL. Начиная с версии 9 это чистое расширение, так как с этой версии СУБД PL/SQL автоматически поддерживает все типы Oracle SQL за счет использования общего одного модуля обработки SQL-запросов (до этого PL/SQL использовал собственную SQL-машину).

 

Все переменные в PL/SQL можно отнести к одному из следующих видов:

 

Вид данных	Описание
Скалярный	Переменные, представляющие собой ровно одно значение (числовое, дату и т.д.)
Составной	Переменные, представляющие именованную группу значений (запись, массив)
Ссылка	Ссылка на объект или курсор
LOB	Указание на массив большого размера

 

 

Скалярные переменные

Числовые типы

Делятся на типы с десятичным хранением (NUMBER) и с двоичным (целые числа типов BINARY_INTEGER и PLS_INTEGER).

 

Подтипы типа NUMBER:

 

Тип PL/ SQL	Совместимость	Соответствующий тип в Oracle
DEC(точность, масштаб)	ANSI	NUMBER(точность, масштаб)
DECIMAL(точность, масштаб)	IBM	NUMBER(точность, масштаб)
DOUBLE PRECISION	ANSI	NUMBER
FLOAT(двоичная точность)	ANSI, IBM	NUMBER
INT	ANSI	NUMBER(38)
INTEGER	ANSI, IBM	NUMBER(38)
NUMERIC(точность, масштаб)	ANSI	NUMBER(точность, масштаб)
REAL	ANSI	NUMBER
SMALLINT	ANSI, IBM	NUMBER(38)

 

Подтипы BINARY_INTEGER: NATURAL, NATURALN (вариант с NOT NULL), POSITIVE, POSITIVEN (вариант с NOT NULL), SIGNTYPE (-1, 0, 1).

 

Тип PLS_INTEGER по внешнему проявлению не отличается от BINARY_INTEGER, но имеет формат хранения компьютера, а не собственный двоичный формат фирмы Oracle, что делает его более производительным при больших вычислениях. Однако с версии 10 внутренний формат BINARY_INTEGER приведен к формату PLS_INTEGER и разницы между этими типами нет совсем.

 

 

Строковые типы

Тип	Описание
CHAR	Строки фиксированной длины до 32767 байт (то есть больше, чем предел в 2000 байт в Oracle 7 и 4000 байт в Oracle 8 для хранимых в БД строк)
VARCHAR2	Строки переменной длины до 32767 байт (то есть больше, чем предел в 2000 байт в Oracle 7 и 4000 байт в Oracle 8 для хранимых в БД строк)
NCHAR NVARCHAR2	NLS-символьные типы. Позволяют обрабатывать символьные данные в мультибайтовой кодировке Unicode.
LONG	Строки переменной длины до 32767 байт. Тип сохранен для обратной совместимости; в частности встречается в некоторых справочных таблицах
RAW	Байтовая строка переменной длины до 32767 байт (в версии 7 было 2000 байт). Не перекодируется при передаче по Oracle Net
LONG RAW	Байтовая строка переменной длины до 32767 байт. Тип сохранен для обратной совместимости версий Oracle
ROWID	Двоичный массив фиксированной длины для хранения физического адреса данных Oracle в шестнадцатеричном формате BBBBBBBB.RRRR.FFFF (Oracle 7) и в формате OOOOOOFFFBBBBBBRRR (Oracle 8)
UROWID (Oracle 8.1)	«Универсальный» формат для ROWID: шестнадцатеричная строка переменной длины (до 4000 байт) с логическим значением ROWID. Используется для хранения адресов строк в индексно организованных (index organized) таблицах или в таблицах DB2 (черех шлюз)

 

 

Моменты времени и интервалы

То же, что в Oracle SQL плюс несколько специальных подтипов.

 

Следующие подтипы в PL/SQL используются для передачи параметров с максимально возможной точностью («без ограничения точности»):

 

Подтип...	... соответствует типу
TIMESTAMP_UNCONSTRAINED	TIMESTAMP (9)
TIMESTAMP_TZ_UNCONSTRAINED	TIMESTAMP (9) WITH TIME ZONE
YMINTERVAL_UNCONSTRAINED	INTERVAL YEAR (9) TO MONTH
DSINTERVAL_UNCONSTRAINED	INTERVAL DAY (9) TO SECOND (9)
TIME_UNCONSTRAINED	TIME (9)(*)
TIME_TZ_UNCONSTRAINED	TIME (9) WITH TIME ZONE(*)

^(*) Как самостоятельный тип в Oracle не реализован, фактически недореализован, но подтип возможен для описания переменных и параметров в PL/SQL.

 

 

Булевы переменные

Фактически – трехзначные переменные с допустимыми значениями TRUE, FALSE и NULL. Правила сравнения и операции – те же, что в Oracle SQL.

 

 

LOB-типы

В отличие от скалярных типов, LOB-типы позволяют хранить не сами данные, а «локаторы» (указатели) на данные, размещенные вне, либо внутри БД.

 

Тип	Описание
BFILE	Указатель на файл с данными в операционной системе. Средствами Oracle данные можно только читать.
BLOB	Указатель на большой неструктурированный массив в БД
CLOB	Указатель на большой символьный массив в БД
NCLOB	Указатель на большой NLS-символьный массив в БД

 

 

Записи

Записи являются более сложными, чем скалярные, структурами данных; данными составного типа. В PL/SQL они бывают трех видов:

 

n повторяющими структуру таблицы в БД

n повторяющими структуру курсора в программе

n явно заданными пользователем в программе

 

 

Присвоения

Индивидуальные поля указываются через точку (например, new_emp_rec.name.first_name) и могут выставляться, читаться и сравниваться самостоятельно. 

 

Записи как целое могут только присваиваться друг другу, но не могут сравниваться логическими операциями (=). 

 

Присвоение значений записи может также выполняться:

 

- конструкцией SELECT … INTO имя_записи FROM …

- конструкцией FETCH имя_курсора INTO { имя_записи | список_полей_записи }

 

(примеры см. ниже).

 

 

Пользовательские подтипы

Для скаляров в программе можно определять собственные подтипы:

 

- без дополнительных ограничений:

 

SUBTYPE dollar_amount_type IS NUMBER;

credit dollar_amount_type;

SUBTYPE names_typ IS emp.ename%TYPE;

name1 names_typ;

name2 names_typ;

...

 

- с дополнительными ограничениями:

 

var_dollar NUMBER (10, 2);

SUBTYPE dollar_amount_type IS var_dollar%TYPE;

 

 

Выражения

Выражения в PL/SQL допускаются над данными простых (не составных) типов. С версии 9 они подчиняются тем же правилам, что и в диалекте SQL в Oracle (до этого правила в PL/SQL были более ограничительны). Кроме этого в выражениях на PL/SQL могут участвовать скалярные типы, отсутствующие в Oracle SQL.

 

Примеры:

 

DECLARE

i NUMBER;

n CONSTANT VARCHAR2 (255):= 'Scott';

c BOOLEAN;

 

 BEGIN

i:= SIN (3) / COS (3);

DBMS_OUTPUT.PUT_LINE ('Tangens of 3 radians: ' || TO_CHAR (i));

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

DBMS_OUTPUT.PUT_LINE ('The same tangens: ' || i);

 

c:= USER LIKE 'S%';

IF c THEN DBMS_OUTPUT.PUT_LINE (USER || ' begins with S'); END IF;

DBMS_OUTPUT.PUT_LINE

     ('User ' ||

       CASE INITCAP (USER)                               -- вариант CASE для версии >= 9

          WHEN n THEN 'is SCOTT'

          WHEN 'Scott' THEN 'is SCOTT too'

          WHEN INITCAP (USER) THEN 'is SCOTT again'

       ELSE 'SCOTT is not here'

       END

    );

 

-- для версии >= 9

IF TIMESTAMP '2003-04-14 15:16:17' > SYSTIMESTAMP THEN

      DBMS_OUTPUT.PUT_LINE (‘OK’);

END IF;

END;

/

 

Обратите внимание на использование функций, системных переменных и на неявное преобразование типов в выражениях выше, в соответствии с правилами Oracle SQL. 

 

Тем не менее, изредка встречаются исключения из общего правила. Так, в отличие от NVL, стандартная функция NVL2 в выражениях PL/SQL невозможна (пример для версии 9.2):

 

SQL> VARIABLE n number

SQL> BEGIN:n:= NVL (1, 2); END;

2 /

 

PL/SQL procedure successfully completed.

 

SQL> BEGIN:n:= NVL2 (1, 2, 3); END;

2 /

BEGIN:n:= NVL2 (1, 2, 3); END;

       *

ERROR at line 1:

ORA-06550: line 1, column 13:

PLS-00201: identifier 'NVL2' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

 

 

SQL> BEGIN SELECT NVL2 (1, 2, 3) INTO:n FROM dual; END;

2 /

 

PL/SQL procedure successfully completed.

 

 

Ветвление программы

Предложение IF-THEN

IF условие THEN

           Программный код

END IF;

 

Существуют рекомендации избегать предложений IF-THEN, отдавая для ясности кода предпочтение равносильной записи:

 

IF условие THEN

           Программный код

ELSE

           NULL;

END IF;

 

 

Предложение IF-THEN-ELSE

IF условие THEN

           Программный код в случае TRUE

ELSE

           Программный код в случае FALSE/ NULL

END IF;

 

 

Предложение IF-THEN-ELSIF

IF условие1 THEN

           Программный код1

ELSIF условие2 THEN

           Программный код2

           …

ELSIF условие N THEN

           Программный код N

[ELSE

           Программный код в случае FALSE/ NULL ]

END IF;

 

 

Предложения CASE

Начиная с версии 9. 

 

По аналогии с CASE-выражениями имеет две разновидности:

 

CASE

WHEN условие1 THEN

Программный код1

 [WHEN условие N THEN

Программный кодN ] …

[ELSE

Программный код ]

END CASE;

 

Вторая разновидность:

 

CASE выражение

WHEN выражение_для_сравнения1 THEN

Программный код1

 [WHEN выражение_для_сравнения N THEN

Программный кодN ] …

[ELSE

Программный код ]

END CASE;

 

Если конструкция ELSE отсутствует, порождается исключительная ситуация. Выдадим в SQL*Plus:

 

SQL> VARIABLE n NUMBER

 

Следующие примеры вызовут ошибку:

 

BEGIN CASE WHEN FALSE THEN:n:= 1; END CASE; END;

/

 

BEGIN CASE:n WHEN 1 THEN:n:= 2; END CASE; END;

/

 

Следующие примеры не приведут к ошибке:

 

BEGIN CASE WHEN TRUE THEN:n:= 1; END CASE; END;

/

 

BEGIN CASE:n WHEN 1 THEN:n:= 2; END CASE; END;

/

 

BEGIN CASE WHEN FALSE THEN:n:= 1; ELSE:n:= 2; END CASE; END;

/

 

 

Циклы

Простой цикл

LOOP

           Программный код

END LOOP;

 

Выход из цикла может осуществляться с помощью GOTO, однако правильнее это делать с помощью специальных операторов EXIT или EXIT WHEN:

 

BEGIN

LOOP DBMS_OUTPUT.PUT_LINE ('Enough!'); EXIT; END LOOP;

END;

/

 

 

Счетный цикл (FOR)

FOR индекс_цикла IN [REVERSE] нижнее_значение.. верхнее_значение

LOOP

Программный код

END LOOP;

 

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

 

Пример, поясняющий область действия переменной цикла и возможность явного ухода из цикла FOR (обратите внимание, что значение переменной цикла в последнем случае не сохраняется, и если это нужно, потребуется прежде выхода сделать присвоение внешней переменной):

 

DECLARE i NUMBER:= 10;

BEGIN

FOR i IN 1.. 5 LOOP DBMS_OUTPUT.PUT_LINE (i); END LOOP;

DBMS_OUTPUT.PUT_LINE (i);

FOR i IN 1.. 5 LOOP DBMS_OUTPUT.PUT_LINE (i); EXIT; END LOOP;

DBMS_OUTPUT.PUT_LINE (i);

END;

/

 

 

Цикл по курсору (FOR)

FOR индексная_запись IN [ имя_курсора | явное_ предложение_ SELECT ]

LOOP

Программный код

END LOOP;

 

Объявление индексной_записи в PL/SQL выполняется автоматически и с типом имя_курсора %TYPE и самостоятельного объявления не требует.

 

Кроме того, в цикле такого рода автоматически:

 

n открывается курсор

n извлекаются данные при прохождении цикла

n курсор закрывается по выходу из цикла

 

Примеры см. ниже.

 

 

Цикл WHILE

WHILE условное выражение

LOOP

Программный код

END LOOP;

 

 

Имитация цикла REPEAT UNTIL

Цикл REPEAT UNTIL в PL/SQL непосредственно не поддерживается, однако его можно просто сымитировать следующим образом:

 

LOOP

           Программный код

           EXIT WHEN условное выражение

END LOOP;

 

 

Метки в циклах и в блоках

Для повышения надежности кода циклы можно размечать метками, например:

 

BEGIN

<< year_loop >>

FOR year IN 1993.. 2003 LOOP

<< month_loop >>

FOR month IN 1.. 12 LOOP

  IF year_loop.year = 2000 AND month = 1

  THEN EXIT year_loop;

  END IF;

END LOOP month_loop;

DBMS_OUTPUT.PUT_LINE ('Year ' || year || ' gone');

END LOOP year_loop;

DBMS_OUTPUT.PUT_LINE ('Year 2000 have come!');

END;

/

 

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

 

<< outer >>

DECLARE i INTEGER:= 1;

BEGIN

<< inner >>

DECLARE i INTEGER:= 2;

BEGIN

  DBMS_OUTPUT.PUT_LINE (i);    -- outer или inner? 1 или 2?

  DBMS_OUTPUT.PUT_LINE (outer. i);

  DBMS_OUTPUT.PUT_LINE (inner. i);

END;

END;

/

 

К сожалению размечивание циклов и блоков сопособно иногда приводить к недоразумению из-за совпадения имен. Сравните примеры:

 

SQL> BEGIN DBMS_OUTPUT.PUT_LINE (user); END;

2 /

SCOTT

 

PL/SQL procedure successfully completed.

 

SQL> DECLARE user VARCHAR2 (10) := 'tiger';

2 BEGIN DBMS_OUTPUT.PUT_LINE (user); END;

3 /

Tiger

 

PL/SQL procedure successfully completed.

 

SQL> <<standard>> DECLARE user VARCHAR2 (10):= 'tiger';

2 BEGIN DBMS_OUTPUT.PUT_LINE (standard.user); END;

3 /

Tiger

 

PL/SQL procedure successfully completed.

 

SQL> <<nonstandard>> DECLARE user VARCHAR2 (10):= 'tiger';

  2 BEGIN DBMS_OUTPUT.PUT_LINE (standard.user); END;

3 /

SCOTT

 

PL/SQL procedure successfully completed.

 

SQL> <<standard>> DECLARE user VARCHAR2 (10):= 'tiger';

2 BEGIN DBMS_OUTPUT.PUT_LINE (sys.standard.user); END;

3 /

SCOTT

 

PL/SQL procedure successfully completed.

 

 

Подпрограммы

Локальные подпрограммы

Внутри блока PL/SQL могут объявляться собственные подпрограммы, невидимые снаружи блока, но в остальном обладающие практически всеми свойствами обычных (хранимых) подпрограмм.

 

Пример определения локальной функции:

 

DECLARE

FUNCTION day (d DATE) RETURN VARCHAR2 IS

BEGIN RETURN TO_CHAR (d, 'Day');

END;

BEGIN

DBMS_OUTPUT.PUT_LINE (day (SYSDATE));

DBMS_OUTPUT.PUT_LINE (day (SYSDATE + 1));

DBMS_OUTPUT.PUT_LINE (day (SYSDATE + 2));

END;

/

 

Объявление локальных подпрограмм должно выполняться в конце раздела объявлений. Допускается использование переменных того же уровня:

 

DECLARE

a NUMBER:= 5;

PROCEDURE summa (b NUMBER) IS

BEGIN a:= a + b;

END;

BEGIN

summa (1); DBMS_OUTPUT.PUT_LINE (a);

summa (9); DBMS_OUTPUT.PUT_LINE (a);

a:= 1.5;

summa (9); DBMS_OUTPUT.PUT_LINE (a);

END;

/

 

Подпрограммы в PL/SQL не запрещают рекурсивное обращение к себе, и при исполнении глубина рекурсии не регламентируется. Это накладывает на разработчика ПО ответственность слежения за недопущением «бесконечной» рекурсии.

 

 

Управление транзакциями

Ниже перечисляются операторы PL/SQL, позволяющие управлять выполнением транзакций из программы. Некоторые дополнительные возможности управления транзакциями имеются во встроенном пакете DBMS_TRANSACTION, а некоторые возможности по управлению взаимодействием транзакций доступны путем использования встроенного пакета DBMS_LOCK.

 

 

COMMIT

COMMIT [WORK] [COMMENT текст ];

 

Слово WORK используется только для читаемости кода, а COMMENT текст может использоваться при разборе незавершившихся распределенных транзакций.

 

 

ROLLBACK

ROLLBACK [WORK] [TO SAVEPOINT] имя _контрольной _точки

 

Можно откатить транзакцию до контрольной точки (через контрольные точки, выполненные позднее) или до начала транзакции.

 

 

SAVEPOINT

SAVEPOINT имя_контрольной_точки;

 

Создает контрольную точку в пределах транзакции. Если имя повторить другим оператором SAVEPOINT, указанная контрольная точка “передвинется” вперед.

 

 

SET TRANSACTION

SET TRANSACTION READ ONLY;

 

Оператор дает начало “читающей” транзакции, в течение которой сеансу доступны только изменения, сделанные до ее точки отсчета. Завершение транзакции осуществляется по COMMIT или ROLLBACK. В пределах “читающей” транзакции можно выполнять только операторы

 

- LOCK TABLE

- SELECT, SELECT INTO

- OPEN

- FETCH

- CLOSE

- COMMIT/ROLLBACK

 

Выдача INSERT или UPDATE приведет к ошибке.

 

«Читающие» транзакции полезно использовать при составлении отчетов.

 

 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 

Аналогично READ ONLY обеспечивает целостность данных при чтении в пределах транзакции (а не SQL-оператора), но допускает модификацию данных.

 

 

SET TRANSACTION USE ROLLBACK SEGMENT имя _сегмента;

 

Приписывает только начавшейся транзакции конкретный сегмент отката, что часто используется в интенсивно модифицирующих транзакциях.

 

 

Блокировки

Блокировки таблицы выполняются SQL-предложением LOCK TABLE:

 

LOCK TABLE список_таблиц IN вид_блокировки MODE [NOWAIT];

 

Явно блокирует перечисленные в списке через запятую таблицы (полностью, а не на уровне строки) в указанном режиме. вид_блокировки может быть следующим:

 

- ROW SHARE

- ROW EXCLUSIVE

- SHARE UPDATE

- SHARE

- SHARE ROW EXCLUSIVE

- EXCLUSIVE

 

Если указано NOWAIT, то СУБД не переведет остальные запросы к таблице в состояние ожиданния, пока блокировка не будет снята (стандартная ситуация), а выдаст сообщение об ошибке ORA-00054.

 

 

Автономные транзакции

Начиная с версии 8.1 отдельные блоки кода можно выполнять в режиме автономной транзакции, на которую не будет распространяться откат или фиксация, осуществляемые для основной транзакции:

 

PROCEDURE main IS

BEGIN

           COMMIT;

           UPDATE …                     -- начало основной транзакции

           DELETE …

           some _ procedure;          -- начало автономной транзакции

           SELECT …

           INSERT …

           ROLLBACK;                  -- конец основной транзакции

END;

 

PROCEDURE some_procedure IS

           PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN                                              -- основная транзакция приостановлена

           SELECT …

           INSERT …                      -- начало автономной транзакции

           UPDATE …

           DELETE …

           COMMIT;                       -- конец автономной транзакции; фиксируются только

                                                           -- изменения, сделанные в рамках этой процедуры;

END;                                                 -- основная транзакция возобновляется

 

Правила видимости данных, измененных в БД основной и автономной транзакциями, соответствуют обычным правилам видимости для двух самостоятельных транзакций. То же касается регулирования изменений одних и тех же данных. Если основная транзакция заблокировала какую-нибудь таблицу, может возникнуть тупиковая ситуация. Избежать ее можно, используя указание NOWAIT в предложениях SELECT … FOR UPDATE. Блокировка может возникнуть в том числе вследствие существования межтабличной связи «внешний ключ»; см. пример ниже.

 

Упражнение. Что получим в результате следующих действий? Проверить в SQL*Plus.

 

Что видно из автономной транзакции:

 

SET SERVEROUTPUT ON

 

-- изменяем зарплату:

UPDATE emp SET sal = sal * 10 WHERE ename = 'SMITH';

 

-- автономная транзакция видит старое значение:

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

 salary emp.sal%TYPE;

BEGIN

 SELECT sal INTO salary FROM emp where ename = 'SMITH';

 DBMS_OUTPUT.PUT_LINE (salary);

END;

/

 

-- а основная транзакция продолжает видеть новое значение:

SELECT sal FROM emp where ename = 'SMITH';

 

ROLLBACK;

 

Построение транзакций:

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 INSERT INTO emp (empno, deptno) VALUES (2222, 40);

END;

/

-- почему возникла ошибка?

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 INSERT INTO emp (empno, deptno) VALUES (2222, 40);

 ROLLBACK;

END;

/

-- OK.

 

INSERT INTO dept (deptno) VALUES (50);

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 INSERT INTO emp (empno, deptno) VALUES (2222, 50);

 ROLLBACK;

END;

/

-- почему возникла ошибка?  

 

ROLLBACK;

 

Поставьте комментарий перед INSERT INTO dept …. -- почему возникла новая ошибка?

 

INSERT INTO dept (deptno) VALUES (50);

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 INSERT INTO emp (empno, deptno) VALUES (2222, 40);

 ROLLBACK;

END;

/

-- OK.

 

ROLLBACK;

 

Что видно из основной транзакции:

-- исходная зарплата:

SELECT sal FROM emp WHERE ename = 'SMITH';

-- изменяем зарплату в автономной транзакции:

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 UPDATE emp SET sal = sal * 10 WHERE ename = 'SMITH';

 COMMIT;

END;

/

 

-- увидим это изменение из основной?

SELECT sal FROM emp WHERE ename = 'SMITH';

-- такая же проверка, только в основной транзакции READ ONLY:

SET TRANSACTION READ ONLY;

 

DECLARE

 PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

 UPDATE emp SET sal = sal / 10 WHERE ename = 'SMITH';

 COMMIT;

END;

/

 

-- а теперь увидим изменение?

SELECT sal FROM emp WHERE ename = 'SMITH';

 

ROLLBACK;

-- вышли из транзакции READ ONLY

SELECT sal FROM emp WHERE ename = 'SMITH';

 

Объяснить разницу в результатах до ROLLBACK и после.

 

Тот же эффект даст использование транзакции ISOLATION LEVEL SERIALIZABLE (но, в отличие от READ ONLY, не будет препятствовать внесению изменений в БД из основной).

 

 

Встроенный динамический SQL

В Oracle имеется два средства для динамического порождения программой предложений SQL:

 

- «встроенный динамический SQL» (native dynamic SQL, начиная с версии Oracle 8.1) и

- встроенный пакет DBMS_SQL (имелся и раньше)

 

Пакет DBMS_SQL обладает несколько большей функциональностью, но существенно более сложен в использовании.

 

 

Использование курсоров

Курсор – именованная область, выделяемая СУБД для обработки каждого SQL-запроса. В программе – переменная «типа курсор».

 

 

Явные курсоры

Курсоры для SQL-предложений, явно объявляемые в разделе описаний блока PL/SQL.

 

 

Объявление явных курсоров

Три возможных вида объявления явных курсоров.

 

Курсоры без параметров:

 

CURSOR employee_cur IS SELECT ename FROM emp;

 

Курсоры с параметрами:

 

CURSOR employee_cur (department IN NUMBER) IS

           SELECT ename FROM emp WHERE deptno = department;

 

В этом примере можно было бы написать (department IN NUMBER DEFAULT 10), и тогда бы это был параметр с умолчанием. Такие параметры можно не указывать при открытии курсора.

 

Заголовок курсора в интерфейсной части пакета (позволяет спрятать реализацию курсора в тело пакета, не предъявляя его в описание):

 

CURSOR employee_cur RETURN emp%ROWTYPE;

 

(В последнем случае в теле пакета может быть указано что-то вроде

 

CURSOR employee_cur RETURN emp%ROWTYPE IS SELECT * FROM emp;)

 

 

Открытие явных курсоров

OPEN имя_курсора [(список выражений для параметров)];

 

Если параметры имеются, но все допускают умолчание, можно написать (с равным успехом) OPEN имя_курсора () или же просто OPEN имя_курсора.

 

По команде OPEN будет вычислен план выполнения запроса; к участкам памяти в курсоре будут привязаны связные переменные и параметры курсора; будет вычислен формат результата; указатель результата встанет на первую строку выдачи.

 

В цикле FOR операция OPEN выполняется неявно.

 

 

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

CLOSE имя_курсора;

 

Закрытие курсора после исчезновения в нем необходимости освобождает память для других запросов. Курсор, открытый в анонимном блоке или в блоке процедуры или функции закрывается автоматически по окончанию работы блока. Глобальные курсоры пакета автоматически закрываются только по окончанию сеанса связи с БД.

 

 

Атрибуты для явных курсоров

У каждого явно открываемого курсора имеются для использования в программе четыре атрибута:

 

Атрибут	Значение
%ISOPEN	Имеет значение TRUE, если курсор открыт и FALSE, если нет
%FOUND	Исключительное состояние INVALID_CURSOR, если курсор не открыт с помощью OPEN или закрыт с помощью CLOSE NULL – перед первым выполнением FETCH TRUE – после успешного выполнения FETCH FALSE – если FETCH не сумел выдать строку
%NOTFOUND	Исключительное состояние INVALID_CURSOR, если курсор не открыт с помощью OPEN или закрыт с помощью CLOSE NULL – перед первым выполнением FETCH FALSE – после успешного выполнения FETCH TRUE – если FETCH не сумел выдать строку
%ROWCOUNT	Исключительное состояние INVALID_CURSOR, если курсор не открыт с помощью OPEN или закрыт с помощью CLOSE Число – общее число строк, извлеченных после последней операции FETCH для курсора

 

 

Синтаксис использования атрибута: имя_курсора % атрибут.

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

 

DECLARE

           caller_rec caller_package.caller_cursor%ROWTYPE;

BEGIN

           OPEN caller_package.caller_cursor

           LOOP

                           FETCH caller_package.caller_cursor INTO caller_rec;

                           EXIT WHEN caller_package.caller_cursor%NOTFOUND

                                           OR

                                           caller_package.caller_cursor%ROWCOUNT > 10;

                           UPDATE call

                                           SET caller_id = caller_rec.caller_id

                                           WHERE call_timestamp < SYSDATE;

           END LOOP;

           CLOSE caller_package.caller_cursor;

END;

 

(Здесь предполагается, что имеется таблица с именем CALL с информацией о телефонных звонках и что есть пакет CALLER_PACKAGE для работы с этой информацией).

 

 

Неявные курсоры

Открываются при непосредственном указании в тексте операторов INSERT, UPDATE, DELETE и SELECT INTO. Явных операций открытия, OPEN, FETCH и CLOSE они не требуют.

 

В случае SELECT INTO возможны исключительные ситуации, при возникновении которых управление передается в раздел обработки исключительных ситуаций:

 

NO_DATA_FOUND – если не извлечено ни единой строки

TOO_MANY_ROWS – если извлечено более одной строки

 

С неявными курсорами связаны следующие атрибуты:

 

Атрибут	Значение
%ISOPEN	Всегда FALSE, так как атрибут открывается неявно и закрывается сразу после отработки SQL-предложения
%FOUND	NULL – перед выполнением SQL-оператора TRUE – если оператор обработал несколько строк (выдал – только одну) FALSE – если оператор не обработал ни одной строки
%NOTFOUND	NULL – перед выполнением SQL-оператора FALSE – если оператор обработал несколько строк (выдал – только одну) TRUE – если оператор не обработал ни одной строки
%ROWCOUNT	Число – количество обработанных строк
%BULK_ROWCOUNT	Псевдо-индексная таблица с числом обработанных строк при использовании привязки массивом (только начиная с версии 8.1)

 

 

Синтаксис использования атрибута: SQL% атрибут.

Пример использования в программе (в SQL*Plus):

 

BEGIN

           UPDATE emp SET sal = sal WHERE deptno = &department_no;

           IF SQL%NOTFOUND THEN

                           DBMS_OUTPUT.PUT_LINE('No employees in this department');

           ELSE

                           DBMS_OUTPUT.PUT_LINE('Perfectly done for '|| SQL%ROWCOUNT ||’ employees’);

           END IF;

END;

/

 

Неявный курсор для множественного отбора в цикле (FOR rec IN (SELECT … FROM …) LOOP … END LOOP;) атрибутов не имеет.

 

 

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

Ссылка на курсор – это именованная структура данных ссылающаяся на курсор, ссылающийся, в свою очередь, на массив выбираемых курсором данных. Ее можно использовать начиная с версии 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

 

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