Создание обновляющих представлений

Creating Updatable Views, By Steve Bobrowski

Источник: http://www.oracle.com/oramag/oracle/01-mar/index.html?o21o8i.html

Стив Бобровский

Эта статья была создана на основе учебного курса, разработанного в компании Animated Learning, Inc./The Database Domain, которая проводит обучение, основанное на Web и CD, администраторов и разработчиков БД Oracle. Более подробную информацию можно получить по адресу: http://www.dbdomain.com

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

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

Введение

Предполагается, что вы имеете базовое понятие о структурах реляционных баз данных, включая таблицы и представления, а также об основных SQL-командах INSERT, UPDATE, DELETE и SELECT. Необходимо также знать, как создавать представления только для чтения, используя SQL-команду CREATE VIEW и как создавать триггеры базы данных, используя PL/SQL.

Для выполнения практических упражнений, приведенных в этой статье, нужно проделать следующие действия для создания тестовой схемы и настройки сессии SQL*Plus:

1. Создайте на вашем компьютере временную директорию c:\temp\courseware.
2. Перепишите скрипт поддержки во временную директорию. Скрипт поддержки можно скачать из ftp://ftp.dbdomain.com/m048.sql .
3. Запустите SQL*Plus.
4. В командной строке SQL*Plus запустите этот скрипт поддержки следующей командой:

SQL> @c:\temp\courseware\m048.sql (для пользователей Windows) или

SQL> @/tmp/courseware/m048.sql (для пользователей UNIX и Linux).

5. После запуска скрипта следуйте его инструкциям и подсказкам.
6. После успешного выполнения скрипта не отсоединяйтесь и не выходите из SQL*Plus, оставьте его открытым.
7. Продолжайте чтение статьи. Когда вы дойдете до упражнений, следуйте инструкциям по выполнению упражнения в текущей сессии SQL*Plus.
8. Поскольку для правильного выполнения практических упражнений, данные и настройки сессии должны иметь определенные значения, пожалуйста, выполняйте все упражнения в том порядке, в котором они появляются, не закрывая SQL*Plus.

Непосредственно обновляющие представления

Если явно не указывать, что создаваемое представление является представлением только для чтения, то Oracle создаст представление, которые можно использовать для вставки, обновления или удаления строк базовой таблицы – другими словами обновляющее представление. Однако, представление не является автоматически или непосредственно обновляющим (inherently updatable), если Oracle не может корректно выполнить операцию вставки, обновления или удаления данных через представление в таблицу, лежащую в основе представления.

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

Операции над множествами (UNION, UNION ALL, INTERSECT или MINUS)

Оператор DISTINCT

Групповые функции, такие как AVG, COUNT, MAX, MIN и т.д.

предложения GROUP BY, ORDER BY, CONNECT BY или START WITH

ссылку на выражение в списке SELECT

подзапрос в списке SELECT

запрос соединения (JOIN).

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

Упражнение 1. Создание непосредственно обновляющих представлений

Используя текущую сессию SQL*Plus, введите следующую команду для создания непосредственно обновляющего представления, основанного на таблице CUSTOMERS.

CREATE VIEW mail_labels AS
 SELECT firstname||' '||lastname AS name,
 companyname,
 address,
 city||', '||state||' '||zipcode AS place
 FROM customers;

В результате вы должны получить простое сообщение:

View created (Представление создано).

Вопрос: Объясните, почему представление ORDER_TOTALS, созданное следующей командой, не является непосредственно обновляющим.

CREATE VIEW order_totals
 (order_id, total)
 AS
 SELECT i.ord_ord_id,
 SUM(i.quantity * p.unitprice)
 FROM items i, parts p
 WHERE i.part_part_id = p.part_id
 GROUP BY i.ord_ord_id;

Ответ: Представление ORDER_TOTALS не является непосредственно обновляющим, потому что запрос, определяющий представление, содержит предложение GROUP BY.

Упражнение 2. Просмотр информации об обновляющем представлении

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

