Создание баз данных. Основы Transact SQL. Обработка ошибок. Управление транзакциями. Триггеры

@@ERROR - Содержит номер ошибки, возникшей при выполнении последнего оператора T-SQL в текущем соединении. Если ошибка не обнаружена, содержит 0. Значение этой системной переменной переустанавливается после выполнения каждого очередного оператора. Если требуется сохранить содержащееся в ней значение, то это значение следует переносить в локальную переменную сразу же после выполнения оператора, для которого должен быть сохранен код ошибки.

@@IDENTITY - Содержит последнее идентификационное значение, вставленное в базу данных в результате выполнения последнего оператора INSERT. Если в последнем операторе INSERT не произошла выработка идентификационного значения, системная переменная @@IDENTITY содержит NULL. Это утверждение остается справедливым, даже если отсутствие идентификационного значения было вызвано аварийным завершением при выполнении оператора. А если с помощью одного оператора осуществляется несколько операций вставки, этой системной переменной присваивается только последнее идентификационное значение.

@@ROWCOUNT - Одна из наиболее широко используемых системных переменных. Возвращает информацию о количестве строк, затронутых последним оператором. Обычно применяется для контроля ошибок, отличных от тех, которые относятся к категории ошибок этапа прогона программы. Например, если в программе обнаруживается, что после вызова на выполнение оператора DELETE с конструкцией WHERE количество затронутых строк равно нулю, то можно сделать вывод, что произошло нечто непредвиденное. После этого сообщение об ошибке может быть активизировано вручную.

! Следует отметить, что с версии SQL Server 2000 глобальные переменные принято называть функциями. Название глобальные сбивало пользователей с толку, позволяя думать, что область действия таких переменных шире, чем у локальных. Глобальным переменным часто ошибочно приписывалась возможность хранить информацию, независимо от того, включена она в пакет либо нет, что, естественно, не соответствовало действительности.

Средства управления потоком команд. Программные конструкции

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

Оператор IF. . .ELSE

Операторы IF. . .ELSE действуют в языке T-SQL в основном так же, как и в любых других языках программирования. Общий синтаксис этого оператора имеет следующий вид:

IF Логическое выражение SQL инструкция I BEGIN Блок SQL инструкций END [ELSE SQL инструкция | BEGIN Блок SQL инструкций END]

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

Следует учитывать, что выполняемым по условию считается только тот оператор, который непосредственно следует за оператором IF (ближайшим к нему). Вместо одного оператора можно предусмотреть выполнение по условию нескольких операторов, объединив их в блок кода с помощью конструкции BEGIN…END.

В приведенном ниже примере условие IF не выполняется, что предотвращает выполнение следующего за ним оператора.

IF 1 = 0

PRINT 'Первая строка'

PRINT 'Вторая строка'

Необязательная команда ELSE позволяет задать инструкцию, которая будет выполнена в случае, если условие IF не будет выполнено. Подобно IF, оператор ELSE управляет только непосредственно следующей за ним командой или блоком кода заключенным между BEGIN…END.

Несмотря на то, что оператор IF выглядит ограниченным, его предложение условия может включать в себя мощные функции, подобно предложению WHERE. В частности это выражения IF EXISTS().

Выражение IF EXISTS() использует в качестве условия наличие какой-либо строки, возвращенной инструкцией SELECT. Так как ищутся любые строки, список столбцов в инструкции SELECT можно заменить звездочкой. Этот метод работает быстрее, чем проверка условия @@ROWCOUNT>0, потому что не требуется подсчет общего количества строк. Как только хотя бы одна строка удовлетворяет условию IF EXISTS(), запрос может продолжать выполнение.

В следующем примере выражение IF EXISTS используется для проверки наличия у клиента с кодом 1 каких-либо заказов перед удалением его из базы. Если по данному клиенту есть информация хотя бы по одному заказу, удаление не производится.

IF EXISTS(SELECT * FROM [Order] WHERE IdCust = 1)

PRINT 'Невозможно удалить клиента поскольку в базе имеются связанные с ним записи'

ELSE

BEGIN

DELETE Customer

WHERE IdCust = 1

PRINT 'Удаление произведено успешно'

END

Операторы WHILE, BREAK и CONTINUE

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

Оператор WHILE имеет следующий синтаксис:

WHILE Логическое выражение SQL инструкция I [BEGIN [BREAK] Блок SQL инструкций [CONTINUE] END]

Безусловно, с помощью оператора WHILE можно обеспечить выполнение в цикле только одного оператора (по аналогии с тем, как обычно используется оператор IF), но на практике конструкции WHILE, за которыми не следует блок BEGIN. . .END, соответствующий полному формату оператора, встречаются редко.

Оператор BREAK позволяет немедленно выйти из цикла, не ожидая того, как будет выполнен проход до конца цикла и произойдет повторная проверка условного выражения.

