SQLГлава 21. Изменение данных через представления *В этой главе речь пойдет об использовании операторов обновления INSERT, UPDATE и DELETE с представлениями. Применение этих операторов к представлениям является косвенным способом их применения к основным таблицам, на которые ссылаются запросы в представлениях. Однако не все представления можно обновлять. Мы рассмотрим правила, определяющие возможность обновления, и изучим их следствия. Вы также научитесь использовать предложение WITH CHECK OPTION для контроля значений, вводимых в таблицу через представление. Обновление представленийПри обновлении представлений изменения передаются в базовую таблицу, на которой основано представление. Представления могут быть основаны на других представлениях, но в конечном итоге будет достигнуто некоторое множество базовых таблиц, в которых и выполняется реальное изменение данных. Однако здесь не все однозначно. Представление состоит из результатов запроса, и при обновлении представления вы обновляете эти результаты. Но обновление не затрагивает запрос или его результаты непосредственно; оно влияет на результаты запроса через изменение данных в таблицах, на которых основано представление, что может вызвать затруднения. Следующий оператор создает представление, показанное на рис. 21.1: CREATE VIEW Citymatch (custcity, salescity) AS SELECT DISTINCT a.city, b.city FROM Customers a, Salespeople b WHERE a.snum = b.snum; Это представление показывает все ссответствия между городами покупателей (custcity) и продавцов (salescity), когда по меньшей мере один покупатель в custcity обслуживается продавцом из salescity. =============== SQL Execution Log ============== | | | SELECT * | | FROM Citymatch; | | =============================================== | | custcity salescity | | --------- --------- | | Berlin San Jose | | London London | | Rome London | | Rome New York | | San Jose Barselona | | San Jose San Jose | | | ================================================= Рисунок 21.1 Представление совпадения по городам Например, одна из строк этой таблицы - London London - показывает, что как минимум один покупатель в Лондоне обслуживается продавцом из Лондона. Эта строка может быть выведена для пары Hoffman - Peel, поскольку и тот, и другой находятся в Лондоне. Однако тот же результат даст совпадение snum покупателя Clemens, тоже находящегося в Лондоне, и его продавца, которым тоже оказался Peel. Поскольку мы выбирали различающиеся комбинации городов, была выведена только одна составная строка с этим значением. Но каким совпадающим значениям из основных таблиц она соответствует? Даже если не использовать DISTINCT при выборе комбинации городов, вопрос остается открытым, поскольку в представлении окажутся две строки с идентичными значениями столбцов. Эти строки будут неразличимы, поэтому вы по-прежнему не сможете сказать, от каких значений основных таблиц они произошли. (Имейте в виду, что запросы без ORDER BY выдают результаты в произвольном порядке. Это справедливо и для запросов внутри представлений, которые, если строго следовать стандарту, не могут содержать ORDER BY. Следовательно, вы не можете различать строки по их расположению в результатах запроса.) В итоге у нас опять окажутся выходные строки, которые не могут быть однозначно связаны с определенными строками основных таблиц. Теперь представим, что нужно удалить строку London London из представления. Какой покупатель должен быть удален из таблицы Customers - Hoffman, Clemens или оба? Должна ли СУБД также удалить продавца Peel из таблицы Salespeople? Однозначного ответа нет, поэтому в представлениях такого рода удаления недопустимы. Представление Citymatch является примером представления только для чтения (read-only view), данные которого можно просматривать, но не изменять. Для решения проблемы можно включить в представление столбцы snum и cnum, позволяющие определить, какое совпадение представлено. В данном случае это будет лучшим выходом, но не забывайте, что наша база данных искусственно упрощена. В реальной жизни подобные ситуации будут возникать, хотя можно ограничить их число, разработав схему с высокой степенью нормализации. Некоторые очень полезные представления просто не могут быть обновлены. Определение обновляемости представленияЕсли можно применить к представлению оператор обновления, то говорят, что оно является обновляемым (updatable), в противном случае - только для чтения. Следуя этой терминологии, мы будем подразумевать под "обновлением представления" применение к нему одного из трех операторов обновления DML (INSERT, UPDATE и DELETE). Как определить, является ли представление обновляемым? В теории баз данных этот вопрос еще дискутируется. Основной принцип заключается в том, что обновляемое представление - это представление, над которым может быть выполнена операция обновления, затрагивающая за один раз только одну строку основной таблицы и не влияющая на остальные строки этой и любых других таблиц. Однако практическое применение этого принципа может представлять трудности. Некоторые представления, которые теоретически являются обновляемыми, в действительности нельзя обновить (по крайней мере в рамках стандарта ISO SQL). Возможность обновления представления определяется основным запросом. Этот запрос должен удовлетворять следующим требованиям:
Обновляемые представления и представления только для чтенияОдним из следствий этих ограничений является то, что обновляемые представления по сути подобны окнам. Они показывают содержимое (не обязательно все) основных таблиц. Они могут быть ограничены определенными строками (с помощью предикатов) или столбцами с определенным именем (путем исключения остальных столбцов), но представляют значения непосредственно и не выполняют над ними никаких действий, в том числе с использованием агрегатных функций и выражений. Они также не сравнивают строки таблицы друг с другом (как это делается в соединениях и подзапросах, а также при использовании DISTINCT). Между обновляемыми представлениями и представлениями толко для чтения есть существенные различия. Зачастую эти представления служат для разных целей. Обновляемые представления обычно испоьзуют аналогично базовым таблицам. Фактически пользователи даже могут не знать, является ли объект запроса базовой таблицей или представлением, - для них это может не иметь никакого значения. Такие представления являются превосходным средством защиты данных, поскольку они позволяют скрыть конфиденциальные или избыточные для данного пользователя части таблицы. (В Главе 22 показано, как разрешить пользователям доступ к представлениям, но не основным таблицам.) Представления только для чтения позволяют переформатировать и обрабатывать данные. Они предоставляют вам библиотеку сложных запросов, которые можно выполнять многократно, поддерживая актуальность производной информации. Кроме того, хранение результатов таких запросов в таблицах, которые затем можно использовать в других запросах (например, в соединениях) имеет преимущество над простым выполнением запросов. Представления только для чтения могут применяться и для защиты данных. Например, можно разрешить некоторым пользователям просмотр агрегированных данных, таких, как средние комиссионные продавца, но закрыть доступ к отдельным значениям комиссионных. Определение обновляемых представленийРассмотрим некоторые примеры обновляемых представлений и представлений только для чтения. CREATE VIEW Dateorders (odate, ocount) AS SELECT odate, COUNT (*) FROM Orders GROUP BY odate; Это представление допускает только чтение из-за наличия агрегатной функции и GROUP BY. CREATE VIEW Londoncust AS SELECT * FROM Customers WHERE city = 'London'; Это представление является обновляемым. CREATE VIEW SJsales (name, number, percentage) AS SELECT sname, snum, comm * 100 FROM Salespeople WHERE city = 'SanJose';
Это представление допускает только чтение из-за выражения CREATE VIEW Salesonthird AS SELECT * FROM Salespeople WHERE snum IN (SELECT snum FROM Orders WHERE odate = '10/03/1990'); С точки зрения SQL92 это представление является обновляемым. В более старых стандартах SQL к нему можно было обращаться только для чтения из-за наличия подзапроса. CREATE VIEW Someorders AS SELECT snum, onum, cnum FROM Orders WHERE odate IN ('10/03/1990', '10/05/1990'); Это представление является обновляемым. CREATE VIEW SanJosePeople AS SELECT snum, sname, 'salesperson' FROM Salespeople WHERE city = 'San Jose' UNION SELECT cnum, cname, ' customer' FROM Customers WHERE city = 'San Jose'; Это представление нельзя обновлять из-за предложения UNION. Поскольку здесь использована форма UNION, а не UNION ALL, все дублирующиеся строки по умолчанию исключаются, и в этом отношении UNION следует рассматривать как DISTINCT. Для UNION ALL стандарт SQL92 по-прежнему не допускает обновлений, хотя некоторые СУБД могут разрешать эту операцию как расширение стандарта. Проверка значений, помещаемых в представленияПри обновлении представлений возникает еще одна проблема: введенные вами значения могут быть "поглощены" основной таблицей. Рассмотрим следующее представление: CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300; Это представление является обновляемым. Оно просто ограничивает доступ к таблице определенными строками и столбцами. Предположим, что мы вставили следующую строку: INSERT INTO Highratings VALUES (2018, 200); Применение оператора INSERT к этому представлению вполне законно. Строка будет действительно вставлена в таблицу Customers. Но оказавшись там, она исчезнет из представления, поскольку значение рейтинга в ней не равно 300. Такое исчезновение обычно представляет собой проблему. Значение 200 могло быть простой опечаткой, но строка уже находится в таблице Customers, и вы даже не можете ее увидеть через представление. Пользователь может не заметить, что он ввел "невидимую" строку, и в любом случае он не сможет удалить ее через представление. Модификации такого сорта можно предотвратить, включив в определение представления WITH CHECK OPTION. Если бы мы определили Highratings следующим образом: CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300 WITH CHECK OPTION; то СУБД не допустила бы приведенную выше вставку. WITH CHECK OPTION действует по принципу "все или ничего" (all-or-nothing). Вы помещаете эту опцию в определение представления, а не оператор обновления, поэтому будут проверяться либо все операторы обновления, применимые к представлению, либо никакие. В общем случае CHECK OPTION следует использовать для всех обновляемых представлений, если только нет каких-либо специфических причин для помещения в таблицу значений, которые не будут отображаться в представлении. В представлениях только для чтения эта опция не нужна. Предикаты и исключенные столбцыПохожая проблема возникает при вставке строк в представление с предикатом, основанном на одном или нескольких исключенных столбцах. Например, кажется, что представление Londonstaff лучше определить таким образом: CREATE VIEW Londonstaff AS SELECT snum, sname, comm FROM Salespeople WHERE city = 'London'; Зачем включать столбец city, если все значения city будут одинаковыми, а название города содержится в имени представления? Посмотрим, что произойдет при попытке вставить строку. Поскольку мы не можем указать city, СУБД введет в этот столбец значение по умолчанию, возможно, NULL. (NULL будет использован в том случае, если мы явно не указали другое значение по умолчанию, подробности см. в Главе 18). Поскольку столбец city не равен 'London', вставленная строка будет исключена из представления. Итак, какие бы строки вы не вводили через представление Londonstaff в таблицу Salespeople, они будут отсутствовать в самом представлении (если только вы явно не указали 'London' в качестве значения по умолчанию). Пользователю не удастся ввести строки в это представление, хотя он сможет вводить строки в основную таблицу, возможно, сам того не подозревая. Даже если добавить WITH CHECK OPTION к определению представления CREATE VIEW Londonstate AS SELECT snum, sname, comm FROM Salespeople WHERE city = 'London' WITH CHECK OPTION; проблема не обязательно будет решена. Мы получим представление, к которому можно применять операторы UPDATE или DELETE, но не INSERT. В некоторых случаях этого достаточно; возможно, пользователям, работающим с этим представлением, не нужно добавлять строки. Но перед созданием подобного представления вы должны убедиться, что имеете дело именно с такой ситуацией. Хотя столбцы, использованные в предикате, не всегда содержат полезную информацию, обычно их стоит включать в представление. Если вы не хотите видеть их среди результатов, это всегда можно обеспечить в запросе к представлению, а не в самом представлении. Иными словами, представление Londonstaff можно было определить так: CREATE VIEW Londonstaff AS SELECT * FROM Salespeople WHERE city = 'London' WITH CHECK OPTION; Этот оператор заполнит представление идентичными значениями city, которые можно просто пропустить в последующем запросе: SELECT snum, sname, comm FROM Londonstaff; Можно определить еще одно представление на основе Londonstaff, предназначенное только для просмотра, а не для изменения данных, хотя в принципе оно также является обновляемым: CREATE VIEW Londonstafflookie AS SELECT snum, sname, comm FROM Londonstaff; В этом случае не нужно добавлять CHECK OPTION. Основной запрос этого представления не содержит предиката. Идея состоит в том, чтобы использовать Londonstafflookie для отчетов, которые не должны быть загромождены строками 'London'. Само же представление Londonstaff можно использовать для изменения данных о продавцах в Лондоне. Данные об остальных продавцах вы должны по-прежнему обновлять путем прямого обращения к таблике Salepeople или через другой набор представлений. Проверка представлений, основанных на других представленияхПри использовании CHECK OPTION возникает сложный вопрос - должны ли эти ограничения каскадироваться? Должно ли предложение CHECK OPTION применяться только к предикату того представления, в котором оно непосредственно содержится, или к предикатам всех представлений, содержащихся в данном представлении? В качестве примера рассмотрим следующее представление: CREATE VIEW Highratings AS SELECT cnum, rating FROM Customers WHERE rating = 300 WITH CHECK OPTION; Предположим, что мы создали другое представление, основанное на первом: CREATE VIEW Myhighratings AS SELECT * FROM Highratings; Можем ли мы теперь использовать в операторе UPDATE рейтинг, отличный от 300? UPDATE Myhighratings SET rating = 200 WHERE cnum = 2004; Ведь это не нарушает предикат в представлении Myhighratings. Ограничение на рейтинг расположено глубже, в представлении Highratings. Ранние стандарты SQL не касались это вопроса. Разумнее всего по умолчанию запретить такие обновления, что и сделано в большинстве СУБД. Вы можете выполнить простой тест, создав приведенные выше представления и попробовав их обновить. Согласно SQL92 CHECK OPTION может иметь варианты CASCADED или LOCAL. CASCADED означает, что будут проверяться предикаты всех основных представлений, и при их нарушении обновления будут отклонены. LOCAL означает, что проверяется только предикат в текущем представлении. Синтаксис обеих разновидностей очень прост: WITH CASCADED CHECK OPTION WITH LOCAL CHECK OPTION РезюмеВы теперь полностью овладели знаниями о представлениях. Кроме правил, определяющих, является ли данное представление модифицируемыми в SQL, вы познакомились с основными понятиями, на которых эти правила базируются - т.е., что модификации в представлениях допустимы только тогда, когда SQL может недвусмысленно определить, какие значения базовой таблицы можно изменять. Это означает, что команда модификации при выполнении не должна требовать ни изменений для многих строк сразу, ни сравнений между многочисленными строками либо базовой таблицы, либо вывода запроса. Поскольку объединения включают в себя сравнение строк, они также запрещены. Вы также поняли различие между некоторыми способами, которые используют модифицируемые представления и представления только_чтение. Вы научились воспринимать модифицируемые представления как окна, отображающие данные одиночной таблицы, но не обязательно исключающие или реорганизующие столбцы посредством выбора только определенных строк отвечающих условию предиката. Представления только_чтение, с другой стороны, могут содержать более допустимые запросы SQL; они могут, следовательно, стать способом хранения запросов, которые вам нужно часто выполнять в неизменной форме. Кроме того, наличие запроса, чей вывод обрабатывается как объект данных, дает вам возможность иметь ясность и удобство при создании запросов в выводе запросов. Вы можете теперь в представлении предотвращать создание командами модификации строк в базовой таблице, которые не представлены в самом представлении, с помощью предложения WITH CHECK OPTION в определения представления. Вы можете также использовать WITH CHECK OPTION как один из способов ограничения в базовой таблице. В автономных запросах, вы обычно используете один или более столбцов в предикате, не представленные среди выбранных для вывода, что не вызывает никаких проблем. Но если эти запросы используются в модифицируемых представлениях, появляются проблемы, так как эти запросы производят представления, которые не могут иметь вставляемых в них строк. Вы видели некоторые подходы к этим проблемам. В Главах 20 и 21, мы говорили, что представления имеют прикладные программы защиты. Вы можете дать пользователям возможность обращаться к представлениям, не разрешая в то же время обращаться к таблицам, в которых эти представления непосредственно находятся. Глава 22 будет исследовать вопросы доступа к объектам данных в SQL. Работа с SQL1. Какое из этих представлений - модифицируемое? #1 CREATE VIEW Dailyorders AS SELECT DISTINCT cnum, snum, onum, odate FROM Orders; #2 CREATE VIEW Custotals AS SELECT cname, SUM (amt) FROM Orders, Customers WHERE Orders.cnum = customer.cnum GROUP BY cname; #3 CREATE VIEW Thirdorders AS SELECT * FROM Dailyorders WHERE odate = 10/03/1990; #4 CREATE VIEW Nullcities AS SELECT snum, sname, city FROM Salespeople WHERE city IS NULL OR sname BETWEEN 'A' AND 'MZ'; 2. Создайте представление таблицы Продавцов с именем Commissions (Комиссионные). Это представление должно включать только поля comm и snum. С помощью этого представления можно будет вводить или изменять комиссионные, но только для значений между .10 и .20. 3. Некоторые SQL-реализации имеют встроенную константу, представляющую текущую дату, иногда называемую " CURDATE ". Слово CURDATE может, следовательно, использоваться в операторе SQL и заменяться текущей датой, когда его значение станет доступным, с помощью таких команд как SELECT или INSERT. Мы будем использовать представление таблицы Заказов с именем Entryorders для вставки строк в таблицу Заказов. Создайте таблицу заказов так, чтобы CURDATE автоматически вставлялась в поле odate, если не указано другое значение. Затем создайте представление Entryorders так, чтобы значения не могли быть указаны. (См. ответы в Приложении A.) |
||