Что такое представления VIEWS в базах данных? И зачем они нужны? Создание и использование представлений

SQL -представление (SQL view) - это виртуальная таблица, составленная из других таблиц или представлений. Представление не имеет своих собственных данных, а объединяет данные из таблиц или представлений, которые в него входят. Представления создаются с помощью операторов SELECT и имеют множество применений:

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

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

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

Sql индексы

Индекс (англ. index) - объект базы данных, создаваемый с целью повышения производительности поиска данных. Таблицы в базе данных могут иметь большое количество строк, которые хранятся в произвольном порядке, и их поиск по заданному критерию путем последовательного просмотра таблицы строка за строкой может занимать много времени. Индекс формируется из значений одного или нескольких столбцов таблицы и указателей на соответствующие строки таблицы и, таким образом, позволяет искать строки, удовлетворяющие критерию поиска. Ускорение работы с использованием индексов достигается в первую очередь за счёт того, что индекс имеет структуру, оптимизированную под поиск - например, сбалансированного дерева.

Триггеры

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

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

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

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

Хранимые процедуры

Хранимая процедура - это последовательность компилированных операторов Transact-SQL, хранящихся в системной базе данных SQL Server. Хранимые процедуры предварительно откомпилированы, поэтому эффективность их выполнения выше, чем у обычных запросов. Хранимые процедуры работают непосредственно на сервере и хорошо укладываются в модель клиент - сервер.

Существует два вида хранимых процедур: системные и пользовательские.

Системные хранимые процедуры предназначены для получения информации из системных таблиц и выполнения различных служебных операций и особенно полезны при администрировании базы данных. Их имена начинаются с sp_ (stored procedure).

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

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

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

Компоненты, ссылающиеся на объекты БД (таблицы, индексы, представления и т. п.), сопоставляются с этими объектами с предварительной проверкой их существования. Этот процесс носит название раз решение ссылок .

В системной таблице syscomments сохраняется исходный текст процедуры, а в таблице sysobjects - ее название.

Создается предварительный план выполнения запроса. Этот предварительный план называется нормализованным планом или деревом запроса и хранится в системной таблице sysprocedures.

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

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

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

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

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

3. Хранимые процедуры могут принимать аргументы при запуске и возвращать значения (в виде результирующих наборов данных).

4. Хранимые процедуры могут запускаться по расписанию (в режиме автоматического выполнения), задаваемому при запуске SQL Server.

5. Хранимые процедуры используются для извлечения или изменения данных в любое время.

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

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

1) Понятие представления
Представления (View) – это объекты БД, которые не содержат собственных таблиц, но их содержимое берется из других таблиц или представлений посредством выполнения запроса.

2) Создание представлений
CREATE VIEW
[()]
AS
CHECK OPTION]

Примечания:
В SQL Server текст представления можно зашифровать с помощью опции WITH ENCRYPTION, указав её после имени представления.

3) Удаление представлений
DROP VIEW CASDADE|RESTRICT

Примечание:
RESTRICT – не должно существовать никаких ссылок на удаляемое представление в представлении и ограничениях, иначе в удалении будет отказано.
CASADE – означает удаление всех объектов, ссылающихся на данное представление.

4) Ключевые слова
a) RECURSIVE
Создается представление, которое получает значения из себя самого.
b) WITH CHECK OPTION
Запрещает обновление таблиц, на основе представлений, если изменяемые или добавляемые данные не отражаются в представлении.
Запрет действует только на значения, не подпадающие под условия, указанные в разделе WHERE .
c) LOCAL
Контролирует, чтобы изменения в базовых таблицах отражались только в текущем представлении.
d) CASCADED
Контролирует отражение изменений во всех представлениях, определенных на данном представлении.

5) Ограничения и особенности
1. Имена столбцов обычно указываются тогда, когда некоторые столбцы являются вычисляемыми и, следовательно, не поименованы, а также тогда, когда два или более столбца имеют одинаковые имена в соответствующих таблицах в запросе. В InterBase всегда.
2. В ряде СУБД нельзя использовать раздел ORDER BY, обеспечивающий сортировку.
3. Представления можно соединять как с базовыми таблицами, так и с другими представлениями с помощью запросов к обоим объектам.