Оператор CONTINUE позволяет прервать отдельную итерацию цикла. Кратко можно описать действие оператора CONTINUE так, что он обеспечивает переход в начало цикла WHILE. Сразу после обнаружения оператора CONTINUE в цикле, независимо от того, где он находится, происходит переход в начало цикла и повторное вычисление условного выражения (а если значение этого выражения больше не равно TRUE, осуществляется выход из цикла).

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

DECLARE @Temp int;

SET @Temp = 0;

WHILE @Temp < 3

BEGIN

PRINT @Temp;

SET @Temp = @Temp + 1;

END

Здесь в цикле целочисленная переменная @Temp увеличивается с 0 до 3 и на каждой итерации ее значение выводится на экран.

Оператор RETURN

Оператор RETURN используется для останова выполнения пакета, а следовательно, хранимой процедуры и триггера (рассматриваются в следующих лабораторных занятиях).

Лабораторная работа №8. Хранимые процедуры

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

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

Как серверные программы хранимые процедуры имеют ряд преимуществ.

Хранимые процедуры хранятся в компилированном виде, поэтому выполняются быстрее, чем пакеты или запросы.

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

Хранимые процедуры имеют модульный вид, поэтому их легко внедрять и изменять. Если клиентское приложение вызывает хранимую процедуру для выполнения некоторой операции, то модификация процедуры в одном месте влияет на ее выполнение у всех пользователей.

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

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

Хранимые процедуры управляются посредством инструкций языка определения данных (DDL) CREATE, ALTER и DROP.

Общий синтаксис T-SQL кода для создания хранимой процедуры имеет следующий вид:

CREATE PROC | PROCEDURE <procedure_name> [ <@parameter> <data_type> [ = <default> ] [ OUT | OUTPUT ] ] [ ,...n ] AS [ BEGIN ] <sql_statements> [ END ]

<procedure_option> ::= [ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE_AS_Clause ]

Структура этого оператора соответствует основному синтаксису CREATE <Object Туре> <Object Name>, лежащему в основе любого оператора CREATE. Единственная отличительная особенность состоит в том, что в нем допускается использовать ключевое слово PROCEDURE или PROC. Оба эти варианта являются допустимыми: PROC является лишь сокращением от PROCEDURE.

Каждая процедура должна иметь уникальное в рамках базы данных имя (procedure_name), соответствующее правилам для идентификаторов объектов.

Процедуры могут иметь любое число входных параметров (@parametr) заданного типа данных (data_type), которые используются внутри процедуры как локальные переменные. При выполнении процедуры для каждого из объявленных формальных параметров должны быть переданы фактические значения. Или же для входного параметра может быть определено значение по умолчанию (default), которое должно быть константой или равняться NULL. В этом случае процедуру можно выполнить без указания значения соответствующего аргумента. Применение входных параметров необязательно.

Можно также указать выходные параметры (помеченные как OUTPUT), позволяющие хранимой процедуре вернуть одно или несколько скалярных значений в подпрограмму, из которой она была вызвана. При создании процедур можно задать три параметра. При создании процедуры с параметром ENCRYPTION SQL Server шифрует определение процедуры. При задании параметра RECOMPILE SQL Server перекомпилирует хранимую процедуру при каждом ее запуске. Параметр EXECUTE AS определяет контекст безопасности для процедуры.

В конце определения хранимой процедуры вслед за ключевым словом AS должно быть приведено непосредственно тело процедуры (sql_statements) в виде кода из одной или нескольких инструкций языка T-SQL.

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

Пример хранимой процедуры без параметров

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

CREATE PROCEDURE spr_getOrders

AS

SELECT IdOrd, IdCust, OrdDate

FROM [Order]

WHERE (OrdDate >= '01.01.2010')

RETURN

Чтобы протестировать новую процедуру, откройте новый запрос SQL Server и выполните следующий код.

EXEC spr_getOrders

Команда EXECUTE или сокращенно EXEC выполняет указанную хранимую процедуру.

В данном случае хранимая процедура вернет все строки из таблицы Order, в которых значение поля OrdDate больше 1 января 2010 года, в соответствии с содержащимся в нем запросом на выборку.

Применение входных параметров

Хранимая процедура предоставляет определенные процедурные возможности (а если она применяется в инфраструктуре .NET, такие возможности становятся весьма значительными), а также обеспечивает повышение производительности, но в большинстве обстоятельств хранимая процедура не позволяет добиться многого, если не предусмотрена возможность передать ей некоторые данные, указывающие на то, какие действия должны быть выполнены с ее помощью. В частности основная проблема, связанная с предыдущей хранимой процедурой (spr_getOrders), состоит в ее статичности. Если пользователям потребуется информация о заказах за другой период времени, то эта процедура им не поможет. Поэтому необходимо предусмотреть возможность передачи в нее соответствующих входных параметров, которые позволили бы динамически изменять период выборки.

