Команда SELECT ... FOR UPDATE в PL/SQL на примерах

SELECT ... FOR UPDATE в PL/SQLПри выполнении команды 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;

 

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6991 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 3107 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Символьные функции и аргументы...
Символьные функции и аргументы... 10835 просмотров Анатолий Wed, 23 May 2018, 18:54:01
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 5731 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Войдите чтобы комментировать