Используя SQL*Plus, введите следующий запрос для отображения информации об обновляемых столбцах нового представления MAIL_LABELS. Обратите внимание, что Oracle8i поддерживает операции INSERT, UPDATE и DELETE не для всех столбцов представления MAIL_LABELS.

SELECT column_name, updatable, insertable, deletable
 FROM user_updatable_columns
 WHERE table_name = 'MAIL_LABELS';

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

COLUMN_NAME UPD INS DEL
_______________ __ __ __
NAME NO NO NO
COMPANYNAME YES YES YES
ADDRESS YES YES YES
PLACE NO NO NO

Упражнение 3. Использование обновляющих представлений

Используя SQL*Plus, введите команду, представленную в Листинге 1, для проверки работоспособности нового представления MAIL_LABELS. Обратите внимание, что некоторые предложения выполняются, в то время как другие возвращают сообщение об ошибке из-за ограничений, связанных с определением этого представления.

Ограниченно обновляющие представления

При создании обновляющего представления можно добавить предложение WITH CHECK OPTION, чтобы создать ограниченное представление и запретить определенные операции вставки и обновления в представление. Рассмотрим, например, следующее ограниченное представление:

CREATE VIEW contacts_ca AS
 SELECT *
 FROM customers
 WHERE state = 'CA'
 WITH CHECK OPTION;

Представление с ограничениями позволяет вставлять и обновлять только такие строки, которые это представление может, в свою очередь, выбрать.

Упражнение 4. Создание ограниченно обновляющего представления

Используя SQL*Plus, введите следующую команду для создания ограниченного представления, сонованного на таблице CUSTOMERS.

CREATE VIEW contacts_ca AS
 SELECT *
 FROM customers
 WHERE state = 'CA'
 WITH CHECK OPTION;

В результате вы должны получить простое сообщение:

View created.

Упражнение 5. Использование ограниченно обновляющего представления

Используя SQL*Plus, введите следующие команды, чтобы посмотреть, как для ограниченно обновляющего представления CONTACTS_CA выполняются и не выполняются различные операторы UPDATE.

UPDATE contacts_ca
 SET address = '100 Skyview Place',
 city = 'Brentwood',
 zipcode = '92011'
 WHERE cust_id = 4;
 
UPDATE contacts_ca
 SET state = 'NY'
 WHERE cust_id = 4;
 
ROLLBACK;

Результаты выполнения:

SQL> UPDATE contacts_ca
 2 SET address = '100 Skyview Place',
 3 city = 'Brentwood',
 4 zipcode = '92011'
 5 WHERE cust_id = 4;
 
1 row updated.
 
SQL> UPDATE contacts_ca
 2 SET state = 'NY'
 3 WHERE cust_id = 4;
UPDATE contacts_ca
 *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
 
SQL> ROLLBACK;
 
Rollback complete.

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

Представление с соединением может быть обновляющим, но при этом накладываются некоторые дополнительные ограничения. По определению, запрос, описывающий представление с соединением, соединяет информацию из нескольких таблиц. Базовая таблица представления считается защищенной по ключу (key-preserved), если каждое значение первичного или уникального ключа базовой таблицы также является уникальным в результирующем множестве, выбираемом представлением с соединением - другими словами, если сущностная целостность базовой таблицы сохраняется представлением с соединением. Для лучшего понимания концепции защищенной по ключу таблицы в представлении с соединением, рассмотрим Рисунок 1.

Рисунок 1: Во-первых, проверим базовые таблицы и их первичные ключи. Целостность таблицы ORDERS обеспечивается ее первичным ключом, который включает столбец ORD_ID. Целостность таблицы ITEMS обеспечивается ее первичным ключом, который включает столбцы ORD_ORD_ID и ITEM_ID. Теперь проверим запрос, определяющий представление с соединением ORDER_ITEMS. Обратите внимание, что целостность таблицы ITEMS сохраняется в представлении с соединением, однако целостность таблицы ORDERS потеряна. Следовательно, таблица ITEMS считается защищенной по ключу таблицей по отношению к представлению ORDERS_ITEMS, в то время как таблица ORDERS таковой не является.

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

