Иногда, при выборке из курсора бывает ситуация, что какой-либо столбец или строки результирующего набора необходимо обновить. То есть, изменить их содержимое. Для того, чтобы это осуществить, непосредственно при объявлении курсора необходимо использовать конструкцию - FOR UPDATE (для обновления ..). А, так же конструкцию, WHERE CURRENT OF (где текущая строка ..) в операторах UPDATE, DELETE. Собственно конструкция FOR UPDATE, является частью оператора SELECT и объявляется последней:
------ SELECT .... FROM .... FOR UPDATE [OF ссылка на столбец][NOWAIT] --------
Где, собственно, "ссылка на столбец" это столбец таблицы, для которой выполнен запрос. Можно так же использовать список столбцов. Например, вот так:
DECLARE CURSOR get_sls IS SELECT * FROM SALESREPS FOR UPDATE OF SALESREPS.QUOTA, SALESREPS.SALES; . . . . -- Для столбцов QUOTA и SALES, таблицы SALESREPS. DECLARE CURSOR get_sls(INMG SALESREPS.MANAGER%TYPE) IS SELECT * FROM SALESREPS WHERE MANAGER = INMG FOR UPDATE; . . . . -- Для всех столбцов таблицы SALESREPS.
Теперь немного теории. Так как обычный запрос с помощью оператора SELECT, при выполнении получает строки таблицы и при этом сама таблица выборки не блокируется, то есть любой другой пользователь может выполнить запрос к той же таблице, получив при этом данные. В Oracle при выполнении запроса, т.е. при извлечении активного набора SELECT, производится моментальный снимок таблицы (snapshot), при этом все изменения сделанные до этого момента кем-либо еще отражаются в данном наборе. А, после того как snapshot получен все изменения, произведенные в данной таблице выборке, даже если они зафиксированы оператором COMMIT, отражаться не будут!!! Для того, чтобы их отразить нужно закрыть и снова открыть курсор, загрузив данные заново! Это и есть алгоритм согласованного чтения данных, о котором я уже упоминал ранее. А вот когда мы объявляем FOR UPDATE - строки активного набора данных блокируются до момента выполнения COMMIT. Таким образом мы запрещаем изменение данных другим сеансам. Если какой-либо сеанс уже блокировал строки, то следующий SELECT FOR UPDATE, будет ждать снятия блокировки. В этом случае можно применить NOWAIT (без ожидания). Если обратиться к заблокированным строкам получим сообщение об ошибке ORA-54. Вот таким образом это работает. А вот конструкция WHERE CURRENT OF используется уже непосредственно при изменении данных:
------ WHERE CURRENT OF курсор ------
Где "курсор" - это курсор, открытый на обновление. Давайте рассмотрим практический пример такого курсора:
DECLARE CURSOR cur_upd(INTG OFFICES.TARGET%TYPE) IS SELECT * FROM SALESREPS WHERE MANAGER IN ( SELECT O.MGR FROM OFFICES O WHERE TARGET > INTG) FOR UPDATE OF SALESREPS.QUOTA; BEGIN FOR get_cur_upd IN cur_upd(700) LOOP UPDATE SALESREPS SET SALESREPS.QUOTA = SALESREPS.QUOTA + 50 WHERE CURRENT OF cur_upd; END LOOP; COMMIT; END; /
После запуска в SQL*Plus получаем:
SQL> DECLARE 2 3 CURSOR cur_upd(INTG OFFICES.TARGET%TYPE) IS 4 SELECT * FROM SALESREPS 5 WHERE MANAGER IN ( 6 SELECT O.MGR FROM OFFICES O 7 WHERE TARGET > INTG) 8 FOR UPDATE OF SALESREPS.QUOTA; 9 10 BEGIN 11 12 FOR get_cur_upd IN cur_upd(700) LOOP 13 14 UPDATE SALESREPS 15 SET SALESREPS.QUOTA = SALESREPS.QUOTA + 50 16 WHERE CURRENT OF cur_upd; 17 18 END LOOP; 19 20 COMMIT; 21 22 END; 23 / Процедура PL/SQL успешно завершена.
Да, а что произошло? Просто строки столбца QUOTA, таблицы SALESREPS, соответствующие условию TARGET > 700 увеличились, на 50! Что, можно проверить, выполнив простой запрос:
SELECT * FROM SALESREPS /
Его посмотрите сами. Обратите внимание на то, что курсор выполнен с передачей параметра и использует цикл LOOP. Конструкция FOR UPDATE OF SALESREPS.QUOTA определяет обновляемый столбец, конструкция WHERE CURRENT OF cur_upd в операторе UPDATE SALESREPS определяет какие строки обновить. Кстати для закрепления, используя пакет DBMS_OUTPUT можете добавить код для того, чтобы было видно, что происходит. Оператор COMMIT, расположенный вне тела цикла, снимает блокировку с таблицы и фиксирует изменения. Давайте, с помощью другого блока, применив более компактный код, вернем все назад:
DECLARE CURSOR cur_upd(INTG OFFICES.TARGET%TYPE) IS SELECT * FROM SALESREPS WHERE MANAGER IN ( SELECT O.MGR FROM OFFICES O WHERE TARGET > INTG) FOR UPDATE NOWAIT; BEGIN FOR get_cur_upd IN cur_upd(700) LOOP UPDATE SALESREPS SET SALESREPS.QUOTA = SALESREPS.QUOTA - 50 WHERE CURRENT OF cur_upd; END LOOP; COMMIT; END; /
После запуска в SQL*Plus получаем:
SQL> DECLARE 2 3 CURSOR cur_upd(INTG OFFICES.TARGET%TYPE) IS 4 SELECT * FROM SALESREPS 5 WHERE MANAGER IN ( 6 SELECT O.MGR FROM OFFICES O 7 WHERE TARGET > INTG) 8 FOR UPDATE NOWAIT; 9 10 BEGIN 11 12 FOR get_cur_upd IN cur_upd(700) LOOP 13 14 UPDATE SALESREPS 15 SET SALESREPS.QUOTA = SALESREPS.QUOTA - 50 16 WHERE CURRENT OF cur_upd; 17 18 END LOOP; 19 20 COMMIT; 21 22 END; 23 / Процедура PL/SQL успешно завершена.
Здесь хорошо видно, что мы применили конструкцию FOR UPDATE NOWAIT, хотя в результате работы мы получили то, что нужно. Так же, применив курсорный цикл FOR, мы сделали более компактный код. Теперь, давайте побеседуем на тему оператора COMMIT. Посмотрим вот такой блок:
DECLARE CURSOR cur_upd(INTG OFFICES.TARGET%TYPE) IS SELECT * FROM SALESREPS WHERE MANAGER IN ( SELECT O.MGR FROM OFFICES O WHERE TARGET > INTG) FOR UPDATE NOWAIT; get_cur_upd cur_upd%ROWTYPE; BEGIN OPEN cur_upd(700); FETCH cur_upd INTO get_cur_upd; COMMIT WORK; FETCH cur_upd INTO get_cur_upd; END; /
Получаем:
SQL> DECLARE 2 3 CURSOR cur_upd(INTG OFFICES.TARGET%TYPE) IS 4 SELECT * FROM SALESREPS 5 WHERE MANAGER IN ( 6 SELECT O.MGR FROM OFFICES O 7 WHERE TARGET > INTG) 8 FOR UPDATE NOWAIT; 9 10 get_cur_upd cur_upd%ROWTYPE; 11 12 BEGIN 13 14 OPEN cur_upd(700); 15 16 FETCH cur_upd INTO get_cur_upd; 17 18 COMMIT WORK; 19 20 FETCH cur_upd INTO get_cur_upd; 21 22 END; 23 / DECLARE * ошибка в строке 1: ORA-01002: выборка из последовательности ORA-06512: на line 20
Оператор COMMIT WORK снял блокировку с таблицы и последующая выборка привела к ошибке. Следовательно, как в предыдущих примерах, располагать COMMIT нужно после цикла. А что, если нужно обновить строки из курсора не применяя конструкции FOR UPDATE ? Можно, если у таблицы есть уникальный ключ, вот так:
DECLARE CURSOR cur_upd(INTG OFFICES.TARGET%TYPE) IS SELECT * FROM SALESREPS WHERE MANAGER IN ( SELECT O.MGR FROM OFFICES O WHERE TARGET > INTG); BEGIN FOR get_cur_upd IN cur_upd(700) LOOP UPDATE SALESREPS SET SALESREPS.QUOTA = SALESREPS.QUOTA - 10 WHERE EMPL_NUM = get_cur_upd.EMPL_NUM; -- Is not FOR UPDATE -- COMMIT WORK; END LOOP; COMMIT; END; /
Получаем:
SQL> DECLARE 2 3 CURSOR cur_upd(INTG OFFICES.TARGET%TYPE) IS 4 SELECT * FROM SALESREPS 5 WHERE MANAGER IN ( 6 SELECT O.MGR FROM OFFICES O 7 WHERE TARGET > INTG); 8 9 10 BEGIN 11 12 FOR get_cur_upd IN cur_upd(700) LOOP 13 14 UPDATE SALESREPS 15 SET SALESREPS.QUOTA = SALESREPS.QUOTA - 10 16 WHERE EMPL_NUM = get_cur_upd.EMPL_NUM; 17 18 -- Is not FOR UPDATE -- 19 COMMIT WORK; 20 21 END LOOP; 22 23 COMMIT; 24 25 END; 26 / Процедура PL/SQL успешно завершена.
Здесь нет конструкции FOR UPDATE, но строки обновляются с применением первичного ключа таблицы. Как правило, так обычно и поступают, но FOR UPDATE так же в отдельных случаях бывает весьма полезной. И оператор COMMIT теперь расположен внутри цикла выборки! Здесь все просто, но только с первого взгляда. Если, что не понятно, можете спрашивать! :)