Главная | Обратная связь

Вставка строк результата запроса

Наконец, обсудим вариант оператора вставки, когда набор вставляемых строк определяется через спецификацию запроса. Предположим, например, что требуется сохранить в отдельной таблице DEPT_SUMMARY сведения о числе служащих каждого отдела, их максимальной, минимальной и суммарной заработной плате. Пусть таблица DEPT_SUMMARY уже создана и имеет следующийзаголовок¹⁵²⁾:

DEPT_SUMMARY:

DEPT_NO : DEPT_NO

DEPT_EMP_NO : INTEGER

DEPT_MAX_SAL : SALARY

DEPT_MIN_SAL : SALARY

DEPT_TOTAL_SAL : SALARY

Тогда заполнить таблицу можно с помощью следующей операции вставки (пример 21.4):

INSERT INTO DEPT_SUMMARY (SELECT DEPT_NO, COUNT(*), MAX (EMP_SAL), MIN (EMP_SAL), SUM (EMP_SAL) FROM EMP GROUP BY DEPT_NO);

21.2.2. Оператор UPDATE для модификации существующих строк в существующих таблицах

Общий синтаксис оператора UPDATE выглядит следующим образом:

UPDATE table_name SET update_assignment_commalist WHERE conditional_expressionupdate_assignment ::= column_name = { value_expression | DEFAULT | NULL }

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

1. для всех строк таблицы с именем table_name вычисляется булевское выражение conditional_expression. Строки, для которых значением этого булевского выражения является true, считаются подлежащими модификации (обозначим множество таких строк через T_m);
2. каждая строка s (s T_m) подвергается модификации таким образом, что значение каждого столбца этой строки, указанного в списке update_assignment_commalist, заменяется значением, указанным в правой части соответствующего элемента спискамодификации¹⁵³⁾. Значения столбцов строки s, не указанные в списке модификации, остаются неизменными.

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

153 Если в правой части элемента модификации присутствует value_expression, в котором содержится запрос, то в случае использования в этом запросе имен столбцов модифицируемой таблицы под значениями этих столбцов понимается значение до модификации.

Приведем примеры операций модификации таблиц.

Пример 21.5. Перевести всех служащих, выполняющих проект с номером 772, в отдел 632 и повысить им заработную плату на 1000 руб.

UPDATE EMP SET DEPT_NO = 632, EMP_SAL = EMP_SAL + 1000.00 WHERE PRO_NO = 772;

При выполнении данной операции на первом шаге в таблице EMP будут найдены все строки, относящиеся к служащим, которые участвуют в проекте с номером 772. На втором шаге во всех этих строках значение столбца DEPT_NO будет изменено на 632, а к значению столбца EMP_SAL будет прибавлено 1000.00.

Пример 21.6. Для всех служащих, работающих в отделах, заработная плата менеджеров которых превышает 30000 руб., установить размер заработной платы, на 1000 руб. превышающий средний размер заработной платы соответствующего отдела, а номера проектов, в которых участвуют эти служащие, сделать неопределенными.

UPDATE EMP SET EMP_SAL = (SELECT AVG (EMP1_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO) + 1000.00, PRO_NO = NULL WHERE (SELECT EMP1.EMP_SAL FROM EMP EMP1, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND DEPT_MNG = EMP1.EMP_NO AND) > 30000.00;

Конечно, если вам больше нравится другой стиль, то запрос, фигурирующий в разделе WHERE, можно переформулировать с использованием вложенного подзапроса (пример 21.6.1).

UPDATE EMP SET EMP_SAL = (SELECT AVG (EMP1_SAL) FROM EMP EMP1 WHERE EMP.DEPT_NO = EMP1.DEPT_NO) + 1000.00, PRO_NO = NULL WHERE DEPT.NO IN (SELECT DEPT.DEPT_NO FROM EMP, DEPT WHERE DEPT_MNG = EMP_NO AND EMP_SAL > 30000.00);

Эти примеры позволяют понять, насколько богаты возможности оператора UPDATE. В разделе WHERE может содержаться любое условие, допускаемое в операторе выборки, а в элементах списка раздела SET может присутствовать любой вид value_expression, в том числе любой запрос, вырабатывающий одиночное значение (скалярный подзапрос).

21.2.3. Оператор DELETE для удаления строк в существующих таблицах

Общий синтаксис оператора DELETE выглядит следующим образом:

DELETE FROM table_name WHERE conditional_expression

В некотором смысле оператор DELETE является частным случаем оператора UPDATE (или, наоборот, действие оператора UPDATE представляет собой комбинацию действий операторов DELETE и INSERT).

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

1. для всех строк таблицы с именем table_name вычисляется булевское выражение conditional_expression. Строки, для которых значением этого булевского выражения является true, считаются подлежащими удалению (обозначим множество таких строк через T_d);
2. каждая строка s (s T_d) удаляется из указанной таблицы.

С целью иллюстрации приведем два примера операции удаления строк.

Пример 21.7. Удалить из таблицы EMP все строки, относящиеся к служащим, которые участвуют в проекте с номером 772.

DELETE FROM EMP WHERE PRO_NO = 772;

Пример 21.8. Удалить из таблицы EMP все строки, относящиеся к служащим, размер заработной платы которых превышает размер заработной платы менеджеров их отделов.

DELETE FROM EMP WHERE EMP_SAL > (SELECT EMP1.EMP_SAL FROM EMP EMP1, DEPT WHERE EMP.DEPT_NO = DEPT.DEPT_NO AND DEPT.DEPT.MNG = EMP1.EMP_NO);

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