Слияние данных

ЛЕКЦИЯ № 15 5.4. Слияние данных.........................................................1 5.5. Удаление существующих данных............................5 5.5.1. Неявный запрос DELETE..................................6 5.5.2. Запрос DELETE с подзапросом........................7 5.5.3. Удаление всех строк таблицы.........................10 5.6. Обновление представлений.....................................11 5.4. Слияние данных Для выбора строк из одной таблицы с целью обновления, удаления или вставки строк в другую таблицу используется запрос-действие MERGE [29, 42]. Выбор действия – обновить, удалить строку или добавить ее – определяется в зависимости от условия. Запрос MERGE фактически представляет собой объединение запросов INSERT, UPDATE и DELETE и позволяет избежать их многократного использования. Запрос MERGE используется во всех трех рассматриваемых в учебнике СУБД. В Firebird запрос имеет синтаксис: MERGE INTO <целевая_таблица> [ [AS] псевдоним ] USING <исходная_таблица> [ [AS] псевдоним ] ON <условие_соединения> [ WHEN MATCHED [ AND <условие> ] THEN { UPDATE SET столбец1 = <выражение1> [, столбец2 = <выражение2>] ... | DELETE} ] [ WHEN NOT MATCHED [ AND <условие> ] THEN INSERT [ ( <список_столбцов> ) ] VALUES ({константа1 | переменная1} [, {константа2 | переменная2}] ... ) ] [RETURNING <список_столбцов> [INTO <список_переменных>]];, где <целевая_таблица>:: = { базовая_таблица | представление}, <исходная_таблица>:: = { базовая_таблица | представление | <производная_таблица> | <хранимая_процедура>}. Запрос MERGE работает следующим образом. Выбирается первая строка из исходной таблицы и проверяется, существует ли такая строка целевой таблицы, что на ней становится истинным <условие_соединения>. Если да – то происходит переход к секции WHEN MATCHED, проверяется дополнительное <условие> при его наличии и производится либо обновление этой строки целевой таблицы согласно запросу UPDATE, либо удаление этой строки (DELETE). Если таких строк целевой таблицы несколько, все они обновляются или удаляются. Если для текущей строки исходной таблицы <условие_соединения> возвращает FALSE для всех строк целевой таблицы, то проверяется дополнительное <условие> при его наличии и в целевую таблицу вставляется строка согласно запросу INSERT, указанному в секции 1 WHEN NOT MATCHED. В одном запросе MERGE могут быть определены секции WHEN MATCHED и WHEN NOT MATCHED одновременно или одно из них (обязательно). Описанная выше процедура повторяется для всех строк исходной таблицы. Платформа Oracle поддерживает запрос MERGE с небольшими отклонениями от схемы синтаксиса ANSI в следующем формате: MERGE INTO <целевая_таблица> [ псевдоним ] USING <исходная_таблица> [ псевдоним ] ON (<условие_соединения>) [ WHEN MATCHED THEN { UPDATE SET столбец1 = { <выражение1> | DEFAULT } [, столбец2 = { <выражение2> | DEFAULT } ] ... [WHERE <условие>] | DELETE [WHERE <условие>] } [...] ] [ WHEN NOT MATCHED THEN INSERT ( <список_столбцов> ) VALUES ({ <выражение1> | DEFAULT } [, { <выражение2> | DEFAULT } ] ... ) ] [WHERE <условие>]; К различиям между стандартом ANSI и его реализацией в Oracle относятся следующие.  Платформа Oracle не позволяет использовать ключевое слово AS для присвоения псевдонима целевой таблице или таблице-источнику.  Условие соединения должно быть обязательно заключено в скобки.  В секции WHEN NOT MATCHED требуется указывать список вставляемых столбцов, хотя в стандарте ANSI это не является обязательным. От синтаксиса Firebird запрос MERGE в Oracle отличается также местом указания дополнительных условий и возможностью задания в секции WHEN MATCHED двух действий – UPDATE и DELETE, выполняющихся в зависимости от этих условий. В MS SQL Server синтаксис запроса MERGE следующий: [ <предложение обобщенного табличного выражения> ] MERGE [ TOP (<выражение>) [ PERCENT ] ] [INTO] <целевая_таблица> [WITH ( <подсказка слияния> )] [[AS] псевдоним ] USING <исходная_таблица> ON <условие_соединения> [ WHEN MATCHED [ AND <условие> ] THEN { UPDATE SET <изменяемое значение> [, <изменяемое значение>]... | DELETE } ] [...] [ WHEN NOT MATCHED [ BY TARGET ] [ AND <условие> ] THEN INSERT [ ( <список_столбцов> ) ] { VALUES ( { DEFAULT | NULL | <выражение> } [, { DEFAULT | NULL | <выражение> }]...) [, ( { DEFAULT | NULL | <выражение> } [, { DEFAULT | NULL | <выражение> }]...)]... | DEFAULT VALUES} [ WHEN NOT MATCHED BY SOURCE [ AND <условие> ] THEN 2 { UPDATE SET <изменяемое значение> [, <изменяемое значение>]... | DELETE } ] [...] [<секция_OUTPUT> ] [OPTION (<подсказка запроса> [, <подсказка запроса>]...) ]; Запрос MERGE в MS SQL Server имеет ряд особенностей. Допускается использование двух секций WHEN MATCHED [37]. Если указаны две секции, первая должна сопровождаться дополнительным условием (AND <условие>). Для любой строки вторая секция WHEN MATCHED применяется только в тех случаях, если не применяется первая. Если имеются две секции WHEN MATCHED, одна должна указывать действие UPDATE, а другая – действие DELETE. Запрос MERGE нельзя использовать для обновления одной строки более одного раза, а также использовать для обновления и удаления одной и той же строки. Может быть указана секция WHEN NOT MATCHED BY SOURCE с действием UPDATE или DELETE. Запрос MERGE может иметь не более двух секций WHEN NOT MATCHED BY SOURCE. Если указаны две секции, то первая должна сопровождаться дополнительным условием. Для любой выбранной строки вторая секция WHEN NOT MATCHED BY SOURCE применяется только в тех случаях, если не применяется первая. Если имеется две секции WHEN NOT MATCHED BY SOURCE, то одна должна указывать действие UPDATE, а другая – действие DELETE. В дополнительном условии можно ссылаться только на столбцы целевой таблицы. Рассмотрим примеры использования запроса MERGE. Допустим, требуется удалить все погашенные ремонтные заявки, поданные абонентами, проживающими на улице с кодом 7. Если у абонента нет зарегистрированных заявок, следует внести информацию о заявке в таблицу Request. Запрос на такое слияние данных в Firebird: MERGE INTO Request R USING (SELECT * FROM Abonent WHERE StreetCD = 7) Ab ON R.AccountCD = Ab.AccountCD WHEN MATCHED AND R.Executed THEN DELETE WHEN NOT MATCHED THEN INSERT (RequestCD, AccountCD, Executed) VALUES (25, Ab.AccountCD, False); В Oracle можно составить аналогичный запрос, расширив его возможностью обновления всех непогашенных ремонтных заявок: MERGE INTO Request R USING (SELECT * FROM Abonent WHERE StreetCD = 7) Ab ON (R.AccountCD = Ab.AccountCD) WHEN MATCHED THEN UPDATE SET R.ExecutorCD = NULL WHERE R.Executed = 0 DELETE WHERE R.Executed = 1 WHEN NOT MATCHED THEN INSERT (RequestCD, AccountCD, Executed) VALUES (25, Ab.AccountCD, '0'); 3 В MS SQL Server первоначальный запрос будет корректен в таком виде: MERGE INTO Request R USING (SELECT * FROM Abonent WHERE StreetCD = 7) Ab ON (R.AccountCD = Ab.AccountCD) WHEN MATCHED AND R.Executed = 0 THEN DELETE WHEN NOT MATCHED THEN INSERT (RequestCD, AccountCD, Executed) VALUES (25, Ab.AccountCD, 0); Фрагмент таблицы Request после выполнения запроса представлен на рис. 5.11.  REQUES ACCOU EXECUT FAILUR INCOMING EXRCUTIO EXECU TCD NTCD ORCD ECD DATE NDATE TED … … … … … … … 10 136159 3 12 01.04.2011 03.04.2011 False … … … … … … … 25 443690 NULL NULL NULL NULL False Рис. 5.11. Обновленные и добавленные строки в таблице Request Производной таблицей, определенной в секции USING, возвращаются 2 строки, содержащие информацию об абонентах с номерами лицевых счетов '136159' и '443690', проживающих на улице с кодом 7. Так как у абонента со счетом '136159' зарегистрирована непогашенная ремонтная заявка с кодом 10, то строка из таблицы Request не удаляется. У абонента со счетом '443690' нет ни одной зарегистрированной заявки, поэтому строка, соответствующая данному абоненту, добавляется в таблицу Request. Следует отметить, что если на улице с кодом 7 проживало бы несколько абонентов, не подававших ремонтные заявки, то запрос не выполнился бы изза повторяющихся вставляемых значений в столбец первичного ключа (RequestCD=25). Для предотвращения такой ситуации при получении очередного уникального значения первичного ключа следует его вычислять или использовать генератор последовательности (п. 6.1.5). Примером такого запроса может быть следующий запрос, который для абонентов с начислениями более 500 увеличивает на 10 % значения соответствующих оплат, а при отсутствии оплат добавляет их с соответствующим значением начисления и датой оплаты, равной текущей: MERGE INTO Paysumma P USING (SELECT * FROM NachislSumma WHERE NachislSum > 500) AS N ON (P.AccountCD = N.AccountCd AND P.ServiceCD=N.ServiceCD AND P.PayMonth=N.NachislMonth AND P.Payyear=N.NachislYear) WHEN MATCHED THEN UPDATE SET PaySum=PaySum*1.1 WHEN NOT MATCHED THEN INSERT VALUES ((SELECT MAX(PayfactCD)+1 FROM PaySumma), N.AccountCD, N.ServiceCD, N.NachislSum, (SELECT CURRENT_DATE FROM RDB$DATABASE), 4 N.NachislMonth, N.NachislYear); В результате выполнения этого запроса в таблицу PaySumma будет добавлена одна новая строка и в 4-х строках увеличатся значения оплат. Реализация данного запроса в Oracle и MS SQL Server отличается только особенностями получения текущей даты, а также отсутствием ключевого слова AS перед псевдонимом таблицы в Oracle. Следует отметить, что если запрос MERGE используется, например, в триггере Firebird совместно с секцией RETURNING, то результаты WHEN MATCHED UPDATE и WHEN NOT MATCHED INSERT доступны в контекстной переменной NEW.столбец, а результаты WHEN MATCHED DELETE – в OLD.столбец. Запрос MERGE является удобным и мощным средством для синхронизации таблицы БД с внешними источниками данных. 5.5. Удаление существующих данных Удаление всех данных или отдельных строк (отдельной строки) из таблицы или представления осуществляется запросом-действием DELETE. Общий вид запроса DELETE простой – указывается, из какой таблицы удаляются данные, и, если требуется, отбираются удаляемые данные: DELETE FROM <таблица> [WHERE <список условий>]; . Существует 2 вида этого запроса: неявный (простой) запрос DELETE и запрос DELETE с подзапросом. Удаление данных с помощью запроса DELETE может быть позиционированным и поисковым (аналогично описанному ранее обновлению данных с помощью запроса UPDATE). В Firebird запрос имеет формат DELETE FROM {базовая_таблица | представление} [ [ AS ] псевдоним] [WHERE { <условие_поиска> | CURRENT OF имя_курсора } ] [PLAN <список_пунктов_плана>] [ORDER BY <элемент_сортировки1> [, <элемент_сортировки 2>]…] [ROWS k [TO r]] [RETURNING <список_столбцов> [INTO <список_переменных>]]; С помощью DELETE можно удалять только отдельные строки, а не индивидуальные значения столбцов, поэтому параметр столбца является недоступным. Платформа Oracle позволяет удалять строки из таблиц, представлений, материализованных представлений, вложенных подзапросов и секционированных представлений и таблиц запросом DELETE в формате [46, 47]: DELETE [FROM] { <целевой_объект> | ONLY (<целевой_объект>) } [псевдоним] [WHERE <условие_поиска> ] [RETURNING <список_столбцов> [INTO <список_переменных>]];, где <целевой_объект> :: = { таблица [[SUB]PARTITION { (имя_раздела) | FOR (ключ_раздела [, ключ_раздела …]) }] | представление 5 | (<запрос_SELECT> [WITH {READ ONLY | CHECK OPTION} [CONSTRAINT имя_ограничения]]) | TABLE (коллекция) [(+)] }. MS SQL Server поддерживает запрос DELETE в следующем виде: [ <предложение обобщенного табличного выражения> ] DELETE [ TOP (<выражение>) [ PERCENT ] ] [FROM] { [<сервер>.][[<база данных>.]<схема>.] { <таблица> | <представление> } | { <функция OPENQUERY> | <функция OPENROWSET> } } [ WITH (<подсказка таблицы> [, <подсказка таблицы>...]) ] [<секция_OUTPUT> ] [FROM (<таблица-источник> [, <таблица-источник>]...) ] [WHERE { <условие_поиска> | CURRENT OF имя_курсора } ] [OPTION (<подсказка запроса> [, <подсказка запроса>]...) ]; Запрос DELETE в MS SQL Server может содержать подзапрос не только в секции WHERE, но также подзапрос для определения обобщенного табличного выражения. Помимо запроса DELETE, Oracle и MS SQL Server поддерживают для полного удаления всех данных таблицы запрос TRUNCATE TABLE, который будет рассмотрен в пункте 5.5.3. 5.5.1. Неявный запрос DELETE Неявный запрос-действие DELETE позволяет удалить содержимое всех строк указанной таблицы или тех ее строк, которые выделяются условием поиска секции WHERE. Условие поиска неявного запроса DELETE не содержит подзапроса. Например, для удаления всего содержимого таблицы Abonent можно выполнить запрос: DELETE FROM Abonent; Обычно требуется удалить только определенные строки из таблицы или представления. Чтобы указать, какие строки нужно удалить, необходимо использовать условие поиска, как это делается для запроса SELECT. Например, чтобы удалить из таблицы Abonent абонента с номером лицевого счета, равным '005488', требуется выполнить запрос: DELETE FROM Abonent WHERE AccountCD = '005488'; Но в данном случае соответствующая строка из таблицы Abonent удалена не будет, так как имеются строки для данного абонента в дочерних таблицах (NachislSumma, PaySumma, Request). Можно использовать DELETE с условием поиска, которое затрагивает несколько строк: DELETE FROM NachislSumma WHERE NachislYear = 2010; Вышеприведенные запросы DELETE корректно работают во всех трех рассматриваемых в учебнике СУБД. 6 Позиционированные удаления данных в Firebird, использующие секцию WHERE CURRENT OF имя_курсора, будут подробно рассмотрены при изучении процедурного языка SQL (гл. 6). В общем случае правильно построить запрос на удаление достаточно сложно. При малейшей неточности запрос DELETE удалит не те данные. Поэтому для предотвращения удаления лишних данных рекомендуется сначала убедиться, что удаляются только требуемые строки путем выборки их запросом SELECT. Это особенно важно при построении запросов DELETE с подзапросом. 5.5.2. Запрос DELETE с подзапросом В секции WHERE запроса-действия DELETE можно использовать вложенные запросы, которые могут быть как независимыми, так и коррелированными. Рассмотрим несколько примеров использования независимого подзапроса в секции WHERE запроса DELETE. Для удаления ремонтных заявок, исполнителем которых является Булгаков Т.И., необходимо выполнить запрос: DELETE FROM Request WHERE ExecutorCD IN (SELECT ExecutorCD FROM Executor WHERE Fio = 'Булгаков Т.И.'); Первые 8 строк таблицы Request после удаления ремонтных заявок представлены на рис. 5.12.  REQUES ACCOU EXECUT FAILUR INCOMING EXRCUTIO EXECU TCD NTCD ORCD ECD DATE NDATE TED 1 005488 1 1 17.12.2011 20.12.2011 True 2 115705 3 1 07.08.2011 12.08.2011 True 3 015527 1 12 28.02.2012 08.03.2012 False 5 080270 4 1 31.12.2011 NULL False 6 080613 1 6 16.06.2011 24.06.2011 True 7 080047 3 2 20.10.2012 24.10.2012 True 10 136159 3 12 01.04.2011 03.04.2011 False 11 136160 1 6 12.01.2013 12.01.2013 True Рис. 5.12. Таблица Request после удаления ремонтных заявок Запрос DELETE удаляет все строки со значением столбца ExecutorCD исполнителя ремонтных заявок с ФИО Булгаков Т.И., возвращаемым подзапросом. Особое место занимают запросы DELETE, где в основном запросе и подзапросе используется одна и та же таблица. В некоторых СУБД не рекомендуется использовать такие запросы. С помощью запроса DELETE FROM PaySumma WHERE AccountCD IN (SELECT AccountCD FROM PaySumma GROUP BY AccountCD HAVING COUNT(PayFactCD) > 6); 7 можно удалить информацию обо всех оплатах тех абонентов, которыми было произведено более 6 платежей. Запрос вида DELETE FROM Request WHERE IncomingDate NOT IN (SELECT MAX(IncomingDate) FROM Request GROUP BY AccountCD); в таблице ремонтных заявок для каждого абонента оставляет только одну, самую позднюю заявку. Таблица Request после выполнения запроса представлена на рис. 5.13.  REQUES ACCOU TCD NTCD 3 015527 5 080270 6 080613 7 080047 9 136169 10 136159 11 136160 14 005488 18 115705 21 443069 EXECUT FAILUR INCOMING EXRCUTIO ORCD ECD DATE NDATE 1 12 28.02.2012 08.03.2012 4 1 31.12.2011 NULL 1 6 16.06.2011 24.06.2011 3 2 20.10.2012 24.10.2012 2 1 06.11.2011 08.11.2011 3 12 01.04.2011 03.04.2011 1 6 12.01.2013 12.01.2013 4 6 04.04.2013 13.04.2013 2 3 28.12.2012 04.01.2013 1 2 13.09.2011 14.09.2011 Рис. 5.13. Таблица Request EXECU TED False False True True True False True True True True Подзапросы в секции WHERE могут иметь несколько уровней вложенности. Они могут также содержать внешние ссылки на целевую таблицу запроса DELETE. При этом единственное ограничение на применение подзапросов заключается в том, что целевую таблицу нельзя указывать в секции FROM подзапроса независимо от уровня вложенности. Это предотвращает ссылки из вложенных запросов на целевую таблицу (часть строк которой может быть удалена), за исключением внешних ссылок на строку, проверяемую в данный момент на соответствие условию поиска запроса DELETE. Рассмотрим использование коррелированного подзапроса в секции WHERE запроса DELETE, т. е. подзапроса, который содержит внешнюю ссылку на текущую строку таблицы, из которой удаляются данные. Например, для удаления всех сведений об оплате услуг абонентами, проживающими на Татарской улице, можно использовать запрос DELETE FROM PaySumma P WHERE EXISTS (SELECT * FROM Abonent A, Street S WHERE S.StreetNM = 'ТАТАРСКАЯ УЛИЦА' AND A.StreetCD = S. StreetCD AND P.AccountCD = A. AccountCD); В этом примере при проверке условия P.AccountCD =A.AccountCD внутренний запрос ссылается на строку таблицы PaySumma, проверяемую в данный момент. Это означает, что подзапрос будет выполняться отдельно для каждой строки таблицы PaySumma. В результате выполнения запроса из таблицы PaySumma будет удалено 24 оплаты абонентами с номерами 8 лицевых счетов '126112', '136160', '136169' и '443069' (именно они проживают на Татарской улице). При реализации запросов DELETE с коррелированными подзапросами в MS SQL Server следует учитывать, что MS SQL Server не допускает определения псевдонима для таблицы, из которой удаляются данные. Таким образом, предыдущий запрос (как и все последующие) должен быть записан без псевдонима: DELETE FROM PaySumma WHERE EXISTS (SELECT * FROM Abonent A, Street S WHERE S.StreetNM = 'ТАТАРСКАЯ УЛИЦА' AND A.StreetCD = S. StreetCD AND PAYSUMMA.AccountCD = A. AccountCD); Для этого примера есть другой способ выполнить те же самые действия. Например, в Firebird: 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, который внутри себя содержит соотнесенный подзапрос. Например, требуется удалить ремонтные заявки тех абонентов, которые оплачивали услуги более 8 раз. Запрос будет выглядеть так: DELETE FROM Request R WHERE R.AccountCD IN (SELECT P1.AccountCD FROM PaySumma P1 WHERE 8 < (SELECT COUNT(*) FROM PaySumma P2 WHERE P2.AccountCD=P1.AccountCD)); Таблица Request после удаления представлена на рис. 5.14.  REQUES ACCOU TCD NTCD 1 005488 3 015527 5 080270 6 080613 7 080047 9 136169 10 136159 11 136160 12 443069 13 005488 14 005488 19 080270 20 080047 21 443069 22 136160 23 136169 EXECUT ORCD 1 1 4 1 3 2 3 1 5 5 4 4 3 1 1 5 FAILUR INCOMING EXRCUTIO ECD DATE NDATE 1 17.12.2011 20.12.2011 12 28.02.2012 08.03.2012 1 31.12.2011 NULL 6 16.06.2011 24.06.2011 2 20.10.2012 24.10.2012 1 06.11.2011 08.11.2011 12 01.04.2011 03.04.2011 6 12.01.2013 12.01.2013 2 08.08.2011 10.08.2011 8 04.09.2010 05.12.2010 6 04.04.2013 13.04.2013 8 17.12.2011 27.12.2011 2 11.10.2011 11.10.2011 2 13.09.2011 14.09.2011 7 18.05.2011 25.05.2011 7 07.05.2011 08.05.2011 EXECU TED True False False True True True False True True True True True True True True True 9 Рис. 5.14. Таблица Request после удаления ремонтных заявок абонентов, оплативших услуги более 8-ми раз Для работы вложенного соотнесенного запроса формируется текущая строка-кандидат (берется первая строка таблицы PaySumma). Подзапрос возвращает количество оплат абонента с номером лицевого счета, содержащегося в строке-кандидате (условие P2.AccountCD=P1.AccountCD). Запрос DELETE удаляет все строки со значением столбца AccountCD, входящим во множество значений, формируемых вложенным соотнесенным запросом. В итоге будут удалены все строки таблицы Request, в которых значение столбца AccountCD равно 115705 (количество произведенных оплат этим абонентом равно 9). Существует и более простое решение данной задачи: DELETE FROM Request A WHERE (SELECT COUNT(*) FROM PaySumma B WHERE B.AccountCD=A.AccountCD) > 8; 5.5.3. Удаление всех строк таблицы Удалить все строки, не затронув структуру таблицы, в Oracle и MS SQL Server можно двумя способами: - если не использовать секцию WHERE в запросе DELETE; - использовать запрос TRUNCATE TABLE. Удаление строк из большой таблицы, содержащей тысячи строк, является «дорогой» операцией, поскольку СУБД поддерживает копии всех удалённых транзакций на случай, если потребуется отменить внесённые изменения. Если таблица большая, этот процесс может оказаться длительным и потребовать много места, пока операция удаления не завершится с помощью COMMIT. Иногда нужно удалить все строки из таблицы, причем точно известно, что восстанавливать их не потребуется. В таких случаях вместо запроса DELETE можно использовать запрос TRUNCATE TABLE. При выполнении такого запроса СУБД удаляет все данные из таблицы, в том числе не сохраняет информацию, необходимую для отмены этих данных. Когда СУБД усекает (truncate) таблицу, строки удаляются безвозвратно, однако структура таблицы остаётся. Общий вид запроса-действия TRUNCATE TABLE в Oracle: TRUNCATE TABLE <таблица>; Формат запроса в MS SQL Server: TRUNCATE TABLE [[<база данных>.]<схема>.]<таблица>; Запрос TRUNCATE TABLE работает быстрее, чем запрос удаления DELETE, и не журналируется. Также он не активирует триггеры, срабатывающие при удалении. При этом следует учесть, что если на таблицу наложены условия, задающие внешние ключи (без использования опции ON DELETE CASCADE), то нельзя усечь таблицу. Удаление не пройдет, даже если подчиненная таблица не имеет вообще никаких строк. Вначале следует отключить условие, используя запрос ALTER TABLE. Например, можно удалить все строки таблицы Request так: 10 TRUNCATE TABLE Request; Однако, например, таблицу Abonent усечь не получится из-за нарушений ссылочной целостности. 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;  /* Представление Summ_Abonent только для чтения из-за наличия вложенного запроса в запросе SELECT */ CREATE VIEW Summ_Abonent AS SELECT * FROM Abonent WHERE AccountCD IN 11 (SELECT AccountCD FROM NachislSumma WHERE NachislSum = 46); Пояснения, стоящие перед запросами CREATE VIEW, представляют собой комментарии SQL и будут подробно рассмотрены далее при изучении процедурного языка (см. разд. 6.1.1). Рассмотрим примеры работы с обновляемыми представлениями, которые наглядно демонстрируют, как изменяются данные представления при манипулировании данными таблицы, на основе которой оно создано, и как изменяются данные таблицы при манипулировании данными представления. Результаты выполнения будут приведены для СУБД Firebird, при этом все рассматриваемые запросы одинаково корректно работают и в Oracle, и в MS SQL Server, отличаясь в некоторых случаях только последовательностью вывода строк результата. Пусть требуется создать смешанное представление, которое содержит снимок таблицы Abonent. Вертикальное подмножество должно включать в себя столбцы с ФИО и номерами лицевых счетов абонентов, а горизонтальное подмножество – строки со значением номера лицевого счета абонента, большего или равного 200000. Для этого можно использовать запрос CREATE VIEW Abonent_View (AccountCD, Fio) AS SELECT AccountCD, Fio FROM Abonent WHERE AccountCD >= '200000' WITH CHECK OPTION; Результат выполнения запроса (рис. 5.15) SELECT * FROM Abonent_View; Это представление является обновляемым:  ACCOUNTtCD FIO 443690 Тулупова М.И. 443069 Стародубцев Е.В. Рис. 5.15. Данные представления Abonent_View  если является подмножеством одной таблицы (нет соединения таблиц);  в списке возвращаемых элементов нет вычисляемых выражений и агрегатных функций;  используется простое условие поиска (без подзапросов);  запрос SELECT, на котором базируется представление, не содержит DISTINCT, секций GROUP BY и HAVING. Следовательно, к этому представлению могут быть применены запросы INSERT, UPDATE и DELETE. Продемонстрируем это с помощью следующих примеров. Пусть требуется вставить строку в набор данных, возвращаемый представлением Abonent_View. Это можно выполнить с помощью запроса INSERT INTO Abonent_View (AccountCD, Fio) VALUES ('999999', 'Васильев С.В.'); 12 Результат выборки всех данных из представления Abonent_View представлен на рис. 5.16. Данные, содержащиеся в таблице Abonent после применения операции вставки в представление Abonent_View, приведены на рис. 5.17.  ACCOUNTtCD FIO 443690 Тулупова М.И. 443069 Стародубцев Е.В. 999999 Васильев С.В. Рис. 5.16. Данные представления Abonent_View после вставки  ACCOUNTCD STREETC HOUSENO FLATNO FIO D 005488 3 4 1 Аксенов С.А. 115705 3 1 82 Мищенко Е.В. 015527 3 1 65 Конюхов В.С. 443690 7 5 1 Тулупова М.И. 136159 7 39 1 Свирина З.А. 443069 4 51 55 Стародубцев Е.В. 136160 4 9 15 Шмаков С.В. 126112 4 7 11 Маркова В.П. 136169 4 7 13 Денисова Е.К. 080613 8 35 11 Лукашина Р.М. 080047 8 39 36 Шубина Т. П. 080270 6 35 6 Тимошкина Н.Г. 999999 NULL NULL NULL Васильев С.В. Рис. 5.17. Данные таблицы Abonent PHONE 556893 769975 761699 214833 NULL 683014 NULL 683301 680305 254417 257842 321002 NULL Следует отметить важное обстоятельство – с помощью созданного представления возможна вставка значений в столбцы AccountCD и Fio и предотвращается вставка определенных значений в остальные столбцы. Открывая доступ к этому представлению, разрешается пользователям и 13 программам вставлять данные только в эти два столбца. Пользователи получают возможность создавать новые строки в таблице Abonent, вставляя данные в представление Abonent_View, но они смогут размещать значения только в двух столбцах, которые заданы в определении представления. Проверим работу опции WITH CHECK OPTION, которая должна ограничивать операции DML, применяемые к представлению, таким образом, чтобы они не нарушали условия поиска запроса SELECT, на котором базируется представление. Начнем с INSERT. Для этого выполним запрос INSERT INTO Abonent_View (AccountCD, Fio) VALUES ('100000', 'Скляров А.А.'); Этот запрос на вставку не будет выполнен, так как содержит вставку значений, нарушающих условие отбора запроса SELECT, с помощью которого представление было создано. Будет выдано сообщение об ошибке, в Firebird такого содержания: «Operation violates CHECK constraint on view or table ABONENT_VIEW» (нарушение ограничения CHECK представления Abonent_View). Если бы при определении представления Abonent_View не было опции WITH CHECK OPTION, то предыдущий запрос не вернул бы ошибку. Строка с номером лицевого счета '100000' была бы вставлена в таблицу Abonent, на которой базируется представление, но в самом представлении ее не было бы видно. Чтобы избежать таких противоречий, нужно включать в определение представления опцию WITH CHECK OPTION. Тогда до реального выполнения операций модификации или вставки строк через представление для каждой строки будет проверяться, соответствует ли она условиям представления. Выполним запрос на удаление: DELETE FROM Abonent_View WHERE AccountCD = '999999'; Данные, содержащиеся в таблице Abonent после удаления строки через представление Abonent_View, приведены на рис. 5.18.  14 ACCOUNTC D 005488 115705 015527 443690 136159 443069 136160 126112 136169 080613 080047 080270 STREETC D 3 HOUSEN O 4 FLATN O 1 FIO Аксенов С.А. 3 1 82 Мищенко Е.В. 3 1 65 Конюхов В.С. 7 5 1 Тулупова М.И. 7 39 1 Свирина З.А. 4 51 55 Стародубце в Е.В. 4 9 15 Шмаков С.В. 4 7 11 Маркова В.П. 4 7 13 Денисова Е.К. 8 35 11 Лукашина Р.М. 8 39 36 Шубина Т. П. 6 35 6 Тимошкина Н.Г. Рис. 5.18. Данные таблицы Abonent после удаления PHON E 556893 769975 761699 214833 NULL 683014 NULL 683301 680305 254417 257842 321002 Рассмотрим еще один пример работы с обновляемым представлением. Пусть необходимо создать представление, которое показывало бы всю информацию об абонентах, кроме информации о номере телефона. При этом должны быть исключены из рассмотрения абоненты с фамилиями, начинающимися с буквы М. Запрос на создание такого представления будет выглядеть так: CREATE VIEW Abonent_Information (Code, Street, House, Flat, Name) AS SELECT AccountCD, StreetCD, HouseNo, FlatNo, Fio FROM ABONENT WHERE Fio NOT LIKE 'М%' WITH CHECK OPTION; Это представление является обновляемым, потому что запрос основан на одной таблице, каждый столбец представления ссылается на простой столбец и секция WHERE не содержит вложенного запроса. Данные, которые пользователь увидит после выполнения запроса, представлены на рис. 5.19. SELECT * FROM Abonent_Information;,  15 CODE STREE T 3 HOUSE FLAT NAME 00548 4 1 Аксенов С.А. 8 01552 3 1 65 Конюхов В.С. 7 44369 7 5 1 Тулупова М.И. 13615 7 39 1 Свирина З.А. 9 44306 4 51 55 Стародубцев Е.В. 9 13616 4 9 15 Шмаков С.В. 13616 4 7 13 Денисова Е.К. 9 08061 8 35 11 Лукашина Р.М. 3 08004 8 39 36 Шубина Т.П. 7 08027 6 35 6 Тимошкина Н.Г. Рис. 5.19. Данные представления Abonent_Information Допустим, что был выполнен запрос на изменение данных в таблице Abonent: UPDATE Abonent SET Fio = 'Булгакова Т.П.' WHERE AccountCD = '080047'; Пользователем также был выполнен запрос на добавление данных в представление Abonent_Information: INSERT INTO Abonent_Information (Code, Street, House, Flat, Name) VALUES ('123456', 3, 12, 34, 'Тарасов А.В.'); Так как столбец Phone может содержать NULL, то вставка строки в представление проходит успешно (рис. 5.20). SELECT * FROM Abonent_Information;  16 CODE STREE T 3 HOUSE FLAT NAME 00548 4 1 Аксенов С.А. 8 01552 3 1 65 Конюхов В.С. 7 44369 7 5 1 Тулупова М.И. 13615 7 39 1 Свирина З.А. 9 44306 4 51 55 Стародубцев Е.В. 9 13616 4 9 15 Шмаков С.В. 13616 4 7 13 Денисова Е.К. 9 08061 8 35 11 Лукашина Р.М. 3 08004 8 39 36 Булгакова Т.П. 7 08027 6 35 6 Тимошкина Н.Г. 12345 3 12 34 Тарасов А.В. 6 Рис. 5.20. Данные представления Abonent_Information после вставки строки и обновления таблицы Abonent Пользователь может выполнять запросы на обновление и удаление данных в представлении. Допустим, были выполнены последовательно 2 запроса: UPDATE Abonent_Information SET Name = 'Симкина З.А.' WHERE Code = '136159'; DELETE FROM Abonent_Information WHERE Code = '123456'; Данные, получаемые в результате выполнения запроса SELECT * FROM Abonent_Information;, представлены на рис. 5.21.  CODE 00548 8 01552 7 44369 13615 STREE T 3 HOUSE FLAT NAME 4 1 Аксенов С.А. 3 1 65 Конюхов В.С. 7 5 1 Тулупова М.И. 7 39 1 Симкина З.А. 17 9 44306 4 51 55 Стародубцев Е.В. 9 13616 4 9 15 Шмаков С.В. 13616 4 7 13 Денисова Е.К. 9 08061 8 35 11 Лукашина Р.М. 3 08004 8 39 36 Булгакова Т.П. 7 08027 6 35 6 Тимошкина Н.Г. Рис. 5.21. Данные представления Abonent_Information после обновления и удаления Если пользователь попытается вставить или обновить значение столбца Name для абонента с номером лицевого счета '005488', установив ФИО абонента, например, как Морозов А.Н., с помощью запроса: UPDATE Abonent_Information SET Name = 'Морозов А.Н.' WHERE Code = '005488';, то подобный запрос потерпит неудачу из-за того, что при создании представления была использована опция WITH CHECK OPTION. Будет выдано сообщение об ошибке: «Operation violates CHECK constraint on view or table Abonent_Information» (нарушение ограничения CHECK представления Abonent_Information). Если пользователь выполнит запрос на удаление: DELETE FROM Abonent_Information WHERE Name LIKE 'М%';, то ошибки не возникнет, но в самой таблице Abonent никаких изменений не произойдет (в данном случае это не является следствием использования опции WITH CHECK OPTION, а есть следствие применения в запросе представления условия поиска WHERE Fio NOT LIKE 'М%'). Чтобы пояснить результаты действия опции WITH CHECK OPTION, рассмотрим еще один пример. Допустим, что в БД созданы два обновляемых представления: Nach_Information_20, показывающее информацию о начислениях со значениями, меньшими 20 (идентификатор факта начисления, номер лицевого счета абонента и значение начисления), и Nach_Information_15, отображающее данные предыдущего представления со значениями более 15. Запросы на создание этих двух представлений: CREATE VIEW Nach_Information_20 (Fact, Code, Summa) AS SELECT NachislFactCD, AccountCD, NachislSum FROM NachislSumma WHERE NachislSum<20 WITH CHECK OPTION;  CREATE VIEW Nach_Information_15 AS SELECT Fact, Code, Summa 18 FROM Nach_Information_20 WHERE Summa >15 WITH CHECK OPTION;  Данные, получаемые после выполнения запроса SELECT * FROM Nach_Information_20;, FACT CODE SUMMA 6 136160 18,30 представлены на рис. 5.22. 21 080047 19,56 22 080613 10,60 26 080613 12,60 29 136159 8,30 32 443690 17,80 34 126112 15,30 36 080613 12,60 40 015527 18,32 48 136159 8,30 50 136160 18,30 Рис. 5.22. Данные представления Nach_Information_20 Данные, получаемые после FACT CODE выполнения запроса 6 136160 SELECT * FROM Nach_Information_15;, представлены на рис. 5.23. SUMMA 18,30 21 080047 19,56 32 443690 17,80 34 126112 15,30 40 015527 18,32 50 136160 18,30 Рис. 5.23. Данные представления 19 Nach_Information_15 В каждом из представлений Nach_Information_20 и Nach_Information_15 может отсутствовать или присутствовать опция WITH CHECK OPTION. Рассмотрим, что будет происходить в каждом из возможных случаев при выполнении следующих двух запросов на модификацию строк (будем называть эти запросы соответственно U1 и U2): UPDATE Nach_Information_15 SET Summa = Summa + 5; UPDATE Nach_Information_15 SET Summa = Summa – 5; Случай 1. Ни в одном из представлений не содержится опция WITH CHECK OPTION. Допустим, был выполнен запрос U1 на увеличение начисленных сумм. После выполнения запроса U1 представление Nach_Information_15 оказывается пустым. В таблице NachislSumma в тех строках, которые попадали в представление Nach_Information_15 (см. рис. 5.23), будут увеличены на 5 значения начислений (NachislSum). В результате значения в этих строках превысят 20, и строки, перестав удовлетворять условию представления Nach_ Information_20, исчезнут из него. Данные представления Nach_Information_20 после выполнения запроса U1 показаны на рис. 5.24. FACT CODE SUMMA 22 08061 10,60 3 26 08061 12,60 3 29 13615 8,30 9 36 08061 12,60 3 48 13615 8,30 9 Рис. 5.24. Данные представления Nach_Information_20 после выполнения запроса U1 Этот результат может быть неожиданным для пользователей БД, которым известно, что условие представления Nach_Information_15 имеет вид Summa>15 и соблюдение этого условия должно сохраняться при увеличении размера начисления. В то же время в таблице NachislSumma обновляются значения столбца NachislSum для 6 строк (с идентификаторами факта начисления 6, 21, 32, 34, 40, 50). 20 После выполнения запроса U2 представление Nach_Information_15 также оказывается пустым. Данные представления Nach_Information_20 после выполнения запроса U2 показаны на рис. 5.25. FACT CODE SUMMA 6 13616 13,30 21 08004 14,56 7 22 08061 10,60 3 26 08061 12,60 3 29 13615 8,30 9 32 44369 12,80 34 12611 10,30 2 36 08061 12,60 3 40 01552 13,32 7 48 13615 8,30 9 50 13616 13,30 Рис. 5.25. Данные представления Nach_Information_20 после выполнения запроса U2 Возможно, результат будет достаточно естественным для пользователей представления Nach_Information_15, которым известно условие представления, но те, кто работает с представлением Nach_Information_20, обнаружат в теле результирующей таблицы 6 строк с измененными значениями в столбце Summa (с идентификаторами факта начисления 6, 21, 32, 34, 40, 50). В таблице NachSumma также будут изменены 6 строк. Случай 2. В определении представления Nach_Information_20 содержится опция WITH CHECK OPTION, а в определении Nach_Information_15 опция WITH CHECK OPTION отсутствует. В этом случае запрос U1 будет отвергнут системой (поскольку его выполнение нарушает условие представления Nach_Information_20). Будет выдано следующее сообщение: «Operation violates CHECK constraint on view or table NACH_INFORMATION_20» (нарушение ограничения CHECK представления). 21 Но заметим, что такое поведение системы будет совершенно неожиданным и непонятным для тех пользователей БД, которым известно только определение представления Nach_Information_15, поскольку запрос U1 явно не может нарушить видимое ими ограничение. С другой стороны, запрос U2 будет выполнен и по-прежнему приведет к опустошению представления Nach_Information_15. Те, кто работает с представлением Nach_Information_20, обнаружат в теле результирующей таблицы 6 строк с измененными значениями столбца NachSum, как и в предыдущем случае (см. рис. 5.25). Случай 3. В определении представления Nach_Information_20 опция WITH CHECK OPTION отсутствует, а в определении Nach_Information_15 содержится опция WITH CHECK OPTION. Запрос U1 (увеличение размера начисления) будет успешно выполнен, поскольку он не противоречит локальным ограничениям представления Nach_Information_15. После выполнения запроса U1 представление Nach_Information_15 оказывается пустым. Его строки перестают удовлетворять условию представления Nach_Information_20 и исчезают из него. Данные представления Nach_Information_20 будут совпадать с данными, показанными на рис. 5.24 (случай 1). В таблице NachSumma также будут изменены 6 строк. Запрос U2 не будет работать (его выполнение не будет допущено условием проверки представления Nach_Information_15). Случай 4. В определении представлений Nach_Information_20 и Nach_Information_15 содержится опция WITH CHECK OPTION. В этом случае запрос U1 не будет выполнен, так как его выполнение нарушает условие проверки представления Nach_Information_20, а запрос U2 не будет выполнен, так как его выполнение нарушает условие проверки представления Nach_Information_15. Только в этом случае операции обновления будут выполняться корректно. Очевидный вывод из приведенного анализа заключается в том, что единственным способом обеспечить корректность выполнения операций обновления через представления (допускающие операции обновления) является включение в определение каждого имеющегося в БД представления опции WITH CHECK OPTION. 22