Работа с данными через представления

183

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

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

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

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

USE SampleDb;

GO
CREATE VIEW view_d2
    AS SELECT Id, LastName
    FROM Employee
    WHERE DepartamentNumber ='d2';

GO
-- Вернет 'Фролов'
SELECT LastName
    FROM view_d2
    WHERE LastName LIKE 'Ф%';

Инструкция SELECT в примере выше трансформируется в следующую эквивалентную форму с использованием таблицы из представления view_d2:

SELECT LastName
    FROM Employee
    WHERE LastName LIKE 'Ф%'
        AND DepartamentNumber ='d2';

Далее мы рассмотрим использование представлений с тремя другими инструкциями языка DML: INSERT, UPDATE и DELETE. Модифицирование данных посредством этих инструкций подобно выборке данных. Единственное отличие состоит в том, что для представления, используемого для вставки, модифицирования и удаления данных из таблицы, на основе которой оно создано, существуют некоторые ограничения.

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

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

В примере ниже создается представление view_dept, которое содержит первые два столбца таблицы Department. Последующая инструкция INSERT вставляет две строки в таблицу, связанную с представлением, используя значения d4 и 'Разработка'. Столбцу Location, который не вошел в представление view_dept, присваивается значение NULL:

USE SampleDb;

GO
CREATE VIEW view_dept
    AS SELECT Number, DepartmentName
    FROM Department;

GO
INSERT INTO view_dept
    VALUES('d4', 'Разработка');

При использовании представления обычно возможно вставить строку, которая не удовлетворяет условиям в предложении WHERE запроса представления. Чтобы ограничить вставку только строками, которые удовлетворяют условиям запроса, применяется предложение WITH CHECK OPTION. При использовании этого предложения компонент Database Engine проверяет каждую вставляемую строку на удовлетворение условий предложения WHERE. Если это предложение отсутствует, такая проверка не выполняется, вследствие чего каждая вставляемая в представление строка также вставляется в таблицу в его основе. Это может вызвать путаницу, когда строка вставляется в представление, но впоследствии не возвращается из этого представления инструкцией SELECT, т.к. для нее принудительно выполняются условия предложения WHERE. Предложение WITH CHECK OPTION также применяется и с инструкцией UPDATE.

В примерах ниже показана разница между применением и неприменением предложения WITH CHECK OPTION соответственно:

USE SampleDb;

GO
CREATE VIEW view_2007_check
    AS SELECT EmpId, ProjectNumber, EnterDate
    FROM Works_on
    WHERE EnterDate BETWEEN '01.01.2007' AND '12.31.2007'
    WITH CHECK OPTION;

GO
INSERT INTO view_2007_check
    VALUES (9031, 'p2', '1.15.2008');

SELECT *
    FROM view_2007_check;

В этом примере система проверяет, соответствует ли вставляемое в столбец EnterDate значение True (истина) при вычислении условия в предложении WHERE инструкции SELECT. Если вставляемое значение не удовлетворяет этим условиям, строка не вставляется.

USE SampleDb;

GO
CREATE VIEW view_2007_nocheck
    AS SELECT EmpId, ProjectNumber, EnterDate
    FROM Works_on
    WHERE EnterDate BETWEEN '01.01.2007' AND '12.31.2007';

GO
INSERT INTO view_2007_nocheck
    VALUES (9031, 'p2', '1.15.2008');

SELECT *
    FROM view_2007_nocheck;

В результате выполнения этого запроса будут отображены следующие строки:

Ограничение на вставку данных в представление

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

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

предложение FROM в определении представления содержит более чем одну таблицу, и список столбцов содержит столбцы более чем из одной таблицы;
столбец в представлении создается из агрегатной функции;
инструкция SELECT в представлении содержит предложение GROUP BY или параметр DISTINCT;
столбец в представлении создается из константы или выражения.

В примере ниже показано представление, которое нельзя использовать для вставки строк в таблицу, на которой основано это представление:

USE SampleDb;

GO
CREATE VIEW view_Sum (SumBudget)
    AS SELECT SUM(Budget)
    FROM Project;

GO
SELECT *
    FROM view_Sum;

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

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

Инструкцию UPDATE можно применять с представлением, как будто бы это была базовая таблица. При модифицировании строк представления также модифицируется содержимое таблицы в его основе. Запрос в примере создает представление, посредством которого затем модифицируется таблица Works_on:

USE SampleDb;

GO
CREATE VIEW view_p1
    AS SELECT EmpId, Job
    FROM Works_on
    WHERE ProjectNumber = 'p1';

GO
UPDATE view_p1
    SET Job = NULL
    WHERE Job = 'Менеджер';

Операцию обновления представления view_p1 в примере выше можно рассматривать эквивалентной выполнению следующей инструкции UPDATE:

UPDATE Works_on
    SET Job = NULL
    WHERE Job = 'Менеджер'
        AND ProjectNumber = 'p1';

Логическое значение предложения WITH CHECK OPTION для инструкции UPDATE имеет такое же значение, как и для инструкции INSERT. Использование предложения WITH CHECK OPTION в инструкции UPDATE показано в примере ниже:

USE SampleDb;

GO
CREATE VIEW view_100000
    AS SELECT Number, Budget
    FROM Project
    WHERE Budget > 100000
    WITH CHECK OPTION;

GO
UPDATE view_100000
    SET Budget = 93000
    WHERE Number = 'p3';

Здесь компонент Database Engine проверяет, будет ли измененное значение столбца Budget давать значение True в условии предложения WHERE инструкции SELECT. Попытка изменения значения завершается неудачей, поскольку условие не удовлетворяется, т.е. вставляемое значение 93000 не больше, чем значение 100000.

В примере ниже показано представление, которое нельзя использовать для изменения значений в таблице, на которой основано представление:

USE SampleDb;

GO
CREATE VIEW view_Pound (projectNumber, budgetPounds)
    AS SELECT Number, Budget * 0.65
    FROM Project
    WHERE Budget > 100000;

GO
SELECT *
    FROM view_Pound;

Результат выполнения этого запроса:

Представление в котором нельзя обновить данные

Представление view_Pound нельзя использовать с инструкцией UPDATE (или с инструкцией INSERT), поскольку значения столбца budgetPounds являются результатом вычисления арифметического выражения, а не первоначальными значениями столбца таблицы, на которой основано это представление.

Удаление данных с помощью представления

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

USE SampleDb;

GO
CREATE VIEW view_project_p1
    AS SELECT EmpId, Job
    FROM Works_on
    WHERE ProjectNumber = 'p1';

GO
DELETE FROM view_project_p1
    WHERE Job = 'Консультант';

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

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

USE SampleDb;

GO
CREATE VIEW view_budget (budgetReduction)
    AS SELECT Budget * 0.9
    FROM Project;

GO
    DELETE FROM view_budget;

Инструкция DELETE в примере выше удаляет все строки таблицы Project, на которой основано представление view_budget.