Обновление данных в таблицах

Объединение INSERT, UPDATE и DELETE в одном операторе

В версии СУБД 9 появилась команда MERGE, позволяющая либо изменить существующие строки какой-нибудь таблицы, либо добавить — в зависимости от сформулированного условия. Условие формулируется на основе соединения целевой таблицы с другой (в общем случае — с источником данных, в качестве которого, однако, помимо обычной таблицы могут выступать еще представление данных, таблицы с внешним хранением с временным хранением данных, а также подзапрос).

Заполним таблицу BONUS данными о сотрудниках, положим, имеющих комиссионные:

INSERT INTO bonus 
   SELECT ename, job, sal, comm
   FROM   emp
   WHERE  comm IS NOT NULL
;

Теперь обновим BONUS данными, "поступившими" из таблицы EMP. Если сотрудник из EMP уже есть в BONUS, повысим ему зарплату, а если нет — добавим к списку BONUS:

 
MERGE
INTO  bonus b
USING emp e
ON  ( b.ename = e.ename )
WHEN MATCHED THEN
UPDATE SET sal = sal * 10
WHEN NOT MATCHED THEN
INSERT VALUES ( e.ename, e.job, e.sal, e.comm )
;

Одна из двух проверок формально может и отсутствовать, что позволяет всего одной командой SQL просто добавить записи, отсутствующие в однотипной таблице.

В версии 10 фраза во фразе WHEN MATCHED можно дополнительно указать DELETE, например:

MERGE 
INTO  bonus b 
USING emp e 
ON  ( b.ename = e.ename )
WHEN MATCHED THEN 
     UPDATE SET sal = sal / 10
     DELETE WHERE sal < 1000
;

(Вернули BONUS в состояние до первого оператора MERGE).

Назначение операции MERGE — ускорить обновление больших таблиц. Обратите внимание, что обновление выполняется применительно к одному состоянию БД и поэтому логически несводимо к последовательному выполнению команд INSERT, UPDATE и, возможно, DELETE.

Целостность выполнения операторов обновления данных и реакция на ошибки

В процессе выполнения множественных операторов обновления данных таблиц изменение отдельной строки может вызвать ошибку — например, вылиться в попытку нарушить ограничение целостности. В таких случаях операция обновления прекращается, а изменения, успевшие произойти до возникновения проблемы, отменяются. Иными словами, операции DML обновления данных либо выполняются целиком, либо, в конечном счете, не выполняются вовсе.

Реакция на ошибки в процессе исполнения

Традиционная реакция на ошибки в процессе выполнения изменяющего данные оператора ("все или ничего") логически оправдана, но не всегда практична в случае больших объемов данных. В версии 10.2 введена возможность не отказываться от исполнения огульно, а вместо этого запоминать возникающие на отдельных строках ошибки в специально подготовленной таблице с целью последующего разбирательства. Специальную таблицу можно завести с помощью особой системной процедуры. Пример ее создания для таблицы EMP и дальнейшего употребления приводится ниже.

SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG ( 'EMP', 'ERR_EMP' )
PL/SQL procedure successfully completed.
SQL> INSERT INTO emp ( empno ) VALUES ( 1111 );
1 row created.
SQL> /
INSERT INTO emp ( empno ) VALUES ( 1111 )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMP) violated
SQL> INSERT INTO emp ( empno ) VALUES ( 1111 ) 
  2  LOG ERRORS INTO err_emp ( 'today error' ) REJECT LIMIT 10;
0 rows created.
SQL> COLUMN ora_err_mesg$ FORMAT A45 WORD
SQL> COLUMN ora_err_tag$ FORMAT A12
SQL> COLUMN err# FORMAT 99999
SQL> COLUMN empno FORMAT A6
SQL> SELECT ora_err_number$ err#, ora_err_mesg$, ora_err_tag$, empno 
SQL> FROM err_emp;
  ERR# ORA_ERR_MESG$                                 ORA_ERR_TAG$ EMPNO
------ --------------------------------------------- ------------ ------
     1 ORA-00001: unique constraint (SCOTT.PK_EMP)   today error  1111
       violated

Обратите внимание, что второй оператор INSERT строку не добавляет (правило первичного ключа нарушить нельзя), но и ошибку в программу не возвращает.

Запрет на изменение данных в таблице

С версии 11 изменение данных в таблице можно запретить, переведя таблицу в состояние READ ONLY:

ALTER TABLE emp READ ONLY;

До этой версии запретить изменения можно было только одновременно во всех объектах, хранящих свои данные в конкретном табличном пространстве.

Фиксация или отказ от изменений в БД

Для сеанса, выдающего команды DML на изменения данных, СУБД создает видимость, что они выполняются сразу в БД. Выполнив тут же запрос, пользователь увидит, будто данные изменились. На деле же они попадут в базу только после выдачи сеансом специальной команды фиксации изменений. Только после этого они станут видны прочим сеансам.

Все изменения со стороны индивидуальных команд DML заносятся Oracle в БД только группами, в рамках транзакции, по завершению транзакции. Команды завершения текущей транзакции:

COMMIT [WORK];
ROLLBACK [WORK];

Завершение транзакции с фиксацией изменений, внесенных операторами DML, происходит только по выдаче (а) команды COMMIT или (б) оператора DDL (скрытым образом завершающего свои действия по изменению таблиц словаря-справочника той же командой COMMIT).

Завершение транзакции с отменой изменений, внесенных операторами DML, происходит только по выдаче команды ROLLBACK, которая или явно выдается программистом, или, в некоторых случаях, неявно порождается самой СУБД (например, по результату аварийного останова работы программы из-за неперехваченной исключительной ситуации).

Упражнение. Вставьте запись в имеющуюся таблицу — откатите изменения. Вставьте запись — зафиксируйте. Создайте таблицу, вставьте запись — откатите изменения. Сохранилась ли таблица и ее данные? Создайте заполненную таблицу предложением CREATE TABLE … AS SELECT …. Откатите изменения.

Oracle нумерует все транзакции сквозным образом постоянно растущими номерами, называемыми SCN (System Change Number, номер изменения системы). Каждая команда COMMIT переводит БД в новое состояние, получающее номер зафиксированной транзакции. Данные БД в более ранних состояниях становятся после этого доступны только средствами (а) восстановления по резервным копиям и (б) "быстрого" восстановления (flashback).

В версии 11 Oracle разрешила не только аннулировать командой ROLLBACK изменения, совершавшиеся в рамках завершаемой транзакции, но также и отменять изменения, выполнявшиеся по очереди несколькими последними транзакциями (завершенными ранее командой COMMIT). Но делается это уже не операцией SQL, а программно, средствами системного пакета DBMS_FLASHBACK.