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

Не все представления в SQL могут быть модифицированы. Модифицируемое представление определяется следующими критериями:

• основывается только на одной базовой таблице;
• содержит первичный ключ этой таблицы;
• не содержит DISTINCT в своем определении;
• не использует GROUP BY или HAVING в своем определении;
• по возможности не применяет в своем определении подзапросы;
• не использует константы или выражения значений среди выбранных полей вывода;
• должен быть включен каждый столбец таблицы, имеющий атрибут NOT NULL;
• оператор SELECT представления не использует агрегирующие (итоговые) функции, соединения таблиц, хранимые процедуры и функции, определенные пользователем;

· основывается на одиночном запросе, поэтому объединение UNION не разрешено.

Пример 1

CREATE VIEW DATEEXAM (EXAM_DATE,QUANTITY)

AS SELECT EXAM_DATE, COUNT (*)

FROM EXAM_MARKS

GROUP BY EXAM_DATE;

Данное представление является необновляемым из-за присутствия в нем агрегирующей функции и GROUP BY.

Пример 2

CREATE VIEW LCUSTT

AS SELECT *

FROM UNIVERSITY

WHERE CITY = 'Москва';

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

Если просмотр удовлетворяет этим условиям, к нему могут применяться операторы INSERT, UPDATE, DELETE. Различия между модифицируемыми представлениями и представлениями, предназначенными только для чтения, не случайны. Цели, для которых их используют, различны. С модифицируемыми представлениями в основном обходятся точно так же, как и с базовыми таблицами. Фактически, пользователи не могут даже осознать, является ли объект, который они запрашивают, базовой таблицей или представлением, т.е. прежде всего это средство защиты для сокрытия конфиденциальных или не относящихся к потребностям данного пользователя частей таблицы. Представления в режиме <только для чтения> позволяют получать и форматировать данные более рационально. Они создают целый арсенал сложных запросов, которые можно выполнить и повторить снова, сохраняя полученную информацию. Результаты этих запросов могут затем использоваться в других запросах, что позволит избежать сложных предикатов и снизить вероятность ошибочных действий.

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

CREATE VIEW view4(Код, Название, Тип, Цена, Налог) AS SELECT КодТовара, Название,

Тип, Цена, Цена*0.05 FROM Товар

Модифицирование представлений

Данные, предъявляемые пользователю через представление, могут изменяться с помощью команд модификации DML, но при этом фактическая модификация данных будет осуществляться не в самой виртуальной таблице-представлении, а будет перенаправлена к соответствующей базовой таблице. Например, запрос на обновление представления NEW_STUDENT

UPDATE NEW_STUDENT

SET CITY = 'Москва'

WHERE STUDENT_ID = 1004;

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

Операции модификации в представлениях, маскирующих строки и столбцы

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

CREATE VIEW MOSC_STUD AS

SELECT STUDENT_ID,SURNAME, STIPEND

FROM STUDENT

WHERE CITY = 'Москва';

Видно, что в данное представление не включено поле CITY таблицы STUDENT.

Что будет происходить при попытках вставки строки в это представление? Так как мы не можем указать значение CITY в представлении как значение по умолчанию (ввиду отсутствия, в нем этого поля), то этим значением будет NULL, и оно будет введено в поле CITY базовой таблицы STUDENT '(считаем, что для этого поля опция NOT NULL не используется). Так как в этом случае значение поля CITY базовой таблицы STUDENT не будет равняться значению 'Москва', вставляемая строка будет исключена из самого представления и поэтому не будет видна пользователю. Причем так будет происходить для любой вставляемой в представление MOSC_STUD строки. Другими словами, пользователь вообще не сможет видеть строки, вводимые им в это представление. Данная проблема не решается и в случае, если в определение представления будет добавлена опция WITH CHECK OPTION:

CREATE VIEW MOSC_STUD AS

SELECT STUDENT_ID,SURNAME, STIPEND

FROM STUDENT

WHERE CITY = 'MoCKBa'

WITH CHECK OPTION;

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

CREATE VIEW MOSC_STUD AS

SELECT *

FROM STUDENT

WHERE CITY = 'Москва'

WITH CHECK OPTION;

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

SELECT STUDENT_ID, SURNAME,.STIPEND

FROM MOSC_STUD;