Следует обратить внимание на то, что в этом примере при проверке условия P.AccountCD =A.AccountCD внутренний запрос ссылается к строке таблицы PaySumma, проверяемой в данный момент. Это означает, что подзапрос будет выполняться отдельно для каждой строки таблицы PaySumma. В результате выполнения запроса из таблицы PaySumma будет удалено 15 записей об оплатах абонентов с номерами лицевых счетов 126112, 136160, 136169 и 443069 (именно они проживают на улице Татарской).

Следует отметить, что для этого примера имеется другой способ выполнить те же самые действия в следующем виде: DELETE FROM PaySumma P WHERE ТАТАРСКАЯ УЛИЦА IN (SELECT StreetNM FROM Street S, Abonent A

WHERE A.StreetCD = S. StreetCD AND P.AccountCD = A. AccountCD);.

В качестве подзапроса в предложении WHERE запроса DELETE можно использовать запрос SELECT, который внутри себя содержит соотнесенный подзапрос.

Например, требуется удалить ремонтные заявки тех исполнителей, которые выполнили менее 4 ремонтных заявок. Запрос будет выглядеть следующим образом:

DELETE FROM Request

WHERE ExecutorCD IN

(SELECT ExecutorCD FROM Request A

WHERE 4 > (SELECT COUNT(ExecutionDate) FROM Request B WHERE A.ExecutorCD=B.ExecutorCD));.

Таблица Request после удаления представлена на рис. 5.13.

Рис. 5.13. Таблица Request после удаления ремонтных заявок исполнителей,

выполнивших менее 4 заявок

Для работы вложенного соотнесенного запроса формируется текущая строка-кандидат (т.е. берется первая строка таблицы Request). Внутренний запрос подзапроса находит количество непустых значений дат выполнения ремонтных заявок для кода исполнителя, содержащегося в строке-кандидате (условие

A.ExecutorCD=B.ExecutorCD ). Запрос DELETE удаляет все строки со значением поля ExecutorCD, входящим во множество значений, формируемых вложенным соотнесенным запросом. В итоге будут удалены все строки таблицы Request, в которых значения поля ExecutorCD равны 2, 4 и 5 (количество выполненных ими заявок 2, 3 и 3 соответственно).

5.6. Обновление представлений

Как было отмечено, одной из операций над представлениями является их непосредственное использование с запросами модификации DML: INSERT, UPDATE и DELETE. Такие представления называются модифицируемыми (или обновляемыми). Представление можно обновлять, если определяющий его запрос соответствует следующим требованиям:

- должен отсутствовать оператор DISTINCT; т.е. повторяющиеся строки не должны исключаться из таблицы результатов запроса;

- в предложении FROM должна быть задана только одна таблица, которую можно обновлять; т.е. у представления должна быть одна исходная таблица, а пользователь должен иметь соответствующие права доступа к ней. Если исходная таблица сама является представлением, то оно также должно удовлетворять этим условиям;

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

- предложение WHERE не должно содержать вложенный запрос; т. е. в нем могут присутствовать только простые условия поиска;

- в запросе не должно содержаться предложение GROUP BY или HAVING. Эти требования базируются на том принципе, что представление разрешается

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

/* Представление Dailyrequest только для чтения из-за наличия DISTINCT в запросе */

CREATE VIEW Dailyrequest

AS SELECT DISTINCT AccountCD, RequestCD, IncomingDate, FailureCD FROM Request;

/* Представление Summtotal только для чтения из-за наличия агрегатной функции в списке возвращаемых элементов, обращения к двум таблицам и использования предложения GROUP BY */

CREATE VIEW Summtotal (Abonent, Summa)

AS SELECT Fio, SUM(NachislSum)

FROM Abonent A, NachislSumma N WHERE A.AccountCD = N.AccountCD GROUP BY Fio;

/* Представление SummAbonent только для чтения из-за наличия вложенного запроса в запросе SELECT */

CREATE VIEW Summ Abonent

AS SELECT * FROM Abonent WHERE AccountCD IN (SELECT AccountCD FROM NachislSumma WHERE NachislSum = 46);

Примечание. Пояснения, стоящие перед запросами CREATE VIEW, представляют собой комментарии SQL и будут подробно рассмотрены далее при изучении процедурного языка.

Рассмотрим примеры работы с обновляемыми представлениями, которые наглядно демонстрируют, как изменяются данные представления при манипулировании данными таблицы, на основе которой оно создано, и как изменяются данные таблицы при манипулировании данными представления.

Пусть требуется создать смешанное представление, которое содержит снимок таблицы Abonent. Вертикальное подмножество должно включать в себя столбцы с фамилиями и номерами лицевых счетов абонентов, а горизонтальное подмножество - строки со значением номера лицевого счёта абонента, больше или равного 200000. Для этого можно использовать следующий запрос: CREATE VIEW Abonent View (AccountCD, Fio) AS SELECT AccountCD, Fio FROM Abonent

WHERE AccountCD >= 200000 WITH CHECK OPTION;.

Результат выполнения запроса

SELECT * FROM Abonent View;

представлен на рис. 5.14.

Рис. 5.14. Данные представления AbonentView

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

- является подмножеством одной таблицы (нет соединения таблиц);

- в списке возвращаемых элементов нет вычисляемых выражений и агрегатных функций;

- используется простое условие поиска (без подзапросов);

- запрос SELECT, на котором базируется представление, не содержит DISTINCT, предложений GROUP BY и HAVING.

Следовательно, к этому представлению могут быть применены запросы INSERT, UPDATE и DELETE. Продемонстрируем это с помощью следующих примеров.