Параметры, передаваемые хранимой процедуре, перечисляются через запятую в инструкции CREATE (ALTER) PROCEDURE непосредственно после ее имени. При объявлении входного параметра необходимо указать имя параметра, тип данных и возможно значение по умолчанию. В общем случае объявление входного параметра имеет следующий вид:

@parameter_name [AS] datatype [= default|NULL]

Правила определения входных параметров во многом аналогичны объявлению локальных переменных. Каждый из параметров должен начинаться с символа @. Для хранимой процедуры он является локальной переменной. Как и все локальные переменные, параметры должны объявляться с допустимыми встроенными или определяемыми пользователями типами данных СУБД SQL Server.

Значительные различия между объявлениями параметров хранимых процедур и объявлениями переменных начинают впервые обнаруживаться, когда дело касается значений, заданных по умолчанию. Прежде всего, при инициализации переменным всегда присваиваются NULL-значения, а на параметры это правило не распространяется. В действительности, если в объявлении параметра не предусмотрено заданное по умолчанию значение, то подразумевается, что этот параметр должен быть обязательным и что при вызове хранимой процедуры должно быть указано его начальное значение. Чтобы задать предусмотренное по умолчанию значение, необходимо добавить знак равенства (=) после обозначения типа данных, а затем указать применяемое по умолчанию значение. Благодаря этому пользователи получают возможность при вызове хранимой процедуры принимать решение о том, следует ли задать другое значение параметра или воспользоваться значением, предусмотренным по умолчанию.

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

ALTER PROCEDURE [dbo].[spr_getOrders]

@dateBegin datetime,

@dateEnd datetime

AS

SELECT IdOrd, IdCust, OrdDate

FROM [Order]

WHERE (OrdDate BETWEEN @dateBegin AND @dateEnd)

RETURN

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

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

EXEC spr_getOrders '01.01.2010', '01.07.2010'

EXEC spr_getOrders

@dateBegin = '01.01.2010',

@dateEnd = '01.07.2010'

EXEC spr_getOrders '01.01.2010', @dateEnd = '01.07.2010'

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

Применение выходных параметров

Выходные параметры позволяют хранимой процедуре возвращать данные вызывающей программе. Для определения выходных параметров используется ключевое слово OUT[PUT], которое обязательно как при определении процедуры, так и при ее вызове. В самой хранимой процедуре выходные параметры являются локальными переменными. В вызывающей процедуре или пакете выходные переменные должны быть предварительно определены, чтобы получить результирующие значения. Когда выполнение хранимой процедуры завершается, текущее значение параметра передастся локальной переменной вызывающей программы.

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

CREATE PROCEDURE spr_addProduct

@Description nvarchar(100),

@InStock int = 0,

@IdProd int OUT

AS

INSERT Product([Description], InStock)

VALUES (@Description, @InStock)

SET @IdProd = @@IDENTITY

RETURN

Пример вызова:

DECLARE@IdProd int

EXEC spr_addProduct

@Description = N'Новый товар',

@IdProd = @IdProd OUTPUT

SELECT@IdProd as N'@IdProd'

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

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

Любая вызываемая на выполнение хранимая процедура возвращает значение, независимо от того, предусмотрен ли в ней возврат значения или нет. По умолчанию после успешного завершения процедуры СУБД SQL Server автоматически возвращает значение, равное нулю.

Чтобы передать некоторое возвращаемое значение из хранимой процедуры обратно в вызывающий код, достаточно применить оператор RETURN:

RETURN [<Целое число>]

Обратите внимание на то, что возвращаемое значение должно быть обязательно целочисленным.

Возвращаемые значения предназначены исключительно для указания на успешное или неудачное завершение хранимой процедуры и позволяют даже обозначить степень или характер успеха или неудачи. Использование возвращаемого значения для возврата фактических данных, таких как идентификационное значение или данные о количестве строк, затронутых хранимой процедурой, рассматривается как недопустимая практика программирования. Возвращаемое значение 0 указывает на успешное выполнение процедуры и установлено по умолчанию. Компания Microsoft зарезервировала значения от -99 до -1 для служебного пользования. Разработчикам для возвращения состояния ошибки пользователю рекомендуется использовать значения -100 и меньше.

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

В предыдущем примере при попытке добавления в таблицу Product информации о новом товаре с дублирующим названием могла произойти ошибка, поскольку по наименованию товара организовано ограничение уникальности. Расширим хранимую процедуру spr_addProduct, предусмотрев предварительную проверку на наличие указанного товара в базе и индикацию об успешности операции через выходной параметр.

ALTER PROCEDURE [dbo].[spr_addProduct]

@Description nvarchar(100),

