Loe raamatut: «Язык PL/SQL», lehekülg 6
%NOTFOUND
FALSE, если из курсора считана очередная строка
%ROWCOUNT
количество считанных до настоящего момента строк
%ISOPEN
TRUE, если курсор открыт
Основная нагрузка при считывании всех строк результирующей выборки ложится на атрибуты курсора %NOTFOUND и %FOUND, которые всегда находятся в связке – либо принимают противоположные логические значения TRUE и FALSE, либо оба UNKNOWN.
Атрибут %FOUND равен TRUE и атрибут %FOUND равен FALSE в то время, пока команда FETCH считывает из курсора все новые и новые (очередные) строки. После того, как последняя строка результирующей выборки будет считана дважды (второй раз, выходит, уже не как очередная), атрибут курсора %FOUND станет FALSE, а %NOTFOUND станет TRUE. На этом поведении атрибутов курсора обычно и формируется условие выхода из циклов, предназначенных для считывания из курсора всех строк результирующей выборки.
Еще одним важным фактом является то, что после открытия курсора, но до выполнения первой команды FETCH, атрибуты %FOUND и %NOTFOUND имеют неопределенное логическое значение (UNKNOWN). Если это не учитывать, то можно совершить одну из распространенных ошибок – в цикле WHILE с условием на истинность атрибута %FOUND цикл не будет выполнен ни разу, несмотря на то, что в результирующей выборке есть строки. Выполнить команду FETCH первый раз надо еще до входа в цикл, тем самым проинициализировав атрибуты курсора.
Приведем пример использования атрибутов курсора в цикле WHILE для считывания всех строк результирующей выборки.
SQL> DECLARE
2 CURSOR cur1 IS SELECT * FROM tab1;
3 rec cur1%ROWTYPE;
4 BEGIN
5 OPEN cur1;
6 FETCH cur1 INTO rec;
7 WHILE cur1%FOUND LOOP
8 DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec.at1)||' '||rec.at2);
9 FETCH cur1 INTO rec;
10 END LOOP;
11 CLOSE cur1;
12 END;
13 /
1 A
2 B
3 C
PL/SQL procedure successfully completed.
Еще один пример показывает использование атрибутов курсора для считывания всех строк выборки в простом цикле LOOP END LOOP с условием выхода EXIT WHEN.
SQL> DECLARE
2 TYPE tab1_rec_type IS RECORD
3 (arg1 tab1.at1%TYPE,
4 arg2 tab1.at2%TYPE);
5 tab1_rec tab1_rec_type;
6 CURSOR cur1 IS SELECT * FROM tab1;
7 BEGIN
8 OPEN cur1;
9 LOOP
10 EXIT WHEN (cur1%NOTFOUND);
11 FETCH cur1 INTO tab1_rec;
12 DBMS_OUTPUT.PUT_LINE(cur1%ROWCOUNT||' '||tab1_rec.arg2);
13 END LOOP;
14 CLOSE cur1;
15 END;
/
1 A
2 B
3 C
3 C
PL/SQL procedure successfully completed.
Обратите внимание на повторный вывод последней строки (3 С). Это еще одна распространенная ошибка. В ходе проведения занятий со студентами авторы десятки раз видели считывание и обработку последней строки выборки дважды. Системное исключение при повторном считывании последней строки выборки, напомним, не инициируется, поэтому такие ошибки в коде трудно обнаруживаются.
Рекомендуется после написания кода, реализующего считывание и обработку всех строк выборки, проверить его с помощью небольших тестов на отсутствие двух распространенных ошибок:
цикл считывания не выполняется ни разу;
последняя строка выборки в цикле обрабатывается дважды.
В приведенном выше примере показано, что значение %ROWCOUNT увеличивается на единицу с каждой считанной строкой, а не отражает общее число отобранных SQL-запросом строк. Видно и что повторное считывание последней строки выборки не влияет на значение атрибута %ROWCOUNT: оно остается равным значению, присвоенному при первом считывании последней строки. В примере значение атрибута %ROWCOUNT как стало равным трем при первом считывании последней строки, так и осталось без изменений после еще одного считывания.
Курсорный цикл FOR
Курсорный цикл FOR позволяет в цикле обработать все строки результирующей выборки SQL-запроса.
SQL> DECLARE
2 CURSOR cur1 IS SELECT at1,at2 FROM tab1;
3 v1 VARCHAR2(4000);
4 BEGIN
5 FOR rec IN cur1 LOOP
6 v1:=LTRIM(v1||' '||rec.at2);
7 END LOOP;
8 DBMS_OUTPUT.PUT_LINE(v1);
9 END;
10 /
A B C
PL/SQL procedure successfully completed.
Обратите внимание, переменная rec, в которую в цикле считываются данные, не требует объявления. Она будет являться записью PL/SQL, такой же, как записи PL/SQL, объявленные с помощью атрибута %ROWTYPE на основе курсора.
Все очень просто. Не нужно явно открывать и закрывать курсор. Вместо команды FETCH просто следует обратиться к текущему значению записи PL/SQL, которая здесь является своеобразной управляющей переменной цикла. Для выхода из цикла больше не нужно проверять атрибуты курсора %NOTFOUND и %FOUND. Если SQL-запрос не отберет ни одной строки, тело цикла просто не выполнится ни разу, если же результирующая выборка непустая, то после перебора всех строк цикл завершится автоматически.
По сути, программист тремя строчками кода говорит компилятору PL/SQL «Мне нужна каждая строка результирующей выборки, и я хочу, чтобы она была помещена в запись PL/SQL, соответствующую курсору». Компилятор PL/SQL формирует соответствующий байт-код со всеми низкоуровневыми вызовами сервера.
Простейший вариант курсорного цикла FOR имеет SQL-запрос, встроенный прямо в описание цикла:
SQL> DECLARE
2 v1 VARCHAR2(4000);
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 можно указывать команды добавления, изменения и удаления данных, для которых компилятор формирует в байт-коде вызовы соответствующих предложений SQL – INSERT, 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) – свойство транзакции, заключающееся в том, что она не может быть выполнена частично: транзакция либо успешно завершается с сохранением всех изменений в данных, либо все без исключения внесенные ею изменения данных полностью отменяются и измененные данные возвращаются к тому состоянию, в котором они находились перед началом транзакции;
согласованность (consistency) – свойство транзакции, заключающееся в том, что транзакция переводит базу данных из одного согласованного состояния в другое согласованное состояние;
изолированность (isolation) – свойство транзакции, заключающееся в ограничении влияния на ее работу других транзакций;
сохраняемость (durability) – свойство транзакции, обеспечивающее сохранность сделанных ею изменений данных после фиксации транзакции, независимо от программных сбоев и отказов оборудования.
Транзакции, обладающие всеми этими свойствами, называются ACID-транзакциями (по первым буквам английских названий свойств). Атомарность (неделимость) – главное свойство транзакции, наличие которого требуется ее определением. Все без исключения изменения транзакции или должны сохраняться в базе данных или же должны полностью отменяться.
Транзакция в Oracle начинается с первого модифицирующего данные предложения SQL (INSERT, UPDATE, DELETE), выполненного после окончания предыдущей транзакции, то есть предложения SELECT транзакцию не начинают. После своего начала транзакция может находиться в одном из трех состояний:
активная транзакция (active transaction) – транзакция, которая начата, но и не зафиксирована и для нее не выполнена отмена;
зафиксированная транзакция (committed transaction) – транзакция, для которой все выполненные ею изменения в данных являются постоянными (permanent);
отмененная транзакция (rolled back transaction) – транзакция, для которой все выполненные ею изменения в данных отменены (все измененные данные возвращены в исходное состояние, в котором они находились перед началом транзакции).
Активная транзакция в каждый момент времени характеризуется степенью своей активности:
выполняющаяся транзакция (running transaction) – транзакция, в рамках которой в этот момент времени выполняется одно из ее предложений SQL;
простаивающая транзакция (idle transaction).
Простаивающую активную транзакцию, все предложения SQL которой выполнены, но фиксации или отмены пока еще не было, называют выполненной. Фиксацию транзакции осуществляет SQL-команда COMMIT, отмену – SQL-команда ROLLBACK. Зафиксированная или отмененная транзакция называется завершенной (completed). После того, как транзакция завершилась, в этой пользовательской сессии следующее модифицирующее данные предложение SQL начинает следующую транзакцию.
В Oracle минимальной единицей данных, которая может быть изменена транзакцией, является строка. Получается, что в каждый момент времени любая строка любой таблицы базы данных может находиться в одном из двух состояний:
отсутствует активная транзакция, изменившая строку (то есть когда-то ранее строка была добавлена в базу данных какой-то давнишней активной транзакцией, потом строка, возможно, изменялась предложениями UPDATE других активных транзакций, но все эти транзакции в свое время были зафиксированы или отменены);
строка изменена активной транзакцией (неважно, выполняющейся или выполненной – главное, что строка изменена, и эти изменения не зафиксированы и не отменены).
В литературе на русском языке часто встречаются фразы вроде «Запрос видит только зафиксированные данные на SCN 10023», представляющие собой кальковый перевод фраз вида «The query only sees committed data with respect to SCN 10023». Дело в том, что более правильные с точки зрения терминологии выражения выглядят довольно громоздко. Мы тоже будем дальше использовать эти ставшие общепринятыми понятия и выражения, просто приведем их определения:
зафиксированными данными называются данные, измененные зафиксированной транзакцией (изменения данных стали постоянными);
предложение SQL «видит» строки, если они используются (не игнорируются) им в ходе своего выполнения – строки участвуют в определении истинности критерия отбора из фразы WHERE и могут попадать в результирующую выборку.
Обращение к данным сервер Oracle осуществляет в одном из двух режимов:
обращение в текущем режиме (current mode, db block gets), при котором происходит обращение к строкам в их текущем состоянии, то есть в состоянии, в котором они находятся прямо сейчас (right now);
обращение в режиме согласованного чтения (consistent read mode, consistent gets), при котором происходит обращение к строкам, находящимся по состоянию на некоторый момент времени, то есть к предыдущим версиям строк.
В Oracle любой SQL-запрос SELECT видит только зафиксированные данные по состоянию на начало своего выполнения. Если другие транзакции уже после начала выполнения SQL-запроса сделают изменения в данных, и даже если эти изменения будут зафиксированы, то все равно SQL-запрос увидит версию данных по состоянию на момент начала его выполнения. Это и называется «согласованным чтением» (consistency read) на уровне отдельных предложений SQL. Часто версии данных по состоянию на некоторый момент времени в прошлом называют старыми данными.
Все сказанное верно и для критерия отбора из конструкции WHERE предложений DELETE, UPDATE и INSERT SELECT. Для удаления и изменения отбираются строки по состоянию на момент начала выполнения этих предложений.
«Под капотом» сервера Oracle для обеспечения корректного выполнения транзакций, согласованного чтения и вот этого всего имеются следующие специальные механизмы:
сегменты отката, обеспечивающие версионность данных;
система блокировок.
Блокировки
Наличие системы блокировок позволяет обеспечить корректное изменение одних и тех же данных.
Блокировкой (lock) называется средство организации доступа к совместно используемому ресурсу. Наложить на ресурс блокировку – это ограничить возможности других по работе с этим ресурсом. В Oracle есть несколько видов блокировок, мы рассмотрим, как работают самые распространенные блокировки строк таблиц транзакциями (блокировки TX).
Общие правила работы блокировок TX в Oracle выглядят так:
блокировки накладывают и снимают транзакции;
наложенную транзакцией блокировку может снять только она сама в ходе завершения транзакции командами COMMIT или ROLLBACK;
блокировки накладываются на строки таблиц (одна блокировка может накладываться на несколько строк сразу);
чтобы изменить или удалить строку, ее сначала надо заблокировать;
заблокированные одной транзакцией строки другая транзакция не может ни заблокировать, ни тем более изменить или удалить;
существует специальная форма SQL-запроса SELECT FOR UPDATE, которая накладывает блокировки на отбираемые по критерию отбора строки;
блокировки являются атрибутами самих строк – чтобы определить, есть ли на строке блокировка, к ней необходимо обратиться.
Для новых строк, добавленных в таблицу предложением INSERT, блокировка TX накладывается транзакцией, выполнившей это предложение.
При изменении строк предложением UPDATE к ним обращаются дважды:
поиск строк-кандидатов для изменений по условию в конструкции WHERE, который осуществляется в режиме согласованного чтения;
собственно изменение найденных строк (строки находятся в текущем состоянии – они или зафиксированы когда-то выполнившимися транзакциями и сейчас с ними никто не работает, или заблокированы активными транзакциями).
Из сказанного следует, что если какая-то подходящая по условию WHERE строка уже после начала выполнения нашего UPDATE будет кем-то добавлена в таблицу и зафиксирована, то так как поиск строк по WHERE осуществляется в режиме согласованного чтения, эта новая строка будет проигнорирована и менять ее наш UPDATE не будет. Для предложения DELETE аналогичное поведение – добавленные после начала его выполнения строки как кандидаты на удаление не находятся (тоже действует режим согласованного чтения на начало выполнения DELETE).
Когда наш процесс приступает к собственно изменению или удалению строки, найденной по критерию отбора в конструкции WHERE, сначала он попытается наложить на эту строку блокировку TX. При этом возможны два исхода в зависимости от того, есть ли на этой строке блокировка, установленная другой (чужой) активной транзакцией (наличие или отсутствие такой блокировки определяется в ходе обращения к строке):