6) Критерии обновляемости представлений
1. Оно должно базироваться только на одной таблице. Желательно, чтобы оно включало первичный ключ таблицы.
2. Оно не должно содержать столбцов, полученных в результате применения функций агрегирования.
3. Оно не может содержать спецификацию DISTINCT в своем определении.
4. Оно не может использовать GROUP BY или HAVING в своем определении.
5. Оно не должно содержать подзапросов.
6. Если оно определено на другом представлении, то и оно должно быть обновляемым.
7. Оно не может включать константы, строки или выражения в списке выходных полей. Перестановка и переименование полей не допустима.
8. Для оператора INSERT оно должно включать любые поля из лежащей в основе представлений базовой таблицы, которые имеют ограничения NOT NULL, однако в качестве значения по умолчанию может быть указано другое значение.

7) Примеры

1. CREATE VIEW LondonStaff
AS SELECT * FROM SalesPeople WHERE City=’London’

2. CREATE VIEW SalesOwn
AS SELECT SNum, SName, City FROM SalesPeople

3. CREATE VIEW NameOrders
AS SELECT ONum, Amt, A.SNum, SName, CName
FROM Orders A, Customer B, SalesPeople C
WHERE A.CNum=B.CNum AND A.SNUM=C.SNum

Примеры на запрет обновления:

1. CREATE VIEW HighRating AS SELECT CNum, Rating

2. Добавляем строку, которую представление не видит:
INSERT INTO HighRating VALUES(2018, 200)
3. Запрещаем добавлять строки вне видимости:
CREATE VIEW HighRating AS SELECT CNum, Rating
FROM Customer WHERE Rating=300
WITH CHECK OPTION
4. Создаем новое, которое разрешает вновь добавлять:
CREATE VIEW MyRating AS SELECT * FROM HighRating

Дается понятие представлений. Определяется роль представлений в вопросах безопасности данных. Описывается процесс управления представлениями: создание, изменение, применение, удаление представлений.

Определение представления

Представления , или просмотры (VIEW ), представляют собой временные, производные (иначе - виртуальные) таблицы и являются объектами базы данных, информация в которых не хранится постоянно, как в базовых таблицах, а формируется динамически при обращении к ним. Обычные таблицы относятся к базовым, т.е. содержащим данные и постоянно находящимся на устройстве хранения информации. Представление не может существовать само по себе, а определяется только в терминах одной или нескольких таблиц. Применение представлений позволяет разработчику базы данных обеспечить каждому пользователю или группе пользователей наиболее подходящие способы работы с данными, что решает проблему простоты их использования и безопасности. Содержимое представлений выбирается из других таблиц с помощью выполнения запроса, причем при изменении значений в таблицах данные в представлении автоматически меняются. Представление - это фактически тот же запрос, который выполняется всякий раз при участии в какой-либо команде. Результат выполнения этого запроса в каждый момент времени становится содержанием представления . У пользователя создается впечатление, что он работает с настоящей, реально существующей таблицей.

У СУБД есть две возможности реализации представлений . Если его определение простое, то система формирует каждую запись представления по мере необходимости, постепенно считывая исходные данные из базовых таблиц. В случае сложного определения СУБД приходится сначала выполнить такую операцию, как материализация представления , т.е. сохранить информацию, из которой состоит представление , во временной таблице. Затем система приступает к выполнению пользовательской команды и формированию ее результатов, после чего временная таблица удаляется.

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

Создания и изменения представлений в стандарте языка и реализации в MS SQL Server совпадают и представлены следующей командой:

<определение_просмотра> ::= { CREATE| ALTER} VIEW имя_просмотра [(имя_столбца [,...n])] AS SELECT_оператор

Рассмотрим назначение основных параметров.

По умолчанию имена столбцов в представлении соответствуют именам столбцов в исходных таблицах. Явное указание имени столбца требуется для вычисляемых столбцов или при объединении нескольких таблиц, имеющих столбцы с одинаковыми именами. Имена столбцов перечисляются через запятую, в соответствии с порядком их следования в представлении .

Параметр WITH ENCRYPTION предписывает серверу шифровать SQL-код запроса, что гарантирует невозможность его несанкционированного просмотра и использования. Если при определении представления необходимо скрыть имена исходных таблиц и столбцов, а также алгоритм объединения данных, необходимо применить этот аргумент.

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

Пример 10.1. Показать в представлении клиентов из Москвы.

Создание представления:

Выборка данных из представления:

SELECT * FROM view1

Обращение к представлению осуществляется с помощью оператора SELECT как к обычной таблице.

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

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

