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

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

Операции SQL по изменению данных в БД — это принадлежащие категории DML INSERT, UPDATE и DELETE плюс вторичная по отношению к ним MERGE. Все они множественные (вслед за реляционной моделью, где это неслучайно), то есть в общем рассчитаны одним действием изменить сразу множество строк таблицы. Исключение составляет формально однострочная разновидность оператора INSERT.

Операции INSERT, UPDATE, DELETE и MERGE унаследованы в Oracle от стандарта SQL. В реляционной теории операций INSERT, UPDATE и DELETE как таковых нет, но они легко моделируются существующими другими.

Добавление новых строк

Добавление одной строки

Пример:

INSERT INTO proj ( projno ) VALUES ( 20 );

В общем случае имена столбцов (до слова VALUES) и значения (после слова VALUES) приводятся списками с равными количествами элементов.

Имена столбцов со свойством NOT NULL и значения для них должны присутствовать в списках обязательно, если только для них не определены умолчательные значения или если значения не заносятся триггерными процедурами. При несоблюдении этих условий возникает ошибка времени исполнения.

В то время как порядок перечисления выражений должен копировать порядок перечисления имен столбцов перед словом VALUES, сам порядок имен столбцов может быть произвольным. Об этом напоминает синтаксис, и это еще одно наследие в SQL реляционной теории. Следующие предложения равносильны:

INSERT 
INTO proj ( projno, pname, bdate, budget ) 
VALUES
   ( 30, 'BETA', SYSDATE, 20000 )
;
INSERT 
INTO proj ( bdate, budget, projno, pname )
VALUES
   ( SYSDATE, 20000, 30, 'BETA' )
;

Для последней пары запросов SQL допускает и более краткую формулировку:

INSERT INTO proj VALUES ( 30, 'BETA', SYSDATE, 20000 );

Эта краткость может показаться соблазнительной, однако подобная формулировка недопустима в реляционной БД, так как предполагает наличие порядка столбцов в таблице SQL, в то время как в отношениях реляционной модели порядка атрибутов не существует. С практической точки зрения полагаться в запросе на порядок столбцов — ненадежно, ведь Oracle разрешает добавлять и удалять столбцы в таблице; так что со временем порядок может нарушиться и операция окажется некорректной. Кроме того, перечисляя выражения для подстановки значений в поля добавляемой строки, программист, не имея перед глазами списка имен полей, легко может ошибиться.

Пример использования подзапроса в выражении во фразе VALUES:

INSERT INTO emp
   ( empno, deptno ) 
VALUES
   ( 1111, ( SELECT deptno FROM dept WHERE loc = 'CHICAGO' ) )
;

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

Добавление строк, полученных подзапросом

Множественный вариант INSERT предполагает добавление одним оператором в таблицу сразу группы строк. Добавляемые строки определяются оператором SELECT.

Пример:

DELETE FROM dept_copy;
INSERT INTO dept_copy SELECT * FROM dept WHERE deptno IN ( 10, 20 );
INSERT 
INTO dept_copy ( loc, dname, deptno )
   SELECT loc, INITCAP ( dname ), deptno + 100 FROM dept
;
INSERT INTO dept_copy ( deptno ) SELECT deptno + 200 FROM dept
;

Замечания

• На формулировку SELECT в предложении INSERT никаких нарочных ограничений не накладывается (в том числе допускаются GROUP BY, агрегатные функции и тому подобное)
• Типы столбцов должны быть совместимы.

Операция вставки INSERT в общем случае множественная. Ее однострочный вариант можно полагать частным случаем множественного.

Добавление строк одним оператором в несколько таблиц

С версии 9 строки, полученные подзапросом, можно "раскидать" по нескольким таблицам единственным оператором INSERT, например:

CREATE TABLE e1000 AS SELECT ename, sal FROM emp WHERE 1 = 2;
CREATE TABLE e2000 AS SELECT ename, sal FROM emp WHERE 1 = 2;
CREATE TABLE e3000 AS SELECT *          FROM emp WHERE 1 = 2;
INSERT ALL
   WHEN sal > 3000 THEN INTO e3000 
   WHEN sal > 2000 THEN INTO e2000 ( ename ) VALUES ( ename )
   WHEN sal > 1000 THEN INTO e1000           VALUES ( ename, sal )
SELECT * FROM emp WHERE job <> 'SALESMAN'
;

Упражнение. Проверьте содержимое таблиц E1000, E2000 и E3000. Сохраните их данные, удалите таблицы и выполните пример заново, указав вместо INSERT ALL слова INSERT FIRST. Сравните новое содержимое таблиц с предыдущим.

Последовательность проверок WHEN можно завершать проверкой ELSE.

Фразы WHEN … THEN можно опускать, тогда будет выполняться безусловная вставка строк в таблицы, например:

INSERT ALL
   WHEN comm IS NULL THEN INTO e1000 VALUES ( ename, sal )
   INTO e2000 ( ename ) VALUES ( ename )
   INTO e3000
SELECT * FROM emp WHERE job <> 'SALESMAN'
;

Вставка строк оператором INSERT разом в несколько таблиц эффективнее последовательности однотабличных INSERT, так как перемещает процедурную логику внутрь машины SQL СУБД. Это заметно при добавлении данных больших объемов. Кроме того, обновление таблиц выполняется применительно к одному состоянию БД, а потому логически не сводимо к последовательному выполнению команд INSERT.

Вот пример использования формально многотабличного оператора INSERT для занесения данных в БД с попутным выполнением переформатирования (похожее переформатирование, но только в запросе SELECT, а не при добавлении в таблицы БД может с версии 11 выполняться конструкцией SELECT ... FROM … UNPIVOT …).

Пример. Построим таблицу исходных данных:

CREATE TABLE e1 AS SELECT ename, sal, comm FROM emp;

Построим пустую таблицу для результата, где вместо двух полей SAL и COMM каждому сотруднику сопоставим отдельное поле с указанием вида платежа и отдельное поле для величины платежа:

CREATE TABLE e2 ( ename, amount )
  AS 
  SELECT ename, sal FROM emp WHERE 1 = 2
;
ALTER TABLE e2 
  ADD ( 
  payment VARCHAR2 ( 10 ) CHECK ( payment IN ( 'salary', 'commission' ) ) 
);

Теперь заполнение таблицы E2 можно выполнить следующим образом:

INSERT ALL 
WHEN sal  IS NOT NULL THEN INTO e2 VALUES ( ename, sal,  'salary' )
WHEN comm IS NOT NULL THEN INTO e2 VALUES ( ename, comm, 'commission' )
SELECT * FROM e1
;

Результат:

ENAME          AMOUNT PAYMENT
---------- ---------- ----------
SMITH             800 salary
ALLEN            1600 salary
WARD             1250 salary
JONES            2975 salary
MARTIN           1250 salary
BLAKE            2850 salary
CLARK            2450 salary
SCOTT            3000 salary
KING             5000 salary
TURNER           1500 salary
ADAMS            1100 salary
JAMES             950 salary
FORD             3000 salary
MILLER           1300 salary
ALLEN             300 commission
WARD              500 commission
MARTIN           1400 commission
TURNER              0 commission
18 rows selected.