Главная » Microsoft SQL Server, Базы данных » Обработка ошибок

0

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

Команда Try. . .Catch привнесла в SQL Server средства обработки ошибок Новинка              *** века. После перевода своих баз данных в среду SQL Server 2005 в первую

2005 G   очередь перестройте обработку ошибок.

Try…Catch

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

BEGIN TRY;

<программа> ;

END TRY BEGIN CATCH;

<программа> ;

END CATCH;

Компилятор T-SQL трактует комбинацию try. . .begin catch как единую команду. Как и в любой другой инструкции, наличие терминатора до или точки с запятой между этими двумя командами приведет к необрабатываемой ошибке. Инструкция begin catch должна следовать непосредственно за end try.

Если при выполнении секции try происходит какая-либо ошибка, то управление немедленно предается секции catch. Если секция try выполняется без ошибок, то блок catch вообще не выполняется. Приведем пример:

BEGIN TRY;

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

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

Select ‘Вторая попытка';

END TRY BEGIN CATCH

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

END CATCH;

SELECT ‘Третья попытка 1 ;

Будет получен следующий результат:

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

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

Третья попытка (1 row(s) affected)

В этом примере SQL Server выполняет секцию try, пока не встречает функцию raise г г or, имитирующую ошибку. После этого управление передается в секцию catch. Следом за блоком catch выполняется следующая по порядку инструкция, выводящая сообщение о третьей попытке.

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

Таблица 18.2. Функции перехвата

Функция Что возвращает
Error_Message() Текст сообщения об ошибке
Error_Number() Номер ошибки
Функция Что возвращает
Error Procedure() Имя хранимой процедуры или триггера, в которых возникла ошибка
Error_Severity() Опасность ошибки
Error_State() Состояние ошибки

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

ERROR_MESSAGE() AS [Message],

ERROR_PROCEDURE() AS [Procedure],

ERROR_LINE() AS Line,

ERROR_NUMBER() AS Number,

ERROR_SEVERITY() AS Severity,

ERROR_STATE() AS State;

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

Message            Procedure Line Number Severity State

Имитация ошибки NULL          4             50000 16                                 1

Полученные данные могут впоследствии быть запротоколированы в журнале ошибок.

Старая глобальная переменная @@Еггог

Исторически сложилось так, что обработка ошибок в Т-SQL всегда хромала на обе ноги. Основная информация хранилась в глобальных переменных @@Еггог и @@rowcount. Она содержала состояние выполнения предыдущей инструкции; нулевое значение соответствовало отсутствию ошибок.

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

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

USE Family;

UPDATE Person

SET PersonID = 1 Where PersonID = 2;

Print @@Error;

Print @@Error;

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

Server: Msg 547, Level 16, State 1, Line 1

UPDATE statement conflicted with COLUMN REFERENCE constraint

‘FK_______ Marriage___ Husband   7B905C75′. The conflict occurred in

database ‘Family’, table ‘Marriage’, column ‘HusbandID’.

The statement has been terminated.

547

0

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

USE Family;

DECLARE @err INT;

UPDATE Person

SET PersonID = 1 Where PersonID = 2 SET @err = @@Error;

IF @err <> 0 Begin

— код обработки ошибки

Print @err

End;

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

Msg 547, Level 16, State 1, Line 1

UPDATE statement conflicted with COLUMN REFERENCE constraint

‘ FK_____ Marriage__ Husban          7B905C75′. The conflict occurred in database

‘Family1, table ‘Marriage1, column ‘HusbandID’.

The statement has been terminated.

547

Глобальная переменная @@RowCount

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

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

В следующем пакете переменная @@RowCount используется для проверки числа обновленных строк. Отсутствие результата искусственно вызывается некорректным условием предложения WHERE. В таблице не существует личности с идентификатором 100, а переменная @@RowCount используется для локализации причины ошибки:

USE FAMILY;

UPDATE Person

SET LastName = ‘Johnson’

WHERE PersonID = 100;

IF @@RowCount = 0 Begin

— код обработки ошибки

Print ‘He обработано ни одной строки’

End;

Результат выполнения пакета будет следующим:

Не обработано ни одной строки

Raiserror

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

Простая форма команды Raiserror

Простая форма команды Raiserror, существующая со времен Sybase, передает только номер ошибки и сообщение. Уровень опасности всегда устанавливается в 16— определяемый пользователем:

RAISERROR нсмер_ошибки, сообщение;

Например, следующий программный код возвращает простое сообщение об ошибке: RAISERROR 5551212 ‘Невозможно обновить клиента’

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

Msg 5551212, Level 16, State 1, Line 1 ‘Невозможно обновить клиента’

Полная форма команды Raiserror

Усовершенствованная форма команды Raiserror вобрала в себя четыре новые функции.

?               Указывает уровень опасности ошибки.

?               Позволяет динамически изменять сообщение.

В Использует хранимые сообщения уровня сервера.

?               Позволяет протоколировать ошибки в журнал.

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

RAISERROR ( сообщение или номер, опасность, состояние, дополнительные_аргументы ) With Log;

Опасность ошибки

В системе Windows установлена система стандартных кодов опасности ошибок (табл. 18.3). Коды, не перечисленные в таблице, зарезервированы компанией Microsoft.

Таблица 18.3. Доступные коды опасности
Код опасности Описание
10 Ошибка не сгенерирована, но возвращается сообщение, например с помощью коман- ДЫ print
11-13 Нет специального значения
14 Информационное сообщение
15 Предупреждение: что-то пошло не так
16 Критичная ошибка. Выполнение процедуры прекращено

Добавление в сообщение переменных параметров

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

