Материал: Graber_Martin_-_SQL_dlya_prostykh_smertnykh_-_2014

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

Рис. 21.1. Представление Citymatch

была получена (надо помнить, что запросы, в которых опущено предложение ORDER ВУ, генерируют выходные данные в произвольном порядке. Это справедливо также и

для запросов, применяемых в представлениях; в таких запросах нельзя использовать

ключевое слово ORDER ВУ. Поэтому порядок двух строк нельзя использовать для

того, чтобы различить их). Это означает, что выходные строки нельзя однозначно свя­ зать со строками из таблиц, участвующих в запросе.

Что произойдет, если попытаться удалить строку London London из представления? Приведет это к удалению из таблицы Customers имен Hoffman или Clemens, или удале­ нию обоих? Удалит ли SQL Рее! из таблицы Salespeople? На эти вопросы невозможно

ответить вполне определенно, поскольку удаления не разрешены для такого рода пред­

ставлений. Представление Citymatch является примером представления только для чте­ ния, в него нельзя вносить какие-либо изменения.

Определение обновляемости 1~редставления

Если команды обновления можно применить к представлению, то говорят, что представление является обновляемым (updataЬ!e); в противном случае оно является только читаемым (read-only). В соответствии с этой терминологией здесь и далее бу­ дет использоваться выражение "обновляемое представление" ("updating а view"), кото­ рое означает, что к представлению применимы все три команды обновления DML (INSERT, UPDATE и DELETE), которые могут изменять его значения.

225

Глава 21. Изменение значений с помощью представле11ий

Как определить, является ли представление обновляемым? В теории баз данных по этому поводу идут активные дебаты. Основной принцип заключается в том, что обнов­ ляемым представлением является то, для которого можно выполнить команду обновле­ ния, изменяя одну и только одну строку таблицы, лежащей в основе представления, в конкретный момент времени без какого-либо воздействия на друтие строки какой-либо другой таблицы. Но проведение этого принципа в практику может быть затруднено. Бо­ лее того, некоторые представления, которые являются обновляемыми в теории, могуr оказаться реально необновляемыми в SQL. Приведем критерии того, является ли пред­ ставление обновляемым в SQL:

•Оно должно базироваться на одной и только одной таблице.

•Оно должно включать первичный ключ таблицы (это не является ограничением ANSI стандарта, но его лучше придерживаться).

•Оно не должно содержать полей, полученных в результате применения функ­

ций агрегирования.

•Оно не может содержать спецификации DIS'FINCT в своем определении.

•Оно не должно использовать GROUP ВУ или НАVING в своем определении.

•Оно не должно использовать подзапросы (это ограничение ANSI, но в некото­

рых программных продуктах его не придерживаются).

•Оно может быть определено на друтом представлении, но это представление должно быть также обновляемым.

•Оно не может содержать константы, строки или выражения (например, comm *

100)в списке выбираемых выходных полей.

•Для INSERT оно должно включать любые поля из лежащей в основе представ­ ления таблицы, которые имеют ограничение NOT NULL, хотя в качестве значе­ ния по умолчанию может быть указано другое значение.

Обновляемые представления в сравнении

с представлениями ,,только для чтения,,

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

226

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

рования и выражений. Они также не выполняют сравнения строк друг с другом (как это можно сделать в соединениях и запросах с помощью ключевого слова DISТINCT).

Различия между обновляемыми представлениями и представлениями "только для чтения" не просто случайны. Цели их применения тоже часто различны. Обновляе­ мые представления обычно испо.1ьзуются точно так же, как базовые таблицы. Прак­ тически пользователи могут даже не знать, с каким объектом они имеют дело: с базовой таблицей или с представлением. Такие представления являются прекрасным механизмом защиты, они позволяют скрыть конфиденциальные или излишние для работы какого-либо пользователя части таблицы. (В главе 22 показано, как открыть пользователю доступ к представлению, но не к таблице, на которой оно базируется.)

С другой стороны, представления "только для чтения", позволяют осуществлять вывод данных и выполнять их переформатирование. Они предоставляют пользовате­ лю библиотеку сложных запросов, которые можно выполнять или повторно выпол­ нять, получая производную информацию точно в том виде, какой требуется. Кроме того, можно получать результаты запросов в таблицах, которые затем можно исполь­ зовать в запросах (например, в соединениях), что дает преимущества перед простым выполнением запросов. Представления "только для чтения" могут иметь приложе­ ния, связанные с защитой. Например, может понадобиться, чтобы определенные