Выполним команду:

INSERT INTO view1 VALUES (12,"Петров", "Самара")

Это допустимая команда в представлении , и строка будет добавлена с помощью представления view1 в таблицу Клиент . Однако, когда информация будет добавлена, строка исчезнет из представления , поскольку название города отлично от Москвы. Иногда такой подход может стать проблемой, т.к. данные уже находятся в таблице, но пользователь их не видит и не в состоянии выполнить их удаление или модификацию. Для исключения подобных моментов служит WITH CHECK OPTION в определении представления . Фраза размещается в определении представления , и все команды модификации будут подвергаться проверке.

Для такого представления вышеупомянутая вставка значений будет отклонена системой.

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

Представление удаляется командой:

DROP VIEW имя_просмотра [,...n]

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

Не все представления в SQL могут быть модифицированы. Модифицируемое представление определяется следующими критериями:

основывается только на одной базовой таблице;
содержит первичный ключ этой таблицы;
не содержит DISTINCT в своем определении;
не использует GROUP BY или HAVING в своем определении;
по возможности не применяет в своем определении подзапросы;
не использует константы или выражения значений среди выбранных полей вывода;
в просмотр должен быть включен каждый столбец таблицы, имеющий атрибут NOT NULL ;
оператор SELECT просмотра не использует агрегирующие (итоговые) функции, соединения таблиц, хранимые процедуры и функции, определенные пользователем;
основывается на одиночном запросе, поэтому объединение UNION не разрешено.

Если просмотр удовлетворяет этим условиям, к нему могут применяться операторы INSERT , UPDATE , DELETE . Различия между и представлениями , предназначенными только для чтения, не случайны. Цели, для которых их используют, различны. С модифицируемыми представлениями в основном обходятся точно так же, как и с базовыми таблицами. Фактически, пользователи не могут даже осознать, является ли объект, который они запрашивают, базовой таблицей или представлением , т.е. прежде всего это средство защиты для сокрытия конфиденциальных или не относящихся к потребностям данного пользователя частей таблицы. Представления в режиме <только для чтения> позволяют получать и форматировать данные более рационально. Они создают целый арсенал сложных запросов, которые можно выполнить и повторить снова, сохраняя полученную информацию. Результаты этих запросов могут затем использоваться в других запросах, что позволит избежать сложных предикатов и снизить вероятность ошибочных действий.

CREATE VIEW view2 AS SELECT Клиент.Фамилия, Клиент.Фирма, Сделка.Количество FROM Клиент INNER JOIN Сделка ON Клиент.КодКлиента=Сделка.КодКлиента Пример 10.3. Немодифицируемое представление с данными из разных таблиц.

CREATE VIEW view3(Тип, Общ_остаток) AS SELECT Тип, Sum(Остаток) FROM Товар GROUP BY Тип Пример 10.4. Немодифицируемое представление с группировкой и итоговыми функциями.

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

CREATE VIEW view4(Код, Название, Тип, Цена, Налог) AS SELECT КодТовара, Название, Тип, Цена, Цена*0.05 FROM Товар Пример 10.5. Модифицируемое представление с вычислениями.

Преимущества и недостатки представлений

Механизм представления - мощное средство СУБД, позволяющее скрыть реальную структуру БД от некоторых пользователей за счет определения представлений . Любая реализация представления должна гарантировать, что состояние представляемого отношения точно соответствует состоянию данных, на которых определено это представление . Обычно вычисление представления производится каждый раз при его использовании. Когда представление создается, информация о нем записывается в каталог БД под собственным именем. Любые изменения в данных адекватно отобразятся в представлении - в этом его отличие от очень похожего на него запроса к БД. В то же время запрос представляет собой как бы <мгновенную фотографию> данных и при изменении последних запрос к БД необходимо повторить. Наличие представлений в БД необходимо для обеспечения логической независимости данных. Если система обеспечивает физическую независимость данных, то изменения в физической структуре БД не влияют на работу пользовательских программ. Логическая независимость подразумевает тот факт, что при изменении логической структуры данных влияние на пользовательские программы также не оказывается, а значит, система должна уметь решать проблемы, связанные с ростом и реструктуризацией БД. Очевидно, что с увеличением количества данных, хранимых в БД, возникает необходимость ее расширения за счет добавления новых атрибутов или отношений - это называется ростом БД. Реструктуризация данных подразумевает сохранение той же самой информации, но изменяется ее расположение, например, за счет перегруппировки атрибутов в отношениях. Предположим, некоторое отношение в силу каких-либо причин необходимо разделить на два. Соединение полученных отношений в представлении воссоздает исходное отношение, а у пользователя складывается впечатление, что никакой реструктуризации не производилось. Помимо решения проблемы реструктуризации представление можно применять для просмотра одних и тех же данных разными пользователями и в различных вариантах. С помощью представлений пользователь имеет возможность ограничить объем данных для удобства работы. Наконец, механизм представлений позволяет скрыть служебные данные, не интересные пользователям.

