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

Изменение существующих значений полей строк

Логически операция UPDATE вторична, так как сводима к последовательности DELETE и INSERT, но в системах SQL технически не отрабатывается. (Строго говоря, это не совсем так. Технически Oracle способна в некоторых случаях именно удалить запись в БД, представляющую строку таблицы, и добавить вместо нее новую, но это не единственный способ осуществления операции.) Она используется ради удобства применения. Любопытно, что если изменяется поле индексированного столбца и заодно с данными таблицы изменяется индекс, его изменение осуществляется ровно последовательным удалением из индекса старого значения и добавлением нового.

Примеры употребления:

UPDATE proj SET pname = 'GAMMA' WHERE projno = 15;
UPDATE proj 
SET 
  pname  = 'GAMMA'
, budget = budget * 1.05 
WHERE projno = 15
;

Поскольку речь идет об изменении значений полей уже существующих строк, в предложении UPDATE присутствует фраза WHERE, уточняющая множество строк для внесения изменения. Правила записи фразы WHERE те же, что и для предложения SELECT. Как и в SELECT, если в предложении UPDATE фраза WHERE не указана, изменение коснется всех строк источника данных.

Следующая форма UPDATE предполагает указание списка проставляемых в таблицу значений только однострочным подзапросом:

UPDATE proj 
SET
  ( pname, budget ) =
  ( SELECT pname || '1', budget * 0.95 FROM proj WHERE projno <= 10 )
;

Упражнение. Проверьте, каков будет результат, если:

• вложенный SELECT вернет более одной строки;
• вложенный SELECT не вернет не одной строки;
• столбец BUDGET будет не заполнен (NULL);
• столбец BUDGET будет частично заполнен.

Еще пример формулирования предложения UPDATE:

UPDATE proj 
SET budget =
      CASE 
         WHEN pname = 'GAMMA' THEN budget
         WHEN budget IS NULL  THEN 0
      ELSE NULL
      END
;

На деле это всего лишь пример использования оператора CASE в построении выражения.

Операция UPDATE изменения существующих значений — множественная в силу своей формулировки.

Общие свойства INSERT и UPDATE

Операции INSERT и UPDATE роднит то, что обе по сути выполняют присвоение значений. Далее говорится о связанных с этим общими их свойствами.

Использование умолчательных значений в INSERT и UPDATE

Выражение для значения поля добавляемой или изменяемой строки можно заменить словом DEFAULT (разрешено в SQL:1999). В случае, когда в определении столбца присутствует выражение для вычисления умолчательного значения, именно оно и будет вычислено, и результат занесен в поле. Если умолчательное значение столбца явно не задавалось, указание слова DEFAULT в качестве значения равносильно указанию NULL (можно полагать, что если в определении столбца конструкция DEFAULT явно не указана, молчаливо предполагается DEFAULT NULL).

Пример:

CREATE TABLE t ( r NUMBER, a NUMBER, b NUMBER DEFAULT 123 )
;
INSERT INTO t ( r, a, b ) VALUES ( 1, 1, 2 );
INSERT INTO t ( r, a, b ) VALUES ( 2, NULL, NULL );
INSERT INTO t ( r, a, b ) VALUES ( 3, DEFAULT, DEFAULT );
INSERT INTO t ( r       ) VALUES ( 4 );
INSERT INTO t             VALUES ( 5, DEFAULT, DEFAULT );
Проверка:
SQL> SELECT * FROM t;
         R          A          B
---------- ---------- ----------
         1          1          2
         2
         3                   123
         4                   123
         5                   123

Аномалия проверки занесенного в БД значения

Необычное поведение традиционных операций сравнения (=, <> и др.) с NULL влечет непривычный эффект проверки добавленного в БД командами INSERT и UPDATE значения. Обратимся снова к таблице T из предыдущего примера:

SQL> VARIABLE n NUMBER
SQL> INSERT INTO t ( r, a ) VALUES ( 6, :n );
1 row created.
SQL> SELECT * FROM t WHERE r = 6 AND a = :n;
no rows selected
SQL> SELECT COUNT ( * ) FROM t WHERE r = 6;
  COUNT(*)
----------
         1

Такое поведение особенно неприятно в программе, и для привлечения внимания именно к этому контексту употребления вместо явного упоминания NULL в примере была применена переменная SQL*Plus. При простом объявлении переменной N она не получила никакого значения, так что появление NULL в запросах оказалось скрытым с ее помощью.

Можно вспомнить, что корни такого поведения Oracle уходят в стандарт SQL.

Удаление строк из таблицы

Выборочное удаление

Основной оператор для удаления строк из таблицы — DELETE.

Примеры:

DELETE FROM proj WHERE projno = 16;
DELETE FROM proj WHERE pname IS NULL;
DELETE FROM dept_copy;

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

Операция удаления строк DELETE — множественная в силу своей формулировки.

Вариант полного удаления

Вместо полного удаления строк командой DELETE (в отсутствии фразы WHERE), например, вместо

DELETE FROM dept_copy;

можно употреблять более быструю команду TRUNCATE TABLE:

TRUNCATE TABLE dept_copy;

Особенности TRUNCATE TABLE:

• DDL-операция невосстанавливаемая операция;
• быстро выполняется (ощутимо на больших таблицах), поскольку строки удаляются как результат укорачивания структуры хранения данных таблицы в БД ("сегмента"), а не поштучно, как при DELETE.

Если очищенную от строк таблицу предполагается впоследствии снова заполнять, время последующего заполнения сократится, если выдать:

TRUNCATE TABLE dept_copy REUSE STORAGE;

В этом случае строки будут полагаться удаленными, а структура хранения данных таблицы в БД останется внешне неизменной.