W Cat - SQL за 24 часа

FROM ИМЯ_ТАБЛИЦЫ

[ WHERE ВЫРАЖЕНИЕ1 [, ВЫРАЖЕНИЕ2 ]]

[ WITH CHECK OPTION ]

[ GROUP BY ]

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

CREATE VIEW CUSTOMERS AS

SELECT *

FROM CUSTOMER_TBL;

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

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

CREATE VIEW EMP_VIEW AS

SELECT LAST_NAME, FIRST_NAME, MIDDLE_NAME

FROM EMPLOYEE_TBL;

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

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

CREATE VIEW NAMES AS

SELECT LAST_NAME || ', ' || FIRST_HAME || ' ' || MIDDLE_NAME

NAME

FROM EMPLOYEE_TBL;

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

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

SELECT *

FROM NAMES;

NAME

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

STEPHENS, TINA D

PLEW, LINDA С

GLASS, BRANDON S

GLASS, JACOB

WALLACE, MARIAH

SPURGEON, TIFFANY

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

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

CREATE VIEW CITY_PAY AS

SELECT E.CITY, AVG(P.PAY_RATE) AVG_PAY

FROM EMPLOYEEJTBL E

EMPLOYEE_PAY_TBL P

WHERE E.EMP_ID = P.EMP_ID

GROUP BY E.CITY;

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

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

SELECT *

FROM CITYJPAY;

CITY AVG_PAY

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

GREENWOOD

INDIANAPOLIS 13.33333

WHITELAND

3 строки выбраны.

После создания представления его использование в операторах SELECT позволяет их значительно упростить.

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

CREATE VIEW ИМЯ_ПРЕДСТАВЛЕНИЯ AS

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

FROM ИМЯ_ТАБЛИЦЫ1, ИМЯ_ТАБЛИЦЫ2 [ , ИМЯ_ТАБЛИЦЫЗ ]

WHERE ИМЯ_ТАБЛИЦЫ1 = ИМЯ_ТАБЛИЦЫ2

[ AND ИМЯ_ТАБЛИЦЫ1 = ИМЯ_ТАБЛИЦЫЗ ]

[ ВЫРАЖЕНИЕ1 ][, ВЫРАЖЕНИЕ2 ]

WITH CHECK OPTION ]

[GROUP BY ]

Вот пример создания представления на основе данных нескольких таблиц.

CREATE VIEW EMPLOYEE_SUMMARY AS

SELECT E.EMP_ID, E.LAST_NAME, P.POSITION, P.DATE_HIRE,

P. PAY_RATE

FROM EMPLOYEEJTBL E

EMPLOYEE_PAY_TBL P

WHERE E.EMP_ID = P.EMP_ID;

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

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

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

CREATE VIEW ПРЕДСТАВЛЕНИЕ2 AS

SELECT * FROM ПРЕДСТАВЛЕНИЕ1

Новые представления на основе уже созданных можно создавать с достаточной глубиной зависимости (представление на основе представления на основе представления...), определяемой ограничениями конкретной реализации языка. Единственной проблемой при создании новых представлений на основе уже имеющихся является их управляемость. Например, если вы сначала создадите представление VIEW2 на основе представления VIEWI, представление VIEWS на основе представления VIEW2, а затем удалите представление VIEWI, то с представлениями VIEW2 и VIEWS возникнут проблемы, поскольку информация, на которые ссылаются эти представления, будет недоступна. Поэтому необходимо хорошо представлять себе место представлений в базе данных и то, на основе каких объектов эти представления созданы. Зависимость представлений схематически показана на рис. 20.2.

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

Рис. 20.2.Схема зависимости представлений

На рис. 20.2 показана схема зависимости представлений, основывающихся не только на таблицах, но и на других представлениях. ПРЕДСТАВЛЕНИЕ1 и ПРЕДСТАВЛЕНИЕ2 зависят от таблицы ТАБЛИЦА. ПРЕДСТАВЛЕНИЕЗ зависит от представления ПРЕДСТАВЛЕНИЕ1. ПРЕДСТАВЛЕНИЕ4 зависит от представлений ПРЕДСТАВЛЕНИЕ1 и ПРЕДСТАВЛЕНИЕ2. ПРЕДСТАВЛЕНИЕ5 зависит от представления ПРЕДСТАВЛЕНИЕ2. На основе анализа зависимостей можно заключить следующее.

• Если удалить ПРЕДСТАВЛЕНИЕ 1, несостоятельными станут ПРЕДСТАВЛЕНИЕЗ и ПРЕДСТАВЛЕНИЕ4.

• Если удалить ПРЕДСТАВЛЕНИЕ2, несостоятельными станут ПРЕДСТАВЛЕНИЕ4

и ПРЕДСТАВЛЕНИЕЗ.

• Если будет удалена ТАБЛИЦА, несостоятельными станут все представления.

Опция 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, поскольку в таком случае гарантируется ссылочная целостность данных лежащей в основе представлений таблицы.