При выполнении команды SELECT для выборки строк из базы данных эти строки не блокируются. Обычно это чрезвычайно удобно, потому что количество записей, заблокированных в конкретный момент времени, сводится к минимуму: блокируются только те строки, которые уже изменены, но еще не зафиксированы приложением. Но даже эти строки доступны для чтения в том состоянии, в котором они находились до внесения изменений.
Однако в некоторых случаях требуется заблокировать набор строк еще до того, как вы приступите к их изменению в программе. Это можно сделать в команде SELECT с помощью секции FOR UPDATE.
При выполнении команды SELECT...FOR UPDATE Oracle автоматически блокирует все строки, определяемые командой SELECT. Никто другой не сможет изменять эти строки до тех пор, пока не будет выполнена команда ROLLBACK или COMMIT, хотя другие сеансы по-прежнему могут читать из них данные.
Рассмотрим пару примеров, демонстрирующих использование предложения FOR UPDATE в курсорах:
CURSOR toys_cur IS
SELECT name, manufacturer, preference_level,
sell_at_yardsale_flag FROM my_sons_collection WHERE hours_used = 0
FOR UPDATE;
CURSOR fall_jobs_cur IS
SELECT task, expected_hours, tools_required, do_it_yourself_flag
FROM winterize
WHERE year_of_task = TO_CHAR (SYSDATE, 'YYYY')
FOR UPDATE OF task;
В первом курсоре используется секция FOR UPDATE без параметров, а во втором — с заданным именем столбца.
Секция FOR UPDATE может применяться в командах SELECT, выбирающих данные из нескольких таблиц. Если при этом в указанном предложении имеется список OF, блокируются строки только тех таблиц, строки которых указываются в этом списке. Если в команде SELECT присутствует секция FOR UPDATE без списка OF, Oracle блокирует все отобранные запросом строки всех таблиц из секции FROM.
Более того, команда SELECT...FOR UPDATE вообще не требует последующего выполнения команд DELETE или UPDATE — она просто сообщает Oracle, что вы собираетесь это сделать (и не позволяет это делать другим).
Наконец, секцию FOR UPDATE можно дополнить ключевым словом NOWAIT. Оно означает, что если таблица заблокирована другим пользователем, Oracle не следует ждать ее освобождения. В этом случае управление будет сразу возвращено программе, чтобы она могла заняться другой работой или просто подождать некоторое время перед повторной попыткой. Также к команде можно присоединить ключевое слово WAIT с указанием максимальной продолжительности ожидания блокировки в секундах. Если ни одно из этих ключевых слов не указано, сеанс блокируется до тех пор, пока таблица не освободится. Причем тайм-аут в данном случае предусмотрен только для удаленных таблиц — он определяется значением инициализационного параметра DISTRIBUTED_LOCK_TIMEOUT.
Снятие блокировок командой COMMIT
Как только для курсора с секцией FOR UPDATE будет выполнена команда OPEN, все строки его результирующего набора блокируются и остаются заблокированными до тех пор, пока внесенные в сеансе изменения не будут закреплены командой COMMIT или отменены командой ROLLBACK. В любом случае заблокированные строки при этом освобождаются; таким образом, после выполнения COMMIT или ROLLBACK текущая позиция в курсоре теряется, и вы не сможете выполнить следующую выборку командой FETCH.
Рассмотрим пример:
DECLARE
/* Подготовка к зиме */
CURSOR fall_jobs_cur IS
SELECT task, expected_hours, tools_required,
do_it_yourself_flag FROM winterize
WHERE year = TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'))
AND completed_flag = 'NOTYET';
BEGIN
/* Для каждой строки, выбранной курсором... */
FOR job_rec IN fall_jobs_cur LOOP
IF job_rec.do_it_yourself_flag = 'YOUCANDOIT'
THEN
/*
|| Найдено очередное задание, фиксируем изменения.
*/
UPDATE winterize SET responsible = 'STEVEN'
WHERE task = job_rec.task
AND year = TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'));
COMMIT;
END IF;
END LOOP;
END;
Предположим, этот цикл нашел первое задание с пометкой YOUCANDOIT. Ответственным за его выполнение назначается STEVEN, но при попытке выборки следующей строки происходит исключение:
ORA-01002: fetch out of sequenceЕсли при извлечении данных из курсора SELECT...FOR UPDATE необходимо производить закрепление или откат изменений, придется включить в программу код, прекращающий дальнейшую выборку (например, команда EXIT, выполняющая выход из цикла).
Предложение WHERE CURRENT OF
PL/SQL позволяет использовать в командах UPDATE и DELETE специальную конструкцию WHERE CURRENT OF, облегчающую процесс изменения последней выбранной из курсора строки данных.
Обновление столбцов последней выбранной строки можно выполнить следующей командой:
UPDATE имя_таблицы SET
предложение_set WHERE CURRENT
OF имя_курсора;Аналогичным образом производится удаление последней выбранной строки:
DELETE
FROM имя_таблицы WHERE
CURRENT OF имя_курсора;
Обратите внимание: в секции WHERE CURRENT OF указывается курсор, а не запись, в которую была помещена очередная строка.
Главное преимущество использования секции WHERE CURRENT OF при модификации или удалении последней извлеченной строки из курсора заключается в том, что один критерий поиска строки не нужно задавать в двух (и более) местах программы. Не будь его, пришлось бы ввести секцию WHERE в определение курсора, а затем повторить его в соответствующих командах UPDATE и DELETE. Если бы в будущем структура таблицы изменилась способом, влияющим на формирование первичного ключа, нам пришлось бы вносить изменения во всех командах SQL, в которых оно используется. С другой стороны, с where current of изменяется только секция WHERE команды SELECT.
На первый взгляд этот момент кажется второстепенным, но это одна из многих областей кода, которые могут использовать неочевидные возможности PL/SQL для сведения к минимуму избыточности в коде. Использование WHERE CURRENT OF, атрибутов %TYPE и %ROWTYPE, циклов FOR с курсором, локальной модуляризации и других языковых конструкций PL/ SQL может существенно упростить сопровождение приложений Oracle.
Давайте посмотрим, как с помощью синтаксиса WHERE CURRENT OF усовершенствовать пример из предыдущего раздела. В цикле FOR нужно обновить строку, только что выбранную из курсора. Попробуем сделать это с помощью команды UPDATE, в которой задано то же условие, что в команде SELECT курсора (первичный ключ таблицы составляют значения столбцов task и year):
WHERE task = job_rec.task
AND year = TO_CHAR (SYSDATE, 'YYYY');
Как было указано ранее, это неверный подход: одна и та же логика программируется в двух местах, и при внесении каких-либо изменений придется следить за синхронизацией этого кода. Было бы намного проще и естественнее, если бы PL/SQL предоставлял такие операции, как удаление только что выбранной строки и обновление ее столбцов.
Именно это и делает секция WHERE CURRENT of! В новой версии предыдущего примера мы воспользуемся им, а заодно заменим цикл FOR простым циклом с условным выходом (в цикле FOR так поступать можно, но не рекомендуется):
DECLARE
CURSOR fall_jobs_cur IS SELECT ... то же, что в предыдущем примере ...;
job_rec fall_jobs_cur%ROWTYPE;
BEGIN
OPEN fall_jobs_cur;
LOOP
FETCH fall_jobs_cur INTO job_rec;
EXIT WHEN fall_jobs_cur%NOTFOUND;
IF job_rec.do_it_yourself_flag = 'YOUCANDOIT'
THEN
UPDATE winterize SET responsible = 'STEVEN'
WHERE CURRENT OF fall_Jobs_cur;
COMMIT;
EXIT;
END IF;
END LOOP;
CLOSE fall_jobs_cur;
END;
