Процедурные расширения

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

В предыдущих статьях мы познакомились с инструкциями Transact-SQL языка описания данных DDL и языка манипуляции данными DML. Большинство этих инструкций можно сгруппировать в пакет. Пакет (batch) - это последовательность инструкций Transact-SQL и процедурных расширений, которые направляются системе базы данных для совместного их выполнения. Количество инструкций в пакете ограничивается допустимым размером скомпилированного пакетного объекта. Преимущество пакета над группой отдельных инструкций состоит в том, что одновременное исполнение всех инструкций позволяет получить значительное улучшение производительности.

Существует несколько ограничений на включение разных инструкций языка Transact-SQL в пакет. Наиболее важным из них является то обстоятельство, что если пакет содержит инструкцию описания данных CREATE VIEW, CREATE PROCEDURE или CREATE TRIGGER, то он не может содержать никаких других инструкций. Иными словами, такая инструкция должна быть единственной инструкцией пакета. Инструкции языка описания данных разделяются с помощью инструкции GO.

Каждое процедурное расширение языка Transact-SQL рассматривается по отдельности в следующих разделах.

Блок инструкций

Блок инструкций может состоять из одной или нескольких инструкций языка Transact-SQL. Каждый блок начинается с инструкции BEGIN и заканчивается инструкцией END, как это показано далее:

BEGIN
    оператор 1
    оператор 2
    ...
END

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

Инструкция IF

Инструкция IF языка Transact-SQL соответствует одноименной инструкции, поддерживаемой почти всеми языками программирования. Инструкция IF выполняет одну или несколько составляющих блок инструкций, если логическое выражение, следующее после ключевого слова IF, возвращает значение true (истина). Если же инструкция IF содержит оператор ELSE, то при условии, что логическое выражение возвращает значение false (ложь), выполняется вторая группа инструкций.

Ниже показан пример использования условной инструкции IF:

USE SampleDb;

IF (SELECT COUNT(*)
        FROM Works_on
        WHERE ProjectNumber = 'p1'
            GROUP BY ProjectNumber ) > 3
PRINT 'Число сотрудников работающих над проектом p1 больше 3'
ELSE BEGIN
    PRINT 'Следующие сотрудники работают над проектом p1:'
    SELECT FirstName, LastName
        FROM Employee, Works_on
        WHERE Employee.Id = Works_on.EmpId
            AND ProjectNumber = 'p1'
END

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

(SELECT COUNT(*)
        FROM Works_on
        WHERE ProjectNumber = 'p1'
            GROUP BY ProjectNumber ) > 3

возвращает значение true (истина) для базы данных SampleDb. Поэтому будет выполняться инструкция PRINT, входящая в часть инструкции IF. Обратите внимание на то обстоятельство, что в этом примере используется подзапрос, чтобы возвратить число строк (посредством агрегатной функции COUNT), удовлетворяющих условию предложения WHERE (ProjectNumber='p1').

Оператор ELSE инструкции IF в примере содержит две инструкции: PRINT и SELECT. Поэтому для выполнения этих инструкций их необходимо заключить в блок между ключевыми словами BEGIN и END. (Инструкция PRINT является процедурным расширением и возвращает определяемое пользователем сообщение.)

Инструкция WHILE

Инструкция WHILE выполняет одну или несколько заключенных в блок инструкций, на протяжении времени, пока (WHILE) логическое выражение возвращает значение true (истина), т.е. позволяет создать цикл. Иными словами, если выражение возвращает true, выполняется инструкция или блок инструкций, после чего снова осуществляется проверка выражения. Этот процесс повторяется до тех пор, пока выражение не возвратит значение FALSE (ложь).

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

В примере ниже показано использование инструкции WHILE:

USE SampleDb;

WHILE (SELECT SUM(Budget)
        FROM Project) < 500000
BEGIN
    UPDATE Project SET Budget = Budget * 1.1
    IF (SELECT MAX(budget)
            FROM project) > 240000
        BREAK
    ELSE CONTINUE
END

В этом примере бюджеты всех проектов увеличиваются на 10% до тех пор, пока общая сумма бюджетов не превысит $500 000. Но выполнение блока прекратится, даже если общая сумма бюджетов будет меньше $500 000, если только бюджет одного из проектов превысит $240 000.

Локальные переменные

Локальные переменные являются важным процедурным расширением языка Transact-SQL. Они применяются для хранения значений любого типа в пакетах и подпрограммах. Локальными они называются по той причине, что они могут быть использованы только в том пакете, в котором они были объявлены. (Компонент Database Engine также поддерживает глобальные переменные, которые уже были рассмотрены ранее.)

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

используя специальную форму инструкции SELECT;
используя инструкцию SET;
непосредственно в инструкции DECLARE посредством знака = (например, @extra_budget MONEY = 1500).

Использование первых двух способов присвоения значения локальным переменным показано в примере ниже:

USE SampleDb;

-- Объявление и инициализация переменных
DECLARE @avg_budget MONEY, 
        @extra_budget MONEY SET @extra_budget = 15000
SELECT @avg_budget = AVG(Budget) FROM Project;

IF (SELECT Budget
    FROM Project
    WHERE Number = 'p1') < @avg_budget
