Ограничения целостности. Представления данных

Выбор таблицы-адресата для внешнего ключа

Столбцам внешнего ключа не запрещено ссылаться на столбцы своей же таблицы:

ALTER TABLE emp 
 ADD CONSTRAINT valid_manager
  FOREIGN KEY ( mgr ) REFERENCES emp ( empno )
;

В стандарте SQL такой внешний ключ называется рекурсивным.

Равным образом внешнему ключу разрешено ссылаться на столбцы таблицы из другой схемы. Только в этом случае потребуется иметь на таблицу из другой схемы привилегию REFERENCES:

CONNECT scott/tiger
-- соединились с СУБД как SCOTT
GRANT REFERENCES ON dept TO yard;
-- выдали право ссылаться внешним ключом на поля DEPT из схемы YARD
CONNECT yard/pass
-- соединились с СУБД как YARD
CREATE TABLE emp AS SELECT * FROM scott.emp;
-- создали таблицу EMP по образу одноименной в схеме SCOTT
ALTER TABLE emp 
   ADD 
FOREIGN KEY ( deptno ) REFERENCES scott.dept ( deptno )
;
-- установили ссылку на таблицу из другой схемы

Обратите внимание, что привилегии на SELECT к таблице-адресату в случае нахождения последней в иной схеме не требуется.

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

Поведение СУБД при попытке удалить "родительскую" запись

Обычное ограничение типа "внешний ключ" запрещает СУБД удалять родительскую запись, если на нее существуют в данный момент ссылки:

DELETE FROM dept WHERE deptno = 10;

Однако можно смоделировать и иную реакцию СУБД, разрешив-таки удаление родительской записи.

Указание ON DELETE CASCADE в определении ключа приведет заодно с удалением родительской записи к автоматическому удалению подчиненных записей:

CREATE TABLE x ( a NUMBER PRIMARY KEY );
CREATE TABLE y ( b NUMBER PRIMARY KEY, 
                 c NUMBER REFERENCES x ( a ) ON DELETE CASCADE );
INSERT INTO x VALUES ( 1 );
INSERT INTO y VALUES ( 2, 1 );
DELETE FROM x;
SELECT * FROM y;

Обе таблицы пусты.

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

CREATE TABLE z ( d NUMBER PRIMARY KEY, 
                 e NUMBER REFERENCES y ( b ) ON DELETE CASCADE );
INSERT INTO x VALUES ( 1 );
INSERT INTO y VALUES ( 2, 1 );
INSERT INTO z VALUES ( 3, 2 );
DELETE FROM x;
SELECT * FROM z;

Автоматическим удалением по цепочке следует пользоваться с осторожностью.

Указание ON DELETE SET NULL в определении ключа приведет заодно с удалением родительской записи к автоматическому удалению значений в полях-ссылках подчиненных записей:

CREATE TABLE w ( f NUMBER REFERENCES z ( d ) ON DELETE SET NULL );
INSERT INTO z VALUES ( 3, NULL );
INSERT INTO w VALUES ( 3 );
DELETE FROM z;
SELECT * FROM w;

Строка в таблице Z пропала, а в таблице W осталась (проверьте!).

Обратите внимание, что фраза CASCADE CONSTRAINTS в предложении DROP TABLE не соответствует ни первому, ни второму из вышеприведенных вариантов, попросту удаляя ограничение типа "внешний ключ" и не трогая значений подчиненных записей:

INSERT INTO x VALUES ( 1 );
INSERT INTO y VALUES ( 2, 1 );
DROP TABLE x CASCADE CONSTRAINTS;
SELECT * FROM y;
DROP TABLE y CASCADE CONSTRAINTS;
DROP TABLE z CASCADE CONSTRAINTS;
DROP TABLE w CASCADE CONSTRAINTS;

Стандарт SQL дает право задавать аналогичное поведение СУБД при попытках изменить родительскую запись, используя для этого формулировку ON UPDATE CASCADE. Oracle такой возможности не дает. Мнения специалистов по поводу целесообразности подобной формулировки разделились на противоположные.

