Obsługa wyjątków w SQL Server – try … catch

           Chyba każdy kto pracuje z bazami danych spotkał się z problemem wyjątków. Zaczyna się niewinnie – prosta funkcja, prosta procedura – wręcz nie da się nic zepsuć. Potem przychodzi jedna modyfikacja, potem druga, pojawia się problem z danymi (np. niespójność danych). Szybciej czy później pojawi się też wyjątek…

Czym jest wyjątek?

Wikipedia mówi: Wyjątek (ang. exception) jest mechanizmem przepływu sterowania używanym w mikroprocesorach oraz współczesnych językach programowania do obsługi zdarzeń wyjątkowych, a w szczególności błędów, których wystąpienie zmienia prawidłowy przebieg wykonywania programu. W momencie zajścia niespodziewanego zdarzenia generowany jest wyjątek, który musi zostać obsłużony poprzez zapamiętanie bieżącego stanu programu i przejście do procedury jego obsługi. (…)

W wolnym tłumaczeniu – wyjątek jest to niestandardowy wynik operacji z którym SQL Server nie jest sobie w stanie poradzić. Wyjątek przerywa wykonywanie obecnie wykonywanej czynności i, o ile nie jest obsłużony, to jest przekazywany do programu lub innego obiektu bazodanowego który wywoływał dany obiekt.

Skomplikowane? Ok, a więc od początku. Zacznijmy od przykładu.

Co zwróci wykonanie poniższego polecenia?

SELECT 1/0

Zwróci wyjątek:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Ok, a teraz o co chodzi z tym przekazywaniem wyjątku:

Tworzymy funkcję zawierającą znane już nam działanie czyli dzielenie przez 0:

CREATE FUNCTION dbo.f_dziel_przez_0()
RETURNS int
AS
BEGIN
DECLARE @wynik int;
SELECT @wynik = 1/0;
RETURN @wynik;
END
GO

Po wykonaniu otrzymamy:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Tworzymy funkcję dbo.f_wywolaj_dziel_przez_0 która wywoła dbo.f_dziel_przez_0

CREATE FUNCTION dbo.f_wywolaj_dziel_przez_0()
RETURNS int
AS
BEGIN
DECLARE @wynik int;
SELECT @wynik = dbo.f_dziel_przez_0();
RETURN @wynik;
END
GO

Po wywołaniu dostajemy ten sam wyjątek co poprzednio:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Wniosek, w trakcie wykonywania kodu programu w funkcji dbo.f_dziel_przez_0 pojawił się wyjątek, który został przekazany do dbo.f_wywolaj_dziel_przez_0.

Czy wyjątek jest zły?

Sam w sobie nie! Na etapie tworzenia oprogramowania dobrze nawet dostać na twarz komunikat z wyjątkiem i informacją co się stało. Gorzej jeśli produkt trafia do klienta, a ten otrzymuje szereg dziwnie brzmiących komunikatów.

 TRY i CATCH czyli sposób na kontrolowanie wyjątków

 Gdybyśmy mieli sposób na panowanie nad wyjątkami moglibyśmy zareagować na nie, np. prezentując użytkownikowi zwięzły i zrozumiały komunikat. Ten sposób to użycie klauzul TRY i CATCH, które Microsoft zdefiniował następująco:

BEGIN TRY
instrukcja lub kod instrukcji
END TRY
BEGIN CATCH
obsługa wyjątku
END CATCH

Przejdźmy do naszego przykładu. Zmodyfikujmy nieco funkcję f_dziel_przez_0:

ALTER FUNCTION dbo.f_dziel_przez_0()
RETURNS int
AS
BEGIN
BEGIN TRY
DECLARE @wynik int;
SELECT @wynik = 1/0;
RETURN @wynik;
END TRY
BEGIN CATCH
RETURN -1;
END CATCH
END
GO

Wykonajmy kod i tu smutna niespodzianka:

Msg 443, Level 16, State 14, Procedure f_dziel_przez_0, Line 5
Invalid use of a side-effecting operator ‘BEGIN TRY’ within a function.
Msg 443, Level 16, State 14, Procedure f_dziel_przez_0, Line 9
Invalid use of a side-effecting operator ‘END TRY’ within a function.
Msg 443, Level 16, State 14, Procedure f_dziel_przez_0, Line 10
Invalid use of a side-effecting operator ‘BEGIN CATCH’ within a function.
Msg 443, Level 16, State 14, Procedure f_dziel_przez_0, Line 12
Invalid use of a side-effecting operator ‘END CATCH’ within a function.
Msg 455, Level 16, State 2, Procedure f_dziel_przez_0, Line 12
The last statement included within a function must be a return statement.

