3 BEGIN
4 FOR rec IN (SELECT at1,at2 FROM tab1) LOOP
5 v1:= v1||' '||rec.at2;
6 END LOOP;
7 DBMS_OUTPUT.PUT_LINE(LTRIM(v1));
8 END;
9 /
A B C
PL/SQL procedure successfully completed.
Как и для неявных курсоров, для курсорного цикла FOR компилятор сам разместит в байт-коде низкоуровневые вызовы открытия курсора, считывания из него строк и закрытия.
Параметры курсора
Объявление курсора может содержать параметрический запрос, значения параметров которого передаются при открытии курсора. Рассмотрим соответствущий пример.
SQL> SELECT * FROM tab1;
AT1 A
-
1 A
2 B
3 C
SQL> DECLARE
2 CURSOR cur2 (i INTEGER) IS SELECT * FROM tab1 WHERE at1>=i;
3 cur2_rec cur2%ROWTYPE;
4 BEGIN
5 OPEN cur2(2); курсор открыт с параметром i, равным 2
6 FETCH cur2 INTO cur2_rec;
7 WHILE cur2%FOUND LOOP
8 DBMS_OUTPUT.PUT_LINE(cur2_rec.at1);
9 FETCH cur2 INTO cur2_rec;
10 END LOOP;
11 CLOSE cur2;
12 END;
13 /
2
3
PL/SQL procedure successfully completed.
Помимо явных курсоров параметризировать можно и команды SELECT INTO и курсорные циклы FOR. Для этого в коде SQL в качестве параметров надо использовать ранее объявленные переменные PL/SQL скалярных типов данных. При препроцессинге эти переменные будут автоматически заменены компилятором PL/SQL на связываемые переменные SQL.
Добавление, изменение и удаление данных
В исходном коде программы PL/SQL можно указывать команды добавления, изменения и удаления данных, для которых компилятор формирует в байт-коде вызовы соответствующих предложений SQLINSERT, UPDATE и DELETE.
SQL> CREATE TABLE tab1 (at1 integer);
Table created.
SQL> DECLARE
2 l_at1 tab1.at1%TYPE;
3 BEGIN
4 l_at1 := 1;
5 INSERT INTO tab1 VALUES (l_at1);
6
7 l_at1 := 2;
8 INSERT INTO tab1 VALUES (l_at1);
9
10 l_at1 := 3;
11 INSERT INTO tab1 VALUES (l_at1);
12 DELETE FROM tab1 WHERE at1=1;
13
14 UPDATE tab1 SET at1=at1+1 WHERE at1=l_at1;
15
16 END;
17 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM tab1;
AT1
2
4
Используемые для выполнения команд INSERT, UPDATE, DELETE неявные курсоры тоже имеют атрибуты. Чтобы получить их значения, следует указывать имя курсора 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 не производилась.
Управление транзакциями в PL/SQL
Транзакции в базах данных Oracle
Транзакцией в базе данных Oracle называется атомарная (неделимая) логическая единица (unit) работы с базой данных, состоящая из одного или нескольких предложений языка SQL. Все транзакции в базах данных Oracle обладают четырьмя основными свойствами транзакций в базах данных:
атомарность (atomcity)свойство транзакции, заключающееся в том, что она не может быть выполнена частично: транзакция либо успешно завершается с сохранением всех изменений в данных, либо все без исключения внесенные ею изменения данных полностью отменяются и измененные данные возвращаются к тому состоянию, в котором они находились перед началом транзакции;