Дополнительное условие для занесения значений в поля строки

Этот вид ограничения записывается с использованием ключевого слова CHECK. Он позволяет сформулировать в форме условного выражения дополнительные проверки на заносимые в поля строки значения. Если условное выражение окажется ложным, СУБД отвергнет изменение значений (реляционная теория предпочитает соблюдать истинность условия, а не отсутствие нарушения, что не одно и то же).

Пример:

ALTER TABLE dept_copy ADD CHECK ( category BETWEEN 'A' AND 'G' );
Другие примеры дополнительной проверки в описании столбца:
...
, zip   CHAR ( 6 ) CHECK ( REGEXP_LIKE ( zip, '[[:digit:]]{6}' ) ) )
, ename VARCHAR2 ( 30 ) CHECK ( ename = INITCAP ( ename ) )
...

Пример указания условных проверок при создании таблицы:

CREATE TABLE emp1 
(
  empno   NUMBER
, job     VARCHAR2 ( 9 ) DEFAULT 'SALESMAN'
, sal     NUMBER   ( 10, 2 )
, comm    NUMBER   ( 9, 0 ) 
,
  CONSTRAINT pk_emp1 PRIMARY KEY ( empno )
, CONSTRAINT ck_sal  CHECK ( sal >= 500 )
, CONSTRAINT check_whole_earning CHECK ( sal + comm < 7000 )
, CONSTRAINT sale_commission CHECK ( job = 'SALESMAN' OR comm IS NULL )
); 

Упражнение. Попробуйте выполнить:

INSERT INTO emp1 ( empno, sal, comm ) VALUES ( 1, 500, 1000 );
INSERT INTO emp1 ( empno, sal, comm ) VALUES ( 2, 8000, 1000 );
INSERT INTO emp1 ( empno, sal )       VALUES ( 3, 8000 );

Как нужно изменить определение таблицы, чтобы исправить ситуацию и сообщить СУБД интуитивное понимание правила SAL + COMM < 7000? Решите аналогичную проблему для ограничения SALE_COMMISSION.

Последний пример из упражнения подчеркивает, что условное выражение в проверке CHECK обрабатывается отлично от фразы WHERE (или оператора CASE). В случае CHECK изменение отвергается, когда условное выражение дает FALSE; в случае WHERE строка выбирается для обработки, когда условное выражение дает TRUE. Область расхождения в трактовке — NULL в качестве результата оценки логического выражения.

Обратите внимание, что формулировать ограничения целостности в тексте предложения CREATE TABLE допускается как на уровне столбца (если только ограничение касается единственного столбца, как, например, PK_EMP1 и CK_SAL), так и на уровне таблицы (CHECK_WHOLE_EARNING и SALE_COMMISSION). Такая разница в записи не отражается на свойствах таблицы.

Упражнение. Удалите созданную таблицу EMP1 и создайте заново, но сформулировав те же ограничения целостности на уровне таблицы.

В отличие от стандарта SQL, в Oracle условное выражение в CHECK не имеет право содержать обращения к БД (в том числе через посредство функций), что существенно ослабляет его общую силу.

Добавление ограничения с отказом от проверки соответствия имеющимся данным

Ограничения типа "внешний ключ" и "проверка значений" (FOREIGN KEY и CHECK) могут добавляться к существующей таблице, даже если данные в таблице им противоречат. Отказаться от проверки данных при добавлении ограничения можно с помощью слова NOVALIDATE. В этом случае ограничение вступит в силу, однако не исключается, что ранее занесенные данные будут его нарушать. Автоматическая проверка ограничения, как и полагается, будет распространяться на все последующие попытки изменения данных в таблице:

