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

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

Определяемая для представления инструкция SELECT может ссылаться на таблицы, другие представления и функции, но не может делать следующего:

♦ содержать предложение COMPUTE или COMPUTE BY;

♦ создавать постоянную или временную таблицу с помощью ключевого слова INTO;

♦ применять предложение OPTION;

♦ ссылаться на временную таблицу;

♦ ссылаться на переменную любого типа;

♦ содержать предложение ORDER BY, если не задан параметр ТОР.

Представление может содержать множественные инструкции SELECT, пока вы применяете операторы UNION ИЛИ UNION ALL.

Пример 1.1.

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

 

        1.2.         Модификация данных с помощью представления

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

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

•  Изменяемые в представлении столбцы должны непосредственно ссылаться на данные столбцов базовой таблицы. Столбцы нельзя сформировать каким-либо другим образом, в том числе: 

ü  при помощи статистической функции: AVG, COUNT, SUM, MIN, MAX,

GROUPING, STDEV, STDEVP, VAR и VARP;

ü  на основе вычисления. Столбец нельзя вычислить по выражению, включающему другие столбцы. Столбцы, сформированные при помощи операторов UNION, UNION ALL, EXCEPT и INTERSECT, считаются вычисляемыми и также не являются обновляемыми.

•  Предложения GROUP BY, HAVING и DISTINCT не влияют на изменяемые столбцы.

•  Предложение TOP не используется вместе с предложением WITH CHECK OPTION.

Пример 1.2.

Создать представление  vw_1_product_ins  для вставки в таблицу product с помощью этого представления товаров только категории 1.

                         

ПРАКТИЧЕСКОЕ ЗАНЯТИЕ 1. 

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

Упражнение 1. Создание представления 

С помощью программы SSMS подключиться к базе данных WorksDB и создать представление для возврата еще недоставленных товаров.

1.  Откройте среду SSMS и установите соединение с экземпляром сервера SQL Server, используя учетную запись sa.

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

USE WorksDB;

GO

UPDATE Sales.SalesOrderHeader

SET ShipDate = NULL

WHERE SalesOrderID = 75123 GO

3.  В имеющемся окне запроса для создания представления введите, выделите и выполните следующий код. 

IF OBJECT_ID ('Sales.v_UnshippedOrders', 'V') IS NOT NULL

DROP VIEW Sales.v_UnshippedOrders;

GO

CREATE VIEW Sales.v_UnshippedOrders

AS 

SELECT SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, 

    Status, OnlineOrderFlag, SalesOrderNumber, PurchaseOrderNumber, 

    AccountNumber, CustomerID, SalesPersonID, TerritoryID, 

    BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, 

    CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, 

    TotalDue, Comment, rowguid, ModifiedDate

FROM Sales.SalesOrderHeader

WHERE ShipDate IS NULL GO

4.  В имеющемся окне запроса для тестирования представления и затем восстановления состояния измененного вами заказа введите, выделите и выполните следующий код. 

SELECT * FROM Sales.v_UnshippedOrders

GO

UPDATE Sales.SalesOrderHeader

SET ShipDate = '07/08/2004' WHERE SalesOrderID = 75123 GO

5.  Сохраните сценарий и закройте окно запроса.

Упражнение 2. Создание представления, содержащего встроенную функцию

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

1. В окне запроса для создания представления введите, выделите и выполните следующий код. 

USE WorksDB;

GO

IF OBJECT_ID ('Sales.SalesPersonPerform', 'V') IS NOT NULL

    DROP VIEW Sales.SalesPersonPerform ;

GO

CREATE VIEW Sales.SalesPersonPerform

AS

SELECT TOP 100 SalesPersonID, SUM(TotalDue) AS TotalSales

FROM Sales.SalesOrderHeader

WHERE OrderDate > CONVERT(DATETIME,'20001231',101)

GROUP BY SalesPersonID; GO

2.  В имеющемся окне запроса для тестирования представления выделите