@InStock int = 0,

@IdProd int OUT

AS

IF EXISTS(SELECT * FROM Product WHERE [Description] = @Description)

RETURN -100

INSERT Product([Description], InStock)

VALUES (@Description, @InStock)

SET @IdProd = @@IDENTITY

RETURN 0

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

EXEC @локальная_переменная = имя_хранимой_процедуры;

DECLARE@return_value int,

@IdProd int

EXEC@return_value = spr_addProduct

@Description = N'Новый товар',

@IdProd = @IdProd OUTPUT

IF @return_value = 0

BEGIN

PRINT 'Товар успешно добавлен'

SELECT @IdProd as N'@IdProd'

END

ELSE

BEGIN

PRINT 'При добавлении товара произошла ошибка'

SELECT 'Return Value' = @return_value

END

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

Возврат списка всех заказов содержащих заданный товар (по IdProd).

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

Удаление из базы данных информации об определенном клиенте (по IdCust). Если с данных клиентом имеются связанные записи (заказы) удаление должно быть отменено. Возвращаемое значение должно определять успешность выполнения операции.

Лабораторная работа №9. Функции

данная база триггер ссылка

Системные функции

SQL Server содержит богатый набор встроенных системных функций, которые формально подразделяются на следующие группы: статистические, функции настройки, функции работы с курсором, функции даты и времени, математические, функции работы с наборами строк, функции безопасности, строковые, системные статистические, функции обработки текста и изображений и прочие. Полный список системных функций, сгруппированных в отдельные папки по вышеуказанным категориям, можно увидеть в Management Studio в узле «Программирование - Функции - Системные функции» дерева обозревателя объектов. Рассмотрим некоторые из наиболее часто используемых скалярных (возвращающих одно значение) встроенных системных функций.

Информационные функции

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

User_name(). Возвращает имя текущего клиента, каким он представился базе данных. Когда пользователю открыт доступ к базе данных, его имя может отличаться от регистрационного имени входа на сервер.

Suser_sname(). Возвращает регистрационное имя пользователя, под которым он вошел на SQL Server. Даже если тот был аутентифицирован как член одной из групп пользователей Windows, функция все равно возвращает имя его учетной записи Windows.

Host_name(). Возвращает имя рабочей станции пользователя.

App_name(). Возвращает имя приложения, подключенного к SQL Server.

Пример использования:

SELECT

USER_NAME() AS 'Имя пользователя БД',

SUSER_SNAME() AS 'Имя входа',

HOST_NAME() AS 'Имя рабочей станции',

APP_NAME() AS 'Имя приложения'

Строковые функции

SQL Server поддерживает больше двух десятков функций для манипулирования строками. Рассмотрим несколько самых полезных из них.

Substring(строка, начальная_позиция, длина). Возвращает фрагмент строки. Первым параметром является сама строка, вторым - номер символа, с которого вырезается фрагмент, третьим - длина вырезаемого фрагмента. Например, результатом инструкции SELECT SUBSTRING('abcdefg', 3, 2) будет подстрока `cd'.

Stuff(строка, позиция_вставки, число_удаляемых_символов, вставляемая_строка). Противоположная по характеру функции substring(), функция stuff() вставляет одну строку в другую; при этом в позиции вставки может быть удалено заданное количество символов исходной строки. Например, результатом инструкции SELECT STUFF('abcdefg', 3, 2, '123') будет строка 'ab123efg'.

Replace(строка, строка). Заменяет заданные фрагменты строки другой строкой. Например, функция REPLACE('abacad', 'a', 'e') возвращает строку `ebeced'

Charindex(символ_поиска, строка, начальная_позиция). Возвращает позицию заданного символа в строке. Например, инструкция SELECT CHARINDEX('c', 'abcdefg', 1) вернет результат 3.

Patindex(%шабпон%, строка). Выполняет поиск по шаблону, который может содержать в строке символы макроподстановки. В следующем примере ищется первое вхождение в строку символа с или d: SELECT PATINDEX('%[cd]%', 'abdedefg'). Результатом данного запроса будет число 3.