В случае выполнения СУБД на отдельно стоящем персональном компьютере использование представлений обычно имеет целью лишь упрощение структуры запросов к базе данных. Однако в случае многопользовательской сетевой СУБД представления играют ключевую роль в определении структуры базы данных и организации защиты информации. Рассмотрим основные преимущества применения представлений в подобной среде.

Независимость от данных

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

Актуальность

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

Повышение защищенности данных

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

Снижение стоимости

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

Дополнительные удобства

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

Возможность настройки

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

Обеспечение целостности данных

Если в операторе CREATE VIEW будет указана фраза WITH CHECK OPTION , то СУБД станет осуществлять контроль за тем, чтобы в исходные таблицы базы данных не была введена ни одна из строк, не удовлетворяющих предложению WHERE в определяющем запросе. Этот механизм гарантирует целостность данных в представлении .

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

Однако использование представлений в среде SQL не лишено недостатков .

Ограниченные возможности обновления

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

Структурные ограничения

Структура представления устанавливается в момент его создания. Если определяющий запрос представлен в форме SELECT * FROM_ , то символ * ссылается на все столбцы, существующие в исходной таблице на момент создания представления . Если впоследствии в исходную таблицу базы данных добавятся новые столбцы, то они не появятся в данном представлении до тех пор, пока это представление не будет удалено и вновь создано.

Снижение производительности

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

В предыдущих статьях инструкции DDL и DML рассматривались применительно к базовым таблицам. Данные базовой таблицы хранятся на диске. В отличие от базовых таблиц, представления по умолчанию не существуют физически, т.е. их содержимое не сохраняется на диске. Это не относится к так называемым индексированным представлениям, которые рассматриваются позже. Представления (views) - это объекты базы данных, которые всегда создаются на основе одной или более базовых таблиц (или других представлений), используя информацию метаданных. Эта информация (включая имя представления и способ получения строк из базовых таблиц) - все, что сохраняется физически для представления. По этой причине представления также называются виртуальными таблицами.

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

Представление создается посредством инструкции CREATE VIEW , синтаксис которой выглядит следующим образом:

CREATE VIEW view_name [(column_list)] AS select_statement Соглашения по синтаксису

Инструкция CREATE VIEW должна быть единственной инструкцией пакета. (Это означает, что эту инструкцию следует отделять от других инструкций группы посредством инструкции GO.)

Параметр view_name задает имя определяемого представления, а в параметре column_list указывается список имен, которые будут использоваться в качестве имен столбцов представления. Если этот необязательный параметр опущен, то используются имена столбцов таблиц, по которым создается представление. Параметр select_statement задает инструкция SELECT, которая извлекает строки и столбцы из таблиц (или других представлений). Параметр WITH ENCRYPTION задает шифрование инструкции SELECT, повышая таким образом уровень безопасности системы баз данных.

Предложение SCHEMABINDING привязывает представление к схеме таблицы, по которой оно создается. Когда это предложение указывается, имена объектов баз данных в инструкции SELECT должны состоять из двух частей, т.е. в виде schema.db_object, где schema - владелец, а db_object может быть таблицей, представлением или определяемой пользователем функцией.

Любая попытка модифицировать структуру представлений или таблиц, на которые ссылается созданное таким образом представление, будет неудачной. Чтобы такие таблицы или представления можно было модифицировать (инструкцией ALTER) или удалять (инструкцией DROP), нужно удалить это представление или убрать из него предложение SCHEMABINDING.

Когда при создании представления указывается параметр VIEW_METADATA , все его столбцы можно обновлять (за исключением столбцов с типом данных timestamp), если представление имеет триггеры INSERT или UPDATE INSTEAD OF.