SQL> CREATE TABLE deptc AS SELECT * FROM dept;
Table created.
SQL> INSERT INTO deptc ( deptno ) VALUES ( 31 );
1 row created.
SQL> ALTER TABLE deptc ADD CHECK ( deptno <= 30 );
ALTER TABLE deptc ADD CHECK ( deptno <= 30 )
*
ERROR at line 1:
ORA-02293: cannot validate (SCOTT.SYS_C005484) - check constraint violated
SQL> ALTER TABLE deptc ADD CHECK ( deptno <= 30 ) NOVALIDATE;
Table altered.
SQL> INSERT INTO deptc ( deptno ) VALUES ( 30 );
1 row created.
SQL> INSERT INTO deptc ( deptno ) VALUES ( 32 );
INSERT INTO deptc ( deptno ) VALUES ( 33 )
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C005484) violated
SQL> SELECT * FROM deptc WHERE deptno > 30;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
        31

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

Такая возможность добавить ограничение без проверки соответствия ему имеющихся данных позволяет:

Приостановка проверки заявляемых ограничений в пределах транзакции

В соответствии со стандартом SQL ANSI/ISO, с версии Oracle 8.1 именованные заявляемые ограничения целостности в Oracle можно определять со словом DEFERRABLE, например:

ALTER TABLE dept ADD CONSTRAINT u_names UNIQUE ( loc ) DEFERRABLE;

В этом случае станет возможным отложить автоматическую проверку ограничения на период до завершения текущей транзакции (подобная степень долговечности отмены проверки оправдывает употребление термина "приостановка"). С этой целью в сеансе связи с СУБД следует выдать:

SET CONSTRAINT u_names DEFERRED;

Теперь можно внести изменение, нарушающее ограничение:

INSERT INTO dept ( loc, deptno ) VALUES ( 'BOSTON', 50 );

Если дубликат не будет устранен, то сообщение об ошибке появится только при попытке выполнить COMMIT или же явочным путем возобновить конкретную проверку:

SET CONSTRAINT u_names IMMEDIATE;

Завершение транзакции (любым образом, COMMIT/ROLLBACK) отменяет все подобные сделанные приостановки. Если выдается COMMIT и есть нарушения данными хотя бы одного любого ограничения, Oracle молча подменит COMMIT на ROLLBACK. Для программиста это важное обстоятельство, так как произойдет отказ от всех изменений в пределах последней транзакции, без разбору. Если же выдается SET CONSTRAINT … IMMEDIATE, транзакция не закроется, а программа получит в ответ сообщение об ошибке.

Упражнение. Проверьте работу отложенных проверок заявляемых ограничений целостности на примере имеющегося ограничения внешнего ключа.

Вместо приостановки/возобновления проверки нескольких ограничений по отдельности можно выполнить эти действия для всех ограничений зараз, например:

SET CONSTRAINT ALL DEFERRED;

Ограничения, объявленные как DEFERRABLE, можно дополнить правилом проверки по умолчанию, например:

CREATE TABLE x (
     a NUMBER CHECK ( a > 0 ) DEFERRABLE INITIALLY DEFERRED,
     b NUMBER CHECK ( b > 1 ) DEFERRABLE INITIALLY IMMEDIATE);

Ограничение для столбца A нормально не проверяется (умолчательно приостановлено) и временно допускает вступление в силу. Ограничение для столбца B нормально проверяется и временно допускает приостановку действия. Само указание слов INITIALLY IMMEDIATE является умолчательным.

Упражнение. Проверьте срабатывание указанных для столбцов таблицы X условий на заносимые значения.

Приостановка проверки ограничений может использоваться:

Не все специалисты в реляционном подходе к проектированию БД разделяют мнение о целесообразности приостановки проверки ограничений, принятой в SQL, указывая на другие способы решения возникающих проблем. Нельзя не заметить, что во время приостановки действия ограничений БД может давать на запросы ответы, неправильные с точки зрения заложенной в нее модели предметной области. В любом случае подобная приостановка должна использоваться программистом исключительно как временная техническая мера.

"Долговременное" отключение ограничений целостности

Действующее в схеме объявляемое ограничение целостности можно отключить также на период, более долгий, чем транзакция, вообще безотносительно к последующим транзакциям:

