Иван Задворьев - Язык PL/SQL

Таблица 4.Атрибуты неявного курсора.

SQL%FOUND

возвращает TRUE, если хотя бы одна строка была обработана DML-предложением SQL

SQL%NOTFOUND

возвращает TRUE, если ни одной строки не было обработано

SQL%ROWCOUNT

возвращает количество обработанных строк

SQL%ISOPEN

для неявных курсоров всегда возвращает FALSE, поскольку Oracle закрывает и открывает их автоматически

Эти атрибуты относятся к последнему использовавшемуся в программе неявному курсору, независимо от того, в каком блоке этот курсор использовался. До выполнения в программе первой команды PL/SQL с использованием неявного курсора атрибуты курсора с именем SQL% остаются неинициализированными (имеют значения UNKNOWN и NULL).

Наиболее часто используются атрибуты SQL%FOUND и SQL%ROWCOUNT, которые позволяют получить информацию о результатах обработки данных – сколько строк было обработано (добавлено, изменено или удалено) и были ли они вообще.

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

SQL> DECLARE

2 l_at1 tab1.at1%TYPE;

3 BEGIN

4

5 l_at1 := 1;

6

7 INSERT INTO tab1 VALUES (l_at1);

8 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

9

10 INSERT INTO tab1 SELECT * FROM tab1;

11 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

12

13 INSERT INTO tab1 SELECT * FROM tab1;

14 DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);

15

16 UPDATE tab1 SET at1=2 WHERE at1=1;

17 IF SQL%FOUND THEN

18 DBMS_OUTPUT.PUT_LINE('Строки изменялись');

19 ELSE

20 DBMS_OUTPUT.PUT_LINE('Строки не изменялись');

21 END IF;

22

23 END;

24 /

1

1

2

Строки изменялись

PL/SQL procedure successfully completed.

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

Рассмотрим еще две возможности языка PL/SQL: конструкцию RETURNING и использование записей PL/SQL в DML-командах. Эти возможности наглядно иллюстрируют удобство использования языка PL/SQL при работе с базами данных Oracle.

Конструкция RETURNING

Конструкция RETURNING позволяет получить новые значения данных в таблицах после их добавления или изменения. Например, после увеличения оклада сотрудника на 10% в дальнейших вычислениях в коде может понадобиться новое значение оклада. Конечно, можно сразу после изменения выполнить выборку данных по этому сотруднику, но это будет еще одна операция, на которую потребуются дополнительные расходы ресурсов. Конструкция RETURNING позволяет их избежать.

Конструкцию RETURNING часто используют для получения значения первичного ключа после добавления новой строки в таблицу с использованием последовательности.

SQL> CREATE SEQUENCE sq1 START WITH 1 INCREMENT BY 2;

Sequence created.

SQL> DECLARE

2 l_at1 tab1.at1%TYPE;

3 BEGIN

4 INSERT INTO tab1 VALUES(sq1.NEXTVAL) RETURNING at1 INTO l_at1;

5 DBMS_OUTPUT.PUT_LINE(l_at1);

6 INSERT INTO tab1 VALUES(sq1.NEXTVAL) RETURNING at1 INTO l_at1;

7 DBMS_OUTPUT.PUT_LINE(l_at1);

8 END;

9 /

1

3

PL/SQL procedure successfully completed.

Использование записей PL/SQL в DML-командах

В DML-командах языка PL/SQL можно использовать и параметры-записи PL/SQL:

для указания того, что в команде UPDATE следует изменить целиком строку таблицы, используется ключевое слово ROW;

в команде INSERT после ключевого слова VALUES вместо списка переменных скалярных типов со значениями всех столбцов добавляемой строки указывается одна переменная-запись PL/SQL, которая целиком «укладывается» в таблицу в виде новой строки.

SQL> CREATE TABLE tab1 (at1 INTEGER, at2 VARCHAR2(1));

Table created.

SQL> DECLARE

2 l_tab1 tab1%ROWTYPE;

3 BEGIN

4 l_tab1.at1 := 1;

5 l_tab1.at2 := 'a';

6 INSERT INTO tab1 VALUES l_tab1;

7 l_tab1.at1 := 2;

8 l_tab1.at2 := 'b';

9 INSERT INTO tab1 VALUES l_tab1;

10 l_tab1.at2 := 'c';

11 UPDATE tab1 SET ROW = l_tab1 WHERE at1=2;

12 END;

13 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM tab1;

AT1 AT2

– –

1 a

2 c

Рекомендуется используемые в DML-командах записи PL/SQL объявлять на основе схем таблиц с помощью атрибута %ROWTYPE. Если впоследствии схемы этих таблиц изменятся, то код PL/SQL останется работоспособным. Таким образом, использование в DML-командах одной записи PL/SQL вместо нескольких переменных скалярных типов приводит к тому, что код становится более компактным и повышается его надежность.

Формирование предложений SQL со связываемыми переменными

Ранее отмечалось, что достоинством языка PL/SQL является формирование компилятором предложений SQL со связываемыми переменными.

Напомним, что связываемой переменной (bind variable) называется метка (placeholder) для переменной в тексте предложения SQL. Перед выполнением предложения SQL происходит связывание переменных (binding variables) – для них задаются фактические значения.

Мы сейчас не будем вдаваться в подробности, скажем только, что использование в предложениях SQL связываемых переменных вместо жестко кодируемых литералов (hard-coded literals) является обязательным условием достижения высокой производительности сервера Oracle.

Выполним программу PL/SQL с тремя командами добавления строк в таблицу test_tab и посмотрим, какие SQL-предложения INSERT были сформированы компилятором PL/SQL для байт-кода и потом выполнены в базе данных виртуальной машиной PL/SQL на самом деле:

CREATE TABLE test_tab (a INTEGER)

SQL> DECLARE

2 l_value INTEGER;

3 BEGIN

4 INSERT INTO test_tab VALUES(123);

5 l_value := 124;

6 INSERT INTO test_tab VALUES(l_value);

7 l_value := 125;

8 INSERT INTO test_tab VALUES(l_value);

9 END;

10 /

PL/SQL procedure successfully completed.

SQL> SELECT SUBSTR(sql_text,1,70) AS SQL_TEXT FROM V$SQL

2 WHERE LOWER(sql_text) LIKE LOWER('%test_tab%');

SQL_TEXT

–

INSERT INTO TEST_TAB VALUES(123)

INSERT INTO TEST_TAB VALUES(:B1 )

Видно, что для двух команд INSERT с переменной l_value компилятором PL/SQL сформировано одно предложение SQL со связываемой переменной :B1, потом оно было дважды выполнено с разными привязанными значениями :B1 (124 и 125). Для жестко закодированного (hard coded) литерала 123 замена на связываемую переменную компилятором PL/SQL не производилась.

Транзакции в базах данных Oracle

Транзакцией в базе данных Oracle называется атомарная (неделимая) логическая единица (unit) работы с базой данных, состоящая из одного или нескольких предложений языка SQL. Все транзакции в базах данных Oracle обладают четырьмя основными свойствами транзакций в базах данных:

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

согласованность (consistency) – свойство транзакции, заключающееся в том, что транзакция переводит базу данных из одного согласованного состояния в другое согласованное состояние;

изолированность (isolation) – свойство транзакции, заключающееся в ограничении влияния на ее работу других транзакций;

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