Руководство По Языку SQL СУБД Firebird 5.0 | CREATE VIEW

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

Представление может быть обновляемым и только для чтения.Если представление обновляемое, то данные, полученные при обращении к такому представлению, можно изменить при помощи DML операторов INSERT, UPDATE, DELETE, UPDATE OR INSERT, MERGE.Изменения, выполняемые над представлением, применяются к базовой таблице(ам).

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

Для того чтобы представление было обновляемым, необходимо выполнение следующих условий:

оператор выборки SELECT обращается только к одной таблице или одному изменяемому представлению;
оператор выборки SELECT не должен обращаться к хранимым процедурам;
все столбцы базовой таблицы или обновляемого представления, которые не присутствуют в данном представлении, должны удовлетворять одному из следующих условий:
- позволять значение NULL
- NOT NULL столбцы должны иметь значение по умолчанию
- значение NOT NULL столбцов должны быть инициализированы в триггерах базовых таблиц
оператор выборки SELECT не содержит полей определённых через подзапросы или другие выражения;
оператор выборки SELECT не содержит полей определённых через агрегатные функции (MIN, MAX, AVG, COUNT, LIST), статистические функции (CORR, COVAR_POP, COVAR_SAMP и др.), функции линейной регрессии (REGR_AVGX, REGR_AVGY и др.) и все виды оконных функций;
оператор выборки SELECT не содержит предложений ORDER BY, GROUP BY, HAVING;
оператор выборки SELECT не содержит ключевого слова DISTINCT и ограничений количества строк с помощью ROWS, FIRST/SKIP, OFFSET/FETCH.

`WITH CHECK OPTIONS`

Необязательное предложение WITH CHECK OPTIONS задаёт для изменяемого представления требования проверки вновь введённых или модифицируемых данных условию, указанному в предложении WHERE оператора выборки SELECT.При попытке вставки новой записи или модификации записи проверяется, выполняется ли для этой записи условие в предложении WHERE, если условие не выполняется, то вставка/модификация не выполняется и будет выдано соответствующее диагностическое сообщение.

Предложение WITH CHECK OPTION может задаваться в операторе создания представления только в том случае, если в главном операторе SELECT представления указано предложение WHERE.Иначе будет выдано сообщение об ошибке.

Note

Если используется предложение WITH CHECK OPTIONS, то система проверяет входные значения на соответствие условию в предложении WHERE до того как они будут переданы в базовую таблицу.Таким образом, если входные значения не проходят проверку, то предложения DEFAULT или триггеры на базовой таблице, не могут исправить входные значения, поскольку действия никогда не будут выполнены.

Кроме того, поля представления не указанные в операторе INSERT передаются в базовую таблицу как значения NULL, независимо от их наличия или отсутствия в предложении WHERE.В результате значения по умолчанию, определённые на таких полях базовой таблицы, не будут применены.С другой стороны, триггеры будут вызываться и работать как ожидалось.

Для представлений у которых отсутствует предложение WITH CHECK OPTIONS, поля, отсутствующие в операторе INSERT, не передаются вовсе, поэтому любые значения по умолчанию будут применены.

Привилегии выполнения

Выполнение SQL кода представлений всегда осуществляется с привилегиями определяющего пользователя (владельца).

Кто может создать представление?

Выполнить оператор CREATE VIEW могут:

Администраторы
Пользователи с привилегией CREATE VIEW.

Пользователь, создавший представление, становится его владельцем.

Для создания представления пользователями, которые не имеют административных привилегий, необходимы также привилегии на чтение (SELECT) данных из базовых таблиц и представлений, и привилегии на выполнение (EXECUTE) используемых селективных хранимых процедур.

Для разрешения вставки, обновления и удаления через представление, необходимо чтобы создатель (владелец) представления имел привилегии INSERT, UPDATE и DELETE на базовые объекты метаданных.

Предоставить привилегии на представление другим пользователям возможно только если владелец представления сам имеет эти привилегии на базовых объектах.Она будет всегда, если владелец представления является владельцем базовых объектов метаданных.

Примеры

Example 1. Создание представления

