7.1. Использование представлений
Представление является еще одним объектом, составляющим логическую структуру любой базы данных. Представление для конечных пользователей выглядит как таблица, однако при этом не содержит данных, а лишь представляет их. Физически же представляемые данные расположены в различных таблицах базы данных.
Представление реализуется в виде сохраненного запроса, на основе которого и производится выборка из различных таблиц базы данных. Представления обладают следующими преимуществами:
• обеспечивают конфиденциальность информации, т.к. позволяют отобразить только необходимую информацию, скрывая определенные поля;
• упрощают визуальное восприятие данных, т.к. пользователь работает с представлением как с единой таблицей, которая создана на основе выборки данных из нескольких таблиц;
• управляют правами доступа к данным, например, вместо того чтобы предоставлять права на выполнение запросов к определенным полям таблиц, проще разрешить выполнение запросов через представление.
SQL Server предоставляет различные способы создания представлений, в частности, с помощью средств Transact-SQL.
7.2. Создание представлений с помощью Transact-SQL
Для создания представления используется команда CREATE VIEW:
CREATE VIEW ИмяПредставления [(поле [,…n])]
[ WITH ENCRYPTION ]
AS
ЗапросНаВыборку
При указании имени представления необходимо придерживаться ранее определенных правил именования объектов, также это имя не должно совпадать с именем уже существующей таблицы в базе данных.
Параметр WITH ENCRYPTION определяет шифрование кода запроса и гарантирует, что пользователи не смогут просмотреть и использовать его.
ЗапросНаВыборку представляет собой оператор SELECT, параметры которого и определяют содержимое представления. Имена полей представления задаются либо с помощью псевдонимов в операторе выборки, либо указываются в параметре поле.
Например, создадим представление, содержащее лишь следующую информацию о сотрудниках: номер, имя и фамилия сотрудника, дата рождения.
CREATE VIEW InfoEmployees ([Номер], [ФИО], [ДатаРождения])
AS
SELECT EmployeeKey,
FirstName+' '+LastName,
CONVERT (char(10), BirthDate, 104)
FROM AdventureWorksDW..DimEmployee
Содержимое представления можно просмотреть так же, как содержимое таблицы: через Обозреватель объектов или сформировав запрос:
SELECT * FROM InfoEmployees.
С помощью данного представления ограничен доступ к некоторым полям исходной таблицы DimEmployees, в этом случае говорят, что на таблицу наложен вертикальный фильтр, т.е. ограничен доступ к части полей таблицы без защиты на уровне столбцов.
Если в коде запроса выборки определено условие отбора записей, то говорят, что на таблицу наложен горизонтальный фильтр.
Например, следующее представление обеспечивает доступ к информации только о тех заказчиках, образование которых High School:
create VIEW InfoCustomersHighSchool
AS
SELECT FirstName, LastName, EnglishEducation
FROM AdventureWorksDW..DimCustomer
WHERE EnglishEducation='High School'
В запросе выборки может быть указана команда SELECT любой сложности, однако при этом не рекомендуется использовать раздел ORDER BY, который в дальнейшем можно применить при выборке данных из созданного представления. Так же рекомендуется при создании представлений на основе нескольких таблиц использовать только внутреннее соединение.
Например, создадим представление, отображающее для каждого заказчика суммарную стоимость его заказов:
CREATE VIEW InfoOrders
AS
SELECT FirstName+' '+LastName as [Заказчик],
convert (money, sum(UnitPrice*OrderQuantity),0) as [Общая стоимость заказов]
FROM (AdventureWorksDW..DimCustomer inner join AdventureWorksDW..FactInternetSales
ON DimCustomer.CustomerKey=FactInternetSales.CustomerKey)
GROUP BY FirstName+' '+LastName
Задание 1. Создайте процедуру, использующую представление InfoOrders, выводящую 5 заказчиков, являющихся лидерами закупок.
7.3. Управление представлениями
Созданное представление может быть изменено выполнением команды ALTER VIEW, имеющей аналогичный синтаксис команде CREATE VIEW. Для удаления представления необходимо выполнить команду:
DROP VIEW ИмяПредставления
Для получения информации о представлении используется хранимая процедура:
sp_help ИмяПредставления
Она отображает список полей представления с описанием их свойств.
Для отображения кода, с помощью которого создано представление, можно воспользоваться хранимой процедурой:
sp_helptext ИмяПредставления
Список объектов, от которых зависит представление, может быть получен выполнением хранимой процедуры:
sp_depends ИмяПредставления
Задание 2. Просмотрите информацию о созданном в предыдущем задании представлении InfoOrders, используя процедуры sp_help, sp_helptext, sp_depends.
Задание 3. Выведите статистику работы компании AdventureWorksDW, отражающую объемы продаж и количество заказов по месяцам.
Указание: Создайте сначала представление, содержащее номер заказа, год и месяц его размещения, итоговую стоимость каждого заказа. Затем на его основе создайте запрос.
Рекомендация: Чтобы получить год и месяц размещения заказа воспользуйтесь функцией преобразования: convert (char(7), FullDateAlternateKey,102) – которая преобразует дату к строке типа yyyy.mm.dd, а т.к. при преобразовании определена строка длиной 7 символов, результатом функции будет строка типа yyyy.mm, т.е. будут выведены только месяцы каждого года.
Задание 4. Определить количество размещенных заказов по временам года.
Указание: Создайте сначала представление, выводящее номера заказов по сезонам (временам года).
Рекомендация: Для выделения сезонов можно использовать следующую конструкцию (после SELECT как имя нового поля):
[Сезон] = Case
When month(FullDateAlternateKey) in (1, 2, 12) Then 'Зима'
When month(FullDateAlternateKey) in (3, 4, 5) Then 'Весна'
When month(FullDateAlternateKey) in (6, 7, 8) Then 'Лето'
When month(FullDateAlternateKey) in (9, 10, 11) Then 'Осень'
End
Сценарий, по которому разыгрался кризис, во многом будет зависеть от того, удастся ли бизнесу и потребителям победить этот кризис в голове. Всемирные бартерные сети
