По материалам статьи Amol Kulkarni: Exception
Handling in SQL Server 2000 and Yukon
Перевод Виталия Степаненко
Введение
Обработка ошибок играет жизненно важную роль при создании хранимых процедур или скриптов. В этой статье я рассмотрю обработку ошибок в SQL Server 2000 и SQL Server Yukon. Перед рассмотрением обработки ошибок я познакомлю вас с компонентами сообщения об ошибке. Вот типичное сообщение об ошибке, которое вы можете получить, работая в SQL Server 2000 Query Analyzer:
Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__test__08EA5793'. Cannot insert duplicate key in object 'test'. The statement has been terminated.
Это сообщение состоит из нескольких компонентов, таких, как Msg, Level, State и Line. Рассмотрим подробно каждый из этих компонентов:
Msg - номер сообщения об ошибке. Каждое сообщение об ошибке имеет свой номер, начиная с 0. В предыдущем примере номер сообщения об ошибки - 2627, и каждое такое сообщение имеет свой собственный текст. Вы можете найти информацию по всем сообщениям об ошибках в таблице sysmessages базы данных master. Создавая ваши собственные скрипты, вы можете добавить свои сообщения об ошибках, используя системную хранимую процедуру sp_addmessage. Сообщения с номерами от 50001 зарезервированы для пользователей. Сообщения с меньшими номерами - системные.
Severity Level - определяет серьезность ошибки. Имеет значение от 0 до 25.
Номера от 0 до 10 указывают на информационные сообщения или предупреждения; номера от 11 до 16 указывают на ошибки в программировании (при создании скриптов); уровень серьезности ошибки от 17 и выше обозначает проблемы с отсутствием ресурсов сервера или проблемы с аппаратным обеспечением. Любая ошибка выше 20 уровня прерывает соединение.
State - имеет значение от 0 до 127. Об этом компоненте Microsoft распространила слишком мало информации, поэтому я не буду рассматривать значения компонента State в этой статье.
Line - определяет номер строки в процедуре, функции, триггере или пакете, где возникла ошибка.
Обработка ошибок в SQL Server 2000 и Yukon.
Существуют две причины возникновения сообщений об ошибках в SQL Server 2000 или Yukon; SQL Server может вызвать ошибку, или вы можете сгенерировать ее лично, используя команду RAISERROR. Рассмотрим команду RAISERROR подробнее. Ниже приведен пример:
RAISERROR('This error is raised by me', 16, 1)
В команде вы указываете текст сообщения, уровень серьезности ошибки и ее состояние. Результат выглядит следующим образом:
Server: Msg 50000, Level 16, State 1, Line 1
This
error is raised by me
Кроме текста, передаваемого в команду RAISERROR, можно также указать номер сообщения об ошибке, созданного при помощи хранимой процедуры sp_addmessage. Номер пользовательского сообщения об ошибке должен иметь значение от 13000 до 2147483647. Если указывается номер, отсутствующий в таблице sysmessages, то вы получите предупреждение о том, что текст сообщения об ошибке не найден.
Теперь, зная основы работы сообщений об ошибках в SQL Server, давайте рассмотрим обработку ошибок в Transact-SQL. SQL Server позволяет обрабатывать ошибки, используя глобальную переменную @@ERROR. Если генерируется ошибка, то переменная @@ERROR содержит соответствующий номер ошибки, в случае же успеха она возвращает 0. Хранящееся значение номера ошибки может быть положительным, отрицательным, или равным 0. Значение переменной @@ERROR непостоянно, т.е. оно изменяется при выполнении каждой команды. Рассмотрим следующий пример:
Запустив этот код, получим сообщение об ошибке:
Msg 515, Level 16, State 2, Line 2 Cannot insert the value NULL into column 'a', table 'AdventureWorks.dbo.ErrorTest '; column does not allow nulls. INSERT fails. The statement has been terminated. Error is 0
По результату можно видеть, что возникла ошибка, но значение переменной @@error равно 0, что означает успешное выполнение. Действительно, 3 строка вызвала ошибку и значение переменной @@error стало равным номеру возникшей ошибки. Но 4 строка выполнилась успешно, поэтому и значение @@error сбросилось в 0. Соответственно, и результат выполнения скрипта равен 0. Поэтому можно заключить, что значение @@error изменяется после выполнения каждой команды. Т.к. значение @@error изменяется так часто, можно воспользоваться локальной переменной для хранения значения @@error, чтобы использовать это значение, когда потребуется, как показано ниже:
В этом случае я использовал локальную переменную @err для хранения значения @@error. Когда я выполнил код, появилось сообщение об ошибке:
Msg 515, Level 16, State 2, Line 2 Cannot insert the value NULL into column 'a', table 'AdventureWorks.dbo.ErrorTest '; column does not allow nulls. INSERT fails. The statement has been terminated. Error is 515
Другим возможным способом обработки ошибок является использование глобальных переменных @@rowcount и @@trancount. Как и @@error, эти переменные также часто изменяют свое значение.
@@rowcount - это глобальная переменная, которая хранит количество строк, обработанных последней выполненной командой. Как я уже упоминал, ее значение также постоянно изменяется; значение этой переменной необходимо сохранять в локальных переменных для последующего использования. В определенных случаях возникает необходимость обработать какую-то ситуацию как ошибку, даже если SQL Server все успешно выполнил. Например, вы выполняете команду UPDATE и ожидаете, что она обработает n строк. Однако если она не обработала ни одну строку, то это не вызывает ошибку T-SQL. Такие ситуации могут быть обработаны при помощи переменной @@rowcount. Можно сгенерировать ошибку, используя RAISERROR, если значение @@rowcount не равно ожидаемому.
@@trancount - это глобальная переменная, которая показывает уровень вложенности транзакции. Каждая команда BEGIN TRANSACTION увеличивает значение @@trancount на 1, и каждая команда COMMIT TRANSACTION уменьшает значение @@trancount на 1. На самом деле транзакции не выполняются, пока значение @@trancount не достигнет 0. Команда ROLLBACK TRANSACTION откатывает все изменения до самой первой команды BEGIN TRANSACTION (если только вы не использовали достаточно экзотическую команду SAVE TRANSACTION), и устанавливает @@trancount в 0, независимо от предыдущего значения.
Рассмотрим следующий код:
Вот результаты запуска скрипта:
(1 row(s) affected) (1 row(s) affected) Transaction count is :1 (1 row(s) affected) (1 row(s) affected) Transaction count is :2 Msg 547, Level 16, State 0, Line 1 INSERT statement conflicted with CHECK constraint 'stud_ck'. The conflict occurred in database 'AdventureWorks', table 'student', column 'marks'. The statement has been terminated. (1 row(s) affected) Error is 547 Transaction count is :3 Transaction count is :2 Transaction count is :1 Transaction count is :0
Из результата выполнения скрипта видно, что значение @@trancount увеличивалось с каждым выполнением BEGIN TRANSACTION, и уменьшалось с каждым выполнением COMMIT TRANSACTION.
Последняя команда COMMIT TRANSACTION установила значение @@trancount в 0, указывая на успешное выполнение скрипта.
Т.к. мы уже обсудили разные пути обработки ошибок в SQL Server 2000, то можно видеть, что такой путь обработки ошибок в SQL Server 2000 неудобен, т.к. для каждой команды должна храниться локальная переменная, что уменьшает ясность кода и увеличивает его сложность и размер.
Новая функциональность при обработке ошибок в SQL Server Yukon.
В Yukon произведено глобальное улучшение обработки ошибок в транзакциях T-SQL. Помимо всей функциональности, присутствующей в SQL Server 2000, теперь можно перехватывать ошибки прерывания транзакции, используя модель TRY/CATCH без потери контекста транзакции. Взглянем на следующий код:
В этом примере я использовал один блок TRY/CATCH и не использовал переменную @@error. Это улучшает читабельность кода и сильно упрощает его написание. Основное правило использования TRY/CATCH состоит в том, что блок CATCH должен сразу следовать за блоком TRY.
Все, что здесь обсуждалось до этого момента, применимо при XACT_ABORT в OFF (по умолчанию). Если XACT_ABORT устанавливается в ON, то большинство ошибок, прерывающих выполнение команды, становятся ошибками, прерывающими выполнение пакета. Рассмотрим следующий код:
Ниже приведен результат выполнения скрипта:
(1 row(s) affected) (1 row(s) affected) Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__YukonTest__5E1FF51F'. Cannot insert duplicate key in object 'YukonTest'. Msg 3903, Level 16, State 1, Line 9 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
В примере 4, когда XACT_ABORT был установлен в OFF, выполнение вставок не прервалось при возникновении ошибки в строке 6. Была прервана только команда, в которой произошла ошибка. Это называется прерыванием команды. В примере 5, когда XACT_ABORT был установлен в ON, выполнение скрипта прервалось. Т.к. выполнение скрипта прервалось в строке 7, то команда в строке 8 не вставила верное значение.
Работаете ли вы с SQL Server 2000 или хотите узнать Yukon, умение писать хороший код с обработкой всех возможных ошибок является важным условием для достижения вершин мастерства.
Amol Kulkarni является сотрудником Tata Consultancy Services (TCS), Hyderabad, India.