CREATE VIEW ENTRY_LEVEL_JOBS AS
SELECT JOB_CODE, JOB_TITLE
FROM JOB
WHERE MAX_SALARY < 15000;

Example 2. Создание представления с проверкой условия фильтрации

Создание представления возвращающего столбцы JOB_CODE и JOB_TITLE только для тех работ, где MAX_SALARY меньше $15000.При вставке новой записи или изменении существующей будет осуществляться проверка условия MAX_SALARY < 15000, если условие не выполняется, то вставка/изменение будет отвергнуто.

CREATE VIEW ENTRY_LEVEL_JOBS AS
SELECT JOB_CODE, JOB_TITLE
FROM JOB
WHERE MAX_SALARY < 15000
WITH CHECK OPTIONS;

Example 3. Создание представления с использованием списка столбцов

CREATE VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.1
FROM PRICE;

Example 4. Создание представления с использованием псевдонимов полей

CREATE VIEW PRICE_WITH_MARKUP AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.1 AS COST_WITH_MARKUP
FROM PRICE;

Example 5. Создание необновляемого представления с использованием хранимой процедуры

CREATE VIEW GOODS_PRICE AS
SELECT
    goods.name AS goodsname,
    price.cost AS cost,
    b.quantity AS quantity
FROM
    goods
    JOIN price ON goods.code_goods = price.code_goods
    LEFT JOIN sp_get_balance(goods.code_goods) b ON 1 = 1;

Example 6. Создание обновляемого представления с использованием триггеров

-- базовые таблицы
RECREATE TABLE t_films(id INT PRIMARY KEY, title VARCHAR(100));
RECREATE TABLE t_sound(id INT PRIMARY KEY, audio BLOB);
RECREATE TABLE t_video(id INT PRIMARY KEY, video BLOB);
COMMIT;

-- создание необновляемого представления
RECREATE VIEW v_films AS
  SELECT f.id, f.title, s.audio, v.video
  FROM t_films f
  LEFT JOIN t_sound s ON f.id = s.id
  LEFT JOIN t_video v ON f.id = v.id;

/* Для того чтобы сделать представление обновляемым создадим
   триггер, который будет производить манипуляции над базовыми
   таблицами.
*/
SET TERM ^;
CREATE OR ALTER TRIGGER v_films_biud FOR v_films
ACTIVE BEFORE INSERT OR UPDATE OR DELETE POSITION 0 AS
BEGIN
  IF (INSERTING) THEN
    new.id = COALESCE(new.id, GEN_ID(g_films, 1));
  IF (NOT DELETING) THEN
  BEGIN
    UPDATE OR INSERT INTO t_films(id, title)
    VALUES(new.id, new.title)
    MATCHING(id);

    UPDATE OR INSERT INTO t_sound(id, audio)
    VALUES(new.id, new.audio)
    MATCHING(id);

    UPDATE OR INSERT INTO t_video(id, video)
    VALUES(new.id, new.video)
    MATCHING(id);
 END
 ELSE
 BEGIN
   DELETE FROM t_films WHERE id = old.id;
   DELETE FROM t_sound WHERE id = old.id;
   DELETE FROM t_video WHERE id = old.id;
 END
END^
SET TERM ;^

/*
 * Теперь мы можем производить манипуляции над
 * этим представлением как будто мы работаем с таблицей
 */
INSERT INTO v_films(title, audio, video)
VALUES('007 coordinates skyfall', 'pif-paf!', 'oh! waw!');

См. также:

ALTER VIEW, CREATE OR ALTER VIEW, RECREATE VIEW, DROP VIEW.

`ALTER VIEW`

Назначение

Изменение существующего представления.

Доступно в

DSQL

Синтаксис

ALTER VIEW viewname [<full_column_list>]
AS <select_statement>
[WITH CHECK OPTION];

<full_column_list> ::= (colname [, colname ...])

Table 1. Параметры оператора `ALTER VIEW`
Параметр	Описание
viewname	Имя существующего представления.
select_statement	Оператор `SELECT`.
full_column_list	Список столбцов представления.
colname	Имя столбца представления.Дубликаты имён столбцов не позволяются.