Инструкция SELECT в представлении не может содержать предложение ORDER BY или параметр INTO. Кроме этого, по временным таблицам нельзя выполнять запросы.

Представления можно использовать для разных целей:

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

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

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

В примере ниже показано создание представления:

Запрос в этом примере выбирает из таблицы Works_on строки, удовлетворяющие условию Job="Консультант". Представление view_Consultant определяется строками и столбцами, возвращаемыми этим запросом. На рисунке ниже отображена таблица Works_on, в которой строки, выбранные в представлении view_Consultant, выделены красным цветом:

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

USE SampleDb; GO CREATE VIEW view_WithoutBudget AS SELECT Number, ProjectName FROM Project;

Запрос в этом примере выбирает для включения в представление view_WithoutBudget все столбцы таблицы Project, за исключением столбца Budget.

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

если столбец представления создается из выражения или агрегатной функции;

если два или больше столбцов представления имеют одинаковое имя в базовой таблице.

В примере ниже показано создание представления, для которого явно указываются имена столбцов:

USE SampleDb; GO CREATE VIEW view_Count(projectNumber, countProject) AS SELECT ProjectNumber, COUNT(*) FROM Works_on GROUP BY ProjectNumber;

Здесь имена столбцов представления view_Count должны быть указаны явно по той причине, что инструкция SELECT содержит агрегатную функцию count(*), которая требует, чтобы все столбцы представления были именованы.

Не требуется явно указывать список столбцов в инструкции CREATE VIEW, если применить заголовки столбцов, как это показано в примере ниже:

USE SampleDb; GO CREATE VIEW view_Count1 AS SELECT ProjectNumber, COUNT(*) countProject FROM Works_on GROUP BY ProjectNumber;

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

Представление view_project_p2 в примере ниже создается из представления view_Consultant. Все запросы, использующие представление view_project_p2, преобразовываются в эквивалентные запросы к базовой таблице Works_on.

Представления можно также создавать посредством среды Management Studio. Для этого выберите в обозревателе объектов базу данных, в которой требуется создать представление, щелкните в ней правой кнопкой мыши узел Views и в открывшемся контекстном меню выберите пункт New View. Откроется редактор представлений, в котором можно выполнять следующие действия:

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

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

Изменение и удаление представлений

Для изменения определения представления в языке Transact-SQL применяется инструкция ALTER VIEW . Синтаксис этой инструкции аналогичен синтаксису инструкции CREATE VIEW, применяющейся для создания представления.

Использование инструкции ALTER VIEW позволяет избежать переназначения существующих разрешений для представления. Кроме этого, изменение представления посредством этой инструкции не влияет на объекты базы данных, зависящие от этого представления. Если же модифицировать представление, сначала удалив его (инструкция DROP VIEW), а затем создав новое представление с требуемыми свойствами (инструкция CREATE VIEW), то все объекты базы данных, которые ссылаются на это представление, не будут работать должным образом, по крайней мере, в период времени между удалением представления и его воссоздания.

Использование инструкции ALTER VIEW показано в примере ниже:

В этом примере инструкция ALTER VIEW расширяет инструкцию SELECT в представлении view_WithoutBudget новым условием в предложении WHERE.

Инструкция DROP VIEW удаляет из системных таблиц определение указанного в ней представления. Применение этой инструкции показано в примере ниже:

USE SampleDb; GO DROP VIEW view_Count;

При удалении представления инструкцией DROP VIEW все другие представления, основанные на удаленном, также удаляются, как показано в примере ниже:

USE SampleDb; GO DROP VIEW view_Consultant;

Здесь инструкция DROP VIEW явно удаляет представление view_Consultant, при этом неявно удаляя представление view_project_p2, основанное на представлении view_Consultant. Теперь попытка выполнить запрос по представлению view_project_p2 возвратит сообщение об ошибке.

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

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

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

Представление создается с помощью команды . После создания представления становятся частью схемы создавшего их пользователя. Переназначить их другому пользователю можно тогда, когда у него имеется системная привилегия CREATE ANY VIEW .

Синтаксис команды CREATE VIEW Oracle

Синтаксис команды Create View

Основные ключевые слова и параметры CREATE VIEW Oracle :
OR REPLACE, FORCE, NOFORCE, Sсhema, View , Alias, AS subquery, WITH CHECK OPTION, Constraint

