W Cat - SQL за 24 часа Страница 50

Опция WITH CHECK OPTION является опцией оператора CREATE VIEW. Эта опция применяется тогда, когда необходимо гарантировать, что все применяемые операторы UPDATE и INSERT удовлетворяют указанным в определении представления условиям. Если при этом указанные условия будут не удовлетворены, оператор UPDATE или INSERT возвратит ошибку. Опция WITH CHECK OPTION имеет свои собственные опции CASCADED и LOCAL. Опция WITH CHECK OPTION фактически гарантирует ссылочную целостность данных путем проверки отсутствия нарушений определения представления при обновлении данных.

Вот пример создания представления с использованием опции WITH CHECK OPTION.

CREATE VIEW EMPLOYEE_PAGERS AS

SELECT LAST_NAME, FIRST_NAME, PAGER

FROM EMPLOYEE_TBL

WHERE PAGER IS NOT NULL

WITH CHECK OPTION;

Представление создано.

Здесь опция WITH CHECK OPTION запрещает ввод значений NULL в столбец PAGER представления, поскольку представление определяется данными, не содержащими значения NULL в столбце PAGER.

Теперь попробуем ввести значение NULL в столбец PAGER.

INSERT INTO EMPLOYE_PAGERS

VALUES ('SMITH','JOHN',NULL);

INSERT INTO EMPLOYEE_PAGERS

*

ERROR at line 1:

ORA-01400: mandatory (NOT NULL) column is missing or NULL

during insert

Опция WITH CHECK OPTION работает.

При использовании опции WITH CHECK OPTION в операторе создания представления из другого представления предлагается также использовать CASCADED или LOCAL.

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

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

• Представление не должно содержать связей.

• Представление не должно содержать выражения GROUP BY.

• Представление не должно содержать ссылок на псевдостолбец ROWNUM.

• Представление не должно содержать итоговых функций.

• Не должно использоваться выражение DISTINCT.

• В выражении WHERE не должно быть вложенных выражений со ссылками на ту же таблицу, на которую ссылается выражение FROM.

По поводу синтаксиса оператора UPDATE обратитесь к тексту урока 14, "Использование подзапросов".

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

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

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

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

CREATE TABLE ИМЯ_ТАБЛИЦЫ AS

SELECT {* | СТОЛБЕЦ1 [, СТОЛБЕЦ2 ]}

FROM ИМЯ_ПРЕДСТАВЛЕНИЯ

[ WHERE УСЛОВИЕ1 [, УСЛОВИЕ2 ]]

[ ORDER BY ]

Для примера создадим представление на основе двух таблиц.

CREATE VIEW ACTIVE_CUSTOMERS AS

SELECT С.*

FROM CUSTOMER_TBL С

ORDERS_TBL О

WHERE C.CUST_ID = O.CUST_ID;

Представление создано.

Теперь создадим таблицу на основе только что созданного представления.

CREATE TABLE CUSTOMER_ROSTER_TBL AS

SELECT CUST_ID, CUST_NAME

FROM ACTIVE_CUSTOMERS;

Таблица создана.

Наконец, выберем данные новой таблицы точно так же, как любой другой.

SELECT *

FROM CUSTOMER_ROSTER_TBL;

CUST_ID CUST_NAME

-----------------------------------

232 LESLIE GLEASON

12 MARYS GIFT SHOP

43 SCHYLERS NOVELTIES

090 WENDY WOLF

287 GAVINS PLACE

432 SCOTTYS MARKET

6 строк выбраны.

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

В операторе CREATE VIEW использовать ключевое слово ORDER BY нельзя, но в операторе CREATE VIEW можно использовать ключевое слово GROUP BY, дающее тот же результат, что и ORDER BY.

Использовать выражение ключевого слова ORDER BY в операторе SELECT, осуществляющем запрос к представлению, выгоднее и проще, чем использовать GROUP BY в операторе CREATE VIEW.

Рассмотрим следующий пример использования ключевого слова GROUP BY в операторе CREATE VIEW.

CREATE VIEW NAMES2 AS

SELECT LAST_NAME || ', ' || FIRST_NAME || ' ' || MIDDLE_NAME

NAME

FROM EMPLOYEE_TBL

GROUP BY LAST_NAME || ', ' || FIRST_NAME || ' ' ||

MIDDLE_NAME;

Представление создано.

Теперь если выбрать все данные только что созданного представления, они будут представлены в алфавитном порядке (поскольку данные были сгруппированы по

SELECT *

FROM NAMES2;

NAME

------------------------

GLASS, BRANDON S

GLASS, JACOB

PLEW, LINDA С

SPURGEON, TIFFANY

STEPHENS, TINA D

WALLACE, MARIAH

6 строк выбраны.

Для удаления представлений из базы данных используется команда DROP VIEW. У этой команды есть две опции - RESTRICT и CASCADE. Если используется RESTRICT и в условиях имеются зависимые представления, то оператор DROP VIEW возвращает ошибку. При использовании опции CASCADE и наличии зависимых представлений оператор DROP VIEW завершается успешно и все зависимые представления тоже удаляются. Например,

DROP VIEW NAMES2;

Представление удалено.

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

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

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

Общий синтаксис оператора для создания синонимов следующий.

CREATE [PUBLIC]PRIVATE] SYNONYM ИМЯ_СИНОНИМА FOR

ТАБЛИЦА |ПРЕДСТАВЛЕНИЕ

В следующем примере создается синоним с именем CUST для таблицы CUSTOMER_TBL. Это позволит не печатать полное имя таблицы каждый раз при ее использовании.

CREATE SYNONYM CUST FOR CUSTOMER_TBL;

Синоним создан.

SELECT CUST_NAME FROM CUST;

CUST_NAME

---------------------------

LESLIE GLEASON

NANCY BUNKER

ANGELA DOBKO

WENDY WOLF

MARYS GIFT SHOP

SCOTTYS MARKET

JASONS AND DALLAS GOODIES

MORGAN CANDIES AND TREATS

SCHYLERS NOVELTIES

GAVINS PLACE

HOLLYS GAMEARAM

HEALTHERS FEATHERS AND THINGS

RAGANS HOBBIES

ANDYS CANDIES

RYANS STUFF

15 строк выбраны.

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