ALTER TABLE dept MODIFY CONSTRAINT u_names DISABLE;

Мотивами для "долговременного" отключения проверки ограничений могут, например, стать:

Допускается задавать отключенность ограничения изначально при его создании.

Включение ограничения аналогично отключению, но с указанием слова ENABLE. Как и при включении проверки после ее приостановки, здесь тоже возможны конфликты с данными, однако способы разрешения конфликтов — свои собственные.

Технология включения и выключения заявляемых ограничений целостности

Если нормальный перевод ограничения в состояние ENABLED невозможен из-за накопленных противоречащих ограничению данных, Oracle предлагает два практических выхода из подобной ситуации:

Оба решения представляют первоочередную ценность для больших таблиц.

Включение ограничения без выполнения проверки имеющихся данных

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

CREATE TABLE t (
     c VARCHAR2 ( 1 ) CONSTRAINT ctest CHECK ( c IN ( 'a', 'b' ) ) DISABLE 
);
INSERT INTO t VALUES ( 'd' );
ALTER TABLE t MODIFY CONSTRAINT ctest ENABLE; 
-- Ошибка !
ALTER TABLE t MODIFY CONSTRAINT ctest ENABLE NOVALIDATE;
INSERT INTO t VALUES ( 'd' ) ; 
-- Ошибка !
Аналогично можно указывать NOVALIDATE или VALIDATE и при отключении проверки ограничения:
ALTER TABLE t MODIFY CONSTRAINT ctest DISABLE VALIDATE; 
-- Ошибка !
ALTER TABLE t MODIFY CONSTRAINT ctest DISABLE NOVALIDATE;

Выявление записей, нарушающих ограничение

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

ALTER TABLE t MODIFY CONSTRAINT ctest ENABLE EXCEPTIONS INTO exceptions;

Если нарушения ограничения CTEST данными обнаружится, в программу вернется ошибка, но при этом в указанную в команде таблицу EXCEPTIONS СУБД занесет список физических адресов строк, нарушающих ограничение. После корректировки данных в таблице T соответствующие ей строки в EXCEPTIONS нужно не забыть удалить самостоятельно, так как Oracle, разумеется, этого не сделает.

Более сложные правила целостности

Заявляемые ограничения целостности в Oracle приспособлены для записи важных, но простейших видов дополнительных ограничений на помещаемые в базу значения данных. Более сложные правила (по более современной терминологии "бизнес"-правила) могут быть заданы с использованием:

Порядок в указанном перечне соответствует убыванию гарантированности соблюдения правил целостности в фактически оказавшихся в базе данных (соответственно — увеличению риска получить в БД некорректные с прикладной точки зрения данные) по отношению к заявляемым в схеме ограничениям.

Представления данных, или же виртуальные таблицы (views)

Буквальным переводом английского термина view в базах данных является "вид" [на данные]. Термин является сокращением от бытовавшего когда-то более точного названия data view. Содержательно правильным переводом могут быть термины "виртуальная", "выводимая", "производная" или же "синтезированная" таблица. Буквальный перевод в русском языке не прижился, а содержательно более ему предпочтительные оказались чересчур громоздкими. По этим и другим причинам у нас широко распространился "перевод" представление (данных). Все используемые и не вошедшие в оборот русские термины чем-то неудачны. Справедливости ради можно отметить, что и выбор оригинального термина не бесспорен. Предлагается, например, вместо view использовать "более правильный" термин derived table.

Название "виртуальная таблица", принятое здесь наравне с "представлением", перекликается с названием "виртуальный столбец", официально появившимся в Oracle версии 11.

Понятие view попало в SQL из реляционной теории, но в выхолощеном виде, потеряв многие интересные качества. Там радикального отличия "основных отношений" от "производных" нет, и по сути все отношения являются как бы "представлениями" ("видимостями") данных, допускающими внесение через себя изменений в БД, если только это теоретически позволительно.