Оператор ALTER VIEW изменяет определение существующего представления, существующие права на представления и зависимости при этом сохраняются.Синтаксис оператора ALTER VIEW полностью аналогичен синтаксису оператора CREATE VIEW.

Warning

Будьте осторожны при изменении количества столбцов представления.Существующий код приложения может стать неработоспособным.Кроме того, PSQL модули, использующие изменённое представление, могут стать некорректными.Информация о том, как это обнаружить, находится в приложении Поле RDB$VALID_BLR.

Кто может изменить представление?

Выполнить оператор ALTER VIEW могут:

Администраторы
Владелец представления;
Пользователи с привилегией ALTER ANY VIEW.

Примеры

Example 1. Изменение представления

ALTER VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.15
FROM PRICE;

См. также:

CREATE VIEW, CREATE OR ALTER VIEW, RECREATE VIEW.

`CREATE OR ALTER VIEW`

Назначение

Создание нового или изменение существующего представления.

Доступно в

DSQL

Синтаксис

CREATE OR ALTER VIEW viewname [<full_column_list>]
AS <select_statement>
[WITH CHECK OPTION];

<full_column_list> ::= (colname [, colname ...])

Table 1. Параметры оператора `CREATE OR ALTER VIEW`
Параметр	Описание
viewname	Имя представления.Может содержать до 63 символов.
select_statement	Оператор `SELECT`.
full_column_list	Список столбцов представления.
colname	Имя столбца представления.Дубликаты имён столбцов не позволяются.

Оператор CREATE OR ALTER VIEW создаёт представление, если оно не существует.В противном случае он изменит представление с сохранением существующих зависимостей.

Примеры

Example 1. Создание нового или изменение существующего представления

CREATE OR ALTER VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.15
FROM PRICE;

См. также:

CREATE VIEW, ALTER VIEW, RECREATE VIEW.

`DROP VIEW`

Назначение

Удаление существующего представления.

Доступно в

DSQL

Синтаксис

DROP VIEW viewname

Table 1. Параметры оператора `DROP VIEW`
Параметр	Описание
viewname	Имя представления.

Оператор DROP VIEW удаляет существующее представление.Если представление имеет зависимости, то удаление не будет произведено.

Кто может удалить представление?

Выполнить оператор DROP VIEW могут:

Администраторы
Владелец представления;
Пользователи с привилегией DROP ANY VIEW.

Примеры

Example 1. Удаление представления

DROP VIEW PRICE_WITH_MARKUP;

См. также:

CREATE VIEW, RECREATE VIEW.

`RECREATE VIEW`

Назначение

Создание нового или пересоздание существующего представления.

Доступно в

DSQL

Синтаксис

RECREATE VIEW viewname [<full_column_list>]
AS <select_statement>
[WITH CHECK OPTION];

<full_column_list> ::= (colname [, colname ...])

Table 1. Параметры оператора `RECREATE VIEW`
Параметр	Описание
viewname	Имя представления.Может содержать до 63 символов.
select_statement	Оператор `SELECT`.
full_column_list	Список столбцов представления.
colname	Имя столбца представления.Дубликаты имён столбцов не позволяются.

Создаёт или пересоздаёт представление.Если представление с таким именем уже существует, то оператор RECREATE VIEW попытается удалить его и создать новое.Оператор RECREATE VIEW не выполнится, если существующее представление имеет зависимости.

Примеры

Example 1. Создание нового или пересоздание существующего представления

RECREATE VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.15
FROM PRICE;

См. также:

CREATE VIEW, CREATE OR VIEW, DROP VIEW.

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

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

WITH CHECK OPTIONS

Привилегии выполнения

Кто может создать представление?

Примеры

ALTER VIEW

Кто может изменить представление?

Примеры

CREATE OR ALTER VIEW

Примеры

DROP VIEW

Кто может удалить представление?

Примеры

RECREATE VIEW

Примеры

`WITH CHECK OPTIONS`

`ALTER VIEW`

`CREATE OR ALTER VIEW`

`DROP VIEW`

`RECREATE VIEW`