RAISERROR (‘Невозможно обновить %s.’, 14, 1, ‘Customer’);

Будет получен следующий результат:

Msg 50000, Level 14, State 1, Line 1 Невозможно обновить Customer.

Хранимые сообщения

Команда raiserror системы Windows также может извлечь сообщение из системного представления sysmessages. Номера сообщений 1-50000 зарезервированы компанией Microsoft, а более высокие доступны для пользователей. Основным преимуществом использования хранимых сообщений является их последовательность и пронумерованность.

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

Таблица sysmessages содержит столбцы идентификатора и текста сообщения, уровня опасности, а также признака протоколирования. В то же время уровень опасности команды raiserror подставляется вместо значения из таблицы SysMessage; таким образом, последнее нивелируется.

Для управления сообщениями в программном коде используется системная хранимая процедура sp_addmessage:

EXEC sp_addmessage 50001, 16, ‘Невозможно обновить %s';

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

Если сообщение уже существует, то в вызов хранимой процедуры можно добавить параметр replace:

EXEC sp_addmessage 50 001, 16,

‘Все еще невозможно обновить %s’, @Replace = ‘Replace';

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

SELECT *

FROM sysmessages

WHERE message_id > 50000;

Может быть получен следующий результат: message_id language_id severity is_event_logged text

50001                           1033              16            0 Все еще невозможно обновить %s

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

?               Сохранить сценарий, изначально использовавшийся для загрузки сообщений.

?               Использовать следующий запрос для генерирования сценария, добавляющего сообщения.

SELECT ‘ЕХЕС sp_addmessage, ‘

+ Cast(message_id as VARCHAR(7))

+ ‘ , ‘ + Cast(Severity as VARCHAR(2))

+ ‘ , ”’ + [text] + ”';’

FROM sysmessages

WHERE message_id > 50000;

Результат будет следующий:

EXEC sp_addmessage, 50001, 16, ‘Все еще невозможно обновить %s';

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

ЕХЕС sp_dropmessage 50001;

Протоколирование ошибок

Еще одним преимуществом использования формы Windows команды га is error является возможность протоколирования ошибок в журнал событий Windows NT и в журнал событий SQL Server. Недостатком журнала событий Windows является то, что ведется он отдельно на каждой рабочей станции. Несмотря на то что этот журнал является отличным местом для хранения ошибок подключения к серверу, для ошибок базы данных он явно не подходит. Существуют два способа задания необходимости протоколирования событий.

?               Если хранимые сообщения создаются с помощью задания параметра @with_log= ‘ with_ log’ или установки флажка Always log в процессе добавления нового сообщения в Management Studio, ошибки будут протоколироваться.

?               Если в команде raiserror указан параметр with log, то будет протоколироваться только данное сообщение об ошибке.

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

RAISERROR (‘Невозможно обновить %s.’, 14, 1, ‘Customer’)

WITH LOG

Результат будет следующим:

Server: Msg 50000, Level 14, State 1, Line 1 Невозможно обновить Customer.

Чтобы просмотреть ошибки в журнале событий (рис. 18.1), выберите в меню Пуск системы Windows пункт Панель управления^Средства администрирования^Журнал событий (Control Panels Administrative Tools’^ Event Viewer). Запустить просмотр журнала событий можно также из меню Все программы (Programs).

Журнал SQL Server

SQL Server поддерживает несколько файлов журналов. При каждом запуске SQL Server создает новый файл журнала. SQL Server постоянно поддерживает семь файлов журналов: шесть архивных и один текущий. Все эти журналы перечислены в окне Object Explorer утилиты Management Studio в узле Management^SQL Server Logs. Дважды щелкните на названии журнала, чтобы открыть окно просмотра, показанное на рис. 18.2. В просмотре вам помогут установка фильтра и функция поиска.

Puc. 18.1. Ошибка SQL Server в окне журнала событий Windows. Обратите внимание на то, что в данные об ошибке включены имена сервера и базы данных

Puc. 18.2. Просмотр ошибок в журнале SQL Server в окне утилиты Management Studio

БЛОК Catch

Для перехвата и обработки ошибок обычно используют структуру Try. . . Catch. В блоке Catch вы можете сделать следующее.

1.              Если пакет использует логические транзакции (begin tran. .. commit tran), то обработчик ошибки должен откатить транзакцию. Лично я рекомендую использовать откат как первое действие, чтобы снять все блокировки, установленные транзакцией.

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

3.              При желании зарегистрируйте ошибку в отдельной таблице.

4.              Завершите выполнение пакета. Если она произошла в хранимой процедуре, пользовательской функции или триггере, то завершите их выполнение командой return.

В следующем примере продемонстрирована обработка ошибок. Если ошибка связана с SQL Server и она обнаружена в блоке try, то управление немедленно передается в блок catch, в котором она обрабатывается:

Begin Try — Код T-SQL End Try Begin Catch

— Код обработки ошибок End Catch

Фатальные ошибки T-SQL

Если обнаружена фатальная ошибка Т-SQL, то выполнение пакета немедленно завершается, и вам не предоставляется никакой возможности просмотреть содержимое переменной @@Еггог, обработать ошибку и, возможно, исправить ситуацию.

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

?               несовместимостью типов данных;

?               недоступностью ресурсов SQL Server;

?               синтаксическими ошибками;

?               дополнительными настройками SQL Server, несовместимыми с конкретной задачей;

?               отсутствием объектов или опечатками в их именах.

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

SELECT Error, Severity, Description FROM Master.dbo.SysMessages WHERE Severity >= 19 ORDER BY Severity, Error

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

Резюме

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

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

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии