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

Для создания представлений используется следующий синтаксис:

CREATE [OR REPLACE] VIEW имя_представления AS

оператор SELECT

[WITH CHECK OPTION];

 

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

-- создание представления, содержащего все поля таблицы

-- CONTRAGENTS за исключением поля Address (скрытие данных)

CREATE OR REPLACE VIEW Contragents_Lite AS

SELECT Contr_id, Name, Phone, Comments FROM Contragents;

Операции выборки из представлений

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

 

-- выборка всей информации обо всех контрагентах

-- за исключением адреса, поскольку поле не было включено

-- в представление

SELECT * FROM Contragents_Lite ORDER BY Name;

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

Если к представлению можно применить операторы обновления (INSERT, UPDATE или DELETE), то представление является обновляемым (updateble), иначе оно является читаемым (read-only).

Ниже приведены критерии того, является ли представление обновляемым в SQL:

· оно базируется на одной таблице;

· оно должно включать первичный ключ таблицы;

· оно не должно включать полей, полученных в результате применения функций агрегирования;

· оно не может содержать спецификации DISTINCT;

· оно не должно использовать GROUP BY или HAVING;

· оно не должно использовать подзапросы;

· оно может быть определено на другом представлении, но это представление должно быть обновляемым;

· оно не может содержать константы, строки или выражения в списке выбираемых выходных полей;

· для INSERT оно должно включать поля из таблицы, которые имеют ограничения NOT NULL.

Например, приведенное представление Contragents_Lite является обновляемым, поскольку оно удовлетворяет всем перечисленным критериям, а представление WareHouse – нет.

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

Для предотвращения описанной ситуации применяется параметр WITH CHECK OPTION, который может указываться при создании обновляемых представлений после оператора SELECT. При наличии этого параметра СУБД предотвращает операции обновления данных, если они нарушают условия горизонтальной или вертикальной фильтрации, определенные в предложении в операторе SELECT:

-- создание обновляемого представления с защитой от

-- изменений скрытого столбца Address

CREATE OR REPLACE VIEW Contragents_Lite AS

SELECT Contr_id, Name, Phone, Comments FROM Contragents

WITH CHECK OPTION;

 

Компоненты языка Transact-SQL

Переменные

Локальные переменные обозначаются префиксом @, глобальные переменные – двойным символом @@. Например: @MyVariable, @@VERSION. Все глобальные переменные определяются SQL Server, вы не можете определить их самостоятельно. Большинство глобальных переменных предоставляют информацию о текущем статусе SQL Server.

Создание локальных переменных:

DECLARE @локальная_переменная тип_данных

Например: DECLARE @var1 int, @var2 int

Большинство типов данных являются скалярными, т.е. содержат одно значение, такое как число или строка. Возможно объявлять переменные с табличным типом данных:

DECLARE @локальная_переменная Table ({определение таблицы})

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

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

• Используя команду SET

SET @myChar = ‘Hello’

• Используя команду SELECT

SELECT @myChar = ‘Hello’

• С вложенным оператором SELECT

SELECT @myChar = MAX (OilName) FROM Oils

Оператор = замещает ключевое слово SELECT.

• Используя команду INSERT INTO с указанием переменной табличного типа

INSERT INTO @myTable SELECT * FROM Oils или

INSERT INTO @myTable VALUES (‘The value’)

 

Управление ходом выполнения

Transact-SQL обрабатывает операторы сценария от начала до конца, не пропуская ни один оператор, если не задано иное. Это не всегда то, что вам нужно. Иногда вам необходимо, чтобы оператор выполнялся, только если некоторое условие истинно, или повторялся до тех пор, пока некоторое условие является истинным, или выполнялся указанное количество раз.

Когда вы приступаете к использованию операторов управления ходом выполнения Transact-SQL, удобно интерпретировать определенную группу команд как блок. Transact-SQL позволяет вам сделать это с помощью пары команд BEGIN...END.

В блок вы можете включить любой оператор Transact-SQL, в том числе другие блоки BEGIN...END, но здесь есть несколько ограничений. Вы не можете сочетать операторы CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER и CREATE VIEW с какими-либо другими операторами. Также вы не можете изменять структуру таблицы и затем ссылаться на новый столбец в этом же блоке.

Условное выполнение IF...ELSE

Если булево выражение, следующее за командой IF, имеет значение TRUE, то будет выполнен оператор или блок операторов, следующий за этим оператором. Если булево выражение имеет значение FALSE, то оператор или блок операторов, следующий за FALSE, будет пропущен.

IF @test

SELECT ‘Истина’

ELSE

SELECT ‘Ложь’

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

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

CASE

В большинстве языков программирования оператор CASE является расширенной формой оператора IF, которая позволяет вам определять множество булевых выражений в одном операторе. В SQL Server CASE является функцией, а не командой. Она используется не сама по себе, как IF, а как часть оператора SELECT или UPDATE.

Синтаксис простой структуры CASE представлен ниже:

CASE значение

WHEN выражение_один THEN результирующее_выражение_один

WHEN выражение_два ТНEN результирующее_выражение_два

•••• ••••

WHEN выражение_п ТНEN результирующее_выражение_п

[ELSE альтернативное_результирующее_выражение]

END

Вы можете использовать в выражении любое количество фраз WHEN. Фраза ELSE необязательна — она выполняется, только если все фразы WHEN оцениваются как FALSE.

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

CASE

WHEN булево_выражение_один ТНEN результирующее_выражение_один

WHEN булево_выражение_два ТНEN результирующее_выражение_два

WHEN булево_выражение_п THEN результирующее_выражение_п

