27. Работа с просмотрами VIEW

27.1. Понятие просмотра как виртуальной таблицы

В БД может быть определен просмотр, являющий собой виртуальную таблицу, в которой представлены записи из одной или нескольких таблиц. Порядок формирования записей в просмотре определяется оператором SELECT. Для создания просмотра применяется оператор

CREATE VIEW ИмяПросмотра [(столбец_view1 [,столбец_view ...])]

AS <onepaтop_select> [WITH CHECK OPTION];

где после ИмениПросмотра следует необязательный список столбцов, оператор_select есть полнофункциональный оператор SELECT, а необязательный параметр WITH CHECK OPTION определяет, допускать ли для обновляемых просмотров ввод записей, не удовлетворяющих условию формирования просмотра.

Для удаления просмотра используется оператор

DROP VIEW ИмяПросмотра;

Пример. Создать просмотр, содержащий дату расхода, наименование товара, количество расхода товара из таблицы RASHOD и цену товара из таблицы TOVAR:

CREATE VIEW FULL_RASHOD AS

SELECT R.DAT_RASH, R.TOVAR, R.KOLVO, T.ZENA

FROM RASHOD R, TOVARY T

WHERE R.TOVAR = T.TOVAR;

После этого к просмотру FULL_RASHOD можно обращаться как к обычной таблице БД:

SELECT * FROM FULL_RASHOD;

Преимущества создания просмотров:

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

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

27.2. Способы формирования просмотров

Просмотр может создаваться как:

1) вертикальный срез таблицы, когда в просмотр включается подмножество столбцов таблицы, например:

CREATE VIEW RASH_VERT AS

SELECT DAT_RASH, TOVAR, KOLVO

FROM RASHOD

2) горизонтальный срез таблицы, когда в просмотр включаются все столбцы, но не все записи таблицы, например:

CREATE VIEW RASH_HORIZ AS

SELECT * FROM RASHOD WHERE TOVAR = "Кока-кола";

3) вертикально-горизонтальный срез таблицы, когда в просмотр включается подмножество столбцов и подмножество строк, например:

CREATE VIEW RASH_VERT_HORIZ AS

SELECT DAT_RASH, TOVAR, KOLVO FROM RASHOD WHERE TOVAR = "Кока-кола";

4) подмножество строк и столбцов соединения разных таблиц, например:

CREATE VIEW FULL_RASHOD AS

SELECT R.DAT_RASH, R.TOVAR, R.KOLVO, T.ZENA

FROM RASHOD R, TOVARY T WHERE R.TOVAR = T.TOVAR;

27.3. Указание столбцов просмотра в операторе CREATE VIEW

Имена столбцов просмотра должны указываться в операторе CREATE VIEW в том случае, когда в качестве столбца просмотра определяется выражение, например:

CREATE VIEW STOIM_RASHOD (DAT_RASH, TOVAR, STOIM) AS

SELECT R.DAT_RASH, R.TOVAR, R.KOLVO * T.ZENA

FROM RASHOD R, TOVARY T WHERE R.TOVAR = T.TOVAR;

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

27.4. Обновляемые и необновляемые просмотры

Чтобы просмотр можно было обновлять, то есть применять к нему операции добавления, изменения и удаления записей, необходимо одновременное выполнение трех условий:

• просмотр должен формироваться из записей только одной таблицы;

• в просмотр должен быть включен каждый столбец таблицы, имеющий атрибут NOT NULL;

• оператор SELECT просмотра не должен использовать агрегирующих функций, режима DISTINCT, предложения HAVING, соединения таблиц, хранимых процедур и функций, определенных пользователем.

Если просмотр удовлетворяет этим условиям, к нему могут применяться операторы

INSERT, UPDATE и DELETE.

Пример. В следующем просмотре можно добавлять, корректировать и удалять записи:

CREATE VIEW UPDATABLE_RASH AS

SELECT N_RASH,DAT_RASH, TOVAR, KOLVO FROM RASHOD;

Для того чтобы к просмотру можно было применить операторы UPDATE и DELETE, для него одновременно должны выполняться два условия:

• просмотр должен формироваться из записей только одной таблицы;

• оператор SELECT просмотра не использует агрегатных функций, режима DISTINCT, предложения HAVING, соединения таблиц, хранимых процедур и функций, определенных пользователем.

Пример. В следующем просмотре можно корректировать и удалять записи, но нельзя добавлять:

CREATE VIEW LESSUPDAPTABLE_RASH AS

SELECT DAT_RASH, TOVAR, KOLVO, POKUP FROM RASHOD;

Пример. В следующем просмотре нельзя добавлять, корректировать и удалять записи:

CREATE VIEW A AS

SELECT R.TOVAR, T.ZENA

FROM RASHOD R, TOVARY T WHERE R.TOVAR = T.TOVAR;

27.5. Использование CHECK OPTION

Если для обновляемого просмотра указан параметр CHECK OPTION, будут отвергаться все попытки добавления новых или изменения существующих записей таким образом, чтобы нарушалось условие WHERE оператора SELECT данного просмотра.

Пример. В данный просмотр невозможно добавить записи со значением поля KOLVO, меньшим 1000:

CREATE VIEW RASH_1000_CHECK AS

SELECT * FROM RASHOD WHERE KOLVO > 1000 WITH CHECK OPTION;

27.6. Компоненты Delphi и использование просмотров

Просмотр есть виртуальная таблица БД. "Виртуальная" означает буквально следующее: при работе с просмотром возникает впечатление, что эта таблица физически существует, хотя на самом деле это не так. Поэтому компонент TTable может содержать имя просмотра в свойстве TableName, а компонент TQuery использовать SQL-запрос, в котором наравне с "нормальными" таблицами производится обращение и к просмотру. Поведение просмотра в приложении аналогично поведению обычной таблицы БД, с учетом особенностей, определяемых параметром CHECK OPTION и возможностью полного (добавление, удаление, изменение), частичного (изменение, удаление записей) обновления просмотра или невозможностью такового. Следует помнить, что подобно НД, возвращаемому оператором SELECT, НД, возвращаемый просмотром, показывает записи в состоянии, в котором они были на момент открытия набора данных компонента TTable или TQuery. Для того чтобы внесенные после открытия НД изменения стали актуальны в просмотре, компонент, использующий просмотр, должен быть переоткрыт.