Время прочтения
16 мин
Просмотры 35K
Привет, Хабр! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.
1. Введение
Эта статья – первая в серии из трёх статей, посвященных обработке ошибок и транзакций в SQL Server. Её цель – дать вам быстрый старт в теме обработки ошибок, показав базовый пример, который подходит для большей части вашего кода. Эта часть написана в расчете на неопытного читателя, и по этой причине я намеренно умалчиваю о многих деталях. В данный момент задача состоит в том, чтобы рассказать как без упора на почему. Если вы принимаете мои слова на веру, вы можете прочесть только эту часть и отложить остальные две для дальнейших этапов в вашей карьере.
С другой стороны, если вы ставите под сомнение мои рекомендации, вам определенно необходимо прочитать две остальные части, где я погружаюсь в детали намного более глубоко, исследуя очень запутанный мир обработки ошибок и транзакций в SQL Server. Вторая и третья части, так же, как и три приложения, предназначены для читателей с более глубоким опытом. Первая статья — короткая, вторая и третья значительно длиннее.
Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.
1.1 Зачем нужна обработка ошибок?
Почему мы обрабатываем ошибки в нашем коде? На это есть много причин. Например, на формах в приложении мы проверяем введенные данные и информируем пользователей о допущенных при вводе ошибках. Ошибки пользователя – это предвиденные ошибки. Но нам также нужно обрабатывать непредвиденные ошибки. То есть, ошибки могут возникнуть из-за того, что мы что-то упустили при написании кода. Простой подход – это прервать выполнение или хотя бы вернуться на этап, в котором мы имеем полный контроль над происходящим. Недостаточно будет просто подчеркнуть, что совершенно непозволительно игнорировать непредвиденные ошибки. Это недостаток, который может вызвать губительные последствия: например, стать причиной того, что приложение будет предоставлять некорректную информацию пользователю или, что еще хуже, сохранять некорректные данные в базе. Также важно сообщать о возникновении ошибки с той целью, чтобы пользователь не думал о том, что операция прошла успешно, в то время как ваш код на самом деле ничего не выполнил.
Мы часто хотим, чтобы в базе данных изменения были атомарными. Например, задача по переводу денег с одного счета на другой. С этой целью мы должны изменить две записи в таблице CashHoldings и добавить две записи в таблицу Transactions. Абсолютно недопустимо, чтобы ошибки или сбой привели к тому, что деньги будут переведены на счет получателя, а со счета отправителя они не будут списаны. По этой причине обработка ошибок также касается и обработки транзакций. В приведенном примере нам нужно обернуть операцию в BEGIN TRANSACTION и COMMIT TRANSACTION, но не только это: в случае ошибки мы должны убедиться, что транзакция откачена.
2. Основные команды
Мы начнем с обзора наиболее важных команд, которые необходимы для обработки ошибок. Во второй части я опишу все команды, относящиеся к обработке ошибок и транзакций.
2.1 TRY-CATCH
Основным механизмом обработки ошибок является конструкция TRY-CATCH, очень напоминающая подобные конструкции в других языках. Структура такова:
BEGIN TRY
<обычный код>
END TRY
BEGIN CATCH
<обработка ошибок>
END CATCH
Если какая-либо ошибка появится в <обычный код>
, выполнение будет переведено в блок CATCH, и будет выполнен код обработки ошибок.
Как правило, в CATCH откатывают любую открытую транзакцию и повторно вызывают ошибку. Таким образом, вызывающая клиентская программа понимает, что что-то пошло не так. Повторный вызов ошибки мы обсудим позже в этой статье.
Вот очень быстрый пример:
BEGIN TRY
DECLARE @x int
SELECT @x = 1/0
PRINT 'Not reached'
END TRY
BEGIN CATCH
PRINT 'This is the error: ' + error_message()
END CATCH
Результат выполнения: This is the error: Divide by zero error encountered.
Мы вернемся к функции error_message() позднее. Стоит отметить, что использование PRINT в обработчике CATCH приводится только в рамках экспериментов и не следует делать так в коде реального приложения.
Если <обычный код>
вызывает хранимую процедуру или запускает триггеры, то любая ошибка, которая в них возникнет, передаст выполнение в блок CATCH. Если более точно, то, когда возникает ошибка, SQL Server раскручивает стек до тех пор, пока не найдёт обработчик CATCH. И если такого обработчика нет, SQL Server отправляет сообщение об ошибке напрямую клиенту.
Есть одно очень важное ограничение у конструкции TRY-CATCH, которое нужно знать: она не ловит ошибки компиляции, которые возникают в той же области видимости. Рассмотрим пример:
CREATE PROCEDURE inner_sp AS
BEGIN TRY
PRINT 'This prints'
SELECT * FROM NoSuchTable
PRINT 'This does not print'
END TRY
BEGIN CATCH
PRINT 'And nor does this print'
END CATCH
go
EXEC inner_sp
Выходные данные:
This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'NoSuchTable'
Как можно видеть, блок TRY присутствует, но при возникновении ошибки выполнение не передается блоку CATCH, как это ожидалось. Это применимо ко всем ошибкам компиляции, таким как пропуск колонок, некорректные псевдонимы и тому подобное, которые возникают во время выполнения. (Ошибки компиляции могут возникнуть в SQL Server во время выполнения из-за отложенного разрешения имен – особенность, благодаря которой SQL Server позволяет создать процедуру, которая обращается к несуществующим таблицам.)
Эти ошибки не являются полностью неуловимыми; вы не можете поймать их в области, в которой они возникают, но вы можете поймать их во внешней области. Добавим такой код к предыдущему примеру:
CREATE PROCEDURE outer_sp AS
BEGIN TRY
EXEC inner_sp
END TRY
BEGIN CATCH
PRINT 'The error message is: ' + error_message()
END CATCH
go
EXEC outer_sp
Теперь мы получим на выходе это:
This prints
The error message is: Invalid object name 'NoSuchTable'.
На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.
2.2 SET XACT_ABORT ON
В начало ваших хранимых процедур следует всегда добавлять это выражение:
SET XACT_ABORT, NOCOUNT ON
Оно активирует два параметра сессии, которые выключены по умолчанию в целях совместимости с предыдущими версиями, но опыт доказывает, что лучший подход – это иметь эти параметры всегда включенными. Поведение SQL Server по умолчанию в той ситуации, когда не используется TRY-CATCH, заключается в том, что некоторые ошибки прерывают выполнение и откатывают любые открытые транзакции, в то время как с другими ошибками выполнение последующих инструкций продолжается. Когда вы включаете XACT_ABORT ON, почти все ошибки начинают вызывать одинаковый эффект: любая открытая транзакция откатывается, и выполнение кода прерывается. Есть несколько исключений, среди которых наиболее заметным является выражение RAISERROR.
Параметр XACT_ABORT необходим для более надежной обработки ошибок и транзакций. В частности, при настройках по умолчанию есть несколько ситуаций, когда выполнение может быть прервано без какого-либо отката транзакции, даже если у вас есть TRY-CATCH. Мы видели такой пример в предыдущем разделе, где мы выяснили, что TRY-CATCH не перехватывает ошибки компиляции, возникшие в той же области. Открытая транзакция, которая не была откачена из-за ошибки, может вызвать серьезные проблемы, если приложение работает дальше без завершения транзакции или ее отката.
Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.
Параметр NOCOUNT не имеет к обработке ошибок никакого отношения, но включение его в код является хорошей практикой. NOCOUNT подавляет сообщения вида (1 row(s) affected), которые вы можете видеть в панели Message в SQL Server Management Studio. В то время как эти сообщения могут быть полезны при работе c SSMS, они могут негативно повлиять на производительность в приложении, так как увеличивают сетевой трафик. Сообщение о количестве строк также может привести к ошибке в плохо написанных клиентских приложениях, которые могут подумать, что это данные, которые вернул запрос.
Выше я использовал синтаксис, который немного необычен. Большинство людей написали бы два отдельных выражения:
SET NOCOUNT ON
SET XACT_ABORT ON
Между ними нет никакого отличия. Я предпочитаю версию с SET и запятой, т.к. это снижает уровень шума в коде. Поскольку эти выражения должны появляться во всех ваших хранимых процедурах, они должны занимать как можно меньше места.
3. Основной пример обработки ошибок
После того, как мы посмотрели на TRY-CATCH и SET XACT_ABORT ON, давайте соединим их вместе в примере, который мы можем использовать во всех наших хранимых процедурах. Для начала я покажу пример, в котором ошибка генерируется в простой форме, а в следующем разделе я рассмотрю решения получше.
Для примера я буду использовать эту простую таблицу.
CREATE TABLE sometable(a int NOT NULL,
b int NOT NULL,
CONSTRAINT pk_sometable PRIMARY KEY(a, b))
Вот хранимая процедура, которая демонстрирует, как вы должны работать с ошибками и транзакциями.
CREATE PROCEDURE insert_data @a int, @b int AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
INSERT sometable(a, b) VALUES (@a, @b)
INSERT sometable(a, b) VALUES (@b, @a)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
Первая строка в процедуре включает XACT_ABORT и NOCOUNT в одном выражении, как я показывал выше. Эта строка – единственная перед BEGIN TRY. Все остальное в процедуре должно располагаться после BEGIN TRY: объявление переменных, создание временных таблиц, табличных переменных, всё. Даже если у вас есть другие SET-команды в процедуре (хотя причины для этого встречаются редко), они должны идти после BEGIN TRY.
Причина, по которой я предпочитаю указывать SET XACT_ABORT и NOCOUNT перед BEGIN TRY, заключается в том, что я рассматриваю это как одну строку шума: она всегда должна быть там, но я не хочу, чтобы это мешало взгляду. Конечно же, это дело вкуса, и если вы предпочитаете ставить SET-команды после BEGIN TRY, ничего страшного. Важно то, что вам не следует ставить что-либо другое перед BEGIN TRY.
Часть между BEGIN TRY и END TRY является основной составляющей процедуры. Поскольку я хотел использовать транзакцию, определенную пользователем, я ввел довольно надуманное бизнес-правило, в котором говорится, что если вы вставляете пару, то обратная пара также должна быть вставлена. Два выражения INSERT находятся внутри BEGIN и COMMIT TRANSACTION. Во многих случаях у вас будет много строк кода между BEGIN TRY и BEGIN TRANSACTION. Иногда у вас также будет код между COMMIT TRANSACTION и END TRY, хотя обычно это только финальный SELECT, возвращающий данные или присваивающий значения выходным параметрам. Если ваша процедура не выполняет каких-либо изменений или имеет только одно выражение INSERT/UPDATE/DELETE/MERGE, то обычно вам вообще не нужно явно указывать транзакцию.
В то время как блок TRY будет выглядеть по-разному от процедуры к процедуре, блок CATCH должен быть более или менее результатом копирования и вставки. То есть вы делаете что-то короткое и простое и затем используете повсюду, не особо задумываясь. Обработчик CATCH, приведенный выше, выполняет три действия:
- Откатывает любые открытые транзакции.
- Повторно вызывает ошибку.
- Убеждается, что возвращаемое процедурой значение отлично от нуля.
Эти три действия должны всегда быть там. Мы можете возразить, что строка
IF @@trancount > 0 ROLLBACK TRANSACTION
не нужна, если нет явной транзакции в процедуре, но это абсолютно неверно. Возможно, вы вызываете хранимую процедуру, которая открывает транзакцию, но которая не может ее откатить из-за ограничений TRY-CATCH. Возможно, вы или кто-то другой добавите явную транзакцию через два года. Вспомните ли вы тогда о том, что нужно добавить строку с откатом? Не рассчитывайте на это. Я также слышу читателей, которые возражают, что если тот, кто вызывает процедуру, открыл транзакцию, мы не должны ее откатывать… Нет, мы должны, и если вы хотите знать почему, вам нужно прочитать вторую и третью части. Откат транзакции в обработчике CATCH – это категорический императив, у которого нет исключений.
Код повторной генерации ошибки включает такую строку:
DECLARE @msg nvarchar(2048) = error_message()
Встроенная функция error_message() возвращает текст возникшей ошибки. В следующей строке ошибка повторно вызывается с помощью выражения RAISERROR. Это не самый простой способ вызова ошибки, но он работает. Другие способы мы рассмотрим в следующей главе.
Замечание: синтаксис для присвоения начального значения переменной в DECLARE был внедрен в SQL Server 2008. Если у вас SQL Server 2005, вам нужно разбить строку на DECLARE и выражение SELECT.
Финальное выражение RETURN – это страховка. RAISERROR никогда не прерывает выполнение, поэтому выполнение следующего выражения будет продолжено. Пока все процедуры используют TRY-CATCH, а также весь клиентский код обрабатывает исключения, нет повода для беспокойства. Но ваша процедура может быть вызвана из старого кода, написанного до SQL Server 2005 и до внедрения TRY-CATCH. В те времена лучшее, что мы могли делать, это смотреть на возвращаемые значения. То, что вы возвращаете с помощью RETURN, не имеет особого значения, если это не нулевое значение (ноль обычно обозначает успешное завершение работы).
Последнее выражение в процедуре – это END CATCH. Никогда не следует помещать какой-либо код после END CATCH. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.
После прочтения теории давайте попробуем тестовый пример:
EXEC insert_data 9, NULL
Результат выполнения:
Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Давайте добавим внешнюю процедуру для того, чтобы увидеть, что происходит при повторном вызове ошибки:
CREATE PROCEDURE outer_sp @a int, @b int AS
SET XACT_ABORT, NOCOUNT ON
BEGIN TRY
EXEC insert_data @a, @b
END TRY
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
DECLARE @msg nvarchar(2048) = error_message()
RAISERROR (@msg, 16, 1)
RETURN 55555
END CATCH
go
EXEC outer_sp 8, 8
Результат работы:
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).
Мы получили корректное сообщение об ошибке, но если вы посмотрите на заголовки этого сообщения и на предыдущее поближе, то можете заметить проблему:
Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Сообщение об ошибке выводит информацию о расположении конечного выражения RAISERROR. В первом случае некорректен только номер строки. Во втором случае некорректно также имя процедуры. Для простых процедур, таких как наш тестовый пример, это не является большой проблемой. Но если у вас есть несколько уровней вложенных сложных процедур, то наличие сообщения об ошибке с отсутствием указания на место её возникновения сделает поиск и устранение ошибки намного более сложным делом. По этой причине желательно генерировать ошибку таким образом, чтобы можно было определить нахождение ошибочного фрагмента кода быстро, и это то, что мы рассмотрим в следующей главе.
4. Три способа генерации ошибки
4.1 Использование error_handler_sp
Мы рассмотрели функцию error_message(), которая возвращает текст сообщения об ошибке. Сообщение об ошибке состоит из нескольких компонентов, и существует своя функция error_xxx() для каждого из них. Мы можем использовать их для повторной генерации полного сообщения, которое содержит оригинальную информацию, хотя и в другом формате. Если делать это в каждом обработчике CATCH, это будет большой недостаток — дублирование кода. Вам не обязательно находиться в блоке CATCH для вызова error_message() и других подобных функций, и они вернут ту же самую информацию, если будут вызваны из хранимой процедуры, которую выполнит блок CATCH.
Позвольте представить вам error_handler_sp:
CREATE PROCEDURE error_handler_sp AS
DECLARE @errmsg nvarchar(2048),
@severity tinyint,
@state tinyint,
@errno int,
@proc sysname,
@lineno int
SELECT @errmsg = error_message(), @severity = error_severity(),
@state = error_state(), @errno = error_number(),
@proc = error_procedure(), @lineno = error_line()
IF @errmsg NOT LIKE '***%'
BEGIN
SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') +
', Line ' + ltrim(str(@lineno)) + '. Errno ' +
ltrim(str(@errno)) + ': ' + @errmsg
END
RAISERROR('%s', @severity, @state, @errmsg)
Первое из того, что делает error_handler_sp – это сохраняет значение всех error_xxx() функций в локальные переменные. Я вернусь к выражению IF через секунду. Вместо него давайте посмотрим на выражение SELECT внутри IF:
SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') +
', Line ' + ltrim(str(@lineno)) + '. Errno ' +
ltrim(str(@errno)) + ': ' + @errmsg
Цель этого SELECT заключается в форматировании сообщения об ошибке, которое передается в RAISERROR. Оно включает в себя всю информацию из оригинального сообщения об ошибке, которое мы не можем вставить напрямую в RAISERROR. Мы должны обработать имя процедуры, которое может быть NULL для ошибок в обычных скриптах или в динамическом SQL. Поэтому используется функция COALESCE. (Если вы не понимаете форму выражения RAISERROR, я рассказываю о нем более детально во второй части.)
Отформатированное сообщение об ошибке начинается с трех звездочек. Этим достигаются две цели: 1) Мы можем сразу видеть, что это сообщение вызвано из обработчика CATCH. 2) Это дает возможность для error_handler_sp отфильтровать ошибки, которые уже были сгенерированы один или более раз, с помощью условия NOT LIKE ‘***%’ для того, чтобы избежать изменения сообщения во второй раз.
Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
EXEC error_handler_sp
RETURN 55555
END CATCH
Давайте попробуем несколько тестовых сценариев.
EXEC insert_data 8, NULL
EXEC outer_sp 8, 8
Результат выполнения:
Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20
*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20
*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).
Заголовки сообщений говорят о том, что ошибка возникла в процедуре error_handler_sp, но текст сообщений об ошибках дает нам настоящее местонахождение ошибки – как название процедуры, так и номер строки.
Я покажу еще два метода вызова ошибок. Однако error_handler_sp является моей главной рекомендацией для читателей, которые читают эту часть. Это — простой вариант, который работает на всех версиях SQL Server начиная с 2005. Существует только один недостаток: в некоторых случаях SQL Server генерирует два сообщения об ошибках, но функции error_xxx() возвращают только одну из них, и поэтому одно из сообщений теряется. Это может быть неудобно при работе с административными командами наподобие BACKUPRESTORE, но проблема редко возникает в коде, предназначенном чисто для приложений.
4.2. Использование ;THROW
В SQL Server 2012 Microsoft представил выражение ;THROW для более легкой обработки ошибок. К сожалению, Microsoft сделал серьезную ошибку при проектировании этой команды и создал опасную ловушку.
С выражением ;THROW вам не нужно никаких хранимых процедур. Ваш обработчик CATCH становится таким же простым, как этот:
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
;THROW
RETURN 55555
END CATCH
Достоинство ;THROW в том, что сообщение об ошибке генерируется точно таким же, как и оригинальное сообщение. Если изначально было два сообщения об ошибках, оба сообщения воспроизводятся, что делает это выражение еще привлекательнее. Как и со всеми другими сообщениями об ошибках, ошибки, сгенерированные ;THROW, могут быть перехвачены внешним обработчиком CATCH и воспроизведены. Если обработчика CATCH нет, выполнение прерывается, поэтому оператор RETURN в данном случае оказывается не нужным. (Я все еще рекомендую оставлять его, на случай, если вы измените свое отношение к ;THROW позже).
Если у вас SQL Server 2012 или более поздняя версия, измените определение insert_data и outer_sp и попробуйте выполнить тесты еще раз. Результат в этот раз будет такой:
Msg 515, Level 16, State 2, Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 2627, Level 14, State 1, Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).
Имя процедуры и номер строки верны и нет никакого другого имени процедуры, которое может нас запутать. Также сохранены оригинальные номера ошибок.
В этом месте вы можете сказать себе: действительно ли Microsoft назвал команду ;THROW? Разве это не просто THROW? На самом деле, если вы посмотрите в Books Online, там не будет точки с запятой. Но точка с запятой должны быть. Официально они отделяют предыдущее выражение, но это опционально, и далеко не все используют точку с запятой в выражениях T-SQL. Более важно, что если вы пропустите точку с запятой перед THROW, то не будет никакой синтаксической ошибки. Но это повлияет на поведение при выполнении выражения, и это поведение будет непостижимым для непосвященных. При наличии активной транзакции вы получите сообщение об ошибке, которое будет полностью отличаться от оригинального. И еще хуже, что при отсутствии активной транзакции ошибка будет тихо выведена без обработки. Такая вещь, как пропуск точки с запятой, не должно иметь таких абсурдных последствий. Для уменьшения риска такого поведения, всегда думайте о команде как о ;THROW (с точкой с запятой).
Нельзя отрицать того, что ;THROW имеет свои преимущества, но точка с запятой не единственная ловушка этой команды. Если вы хотите использовать ее, я призываю вас прочитать по крайней мере вторую часть этой серии, где я раскрываю больше деталей о команде ;THROW. До этого момента, используйте error_handler_sp.
4.3. Использование SqlEventLog
Третий способ обработки ошибок – это использование SqlEventLog, который я описываю очень детально в третьей части. Здесь я лишь сделаю короткий обзор.
SqlEventLog предоставляет хранимую процедуру slog.catchhandler_sp, которая работает так же, как и error_handler_sp: она использует функции error_xxx() для сбора информации и выводит сообщение об ошибке, сохраняя всю информацию о ней. Вдобавок к этому, она логирует ошибку в таблицу splog.sqleventlog. В зависимости от типа приложения, которое у вас есть, эта таблица может быть очень ценным объектом.
Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:
BEGIN CATCH
IF @@trancount > 0 ROLLBACK TRANSACTION
EXEC slog.catchhandler_sp @@procid
RETURN 55555
END CATCH
@@procid возвращает идентификатор объекта текущей хранимой процедуры. Это то, что SqlEventLog использует для логирования информации в таблицу. Используя те же тестовые сценарии, получим результат их работы с использованием catchhandler_sp:
Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125
{515} Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125
{2627} Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).
Как вы видите, сообщение об ошибке отформатировано немного не так, как это делает error_handler_sp, но основная идея такая же. Вот образец того, что было записано в таблицу slog.sqleventlog:
logid | logdate | errno | severity | logproc | linenum | msgtext |
1 | 2015-01-25 22:40:24.393 | 515 | 16 | insert_data | 5 | Cannot insert … |
2 | 2015-01-25 22:40:24.395 | 2627 | 14 | insert_data | 6 | Violation of … |
Если вы хотите попробовать SqlEventLog, вы можете загрузить файл sqleventlog.zip. Инструкция по установке находится в третьей части, раздел Установка SqlEventLog.
5. Финальные замечания
Вы изучили основной образец для обработки ошибок и транзакций в хранимых процедурах. Он не идеален, но он должен работать в 90-95% вашего кода. Есть несколько ограничений, на которые стоит обратить внимание:
- Как мы видели, ошибки компиляции не могут быть перехвачены в той же процедуре, в которой они возникли, а только во внешней процедуре.
- Пример не работает с пользовательскими функциями, так как ни TRY-CATCH, ни RAISERROR нельзя в них использовать.
- Когда хранимая процедура на Linked Server вызывает ошибку, эта ошибка может миновать обработчик в хранимой процедуре на локальном сервере и отправиться напрямую клиенту.
- Когда процедура вызвана как INSERT-EXEC, вы получите неприятную ошибку, потому что ROLLBACK TRANSACTION не допускается в данном случае.
- Как упомянуто выше, если вы используете error_handler_sp или SqlEventLog, мы потеряете одно сообщение, когда SQL Server выдаст два сообщения для одной ошибки. При использовании ;THROW такой проблемы нет.
Я рассказываю об этих ситуациях более подробно в других статьях этой серии.
Перед тем как закончить, я хочу кратко коснуться триггеров и клиентского кода.
Триггеры
Пример для обработки ошибок в триггерах не сильно отличается от того, что используется в хранимых процедурах, за исключением одной маленькой детали: вы не должны использовать выражение RETURN (потому что RETURN не допускается использовать в триггерах).
С триггерами важно понимать, что они являются частью команды, которая запустила триггер, и в триггере вы находитесь внутри транзакции, даже если не используете BEGIN TRANSACTION.
Иногда я вижу на форумах людей, которые спрашивают, могут ли они написать триггер, который не откатывает в случае падения запустившую его команду. Ответ таков: нет способа сделать это надежно, поэтому не стоит даже пытаться. Если в этом есть необходимость, по возможности не следует использовать триггер вообще, а найти другое решение. Во второй и третьей частях я рассматриваю обработку ошибок в триггерах более подробно.
Клиентский код
У вас должна быть обработка ошибок в коде клиента, если он имеет доступ к базе. То есть вы должны всегда предполагать, что при любом вызове что-то может пойти не так. Как именно внедрить обработку ошибок, зависит от конкретной среды.
Здесь я только обращу внимание на важную вещь: реакцией на ошибку, возвращенную SQL Server, должно быть завершение запроса во избежание открытых бесхозных транзакций:
IF @@trancount > 0 ROLLBACK TRANSACTION
Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).
6. Конец первой части
Это конец первой из трех частей серии. Если вы хотели изучить вопрос обработки ошибок быстро, вы можете закончить чтение здесь. Если вы настроены идти дальше, вам следует прочитать вторую часть, где наше путешествие по запутанным джунглям обработки ошибок и транзакций в SQL Server начинается по-настоящему.
… и не забывайте добавлять эту строку в начало ваших хранимых процедур:
SET XACT_ABORT, NOCOUNT ON
Overview
The robust Transact-SQL (T-SQL) syntax in SQL Server provides developers with an efficient way to handle errors within stored procedures. This article discusses the @@ERROR, SP_ADDMESSAGE, and RAISERROR functions within SQL Server.
@@ERROR Function in SQL Server
Upon the completion of any T-SQL statement, SQL Server sets the @@ERROR object. If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designated error code. All SQL Server error codes can be found within the master.dbo.sysmessages system table. One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable.
Anatomy of an Error
All errors raised by SQL Server return the following information.
- Number — Each error has a unique number assigned to it.
- Message — Contains information about the error. Many errors have substitution variables that can be placed within the text. We will cover this in the SP_ADDMESSAGE and RAISERROR sections.
- Severity — Indicates how serious the error is. The values are between 1 and 25.
- State — As quoted from SQL Server books on line: «Some error codes can be raised at multiple points in the source code for SQL Server. For example, an 1105′ error can be raised for several different conditions. Each place the error code is raised assigns a unique state code. A Microsoft support engineer can use the state code from an error to find the location in the source code where that error code is being raised, which may provide additional ideas on how to diagnose the problem.»
- Procedure name — If the destruction occurred within a stored procedure, the name is returned.
- Line — The line number of the demon code.
There are two classes of error messages in SQL Server — fatal and nonfatal. Remember that fatal errors will kill the client connection. Creating stored procedures that cause fatal errors is a good way to get rid of slacking programmers or consultants.
SP_ADDMESSAGE in SQL Server
Use the sp_addmessage feature to add your own customized messages. The following information is given about each parameter:
- @msgnum (smallint) — This is the message ID. All user-defined messages start with 50001. The combination of this parameter and language must be unique.
- @severity (smallint) — The severity level is between 1 and 25. Only the system administrator can add a message above the level of 18. Messages below the age of 18 are still considered to be «toddlers,» and anyone can add them.
- @msgtext nvarchar(255) — This is the text of the error message. Variables can be used within the text. This functionality is similar to the printf feature within C.
- @lang — Since SQL Server can support different languages, you can store your error messages in multiple languages. This will help greatly when we start joint-developing with people from different planets. If left NULL, it will default to the default language of the current session.
- @with_log varchar(5) — This value can be TRUE or FALSE. If you set this parameter to «TRUE,» then messages are written to the Windows and SQL Server application log.
- @replace varchar(7) — This allows you to replace an existing error message with a new message text and severity level. The default is NULL. This is a great way to tick the database administrator off!
RAISERROR in SQL Server
You can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application.
The following information is given about the parameters.
- @msg_id — This is the message ID of your user-defined message. All adhoc error messages are given the message ID of 50000.
- @msg_str — The message can have up to 400 characters. If the message contains more than 400 characters, only the first 397 will be displayed and an ellipsis will be added to indicate that the message has been cut. You can also use formatting values within the message text. An example of this is given below.
- @argument — These are values to be used within the message text.
- @WITH — Acceptable values are LOG, NOWAIT, and SETERROR. The LOG option logs the error to the server and application log; NOWAIT sends the messages immediately back to the client application; and SETERROR sets the @@ERROR value to the @MSG_ID or 50000, regardless of the serverity level.
The User-defined Error Message
For the formatting, I will use %s for strings and %I for integers. This functionality is similar to the printf function in C. I know you still have those C notes from college somewhere!
- USE master
- EXEC sp_addmessage
- 50010, /*message id*/
- 16, /*severity level*/
- ‘Error in stored procedure %s’, /*message text*/
- ‘us_english’, /*language*/
- ‘FALSE’, /*log this error*/
- NULL /*replace existing error*/
Using RAISERROR to Call the Error Message
I will use the pubs database for this demonstration.
Step 1. Create the following procedure.
- CREATE PROCEDURE spDemo
- AS BEGIN
- SELECT TOP 10 * FROM AUTHORS
- IF @@ROWCOUNT < 11
- RAISERROR (50010,12,1,‘Raise Error Demo’)
- END
Step 2. Execute the procedure.
- Exec spDemo
You will then get the following error message.
«Server: Msg 50010, Level 12, State 1, Procedure spDemo, Line 5
Error in stored procedure Raise Error Demo»
Here is another example of using RAISERROR without a user-defined message.
- RAISERROR (‘An error occurred because we are overworked and underpaid!’,10,1)
Example of @@ERROR and RAISERROR
Step 1. Create the following procedure in the pubs database.
- CREATE PROCEDURE spDiscounts(@TYPE VARCHAR(40),
- @STORE CHAR(4),@LOW SMALLINT, @HIGH SMALLINT, @DISCOUNT NUMERIC(9,2))
- AS BEGIN
- IF @DISCOUNT > 7
- BEGIN
- RAISERROR (‘You entered %d, the discount can not be greater than 7.’, 10, 1, @DISCOUNT)
- END
- ELSE
- BEGIN
- BEGIN TRANSACTION
- INSERT INTO DISCOUNTS(DISCOUNTTYPE, STOR_ID, LOWQTY, HIGHQTY, DISCOUNT)
- VALUES (@TYPE,@STORE,@LOW,@HIGH,@DISCOUNT)
- IF @@ERROR <> 0
- ROLLBACK TRANSACTION
- ELSE
- COMMIT TRANSACTION
- END
- END
Step 2. Execute the following procedure.
- exec spDiscounts ‘My Discount’, NULL, 10, 100, 12
You will receive the following error message:
«You entered 12; the discount cannot be greater than 7.»
Returning logical error messages like this to the client application will save hours of head scratching.
Summary
I hope I have spawned a few ideas of what you can do with @@ERROR, SP_ADDMESSAGE, and RAISERROR. T-SQL gives developers a very powerful tool to use when creating stored procedures. You can find well-written documentation within the SQL Server books on line. If you have any problems, questions, or comments about this article please email me.
Overview
The robust Transact-SQL (T-SQL) syntax in SQL Server provides developers with an efficient way to handle errors within stored procedures. This article discusses the @@ERROR, SP_ADDMESSAGE, and RAISERROR functions within SQL Server.
@@ERROR Function in SQL Server
Upon the completion of any T-SQL statement, SQL Server sets the @@ERROR object. If the statement was successful, @@ERROR is set to 0, otherwise it is set to the designated error code. All SQL Server error codes can be found within the master.dbo.sysmessages system table. One important thing to remember is that @@ERROR is cleared each time a statement is executed. It is a good practice to store the value within a local variable.
Anatomy of an Error
All errors raised by SQL Server return the following information.
- Number — Each error has a unique number assigned to it.
- Message — Contains information about the error. Many errors have substitution variables that can be placed within the text. We will cover this in the SP_ADDMESSAGE and RAISERROR sections.
- Severity — Indicates how serious the error is. The values are between 1 and 25.
- State — As quoted from SQL Server books on line: «Some error codes can be raised at multiple points in the source code for SQL Server. For example, an 1105′ error can be raised for several different conditions. Each place the error code is raised assigns a unique state code. A Microsoft support engineer can use the state code from an error to find the location in the source code where that error code is being raised, which may provide additional ideas on how to diagnose the problem.»
- Procedure name — If the destruction occurred within a stored procedure, the name is returned.
- Line — The line number of the demon code.
There are two classes of error messages in SQL Server — fatal and nonfatal. Remember that fatal errors will kill the client connection. Creating stored procedures that cause fatal errors is a good way to get rid of slacking programmers or consultants.
SP_ADDMESSAGE in SQL Server
Use the sp_addmessage feature to add your own customized messages. The following information is given about each parameter:
- @msgnum (smallint) — This is the message ID. All user-defined messages start with 50001. The combination of this parameter and language must be unique.
- @severity (smallint) — The severity level is between 1 and 25. Only the system administrator can add a message above the level of 18. Messages below the age of 18 are still considered to be «toddlers,» and anyone can add them.
- @msgtext nvarchar(255) — This is the text of the error message. Variables can be used within the text. This functionality is similar to the printf feature within C.
- @lang — Since SQL Server can support different languages, you can store your error messages in multiple languages. This will help greatly when we start joint-developing with people from different planets. If left NULL, it will default to the default language of the current session.
- @with_log varchar(5) — This value can be TRUE or FALSE. If you set this parameter to «TRUE,» then messages are written to the Windows and SQL Server application log.
- @replace varchar(7) — This allows you to replace an existing error message with a new message text and severity level. The default is NULL. This is a great way to tick the database administrator off!
RAISERROR in SQL Server
You can also use the RAISERROR command to create an error message. RAISERROR can send the error message information back to a client application.
The following information is given about the parameters.
- @msg_id — This is the message ID of your user-defined message. All adhoc error messages are given the message ID of 50000.
- @msg_str — The message can have up to 400 characters. If the message contains more than 400 characters, only the first 397 will be displayed and an ellipsis will be added to indicate that the message has been cut. You can also use formatting values within the message text. An example of this is given below.
- @argument — These are values to be used within the message text.
- @WITH — Acceptable values are LOG, NOWAIT, and SETERROR. The LOG option logs the error to the server and application log; NOWAIT sends the messages immediately back to the client application; and SETERROR sets the @@ERROR value to the @MSG_ID or 50000, regardless of the serverity level.
The User-defined Error Message
For the formatting, I will use %s for strings and %I for integers. This functionality is similar to the printf function in C. I know you still have those C notes from college somewhere!
- USE master
- EXEC sp_addmessage
- 50010, /*message id*/
- 16, /*severity level*/
- ‘Error in stored procedure %s’, /*message text*/
- ‘us_english’, /*language*/
- ‘FALSE’, /*log this error*/
- NULL /*replace existing error*/
Using RAISERROR to Call the Error Message
I will use the pubs database for this demonstration.
Step 1. Create the following procedure.
- CREATE PROCEDURE spDemo
- AS BEGIN
- SELECT TOP 10 * FROM AUTHORS
- IF @@ROWCOUNT < 11
- RAISERROR (50010,12,1,‘Raise Error Demo’)
- END
Step 2. Execute the procedure.
- Exec spDemo
You will then get the following error message.
«Server: Msg 50010, Level 12, State 1, Procedure spDemo, Line 5
Error in stored procedure Raise Error Demo»
Here is another example of using RAISERROR without a user-defined message.
- RAISERROR (‘An error occurred because we are overworked and underpaid!’,10,1)
Example of @@ERROR and RAISERROR
Step 1. Create the following procedure in the pubs database.
- CREATE PROCEDURE spDiscounts(@TYPE VARCHAR(40),
- @STORE CHAR(4),@LOW SMALLINT, @HIGH SMALLINT, @DISCOUNT NUMERIC(9,2))
- AS BEGIN
- IF @DISCOUNT > 7
- BEGIN
- RAISERROR (‘You entered %d, the discount can not be greater than 7.’, 10, 1, @DISCOUNT)
- END
- ELSE
- BEGIN
- BEGIN TRANSACTION
- INSERT INTO DISCOUNTS(DISCOUNTTYPE, STOR_ID, LOWQTY, HIGHQTY, DISCOUNT)
- VALUES (@TYPE,@STORE,@LOW,@HIGH,@DISCOUNT)
- IF @@ERROR <> 0
- ROLLBACK TRANSACTION
- ELSE
- COMMIT TRANSACTION
- END
- END
Step 2. Execute the following procedure.
- exec spDiscounts ‘My Discount’, NULL, 10, 100, 12
You will receive the following error message:
«You entered 12; the discount cannot be greater than 7.»
Returning logical error messages like this to the client application will save hours of head scratching.
Summary
I hope I have spawned a few ideas of what you can do with @@ERROR, SP_ADDMESSAGE, and RAISERROR. T-SQL gives developers a very powerful tool to use when creating stored procedures. You can find well-written documentation within the SQL Server books on line. If you have any problems, questions, or comments about this article please email me.
2009 г. Особенности обработки ошибок сервера базы данных OracleК.п.н. Владимир Лихачёв, Калужский педагогический университет им К.Э.Циолковского. Введение
Как ни странно, ситуация с формированием сообщений об ошибках в программах довольно часто сильно отличается от обработки самих ошибок. При обработке ошибок обычно удается выработать общую стратегию, что позволяет локализовать их обработку в одной или нескольких функциях. Аналогичный подход для сообщений об ошибках может быть реализован на основе того, что в сообщении об ошибке сервер Oracle указывает тип ошибки и объект базы данных, который явился причиной её возникновения. Такими объектами обычно являются ограничения, как, например, первичные, уникальные и внешние ключи, уникальные индексы, ограничения “not null” и др. Из системных таблиц и представлений базы данных может быть получена подробная информация об этих ограничениях и определены значения, изменение которых и привело к возникновению ошибки. Но проблема заключается в том, что реализация такого механизма формирования сообщений об ошибках в реальных приложениях встречает целый ряд сложностей:
Совокупность этих факторов обычно приводит к тому, что формирование сообщений даже об однотипных ошибках реализуется индивидуально для каждой транзакции. В результате код для формирования сообщений об ошибках оказывается распределенным по всему приложению, что усложняет его сопровождение. Из-за необходимости написания кода практически для каждой возможной ошибки, часть ошибок, о которых известно разработчику, оказываются без соответствующих сообщений для пользователя. В результате достаточно информативные сообщения для конечного пользователя формируются только для некоторой части ошибок, в остальных же случаях ему остается довольствоваться в лучшем случае сообщениями от самого сервера базы данных. Информативность таких сообщений для обычного пользователя в большинстве случаев недостаточна для выявления причины возникшей проблемы и её устранения. Рассматриваемый в статье метод формирования информативных сообщений об ошибках для пользователя является довольно универсальным, может быть реализован как в клиентских приложениях, так и на стороне сервера Oracle. Он может использоваться в различных типах программ, как, например:
Описанные выше проблемы формирования сообщений могут быть решены, если сообщения об ошибках условно разделить на две группы:
Описываемый в статье метод формирования сообщений об ошибках БД может быть применён для многих серверов реляционных баз данных. Пример его использования для баз данных сервера Firebird рассматривается в статье [1]. 1. Универсальные сообщения об ошибках, вызванных ограничениями БД
Как уже говорилось выше, основная идея создания универсальных сообщений заключается в том, чтобы на основе данных из сообщения об ошибке от Oracle и о структуре базы данных сформировать достаточно информативное и понятное для конечного пользователя сообщение.
CREATE TABLE DEMO.GOODS (
CODE INTEGER NOT NULL ,
TITLE VARCHAR2(50 byte) NOT NULL ,
PRICE NUMBER(16, 2) NOT NULL ,
CONSTRAINT CK_PRICE CHECK (PRICE > 0),
CONSTRAINT PK_GOODS PRIMARY KEY(CODE));
COMMENT ON TABLE DEMO.GOODS is 'Товары';
COMMENT ON COLUMN DEMO.GOODS.CODE is 'Код товара';
COMMENT ON COLUMN DEMO.GOODS.TITLE is 'Название';
COMMENT ON COLUMN DEMO.GOODS.PRICE is 'Цена';
CREATE UNIQUE INDEX DEMO.IDX_GOODS_TITLE ON DEMO.GOODS (TITLE);
Скрипт 1.1. Создание таблицы “GOODS”. Сервер в этом случае сгенерирует ошибку, так как столбец “TITLE”, в котором хранится название товара, включено в уникальный индекс “DEMO.IDX_GOODS_TITLE”: ORA-00001: нарушено ограничение уникальности (DEMO.IDX_GOODS_TITLE) Вместо этого сообщения для пользователя может быть сформировано, например, одно из сообщений:
Хотя эти сообщения и различаются, но в них всех указывается информация об объекте, для которого нарушено ограничение уникальности – это поле “Название” таблицы “Товары”. Одна из проблем формирования такого типа сообщений, заключается в том, что пользовательские названия полей и таблиц, отличаются от имен таблиц и столбцов в базе данных. Чтобы пользователю было понятно сообщение об ошибке, в нем должны использоваться именно пользовательские названия. Для сопоставления имен таблиц и полей и их пользовательских названий может использоваться отдельная таблица или комментарии для таблиц и столбцов. Последний вариант можно считать более предпочтительным, так как это позволяет одновременно документировать базу данных. Именно поэтому в скрипте 1.1 в качестве комментариев для таблицы и её столбцов приведены их пользовательские названия. Если сравнить выше приведённые сообщения и комментарии для таблицы и столбцов, то можно заметить, что формирование первого сообщения является наиболее простым вариантом. Для формирования двух других сообщений может потребоваться лексический синтез, но это уже отдельная задача. Хочется обратить внимание, что в дальнейшем в статье приводится только один из возможных вариантов сообщения для каждого случая ошибки. На практике выбор стиля сообщения и его содержания может зависеть от целого ряда факторов и будет определяться разработчиком системы. Конечно, нельзя исключать ситуацию, когда для таблицы или столбца отсутствуют комментарии, которые должны быть указаны в сообщении. В этой ситуации в сообщении об ошибке возможно отображение непосредственно имени таблицы или столбца. Далее рассматривается формирование универсальных сообщений для наиболее часто встречающихся ошибок, обусловленных ограничениями БД. 2. Не указано значение поля, обязательного для заполнения (ограничение NOT NULL)Эта ошибка генерируется сервером в нескольких случаях:
Во всех этих случаях сервер генерирует ошибку: ORA-01400: невозможно <вставить/заменить> NULL в ("<Схема>"."<Таблица>"."<Столбец>") Для получения описания таблицы и столбца из сообщения об ошибке, можно использовать запрос 2.1. select tc.comments astable_comment, cc.comments ascolumn_comment from all_tab_columns c, all_tab_comments tc, all_col_comments cc where c.owner = :owner and c.table_name = :table_name and c.column_name = :column_name and tc.owner = c.owner and tc.table_name = c.table_name and cc.owner = c.owner and cc.table_name = c.table_name and cc.column_name = c.column_name Запрос 2.1. Получение описания таблицы и столбца В качестве параметров запроса “owner”, ”table_name”, ”column_name” необходимо указать соответственно имя схемы, таблицы и столбца из сообщения об ошибке. Запрос возвращает комментарии для таблицы и столбца. Используя результаты этого запроса, может быть сформировано сообщение об ошибке, например, следующего содержания: Необходимо указать значение столбца “<Описание поля>” в таблице “<Описание таблицы>” при <добавлении новой/изменении> записи. 3. Нарушена уникальность значения поля или набора столбцовНеобходимость ввода уникального значения столбца может требоваться в основном в трех случаях:
Во всех трех случаях Oracle Database генерирует одну и ту же ошибку: ORA-00001: нарушено ограничение уникальности (<Схема>.<Ограничение>) В сообщении об ошибке указывается ограничение, которое вызвало ошибку. Для получения информации о столбцах, входящих в главный или уникальный ключи, select dcs.constraint_type, cc.table_name, tc.comments astable_comment, cc.column_name, ccom.comments as column_comment from all_cons_columns cc join all_tab_comments tc on (tc.owner = cc.owner and tc.table_name = cc.table_name) join all_col_comments ccom on (ccom.owner = cc.owner and ccom.table_name = cc.table_name and ccom.column_name = cc.column_name ) join all_constraints dcs on (dcs.constraint_name = cc.constraint_name) where cc.owner = :owner and cc.constraint_name = :key_name
select ic.table_name, tc.comments astable_comment, ic.column_name, ccom.comments ascolumn_comment from all_ind_columns ic join all_tab_comments tc on (tc.owner = ic.table_owner and tc.table_name = ic.table_name) join all_col_comments ccom on (ccom.owner = ic.table_owner and ccom.table_name = ic.table_name and ccom.column_name = ic.column_name ) where table_owner = :owner and index_name = :index_name
В качестве параметров запросам передаётся имя схемы (“owner“), имя ключа (“key_name“) или индекса (“index_name“). Запросы возвращают имена и комментарии для таблиц и столбцов, входящих в ограничение. Запрос 3.1 возвращает так же тип ограничения (“constraint_type”): “P” – главный ключ, “U” – уникальный ключ. Количество записей, возвращаемых запросами, соответствует количеству столбцов в ограничении уникальности. На основе полученной информации об ограничении уникальности для пользователя могут быть сформированы варианты 4. Ошибки, вызываемые ограничениями внешних ключейПри выполнении операций над табличными данными, связанными внешними ключами,
Для получения информации о столбцах главной и подчиненной таблиц, входящих во внешний ключ, можно использовать приведенный ниже запрос 4.1. select a.constraint_name, a.table_name, tc1.comments astable_comment, a2.column_name, cc1.comments ascolumn_comment, b.owner asr_owner, b.table_name asr_table_name, tc2.comments asr_table_comment, b2.column_name asr_column_name, cc2.comments asr_column_comment from all_constraints a, all_constraints b, all_cons_columns a2, all_cons_columns b2, all_tab_comments tc1, all_col_comments cc1, all_tab_comments tc2, all_col_comments cc2 where a.owner = :owner and a.constraint_type = 'R' and a.constraint_name = :foreign_key and b.constraint_type in ('P','U') and b.constraint_name = a.r_constraint_name and b.owner = a.r_owner and a2.constraint_name = a.constraint_name and a2.table_name = a.table_name and a2.owner = a.owner and b2.constraint_name = b.constraint_name and b2.table_name = b.table_name and b2.owner = b.owner and b2.position = a2.position and tc1.owner = a.owner and tc1.table_name = a.table_name and cc1.owner = a2.owner and cc1.table_name = a2.table_name and cc1.column_name = a2.column_name and tc2.owner = b.owner and tc2.table_name = b.table_name and cc2.owner = b2.owner and cc2.table_name = b2.table_name and cc2.column_name = b2.column_name
Запрос имеет два параметра: “owner” и “foreign_key” – схема и внешний ключ, о котором необходимо получить информацию. Он возвращает информацию о столбцах, входящих во внешний ключ: «table_name», «table_comment» — имя и описание подчиненной таблицы; «column_name», «column_comment» — имя и описание столбца подчиненной таблицы. Столбцы запроса с префиксом “r_” возвращают информацию о главной таблице. Количество записей возвращаемых запросом соответствует количеству столбцов, входящих во внешний ключ. На основе этой информации могут быть сформированы сообщения об ошибках внешних ключей для пользователя. 5. Специальные сообщения об ошибках, вызванных ограничениями БДНеобходимость использования специальных сообщений может возникнуть в случае, если универсальное сообщение об ошибке по каким-то причинам не может использоваться или не может быть сформировано. Примером последнего случая являются ограничения CHECK для таблиц. В условиях ограничений могут использоваться запросы и условия, анализ которых может оказаться довольно сложной задачей. Поэтому для этих ограничений часто удобнее использовать сообщения, которые определяются на этапе разработки. Можно выделить две группы специальных сообщений об ошибках. Первый тип специальных сообщений предназначен для использования во всех приложениях, которые работают c общей базой данных. Их можно условно назвать “специальные сообщения об ошибках уровня базы данных”. Вторая группа сообщений специфична для конкретного приложения. Они могут быть необходимы, когда различные приложения должны выдавать пользователю различные сообщения об одной и той же ошибке. Их можно условно назвать “специальные сообщения об ошибках уровня приложения”. Информацию о первой группе сообщений удобно хранить в самой базе данных и использовать для этого отдельную таблицу. Сообщения, специфичные для программы могут храниться в её ресурсах, например, в виде отдельного файла или также в БД. Идентификация специальных сообщений может выполняться на основе кода ошибки, имени схемы и одного или нескольких ключевых слов из сообщения об ошибке. 6. Сообщения об ошибках ограничений CHECK для таблицПри возникновении ошибки, вызванной ограничением CHECK для таблицы, сервер генерирует ошибку: ORA-02290: нарушено ограничение целостности CHECK (<Схема>.<Имя ограничения>)
Как уже говорилось выше, для таких ошибок часто удобно использовать специальные сообщения. Например, для ограничения «CK_PRICE» таблицы “GOODS” может использоваться специальное сообщение, хранимое в таблице специальных сообщений: Цена товара в справочнике “Товары” должна быть больше нуля. 7. Комплексное использование специальных и универсальных сообщений об ошибкахГибкий механизм формирования информативных сообщений об ошибках для пользователя реализуется в несколько этапов (рис. 1):
Возможны более сложные случаи, чем приведенный в этой статье. Например, если сообщение формируется в
В ряде случаев такие сообщения могут быть даже более информативными, чем сформированные на предыдущих этапах. CREATE OR REPLACE TRIGGER DEMO.TRIGGER_GOODS BEFORE
INSERT
OR UPDATE OF PRICE ON DEMO.GOODS FOR EACH ROW BEGIN
IF :NEW.PRICE <= 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Цена товара "' || :NEW.TITLE || '
" должна быть больше 0 руб (указана цена '|| :NEW.PRICE ||' руб)');
END IF;
END TRIGGER_GOODS;
В случае цены товара меньшей или равной нулю сервер сгенерирует ошибку, например: ORA-20001: Цена товара "Лейка" должна быть больше 0 руб (указана цена 0 руб) Клиентское приложение может сразу передать это сообщение пользователю
Другой причиной может быть появление ошибки, для которой формирование
Хочется обратить внимание, что даже если в приложении используются только специальные сообщения об ошибках, то использование общей функции для формирования сообщений позволит улучшить структуру программы. При необходимости формат специальных сообщений может иметь поддержку ссылок на справочную систему, рисунки и т.д. ЗаключениеЦелью данной статьи является показать основные идеи метода, который может использоваться для формирования информативных сообщений об ошибках базы данных Oracle для конечного пользователя. Литература
|