лицы DEPT EAST, DEPT WEST и DEPT MID. Структура всех трех

таблиц (количество и типы столбцов) такая же, как и у DEPT, и в настоящий момент они пусты.

Решение

Решение - вставить результат запроса в необходимые таблицы. Отличие от Копирования строк из одной таблицы в другую состоит в том, что в данном случае имеется несколько таблиц на выходе.

Oracle

Используйте выражения INSERT ALL (вставить во все) или INSERT FIRST (вставить в первую). Синтаксис обоих выражений одинаковый, кроме ключевых слов ALL и FIRST. В следующем примере используется выражение INSERT ALL, что обеспечивает вставку во все возможные таблицы:

1 insert all

2 when loc in (NEW YORK,BOSTON) then

3 into dept east (deptno,dname,loc) values (deptno,dname,loc)

4 when loc = CHICAGO then

5 into dept mid (deptno,dname,loc) values (deptno,dname,loc)

6 else

7 into dept west (deptno,dname,loc) values (deptno,dname,loc)

8 select deptno,dname,loc

9 from dept

Осуществляйте вставку во вложенное представление, в котором происходит объединение (UNION ALL) всех вставляемых таблиц. Причем необходимо наложить ограничения на таблицы, гарантирующие размещение строк в соответствующих таблицах:

create table dept east ( deptno integer, dname varchar(10),

loc varchar(10) check (loc in (NEW YORK,BOSTON)))

create table dept mid ( deptno integer, dname varchar(10),

loc varchar(10) check (loc = CHICAGO))

create table dept west ( deptno integer, dname varchar(10),

loc varchar(10) check (loc = DALLAS))

1 insert into (

2 select * from dept west union all

3 select * from dept east union all

4 select * from dept mid

5 ) select * from dept

MySQL, PostgreSQL и SQL Server

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

Обсуждение

Oracle

В Oracle для вставки данных в несколько таблиц используются блоки WHEN-THEN-ELSE, которые обеспечивают обработку строк, возвращаемых вложенным SELECT, и соответствующую их вставку. В примере данного рецепта выражения INSERT ALL and INSERT FIRST обеспечили бы один и тот же результат, но между ними есть отличие.

INSERT FIRST завершит вычисление WHEN-THEN-ELSE, как только

условие будет выполнено; INSERT ALL проверит все условия, даже если предыдущие условия выполнены. Таким образом, INSERT ALL может использоваться для вставки одной строки в несколько таблиц.

Мое решение для DB2 немного сбивает с толку. Оно требует, чтобы на вставляемые таблицы были наложены ограничения, гарантирующие, что каждая строка, возвращаемая подзапросом, будет направлена в соответствующую таблицу. Техника заключается во вставке в представление, которое определено как объединение всех (UNION ALL) таблиц. Если проверочные ограничения выражения INSERT не уникальны (т. е. несколько таблиц имеют одинаковое проверочное ограничение), выражение INSERT не будет знать, где размещать строки, и даст сбой.

MySQL, PostgreSQL и SQL Server

На момент написания данной книги только Oracle и DB2 предоставляли механизмы для вставки строк, возвращенных запросом, в одну или более таблиц в рамках одного выражения.

Блокировка вставки в определенные столбцы Задача

Требуется предотвратить вставку значений в определенные столбцы таблицы пользователями или программным обеспечением. Например, вы хотите предоставить программе возможность вставлять значения в таблицу EMP, но только в столбцы EMPNO, ENAME и JOB.

Решение

Создайте представление, базированное на данной таблице, содержащее столбцы, доступные для вставки. Затем обеспечьте, чтобы вся вставка осуществлялась только посредством этого представления. Например, создадим представление, содержащее три столбца таблицы EMP:

create view new emps as select empno, ename, job from emp

Открывая доступ к этому представлению, вы разрешаете пользователям и программам вставлять данные только в эти три поля представления. Не давайте пользователям разрешение на вставку в таблицу EMP напрямую. Пользователи получат возможность создавать новые записи в EMP, вставляя данные в представление NEW EMPS, но они смогут размещать значения только в трех столбцах, которые заданы в описании представления.

Обсуждение

При вставке в простое представление, такое как приведено в данном решении, сервер базы данных вставит эти данные в базовую таблицу. Например, следующая вставка:

insert into new emps (empno, ename, job) values (1, Jonathan, Editor)

будет преобразована сервером в:

insert into emp

(empno, ename, job)

values (1, Jonathan, Editor)

Также можно, но, вероятно, имеет меньший практический смысл, вставлять данные во вложенное представление (в настоящее время поддерживается только Oracle):

insert into

(select empno, ename, job from emp) values (1, Jonathan, Editor)

Вставка в представления - сложный вопрос. Правила сильно усложняются с повышением сложности представлений. Если вы планируете использовать возможность вставки в представления, обязательно досконально изучите и разберитесь с документацией производителя, касающейся этого вопроса.

Изменение записей в таблице Задача

Требуется изменить значения некоторых или всех строк таблицы. Например, повысить заработные платы всех служащих 20-го отдела на 10%. В следующем результирующем множестве представлены столбцы DEPTNO, ENAME и SAL с данными служащих этого отдела:

select deptno,ename,sal from emp