Представления

Работа с базами данных в .NET Framework --- SQL Server 2012 --- Представления

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

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

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

CREATE VIEW view_name [(column_list)]
    [WITH {ENCRYPTION | SCHEMABINDING | VIEW_METADATA}]
    AS select_statement
    [WITH CHECK OPTION]


Соглашения по синтаксису

Инструкция 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. Кроме этого, по временным таблицам нельзя выполнять запросы.

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

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

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

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

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

USE SampleDb;

GO
CREATE VIEW view_Consultant
    AS SELECT EmpId, ProjectNumber, EnterDate
    FROM Works_on
    WHERE Job = 'Консультант';

Запрос в этом примере выбирает из таблицы 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;

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

USE SampleDb;

GO
CREATE VIEW view_project_p2
    AS SELECT EmpId
    FROM view_Consultant
        WHERE ProjectNumber ='p2';

Представление 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 показано в примере ниже:

USE SampleDb;

GO
ALTER VIEW view_WithoutBudget
    AS SELECT Number, ProjectName
    FROM Project
    WHERE Number >= 'p3';

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

Редактирование информации о представлениях

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

А представление каталога sys.views содержит дополнительную информацию о существующих представлениях. Наиболее важным столбцом этого представления является столбец with_check_option, который информирует, указано или нет предложение WITH CHECK OPTION. Запрос для определенного представления можно отобразить посредством системной процедуры sp_helptext.