Microsoft nie pozwala na użycie sekcji TRY/CATCH w funkcjach użytkownika (user defined function – UDF). Napiszmy więc procedurę która wywoła funkcję f_dziel_przez_0:

CREATE PROCEDURE dbo.p_dziel_przez_0
AS
BEGIN
BEGIN TRY
DECLARE @wynik int;
SELECT @wynik = dbo.f_dziel_przez_0();
RETURN @wynik;
END TRY
BEGIN CATCH
RETURN -1;
END CATCH
END
GO

Procedura wykonała się poprawnie. Dla potrzeb przykładu załóżmy, że procedura zwraca jedynie wynik dodatni, a więc błąd możemy oznaczyć jako „-1”. Wykonajmy tą procedurę:

Otrzymaliśmy -1 zamiast wyjątku. Teraz wiedząc, że -1 oznacza błąd możemy przygotować przyjazny dla użytkownika komunikat o błędzie.

Zagnieżdżenia TRY CATCH

TRY i CATCH może wielokrotnie występować w procedurze. Załóżmy że nasza procedura wykonuje dwie logiczne operacje. W przypadku wystąpienia błędu chcielibyśmy wiedzieć w którym bloku wystąpił błąd. Zmodyfikujmy nieco naszą procedurę:

ALTER PROCEDURE dbo.p_dziel_przez_0
AS
BEGIN
DECLARE @wynik int;
/* pierwszy blok danych*/
BEGIN TRY
SELECT @wynik = 1/1;
END TRY
BEGIN CATCH
RETURN -1;
END CATCH
/* drugi blok danych*/
BEGIN TRY
SELECT @wynik = 1/0;
END TRY
BEGIN CATCH
RETURN -2;
END CATCH
RETURN @wynik;
END
GO

Otrzymaliśmy wynik -2, a więc drugi blok operacji zwrócił wyjątek.

Ok, teraz skomplikujmy nieco sprawę, pokazując przy okazji jak może być zagnieżdżona sekcja TRY CATCH w innej takiej sekcji:

ALTER PROCEDURE dbo.p_dziel_przez_0
AS
BEGIN
DECLARE @wynik int;
BEGIN TRY
/* pierwszy blok danych*/
BEGIN TRY
SELECT @wynik = 1/1;
END TRY
BEGIN CATCH
RETURN -1;
END CATCH
/* drugi blok danych*/
BEGIN TRY
SELECT @wynik = 1/2;
END TRY
BEGIN CATCH
RETURN -2;
END CATCH
SELECT @wynik = 1/0;
END TRY
BEGIN CATCH
RETURN -3;
END CATCH
RETURN @wynik;
END
GO

Jak widzimy zarówno pierwszy jak i drugi blok danych wykonuje się poprawnie, dalej następuje wyjątek, który jest przechwytywany przez ostatni TRY CATCH. Ostatecznie procedura zwraca -3.

Informacje w sekcji CATCH

Do tej pory w sekcji CATCH umieszczaliśmy własne definicje błędów. Microsoft przygotował nam jednak duży zbiór informacji na którym niejednokrotnie możemy oprzeć informację zwrotną z procedury.

Przykład żywcem wzięty ze strony Microsoftu:

BEGIN TRY
— Generate a divide-by-zero error.
SELECT 1/0;
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

Teraz do czego możemy to wykorzystać? Zmieńmy naszą procedurę:

ALTER PROCEDURE dbo.p_dziel_przez_0
AS
BEGIN
DECLARE @wynik int;
/* pierwszy blok danych*/
BEGIN TRY
SELECT @wynik = 1/0;
END TRY
BEGIN CATCH
SELECT -1, ERROR_NUMBER() as error, (ERROR_MESSAGE() +’ Linia: ‘ +CAST(ERROR_LINE() AS varchar(10))) AS textERROR ;
RETURN -1;
END CATCH
SELECT @wynik, NULL as error, NULL AS textERROR ;
RETURN 0;
END
GO

Dostaliśmy dataset:

-1        8134    Divide by zero error encountered. Linia: 8

Natomiast procedura zwróciła -1.

Na tej podstawie możemy zaprezentować użytkownikowi przyjemny dla oka komunikat, a sami posiadamy informację o miejscu wystąpienia i rodzaju błędu.

pozdrawiam,

 

Podziel się na:
  • Google Bookmarks
  • RSS
  • Dodaj do ulubionych
  • email
  • Facebook
  • Twitter
  • Blogger.com
  • LinkedIn
  • Gadu-Gadu Live