Кроме основных правил, применяемых для автоматически или непосредственно обновляющих представлений, для представлений с соединением, которые могут обновляться операторами INSERT, UPDATE и DELETE используются следующие правила:

Строки в представлении с соединением, не являющееся ограниченным (представление с соединением, созданное без предложения WITH CHECK OPTION), можно вставлять, если оператор INSERT вставляет данные только в одну базовую защищенную по ключу таблицу представления.

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

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

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

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

Используя SQL*Plus, введите следующие команды для создания обновляющего представления с соединением, а затем посмотрите, какие столбцы этого представления являются обновляемыми.

CREATE VIEW order_items AS
 SELECT o.ord_id,
 o.orderdate,
 i.item_id,
 i.part_part_id AS part_id,
 i.quantity
 FROM orders o, items i
 WHERE o.ord_id = i.ord_ord_id;
 
SELECT column_name, updatable, insertable, deletable
 FROM user_updatable_columns
 WHERE table_name = 'ORDER_ITEMS';
 
В результате должно получиться следующее:
 
SQL> CREATE VIEW order_items AS
 2 SELECT o.ord_id,
 3 o.orderdate,
 4 i.item_id,
 5 i.part_part_id AS part_id,
 6 i.quantity
 7 FROM orders o, items i
 8 WHERE o.ord_id = i.ord_ord_id;
 
View created.
 
SQL> SELECT column_name, updatable, insertable, deletable
 2 FROM user_updatable_columns
 3 WHERE table_name = 'ORDER_ITEMS';
 
COLUMN_NAME UPD INS DEL
___________ ___ ___ ___
ORDERDATE NO NO NO
ITEM_ID YES YES YES
PART_ID YES YES YES
QUANTITY YES YES YES
ORD_ID NO NO NO

Знакомство с триггерами INSTEAD OF

Все предыдущие разделы этой статьи подробно рассматривали непосредственно обновляющие представления. Рассмотрим теперь, как можно сделать любое представление обновляющим, вне зависимости от его сложности, используя триггеры INSTEAD OF.

Триггер INSTEAD OF является специальным типом триггера, который говорит Oracle, как обрабатывать DML-операцию (INSERT, UPDATE или DELETE), выполняемую представлением. Например, допустим, что у вас есть представление, которое соединяет информацию из двух различных таблиц. При выполнении в представлении оператора INSERT, содержащего поля обеих базовых таблиц, Oracle8i не будет знать точно, каким образом вставить эти новые строки в основные базовые таблицы. Создав триггер INSTEAD OF для представления, вы сможете точно сказать Oracle8i, как обрабатывать такие операторы INSERT. Для создания триггера INSTEAD OF для представления, используется следующий синтаксис SQL-команды CREATE TRIGGER:

CREATE [OR REPLACE] TRIGGER 
[schema.]trigger
 INSTEAD OF
 {DELETE|INSERT|UPDATE [OF column [,column]
... ]}
 [OR {DELETE|INSERT|UPDATE [OF column 
[,column] ... ]} ] ...
 ON [schema.]view
 ... trigger body ...

Замечание: Если вы создаете представление, определяющий запрос которого ссылается на представление, имеющее триггеры INSTEAD OF, то вы все равно должны создавать триггеры INSTEAD OF для нового представления, даже если новое представление является непосредственно обновляющим.

Упражнение 7. Создание триггера INSTEAD OF для обновляющего представления

Используя SQL*Plus, введите команды, представленные в Листинге 2, для создания представления, соединяющего поля из таблиц CUSTOMERS и ORDERS, и триггера INSTEAD OF для обработки операторов INSERT, адресованных этому представлению. Обратите внимание, что триггер выполняет два различных оператора INSERT с соответствующими полями для двух базовых таблиц представления.