В SQL термин используется для обозначения запроса SELECT, текст и разобранная структура которого хранятся в БД под определенным именем. Формальное основание такому хранению дает то обстоятельство, что любой запрос в SQL возвращает набор данных, структурированный в виде столбцов и строк, равно как в таблице, извлеченной из БД. Ничто не мешает взять этот набор данных в качестве источника для другого запроса. Если в запросе указать в качестве источника данных не реальную таблицу, а виртуальную ("представление"), Oracle обнаружит подмену, вычислит сходу запрос для нее и полученный результат предъявит для обработки основному запросу. Стоит только добавить, что это никогда не нарушаемая логика обработки, в то время как технически Oracle вполне может поступать иначе. Например, при определенных обстоятельствах Oracle может "растворить" текст view в основном запросе и вычислять основной запрос сразу и без предварительной фазы вычисления подзапроса-view или же вести вычисление и основного запроса, и данных подзапроса-view параллельно и так далее.

Таким образом, для запросов SELECT, поступающих из приложения, нет никакой содержательной разницы, скрывается ли под именем источника данных виртуальная или реальная таблица, и тем самым выдержана преемственность реляционной модели. Разница может касаться только затрат ресурсов СУБД на выполнение.

Просто таблицы можно в противовес view уточнять словами "реальные", "основные", "базовые" (что то же самое), "исходные". Часто возникает соблазн назвать их "хранимыми", противопоставляя "вычислимым" view, но Oracle (и не только) дает пример "исходных" таблиц, не хранимых в БД: это так называемые X$-таблицы, служащие администрированию.

Виртуальные таблицы позволяют решать в базе данных по крайней мере три важные задачи:

Примеры определений представлений данных

Ниже приводятся некоторые примеры создания и удаления виртуальных таблиц. Таблицы EMP и DEPT являются "реальными" (иначе — основными, или базовыми), а таблицы JOBS, COMMISSIONERS и EMPLOYEES — виртуальными, то есть представлениями данных.

CREATE VIEW jobs AS SELECT DISTINCT job FROM emp;
CREATE VIEW commissioners 
AS 
   SELECT * 
   FROM   emp
   WHERE  comm IS NOT NULL
;
CREATE VIEW employees ( name, place ) 
AS 
   SELECT ename, loc
   FROM   emp, dept
   WHERE  emp.deptno = dept.deptno
;
DROP VIEW employees;

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

Техника представлений позволяет дать программисту удобный взгляд на данные. Так как с точки зрения выборки данных эти виртуальные таблицы ничем не отличаются от реальных, со временем неизбежно возникает желание применять к ним не только SELECT, но и операторы DML. Смысл применения к представлению данных операторов INSERT, UPDATE и DELETE логично понимать как попытку внести изменения в основные таблицы таким образом, чтобы создалось впечатление изменения таблицы виртуальной. То есть, строго говоря, речь идет не об "обновлении представлений данных" операторами DML, а об "обновлении БД" этими операторами "через представления".

Осуществить такие изменения в БД в каждом конкретном случае удается не всегда, и по разным причинам: отчасти по объективным, отчасти по субъективным.

Oracle делит все представления данных на две категории: обновляемые (указанным выше образом) и необновляемые. Простейшим случаем обновляемого является представление, построенное запросом к единственной основной таблице. С версии 8 иногда стало возможно обновление представлений, построенных на основе запроса к более чем одной основной таблице. (При этом формальная обновляемость не гарантирует фактическое выполнение конкретной операции DML применительно к view: иногда оно может вступить в противоречие ограничениям целостности, связаным с таблицей. Примером того, как можно попытаться избежать подобной неопределенности, является включение в запрос для view поля первичного ключа — разумеется, когда таковой у основной таблицы имеется.)

В любом случае Oracle запрещает обновление данных, если в определении представления предложение SELECT содержит обобщение в том или ином виде, как то:

Если таблица причислена к обновляемым, некоторые ее столбцы могут оказаться закрытыми для обновления. Так, чтобы столбец допускал изменения, требуется, чтобы во фразе SELECT подлежащего запроса он:

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