пользователи видели только агрегатные данные, такие, например, как средние ко­

миссионные для продавца, не имея возможности увидеть индивидуальные значения

комиссионных.

Способуказать, какие представления являются

обновляемыми

Перед вами несколько примеров обновляемых представлений и представлений "только для чтения":

CREATE VIEW Dateorders (odate, ocount) AS SELECT odate, COUNT (•)

FROM Orders GROUP ВУ odate;

Это представление "только для чтения", поскольку в него входят функции агреги­ рования и GROUP ВУ.

CREATE VIEW Londoncust AS SELECT *

FROM Customers

WHERE city = 'London';

Это представление является обновляемым.

CREATE VIEW SJsales (name, number, percentage) AS SELECT sname, snum, comm * 100

227

Глава 21. Изменение значений с помощью представлений

FROM Salespeople

WHERE city = ·san Jose·;

Это представление является только читаемым, поскольку содержит выражение

'comm * 100'. Перестановка и переименование полей допустимы. Некоторые про­ граммные продукты разрешают удаления в представлении или обновление значений

столбцов sname и snum. CREATE VIEW Salesonthird

AS SELECT *

FROM Salespeople

WHERE snum IN

(SELECT snum FROM Orders

WHERE odate = 10/03/1990);

Это представление "только для чтения" в смысле ANSI, поскольку содержит подза­ прос. В некоторых программах оно может быть допустимым.

CREATE VIEW Someorders

AS SELECT snum, onum, cnum

FROM Orders

WHERE odate IN (10/03/1990, 10/05/1990);

Это представление является обновляемым.

Выбор значений, размещенных

в представлениях

Другая проблема, связанная с использованием представлений, состоит в том, что

можно вводить значения, которые "поглощаются" лежащей в основе представления

таблицей. Рассмотрим такое представление:

CREATE VIEW Highratings

AS SELECT cnum, rating

FROM Customers

WHERE rating = 300;

Это обновляемое представление. Оно просто ограничивает доступ к таблице опре­ деленными строками и столбцами. Предположим, что вставляется следующая строка:

INSERT INTO Highratings

VALUES (2018, 200);

228

Выбор значений, размещенных в 11редставле11иях

Это правильная команда INSERT для данного представления. Строка будет встав­

лена через представление Highratings в таблицу Customers. Однако в данном случае она не вставляется в представление, поскольку значение рейтинга не равно 300. Здесь также возникает проблема. Приемлемым может явиться значение 200, но теперь стро­ ку из таблицы Customers не видно. Пользователь не может быть уверен в правильно­

сти того, что он ввел, поскольку строка не представлена явно, он также не может и

удалить ее в любом случае.

Такую проблему можно решить, указав WIТH СНЕСК OPТION в определении представления. Если использовать WIТH СНЕСК OPТION в определении представле­

ния Highrating:

CREATE VIEW Highratings AS SELECT cnum, rating

FROM Customers

WHERE rating = 300

WITH СНЕСК OPTION;

то рассмотренная выше вставка будет отвергнута.

WIТH СНЕСК OPТION относится к типу средств "все или ничего". Это предложе­

ние вводится в определение представления, а не в команду DML, значит либо будут

контролироваться все команды обновления представления, либо ни одна. Обычно кон­ троль нужен, поэтому удобно иметь WIТH СНЕСК OPТION в определении представ­ ления, если только нет особых причин для того, чтобы разрешить ввод в таблицу,

лежащую в основе представления, значений, которые в самом представлении не со­

держатся.

Предикаты и исключаемые поля

Другая сходная проблема связана с включением в представление строк с помощью предиката, базирующегося на одном или более полях, не присутствующих в представ­ лении. Например, может оказаться желательным определить Loпdonstaff таким обра­

зом:

CREATE VIEW Londonstaff

AS SELECT snum, sname, comm

FROM Salespeople

WHERE city = "London·;

В самом деле, зачем включать значение поля city, если все значения этого поля оди­

наковы, а название представления говорит о том, какое конкретное значение поля city

участвует в формировании представления? Что происходит при попытке вставить строку? Поскольку нельзя указать значение поля city, для него будет принято значение по умолчанию, возможно, значение NULL (NULL будет использоваться, если только явно не указано другое значение). Поскольку строки, добавленные в представление, не удовлетворяют условию формирования представления, они будут из него исключе-

229