•••• ••••

[ELSE альтернативное_результирующее_выражение]

END

Например:

SELECT TOP 10 OilName, LatinName,

CASE

WHEN LEFT (OilName, 1) = 'B' THEN 'Name B’

WHEN LEFT (LatinName, 1) = 'C' THEN ' LatinName C'

ELSE ' Neither'

END AS TestResults

FROM Oils

ORDER BY OilName

 

В этой форме CASE вы можете указать целое булево выражение в каждой фразе WHEN вместо неявного сравнения выражения в простой форме. Учтите, что при определении истинности можно оценивать несколько булевых_выражений. Transact-SQL возвратит только первое результирующее _выражение, а затем перейдет к оператору, следующему за END.

Команда GOTO

Структуры IF...ELSE и CASE управляют порядком выполнения операторов, основываясь на результатах вычисления булевого выражения. Команда GOTO является безусловной. Она передает выполнение непосредственно к оператору, следующему после метки, которая на него указывает. Сама команда GOTO имеет очень простой синтаксис:

GOTO имя_метки

GOTO является весьма непопулярной в кругу программистов командой. Применение команды GOTO, тем не менее, бывает вполне оправданным, особенно при обработке ошибок. Если вы будете аккуратно использовать эту команду, она даже сможет сделать код проще для понимания. Используйте команду GOTO для безусловного перехода.

Например:

GOTO M

……

M:

PRINT ‘ Name 1 ’

Циклы

Последняя из команд управления ходом выполнения позволяет вам выполнять оператор или блок операторов до тех пор, пока выполняется (является истинным) указанное условие.

Простейшая форма цикла WHILE содержит булево выражение и оператор или блок операторов. Операторы будут повторяться, пока булево выражение не примет FALSE. Если при первой оценке булево выражение имеет значение FALSE, то оператор или блок операторов не будет выполняться вообще.

Например:

DECLARE @counter int

SET @counter = 1

WHILE @counter < 11

BEGIN

PRINT @counter

SET @counter = @counter + 1

END

Синтаксис оператора WHILE также позволяет вам осуществлять и более сложную логику выполнения, в отличие от простой логики в предыдущей примере. Фраза BREAK вызывает выход из цикла; выполнение продол­жается с оператора, следующего за фразой END блока оператора струк­туры WHILE. Фраза CONTINUE возвращает выполнение на начало цик­ла, при этом операторы, следующие за CONTINUE в пределах блока операторов, будут пропущены. Оба оператора, BREAK и CONTINUE обычно выполняются как условные в пределах оператора IF.

Если вам потребуется, вы можете использовать команды BREAK и CONTINUE в одном и том же операторе WHILE.

Например:

DECLARE @counter int

SET @counter = 1

WHILE @counter < 11

BEGIN

SET @counter = @counter + 1

IF (@counter % 2) = 0 CONTINUE

PRINT @counter

END

 

Функции преобразования

Внутреннее представление различных столбцов данных не всегда удобно для от­четов. Поэтому в T-SQL включены следующие две функции, выполняющие пре­образование типов данных.

CONVERT (data_type, variable, style)

Функция CONVERT() преобразует переменную (или значение столбца) к заданному типу данных. В аргументе data_type может быть задано имя любого допустимого типа данных SQL Server. Если это тип данных nchar, nvarchar, char, varchar, binary или varbinary, в скобках можно задать числовое значение, определяющее его длину.

В аргументе style задается желаемый формат данных после преобразования пере­менной типа datatime или smalldatatime к символьному типу данных. Допустимые значения этого аргумента приведены в табл.4.

Функция CONVERT() используется для преобразования типов данных перед выво­дом их в окне Query Analyzer или для конкатенации символьных и числовых зна­чений перед передачей их инструкции PRINT.

Максимальная длина столбцов CompanyName и ContactName таблицы Customers в базе данных Northwind составляет соответственно 40 и 30 символов. Если включить эти столбцы в запрос как есть, строки результирующего набора данных не поместятся в окне Query Analyzer. Сделать эти столбцы покороче можно с помощью такой инструкции:

USE NORTHWIND

SELECT CONVERT(nchar(30), CompanyName),

CONVERT(nchar(15), ContactName)

FROM Customers

Следующая инструкция формирует итоговые данные по всем компаниям и воз­вращает их вместе с названиями таковых:

USE NORTHWIND

SELECT CONVERT(char(25), CompanyName),

CONVERT(char(10), SUM(Quantity * UnitPrice * (1 - Discount)))

FROM [Order Details], Orders, Customers

WHERE [Order Details].OrderID = Orders.OrderID AND

Orders.CustomerID = Customers.CustomerID

GROUP BY CompanyName

ORDER BY CompanyName

Вот фрагмент выходных данных, генерируемых этой инструкцией:

Around the Horn 13806.8

Berglunds snabbkop 24927.6

Blauer See Delikatessen 3421.95

Blondesddsl pere et fils 18534.1

Bolido Comidas preparadas 4232.85

Bon app’ 21963.3

Bottom-Dollar Markets 20801.6

Если не пользоваться функцией CONVERT(), названия компаний будут дополняться пробелами до длины 40 символов, а в числах будет слишком много десятичных знаков:

Around the Horn 13806.800003051758

Berglunds snabbkop 24927.57746887207

Blauer See Delikatessen 3421.9500045776367

CAST (variable AS data_type)

Функция CAST() преобразует значение переменной или столбца к заданному типу данных. Эта функция делает то же самое, что и функция CONVERT() и включена в T-SQL для обеспечения совместимости со стандартом SQL-92.