Более точный список правил предъявления операции DML к представлениям данных приведен в общем виде в документации по Oracle, а применительно к конкретным представлениям список обновляемых столбцов можно узнать из таблицы USER_UPDATABLE_COLUMNS словаря-справочника.

Из приводившихся выше определений:

Радикально решить все проблемы обновления данных через представления способна триггерная процедура типа INSTEAD OF, с помощью которой разработчик получает шанс самостоятельно запрограммировать необходимые изменения в БД, отражающие, по его мнению, смысл применения INSERT, UPDATE или DELETE к любому представлению.

Ограничения целостности для представлений данных

Попытки внести в БД изменения через представления данных можно связать ограничениями. К определению представлений неприменимы ограничения целостности, существующие для обычных таблиц (хотя в реляционном подходе виртуальные, производные отношения и могут иметь подобные ограничения), однако имеются два вида собственных: запрет непосредственных обновлений и ограничение возможных изменений областью видимости через view. Оба применяются к таблице целиком, а не к отдельным строке или столбцу.

Запрет непосредственных обновлений

При создании представления можно запретить изменение данных БД через него непосредственно командами DML:

CREATE VIEW employeers ( name, location ) 
AS 
   SELECT ename, loc
   FROM   emp, dept
   WHERE  emp.deptno = dept.deptno
WITH READ ONLY
;

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

Для ограничения READ ONLY у виртуальных таблиц не предусмотрено собственное именование конструкцией CONSTRAINT, и оно всегда получает системное название в перечне в USER_CONSTRAINTS.

Сужение возможности непосредственных обновлений

Указание WITH CHECK OPTION при определении представления не запрещает применять к нему операции INSERT, UPDATE и DELETE вообще, а запрещает только те изменения, которые совершатся в подлежащих таблицах, но не будут обнаруживать себя в данных самого представления. В то же время в отсутствие такого указания ненаблюдаемые через представление изменения в БД будут допускаться. Создадим представление о сотрудниках отдела 10:

CREATE VIEW emp10 
AS 
   SELECT *
   FROM   emp
   WHERE  deptno = 10
;

Получаем:

SQL> INSERT INTO emp10 ( empno, deptno ) VALUES ( 1111, 20 );
1 row created.
SQL> SELECT empno, deptno FROM emp10 WHERE empno = 1111;
no rows selected
SQL> SELECT empno, deptno FROM emp WHERE empno = 1111;
     EMPNO     DEPTNO
---------- ----------
      1111         20
SQL> ROLLBACK;
Rollback complete.

То есть, предъявив INSERT к представлению EMP10, мы добавили в таблицу EMP сотрудника, которого само представление нам не показывает.

Изменим определение EMP10:

CREATE OR REPLACE VIEW emp10 
AS 
   SELECT *
   FROM   emp
   WHERE  deptno = 10
WITH CHECK OPTION 
CONSTRAINT only_dept10
;

Получаем:

SQL> INSERT INTO emp10 ( empno, deptno ) VALUES ( 1111, 20 );
INSERT INTO emp10 ( empno, deptno ) VALUES ( 1111, 20 )
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> INSERT INTO emp10 ( empno, deptno ) VALUES ( 1111, 10 );
1 row created.
SQL> ROLLBACK;
Rollback complete.

Для ограничения CHECK OPTION у представлений предусмотрено собственное именование конструкцией CONSTRAINT, что выше и сделано. Без этого ограничение получит в USER_CONSTRAINTS системное название.

Материализованные (овеществленные) представления данных