Right(строка, число) и Left(строка, число). Возвращает крайнюю правую или левую часть строки. Например, результатом запроса SELECT LEFT('abcdefg',2) будет `ab'

Len(строка). Возвращает длину строки.

Rtrim(строка) и Ltrim(строка). Эти функции удаляют соответственно пробелы в начале и в конце строки.

Upper(строка) и Lower(строка). Преобразует символы строки соответственно в верхний или нижний регистр.

Функции работы с датой и временем

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

Константа	Значение
yy или yyyy	Год
qq или q	Квартал
mm или m	Месяц
wk или ww	Неделя
dw или w	День недели
dy или y	День года
dd или d	День
hh	Час
mi или n	Минута
ss или s	Секунда
ms	Миллисекунда

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

PRINT DATEADD(d, 3, GETDATE())

Ниже приведен полный список доступных функций даты и времени.

Dateadd(datepart, величина, дата_начала). Добавляет к дате указанную величину.

Datediff(datepart, величина, дата_начала). Выводит количество единиц времени, заданных в аргументе datepart, между двумя датами.

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

Datepart(datepart, дата). Извлекает определенный фрагмент из заданной даты.

Day(дата). Извлекает день из даты.

Getdate. Возвращает текущее время и дату.

Getutcdate. Возвращает текущую дату и время, преобразованное в формат универсального синхронизированного времени (UTC).

Month(дата). Извлекает месяц из даты.

Year(дата). Извлекает год из даты.

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

SELECT *, DATENAME(dw, OrdDate) AS 'День недели'

FROM [Order]

WHERE MONTH(OrdDate) = 9

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

Для явных преобразований одно типа данных в другой в SQL Server используют функции cast() и convert().

Cast(исходные_данные AS тип_данных). Стандарт ANSI SQL рекомендует явное преобразование одного типа данных в другой. Даже если такое преобразование может быть выполнено неявно сервером, использование функции cast() гарантирует получение нужного типа. Функция cast() программируется несколько отлично от других. Вместо разделения двух своих аргументов запятой используется ключевое слово AS, за которым следует требуемый тип данных, например:

SELECT [Description], 'Остаток на складе: ' + CAST(InStock AS varchar(10))

FROM Product

Convert(тип_данных, выражение [, стиль]). Эта функция возвращает значение, преобразованное в другой тип данных с произвольным форматированием. Эта функция не предусмотрена стандартом ANSI SQL. Первым ее аргументом является желаемый тип данных, применяемый к выражению. Аргумент стиль предполагает применение к результату некоторого стиля. Стиль обычно применяется при преобразовании из типа даты-времени в символьный и наоборот. Как правило, одно- или двухцифровой стиль предполагает двухцифровой год, а трехцифровой - четырехцифровой год. К примеру, стиль 1 подразумевает следующий формат данных: 01/01/03, в тоже время стиль 3 - 01/01/2003. Пример:

SELECT CONVERT(nvarchar(25), GETDATE(), 1)

SELECT CONVERT(nvarchar(25), GETDATE(), 100)

Функции для обработки пустых значений

Часто пустое значение нужно преобразовать в некоторое допустимое, чтобы данные можно было понять или чтобы выражение имело результат. Пустые значения требуют специальной обработки при использовании в выражениях, и язык SQL содержит ряд функций, специально предназначенных для работы с пустыми значениями. Функции isnull () и coalesce() преобразуют пустые значения в пригодные для использования, а функция nullif() создает пустое значение, если выполняется определенное условие.

Наиболее часто используемой функцией, предназначенной для работы с пустыми значениями, является isnull(). Эта функция в качестве аргумента принимает одно выражение или столбец, а также подстановочное значение. Если первый аргумент является допустимым значением (т.е. не пустым), эта функция возвращает его. Однако если первый аргумент представляет собой пустое значение, то возвращается значение второго аргумента. Общий синтаксис функции следующий:

Isnull(исходное_выражение, замещающее_значение).

Следующий пример подставляет строку 'не указан' вместо пустого значения там, где для клиента не определен телефон:

SELECT FName, LName, ISNULL(Phone, 'не указан') AS Phone

FROM Customer

Функция coalesce() принимает список выражений или столбцов и возвращает первое значение, которое окажется не пустым. Ее общий синтаксис следующий:

Coalesce(выражение, выражение, ...)

В следующем примере продемонстрирована функция coalesce(), возвращающая первое непустое значение (в данном случае это 3): SELECT COALESCE(NULL, 1+NULL, 1+2, 'abc'))

Иногда пустое значение нужно создать на месте заменяющего его суррогатного. Если база данных заполнена значениями n/a, - или пустыми строками там, где должны находиться пустые значения, вы можете воспользоваться функцией nullif() и расчистить базу данных.

Функция nullif() принимает два аргумента. Если они равны, то возвращается пустое значение, в противном случае возвращается первый параметр.

Следующий фрагмент кода преобразует все пробелы в столбце FName в пустые значения.

SELECT NULLIF(LTRIM(RTRIM(FName)),'') AS FName

FROM Customer

Пользовательские функции

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

Пользовательские функции обладают следующими преимущества:

С их помощью можно внедрить в запросы сложную логику.

Создавая новые функции, можно проектировать сложные выражения.

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

Они обладают достоинствами хранимых процедур, так как могут быть скомпилированы и оптимизированы таким же способом.

Главным аргументом против использования пользовательских функций является вопрос переносимости. Пользовательские функции привязаны к SQL Server, и любую базу данных, использующую множество таких функций, будет сложно или даже невозможно перенести на другую платформу СУБД без существенной переработки. Эта задача усложняется тем, что также должны быть переписаны и все инструкции SELECT, в которые внедрены пользовательские функции. Если в будущем планируется развертывание базы данных на других платформах, то лучше заменить все пользовательские функции представлениями или хранимыми процедурами.

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

Пользовательские функции могут возвращать значения, относящиеся к большинству типов данных SQL Server. Не допускается использовать в качестве типов возвращаемых значений лишь такие типы, как text, ntext, image, cursor и timestamp.

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

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

Пользовательские функции подразделяются на три типа:

Скалярные, возвращающие одно значение.

Внедренные табличные, аналогичные представлениям.

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

Скалярные функции

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

В скалярных пользовательских функциях не допускаются операции обновления базы данных, но в то же время они могут работать с локальными временными таблицами. Они не могут возвращать данные BLOB (двоичные большие объекты) таких типов, как text, image и ntext, равно как табличные переменные и курсоры.

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

CREATE FUNCTION имя_функции (входные_параметры) RETURNS тип_данных AS BEGIN текст_ функции RETURN выражение END

В списке входных параметров должны быть указаны типы данных и, в случае необходимости, значения по умолчанию, аналогично хранимым процедурам (параметр = умолчание). Параметры функции отличаются от параметров хранимых процедур тем, что даже если определены значения по умолчанию, параметры все равно должны присутствовать в вызове функции (т.е. параметры с определенными по умолчанию значениями все равно обязательны). Чтобы запросить значение по умолчанию при вызове функции, ей передается ключевое слово default.

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

CREATE FUNCTION dbo.Multiply (@A int, @B int = 3)

RETURNS INT

BEGIN

RETURN @A * @B

END

Скалярные функции могут использоваться в любом месте выражений, где допустимо одно значение. Пользовательские скалярные функции должны всегда вызываться с помощью двухкомпонентного имени (владелец.имя). В следующем примере продемонстрирован вызов ранее созданной функции Multiply:

SELECT dbo.Multiply(3,4)

SELECT dbo.Multiply(7, DEFAULT)

Следующий код создает функцию, возвращающую имя заданного клиента в формате Фамилия И.

CREATE FUNCTION getFICust (@IdCust int)

RETURNS varchar(25)

AS

BEGIN

DECLARE @result varchar(25)

SET @result = 'NULL'

SELECT @result = LName + ' ' + SUBSTRING(FName, 1, 1) + '.'

FROM Customer

WHERE IdCust = @IdCust

RETURN @result

END

Тестирование созданной функции:

SELECT dbo.getFICust(IdCust) AS CustName

FROM Customer

ORDER BY LName, FName

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

Количество товара на складе по заданному уникальному идентификатору товара;

Суммарную стоимость товаров в заданном заказе.

Внедренные табличные функции

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

Внедренная табличная функция не имеет в своем теле блока BEGIN ... END - вместо этого возвращается результирующий набор данных инструкции SELECT в виде таблицы с заданным именем:

CREATE FUNCTION имя_функции (параметры) RETURNS Table AS RETURN (инструкция_SELECT)

Следующая внедренная табличная функция является функциональным эквивалентом представления v_Customer созданного в лаб. занятии №6.

CREATE FUNCTION fCustomers ()

RETURNS TABLE

AS

RETURN

(

SELECT Customer.IdCust, Customer.FName, Customer.LName, City.CityName

FROM Customer INNER JOIN

City ON Customer.IdCity = City.IdCity

)

Для извлечения данных с помощью функции fCustomers вызовите ее в предложении FROM инструкции SELECT:

SELECT *

FROM dbo.fCustomers()

ORDER BY LName, FName

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

CREATE FUNCTION [dbo].[fCustomersForCity] (@IdCity int = NULL)

RETURNS TABLE

AS

RETURN

(

SELECT IdCust, FName, LName

FROM Customer

WHERE IdCity = @IdCity OR @IdCity IS NULL

)

Если функция вызывается с параметром по умолчанию, то возвращается список всех клиентов:

SELECT *

FROM dbo.fCustomersForCity(DEFAULT)

Если же в качестве параметра передается уникальный идентификатор города, то скомпилированная инструкция SELECT в функции вернет только клиентов из города с заданным кодом:

SELECT *

FROM dbo.fCustomersForCity(1)

Табличные функции с множеством инструкций

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

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

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

CREATE FUNCTION имя_функиии (входные_параметры) RETURNS @имя_таблицы TABLE (столбцы) AS BEGIN Программный код заполнения табличной переменной RETURN END

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

В начале инструкции CREATE FUNCTION создается табличная переменная.

В теле функции с помощью инструкций INSERT заполняют переменную.

После выполнения функции значение табличной переменной передается во внешнюю процедуру как результат функции.

Запишем предыдущую функцию в виде многооператорной функции.

CREATE FUNCTION [dbo].[fCustomersByCity2]

(

@IdCity int = NULL

)

RETURNS

@Result TABLE

(

IdCust int,

FName nvarchar(20),

LName nvarchar(20)

)

AS

BEGIN

IF (@IdCity IS NULL)

INSERT @Result

SELECT IdCust, FName, LName

FROM Customer

ELSE

INSERT @Result

SELECT IdCust, FName, LName

FROM Customer

WHERE IdCity = @IdCity

RETURN

END

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

Список всех товаров, которые не были ни разу заказаны

Список всех заказов за заданный период времени.

Лабораторная работа №10. Обработка ошибок. Управление транзакциями. Триггеры

Обработка ошибок. Блок TRY…CATCH.

Стандартным способом перехвата и обработки ошибок в Transact-SQL (начиная с версии SQL Server 2005) является использование конструкции TRY...CATCH, который напоминает обработку исключений, применяемую во многих языках программирования (Delphi, C++, C# и т.д.).

Общий синтаксис конструкции TRY...CATCH следующий:

BEGIN TRY { инструкции T-SQL } END TRY BEGIN CATCH [ { инструкции T-SQL } ] END CATCH [ ; ]

Конструкция TRY…CATCH состоит из двух частей: блок TRY и блок CATCH. При обнаружении ошибки в инструкции T-SQL внутри блока TRY управление передается блоку CATCH, где эта ошибка может быть обработана.

Блок TRY начинается с инструкции BEGIN TRY и завершается инструкцией END TRY. Между ними могут быть помещены одна или несколько инструкций T-SQL, при выполнении которых может произойти ошибка.

За блоком TRY сразу же должен следовать блок обработки ошибок CATCH. Блок CATCH начинается с инструкции BEGIN CATCH и завершается инструкцией END CATCH. В Transact-SQL каждый блок TRY ассоциирован только с одним блоком CATCH.

По завершении обработки исключения блоком CATCH управление передается первой инструкции T-SQL, следующей за инструкцией END CATCH. Если инструкция END CATCH является последней инструкцией хранимой процедуры или триггера, управление возвращается коду, вызвавшему эту хранимую процедуру или триггер. Инструкции T-SQL в блоке TRY, следующие за инструкцией, вызвавшей ошибку, не выполняются.

Если в блоке TRY ошибок нет, управление передается инструкции, следующей непосредственно за связанной с ней инструкцией END CATCH. Если инструкция END CATCH является последней инструкцией хранимой процедуры или триггера, управление передается инструкции, вызвавшей эту хранимую процедуру или триггер.

Рассмотрим следующий пример:

BEGIN TRY

PRINT 'Первая попытка';

--Имитация ошибки (деление на ноль)

DECLARE @i int

SET @i = 5/0

PRINT 'Вторая попытка';

END TRY

BEGIN CATCH

PRINT 'Секция обработки ошибки';

END CATCH;

PRINT 'Третья попытка';

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

Первая попытка

Секция обработки ошибки

Третья попытка

В этом примере SQL Server выполняет секцию TRY, пока не встречает строку имитирующую ошибку (в данном случае деление на ноль). После этого все последующие инструкции в блоке TRY (в данном случае вывод сообщения о второй попытке) пропускаются, и управление передается в секцию CATCH. Следом за блоком CATCH выполняется следующая по порядку инструкция, выводящая сообщение о третьей попытке.

Если в данном примере закомментировать строку, вызывающую ошибку, блок TRY будет выполнен полностью, а блок CATCH - проигнорирован:

Первая попытка

Вторая попытка

Третья попытка

Активация сообщений об ошибках вручную. Инструкция RAISERROR

Чтобы вернуть произвольное сообщение об ошибке в вызывающую процедуру или клиентское приложение, используют команду RAISERROR.

Синтаксис этой команды следующий:

RAISERROR ( сообщение или номер ошибки, степень_серьезности, состояние, [ дополнительные_аргументы ])

Степень серьезности ошибки является указанием на то, какие меры следует принимать с учетом этой ошибки. Система обозначений степеней серьезности ошибок в СУБД SQL Server охватывает широкий спектр сообщений об ошибках, включая те, которые по существу являются информационными (со значениями степеней серьезности 1-18), считаются относящимися к системному уровню (19-25) и даже рассматриваются как катастрофические (20-25). При возникновении ошибок со степенями серьезности 20 и выше автоматически завершается работа пользовательских соединений. Если необходимо завершить выполнение процедуры и активировать в клиентской программе ошибку, как правило, указывается степень серьезности 16.

Обозначение состояния представляет собой произвольную величину. Этот параметр оператора RAISERROR был введен в действие с учетом того, что одна и та же ошибка может возникнуть в несколько местах кода. А параметр с обозначением состояния предоставляет возможность передать вместе с сообщением своего рода маркер участка кода, который показывает, где именно произошла ошибка. Числа с обозначением состояния могут находиться в пределах от 1 до 127.

Замените в предыдущем примере строку с ошибкой деления на ноль командой, генерирующей пользовательскую ошибку:

RAISERROR('Имитация ошибки',16,1)

Управление транзакциями

Концепция транзакций - неотъемлемая часть любой клиент-серверной базы данных.

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

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

уменьшение баланса исходящего счета;

увеличение баланса принимающего счета.

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

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

BEGIN TRAN[SACTION] - объявление начала транзакции (в журнале транзакций фиксируются первоначальные значения изменяемых данных и момент начала транзакции).

COMMIT TRAN[SACTION] - фиксация транзакции (если в теле транзакции не было ошибок, то эта команда предписывает серверу зафиксировать все изменения, сделанные в транзакции, после чего в журнале транзакций помечается, что изменения зафиксированы и транзакция завершена).

ROLLBACK TRAN[SACTION] - откат транзакции (когда сервер встречает эту команду, происходит откат транзакции (отмена всех изменений), восстанавливается первоначальное состояние системы и в журнале транзакций отмечается, что транзакция была отменена).

Рассмотрим следующий пример.

Откройте новое окно запроса и выберите Sales в качестве активной базы данных

Введите и выполните следующий запрос

BEGIN TRANSACTION

Будет запущена транзакция. Все модификации данных в этом соединении не будут видны для других соединений.

Введите и выполните следующий запрос

INSERT City

VALUES ('Новый город')

Чтобы проверить, что модификация прошла успешно, введите и выполните следующий запрос

SELECT *

FROM City

В таблице появилась новая запись, но эти изменения видны только в данном соединении

Откройте новое окно запроса, введите и выполните в нем предыдущий запрос. Запрос не вернет результатов, поскольку он ждет завершения транзакции, запущенной в другом окне.

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

ROLLBACK TRANSACTION

Модификация данных отменена. Вернитесь во второе окно. Обратите внимание, что запрос выполнился и вернул данные. Добавленная строка отсутствует.

Операция оформления нового заказа предполагает добавление новых записей сразу в две таблицы: Order и OrdItem. Реализуем данную двойную операцию в виде единой транзакции:

BEGIN TRAN

BEGIN TRY

INSERT [Order](IdCust)

VALUES (2)

INSERT OrdItem(IdOrd,IdProd,Qty,Price)

VALUES (SCOPE_IDENTITY(),1,1,5)

END TRY

BEGIN CATCH

ROLLBACK TRAN

RAISERROR('Ошибка',16,1)

RETURN

END CATCH

COMMIT TRAN

Триггеры

Триггер, подобно хранимой процедуре, представляет собой сохраненный на сервере набор инструкций T-SQL. Главное отличие заключается в том, что его невозможно выполнить вручную с помощью команды EXEC. Триггер вызывается на выполнение не пользователем, а прикрепляется к определенной таблице и инициируется самим сервером баз данных как отклик на события вставки, обновления и удаления данных из этой таблицы, т.е. триггер выполняется автоматически как часть самого оператора модификации данных. Триггер на вставку запускается, когда в таблицу вставляется новая запись. Триггер на удаление запускается, когда из таблицы удаляется некоторая запись. Триггер на обновление запускается, когда некоторая запись таблицы изменяется. Кроме того можно определить триггер реагирующий сразу на несколько разных типов операций модификации, например на обновление и вставку. Триггер выполняется внутри того же пространства транзакции, что и оператор модификации данных, поэтому откат транзакции в триггере отменяет и саму исходную операцию модификации данных.

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

Сравнивать предшествующие и новые версии данных. В большинстве случаев в момент выполнения триггера необходимо знать, какие изменения были выполнены исходным оператором модификации данных. Сведения об этом можно найти в таблицах inserted (вставленные) и deleted (удаленные), которые становятся доступны внутри триггера. Эти таблицы - фактически представления строк в файле регистрации транзакции, которые были изменены оператором и имеют структуры и имена столбцов, идентичные таблице, которая изменилась. Таким образом, оценить, какие именно изменения были произведены в таблице, можно исследуя содержимое таблиц inserted и deleted, как показано в следующей таблице, и соответственно предпринимать те или иные действия в зависимости от обнаруженных различий.

Оператор	Содержимое таблицы inserted	Cодержимое таблицы deleted
INSERT	Добавленные строки	Пусто
UPDATE	Новые строки	Старые строки
DELETE	Пусто	Удаленные строки

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

Осуществлять считывание из других таблиц.

Изменять другие таблицы.

Выполнять хранимые процедуры и функции.

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

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