BEGIN
    UPDATE Project
        SET Budget = Budget + @extra_budget
        WHERE Number ='p1'
        PRINT N'Бюджет проекта p1 увеличен на ' + convert(nvarchar(30), @extra_budget) 
END
ELSE PRINT N'Бюджет проекта p1 не изменился'

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

Пример использования локальных переменных в T-SQL

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

Смешанные процедурные инструкции

Процедурные расширения языка Transact-SQL также содержат следующие инструкции:

RETURN;
GOTO;
RAISEERROR;
WAITFOR

Инструкция RETURN выполняет ту же самую функцию внутри пакета, что и инструкция BREAK внутри цикла WHILE. Иными словами, инструкция RETURN останавливает выполнение пакета и начинает исполнение первой инструкции, следующей за пакетом.

Инструкция GOTO передает управление при выполнении пакета инструкции Transact-SQL внутри пакета, обозначенной маркером. Инструкция RAISEERROR выводит определенное пользователем сообщение об ошибке и устанавливает флаг системной ошибки. Номер ошибки в определяемом пользователем сообщении должен быть больше, чем 50 000, т.к. все номера ошибок меньшие или равные 50 000 определены системой и зарезервированы компонентом Database Engine. Значения номеров ошибок сохраняются в глобальной переменной @@error.

Инструкция WAITFOR определяет задержку на период времени (с параметром DELAY) или определенное время (с параметром TIME), на протяжении которой система должна ожидать, прежде чем исполнять следующую инструкцию пакета. Синтаксис этой инструкции выглядит следующим образом:

WAITFOR 
	{DELAY 'time' | TIME 'time' | TIMEOUT 'timeout' }

Параметр DELAY указывает системе баз данных ожидать, пока не истечет указанный период времени, а параметр TIME указывает точку во времени, в одном из допустимых форматов, до которой ожидать. Параметр TIMEOUT, за которым следует аргумент timeout, задает период времени в миллисекундах, в течение которого надо ожидать прибытия сообщения в очередь.

Обработка исключений с помощью инструкций TRY, CATCH и THROW

В версиях SQL Server более ранних, чем SQL Server 2005 требовалось наличие кода для обработки ошибок после каждой инструкции Transact-SQL, которая могла бы вызвать ошибку. (Для обработки ошибок можно использовать глобальную переменную @@error.) Начиная с версии SQL Server 2005, исключения можно перехватывать для обработки с помощью инструкций TRY и CATCH. В этом разделе сначала объясняется значение понятия "исключение", после чего обсуждается работа этих двух инструкций.

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

Роль инструкции TRY заключается в перехвате исключения. (Поскольку для реализации этого процесса обычно требуется несколько инструкций, то обычно применяется термин "блок TRY", а не "инструкция TRY".) Если же в блоке TRY возникает исключение, компонент системы, называющийся обработчиком исключений, доставляет это исключение для обработки другой части программы. Эта часть программы обозначается ключевым словом CATCH и поэтому называется блоком CATCH.

Обработка исключений с использованием инструкций TRY и CATCH является общим методом обработки ошибок, применяемым в современных языках программирования, таких как C# и Java.

Обработка исключений с помощью блоков TRY и CATCH предоставляет программисту множество преимуществ, включая следующие:

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

В SQL Server 2012 добавлена еще одна инструкция THROW, имеющая отношение к обработке ошибок. Эта инструкция позволяет вызвать исключение, которое улавливается в блоке обработки исключений. Иными словами, инструкция THROW - это другой механизм возврата, который работает подобно рассмотренной ранее инструкции RAISEERROR.

Использование инструкций TRY, CATCH и THROW для обработки исключений показано в примере ниже:

USE SampleDb;

BEGIN TRY
    BEGIN TRANSACTION
        insert into Employee values(11111, 'Анна', 'Самойленко','d2');
        insert into Employee values(22222, 'Игорь', 'Васин','d4');
        -- Создаем ошибку ссылочной целостности
        insert into Employee values(33333, 'Петр', 'Сидоров', 'd2');
    COMMIT TRANSACTION
    PRINT 'Транзакция выполнена'
END TRY
BEGIN CATCH
    ROLLBACK
        PRINT 'Отмена транзакции';
    THROW
END CATCH

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

Выполнение кода в примере осуществляется следующим образом. После успешного выполнения первой инструкции INSERT попытка исполнения второй инструкции вызывает ошибку нарушения ссылочной целостности. Так как все три инструкции заключены в блок TRY, возникает исключение для всего блока и обработчик исключений начинает исполнение блока CATCH. Выполнение кода в этом блоке осуществляет откат исполнения всех инструкций в блоке TRY и выводит соответствующее сообщение. После этого инструкция THROW возвращает управление исполнением вызывающему объекту. Вследствие всего этого содержимое таблицы Employee не будет изменено.

Инструкции Transact-SQL - BEGIN TRANSACTION, COMMIT TRANSACTION и ROLLBACK начинают, фиксируют и выполняют откат транзакций соответственно. Предмет транзакций, в общем, и эти инструкции, в частности, мы будем рассматривать в одной из следующих статей.