OR REPLACE — пересоздает представление, если оно уже существует. Можно использовать эту опцию для изменения определения представления без того, чтобы удалять его, создавать заново и вновь назначать все объектные привилегии, которые были назначены по данному представлению;

FORCE — создает представление независимо от того, существуют ли базовые таблицы этого представления, и от того, имеет ли владелец схемы, содержащей представление, привилегии по этим таблицам. Необходимо чтобы оба названных условия были удовлетворены, прежде чем по данному представлению можно будет выдавать любые предложения SELECT , INSERT , UPDATE или DELETE . По умолчанию применяется параметр NOFORCE ;

NOFORCE — создает представление только в том случае, если существуют базовые таблицы этого представления, а владелец схемы, содержащей представление, имеет привилегии по этим таблицам;

Sсhema — схема, в которой создается представление. Если СХЕМА опущена, то ORACLE создает представление в схеме пользователя;

View — ключевое слово view это имя создаваемого представления;

Alias — специфицирует имена для выражений, выбираемых запросом представления. Число алиасов должно совпадать с количеством выражений, выбираемых подзапросом. Алиасы должны удовлетворять правилам именования объектов схем. Алиасы должны быть уникальны внутри представления. Если алиасы опускаются, то ORACLE определяет их по именам или алиасам столбцов в запросе представления. Поэтому использовать алиасы нужно, если запрос представления содержит, помимо имен столбцов, выражения;

AS subquery — идентифицирует столбцы и строки таблиц, на которых базируется представление. Запрос представления может быть любым предложением SELECT , не содержащим фраз ORDER BY или FOR UPDATE . Его список выборки может содержать до 254 выражений;

WITH CHECK OPTION — указывает, что вставки и обновления, которые будут осуществляться через этот запрос, должны давать в результате только такие строки, которые могут быть выбраны запросом этого же представления. Опция CHECK OPTION не может гарантировать этого, если существует подзапрос в запросе этого представления или любого представления, на котором базируется данное представление. Другими словами, при указании параметра WITH CHECK OPTION пользователь не может вводить, удалять и обновлять информацию таблицы, из которой он не имеет возможности считать информацию через простое представление (создаваемое из данных одной таблицы). Обновляемое представление, использующее несколько связанных таблиц, нельзя создавать с данным параметром;

Constraint — имя, которое присваивается ограничению CHECK OPTION . Если этот идентификатор опущен, то ORACLE автоматически назначает этому ограничению имя следующего вида:

SYS_Cn , где n — целое, которое делает имя ограничения уникальным внутри базы данных.

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

Если команды обновления DML (INSERT , UPDATE , DELETE ) можно применить к представлению, то говорят, что представление является обновляемым (updatable); в противном случае оно является только читаемым (read-only). Представление является обновляемым, если при его создании учитывались следующие критерии:

представление должно включать первичный ключ таблицы
не должно содержать полей, полученных в результате применения функций агрегирования
не должно содержать DISTINCT, GROUP BY, HAVING в своем определении
может быть определено на другом представлении, но это представление должно быть обновляемым
не может содержать константы, строки или выражения (например, comm*100) в списке выбираемых выходных полей

Пример 1.
CREATE VIEW Oracle .
Простое представление, которое создается из данных одной таблицы:

London_view AS SELECT * FROM Salespeople WHERE city = ‘London’;

Пример 2.
CREATE VIEW Oracle .
При создании представления можно можно задать новые имена полей:

Rating_view(rating,number) AS SELECT rating, COUNT (*) FROM Customers GROUP BY rating;

Пример 3.
CREATE VIEW Oracle .
Представления могут получать информацию из любого количества базовых таблиц:

Nameorders AS SELECT onum, amt,a.snum, sname, cname FROM Orders a, Customers b, Salespeople C WHERE a.cnum = b.cnum AND a.snum = c.snum;

Пример 4.
CREATE VIEW Oracle .
При создании представлений можно использовать подзапросы, включая и связанные подзапросы:

Sales_view AS SELECT b.odate, a.snum, a.sname, FROM Salespeople a, Orders b WHERE a.snum = b.snum AND b.amt = (SELECT MAX (amt) FROM Orders c WHERE c.odate = b.odate);

Пример 5.
CREATE VIEW Oracle .

Empl_v04 AS SELECT e.eid, e.sname, e.fname, e.otch, p.pname, d.dname FROM posts p, departments d, employees e WHERE e.did = d.did AND e.pid = p.pid;