Упражнение 8. Просмотр информации о представлении ORDER_INFO

Перед тем как проверять новое представление ORDER _INFO, и связанный с ним триггер INSTEAD OF, используйте SQL*Plus для ввода следующего запроса, который отобразит информацию об обновляемых столбцах представления.

SELECT column_name, updatable, insertable, deletable
 FROM user_updatable_columns
 WHERE table_name = 'ORDER_INFO';
В результате должно получиться следующее:
 
COLUMN_NAME UPD INS DEL
___________ ___ ___ ___
CUST_ID NO YES NO
COMPANYNAME NO YES NO
LASTNAME NO YES NO
FIRSTNAME NO YES NO
ORD_ID YES YES YES
ORDERDATE YES YES YES
SHIPDATE YES YES YES
PAIDDATE YES YES YES

Упражнение 9. Проверка триггера INSTEAD OF

Используя SQL*Plus, введите следующий оператор INSERT и последующие запросы для проверки работоспособности триггера INSTEAD OF, связанного с представлением ORDER_INFO.

INSERT INTO order_info
 ( cust_id,
 companyname,
 lastname,
 firstname,
 ord_id,
 orderdate )
VALUES
 ( 10,
 'Acme',
 'Schwartz',
 'Elsie',
 6,
 '13-DEC-97' ); 
 
SELECT cust_id, lastname
 FROM customers
 WHERE cust_id = 10;
 
SELECT ord_id, orderdate
 FROM orders
 WHERE ord_id = 6;

В результате должно получиться следующее:

SQL> INSERT INTO order_info
 2 ( cust_id,
 3 companyname,
 4 lastname,
 5 firstname,
 6 ord_id,
 7 orderdate )
 8 VALUES
 9 ( 10,
 10 'Acme',
 11 'Schwartz',
 12 'Elsie',
 13 6,
 14 '13-DEC-97' );
 
1 row created.
 
SQL> SELECT cust_id, lastname
 2 FROM customers
 3 WHERE cust_id = 10;
 
 CUST_ID LASTNAME
 _____ _______
 10 Schwartz
 
SQL> SELECT ord_id, orderdate
 2 FROM orders
 3 WHERE ord_id = 6;
 
 ORD_ID ORDERDATE
 _____ ________
 6 13-DEC-97

Заключение

Помните следующие важные моменты:

Представление не является непосредственно обновляющим, за исключением случаев, когда Oracle8i может корректно выполнить операторы INSERT, UPDATE или DELETE через представление в базовую таблицу, на которой оно основано.

Представление с ограничением (WITH CHECK OPTION) позволяет выполнять только те операторы INSERT, UPDATE и DELETE которые создают строки, которые это представление может в свою очередь выбрать.

Кроме основных правил для непосредственно обновляющих представлений, представление с соединением, которое является непосредственно обновляющим операторами INSERT, UPDATE и DELETE должно удовлетворять дополнительным ограничениям.

Любое представление можно сделать обновляющим, вне зависимости от его сложности, используя триггеры INSTEAD OF.

Контрольные вопросы и ответы

Упражнение 2:

Вопрос: Объясните, почему столбцы NAME и PLACE представления MAIL_LABELS, созданного в упражнении 1, закрыты для вставки, обновления и удаления.

Ответ: Столбцы NAME и PLACE не являются обновляемыми, потому что они являются виртуальными столбцами, то есть при создании представления в списке SELECT определяющего запроса для этих столбцов использовались выражения.

Упражнение 4:

Вопрос: Почему Oracle возвращает ошибку при попытке выполнить следующее предложение?

CREATE VIEW contacts_ca AS
 SELECT *
 FROM customers
 WHERE state = 'CA'
 WITH READ ONLY
 WITH CHECK OPTION;

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

Упражнение 5:

Вопрос: Почему Oracle выполняет первый оператор UPDATE в этом упражнении, но запрещает выполнение второго оператора UPDATE?