"Материализованные" (овеществленные) представления данных (materialized views) появились в версии Oracle 8.1 как вариация одноименной категории объектов БД, предлагаемой стандартом SQL. Аналогично обычным представлениям, они предполагают хранение формулировки запроса SELECT к таблицам-источникам, однако вдобавок сохраняют и сам результат запроса в виде хранимой таблицы. Делается это с основною целью обеспечить более быстрый, или же попросту надежный доступ к данным, ввиду отсутствия необходимости вычислять подзапрос по ходу обработки основного запроса и возможности вместо этого взять уже посчитанный результат. Оборотной стороной является усложнение техники работы с данными, неизбежное вследствие раздвоения, возникающего на логическом уровне схемы (источники подзапроса — посчитанный результат). Так, для этого рода объектов следует предусмотреть и обеспечить синхронизацию данных.

Двумя основными областями использования материализованных представлений данных являются следующие.

Материализованные представления создаются, переопределяются и удаляются командами {CREATE | ALTER | DROP} MATERIALIZED VIEW …, например:

CREATE MATERIALIZED VIEW jobsal 
AS 
  SELECT job, SUM ( sal ) AS sum_sal
  FROM   emp 
  GROUP  BY job
;

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

Реляционная теория предпочитает различать понятия snapshot и materialized view. Более того, термин materialized view она считает некорректным (и даже логически противоречивым), полагая правильным употребление на его месте snapshot, ввиду того что именно в названии snapshot заложено возможное отставание "материализованных" данных от текущего состояния таблиц-источников.

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

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

Эффективность же их использования в БД определяется следующими общими факторами:

Представления данных, встроенные в запрос

(Неименованые) представления данных, встроенные в запрос, не создаются как хранимые в БД объекты, а используются только в виде составных частей запросов SQL. Оригинальное название — inline view, то есть представления, "встроенные в текст" основного запроса.

Формально неименованое представление данных — это подзапрос, подставленный на место источника данных в SELECT или в команды DML на изменение данных. Примеры применения во фразе FROM предложения SELECT приводились выше и относительно очевидны.

Пример неименованого представления данных в операторе изменения значений UPDATE:

UPDATE ( SELECT e.ename, d.dname 
         FROM   emp e INNER JOIN dept d
                USING ( deptno )
         WHERE  d.dname = 'SALES'
         AND    e.comm IS NULL
       ) 
SET ename = INITCAP ( ename )
;

Обратите внимание, что вложенный SELECT ("неименованое представление данных") делает выборку из двух таблиц. Следовательно, этот оператор UPDATE не сводим к привычно применяемому к одной таблице, по крайней мере простым образом.

Подзапросы подобного рода объединяет с обычными представлениями то обстоятельство, что они не допускают автоматически возможность "изменений" всех своих столбцов. Такие "изменения" иногда делать можно (пример выше), а иногда нельзя. Правила разрешения правки значений как раз совпадают с существующими для обычных представлений. Если программисту их потребуется в конкретных обстоятельствах уточнить, ему достаточно создать на основе подзапроса обычное представление и справиться о возможности правки полей в USER_UPDATABLE_COLUMNS. Однако если такой подзапрос эпизодический и не требует сохранения в БД, Oracle позволяет попросту вставить его в оператор DML указанным выше способом.

Упражнение. Верните именам сотрудников отдела продаж, не получающих комиссионные, первоначальное написание заглавными буквами. Замените "SET ename = INITCAP (ename)" на "SET dname = INITCAP (dname)" и попробуйте повторить запрос. Замените порядок указания таблиц во фразе FROM и вновь повторите запрос.

Еще примеры:

DELETE 
       FROM   emp e INNER JOIN dept d
              USING ( deptno )
       WHERE  e.job <> 'CLERK'
WHERE deptno = 10
;
INSERT 
INTO ( SELECT e.empno, deptno
       FROM   emp e INNER JOIN dept d
              USING ( deptno )
       WHERE  e.job NOT IN ( 'CLERK', 'SALESMAN', 'MANAGER' )
       WITH CHECK OPTION
     )
VALUES ( 1111, 10 )
;

Упражнение. В последнем операторе замените VALUES ( 1111, 10 ) на VALUES ( 1112, 20 ) и повторите запрос. Следом уберите указание WITH CHECK OPTION и снова повторите. Объясните результаты.

Вернем значения EMP:

ROLLBACK;