Ответ: После выполнения первого оператора UPDATE представление может возвратить эту строку из базовой таблицы CUSTOMERS, поскольку значением столбца STATE в обновленной пользователем записи остается CA. Однако Oracle не выполняет второй оператор UPDATE, потому что значением столбца STATE в обновленной пользователем записи становится NY, и представление не может больше выбирать эту запись.

Упражнение 6:

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

Ответ: Представление сохраняет целостность базовой таблицы ITEMS, но не сохраняет целостность базовой таблицы ORDERS.

Упражнение 8:

Вопрос: Почему для всех столбцов представления ORDER_INFO запрещено обновление и удаление, хотя для представления существует триггер INSTEAD OF?

Ответ: Триггер INSTEAD OF для представления не обрабатывает операторы UPDATE и DELETE, только операторы INSERT.

Упражнение 9:

Вопрос: Можете ли вы объяснить, что происходит, когда выполняется оператор INSERT в этом упражнении?

Ответ: Оператор INSERT обращается к представлению ORDER_INFO, которое имеет соответствующий триггер INSTEAD OF, ORDER_INFO _INSERT, для обработки оператора INSERT. В теле триггера ORDER_INFO_INSERT значения оператора INSERT, вызвавшего триггер, вставляются в две базовые таблицы представления CUSTOMERS и ORDERS двумя отдельными операторами INSERT.

Вопрос: Что произойдет, если для представления ORDER_INFO не будет создан триггер ORDER_INFO_INSERT, перед тем как выполнить оператор INSERT из предыдущего примера?

Ответ: Oracle вернет ошибку, поскольку он не сможет вставить значения оператора INSERT в представление ORDER_INFO, которое соединяет данные из двух таблиц.

Приложения

 
Листинг 1
Описание таблицы recipes.
 
SQL> DESC recipes
 
 Name Null? Type
 ----------------- --------- ---------------
 ID NOT NULL NUMBER
 NAME NOT NULL VARCHAR2(100)
 PREP_TIME_MINUTES NUMBER
 SERVINGS NUMBER
 DESCRIPTION VARCHAR2(1000)
 COOKING_INSTRUCTIONS CLOB
 DISH_IMAGE ORDSYS.ORDIMAGE
 
 

 
Листинг 2
Создание триггера INSTEAD OF для обновляющего представления 
 
CREATE VIEW order_info
 AS
 SELECT c.cust_id,
 c.companyname,
 c.lastname,
 c.firstname,
 o.ord_id,
 o.orderdate,
 o.shipdate,
 o.paiddate
 FROM customers c, orders o
 WHERE c.cust_id = o.cust_cust_id;
 
CREATE OR REPLACE TRIGGER order_info_insert
INSTEAD OF INSERT ON order_info
DECLARE
 duplicate_info EXCEPTION;
 PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
_ Insert appropriate fields into each base table.
_ Exception handler returns error messages for
_ attempts to insert duplicate information.
 INSERT INTO customers
 (cust_id, companyname, lastname, firstname)
 VALUES (
 :new.cust_id,
 :new.companyname,
 :new.lastname,
 :new.firstname );
 INSERT INTO orders
 (ord_id, orderdate, shipdate, paiddate)
 VALUES (
 :new.ord_id,
 :new.orderdate,
 :new.shipdate,
 :new.paiddate );
EXCEPTION
 WHEN duplicate_info THEN
 RAISE_APPLICATION_ERROR (
 num => -20107,
 msg => 'Duplicate customer or order ID' );
END order_info_insert;
/
 
View created.
Trigger created.
 
 

Об авторе:
Стив Бобровский является главным администратором компании Animated Learning (http://www.animatedlearning.com). Он автор книг издательства Oracle Press “Архитектура Oracle8”, “Oracle8i для Windows NT. Руководство для начинающих”, “Oracle8i для Linux. Руководство для начинающих”. Познакомиться с описанием этих книг можно в он-лайне по адресу www.osborne.com/oracle/