Try Catch는 예외처리(Exception Handling)에 사용한다.
예외를 일을킬 수 있는 Transact-SQL의 문들 Begin Try ... End Try 블록으로 감싼다.
Begin Try
예외가 발생할 수 있는 문들(Statements)
End Try
Try 블록 뒤에는 Begin Catch와 End Catch 블록을 사용한다.
Begin Catch
예외를 처리하는 문들
End Catch
완성된 Try Catch 구문
Begin Try
예외가 발생할 수 있는 문들(Statements)
End Try
Begin Catch
예외를 처리하는 문들
End Catch
Try 블록에 에러가 발생하지 않으면 Catch 블록은 실해되지 않는다. 하지만 Try 블록에서 예외가 발생한다면 Catch블록이 실행된다.
# Catch 블록 functions
Catch 블록 내에서 에러가 발생환 상세한 정보를 아래 함수들이 보여준다.
1. Error_Line() : 예외가 발생한 곳의 Line number를 반환.
2. Error_Message() : Error 메시지 반환.
3. Error_Procedure() : Error가 발생한 프로시저나 트리거(trigger)의 이름을 반환.
4. Error_Number() : Error 번호 반환.
5. Error_Serverity() : Error가 발생한 등급의 심각도를 반환.
6. Error_State() : 발생된 Error의 상태번호를 반환.
위 함수들을 Catch 블록에서만 사용해야한다. 만약 Catch 블록 밖에서 이 함수들을 사용하면 Null을 반환한다.
중첩(nested) Try Catch문
Begin Try
예외가 발생할 수 있는 문들(Statements)
End Try
Begin Catch
예외를 처리하는 문들
Begin Try
중첩 Try 블록
End Try
Begin Catch
중첩 Catch 블록
End Catch
End Catch
Try Catch 예제
Create usp_divide(
@a decimal,
@b decimal,
@c decimal output
) as
Begin
Begin Try
SET @c = @a / @b;
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
End;
Go
위 저장 프로시저에서 Try 에 나눗셈을 Catch 블록에 Error 정보를 반환하는 함수들을 두었다.
10 / 2를 하면 5가 반환된다.
DECLARE @r decimal;
EXEC usp_divide 10, 2, @r output;
PRINT @r;
하지만 0을 나누면
DECLARE @r2 decimal;
EXEC usp_divide 10, 0, @r2 output;
PRINT @r2;
에러가 발생하고 Catch 블록이 실행된다.
# 트랜젝션(Transaction)에서 Try Catch 사용
Catch 문내에 XACT_STATE() 함수를 사용해서 트랜젝션의 상태를 확인해 볼 수 있다.
1. XACT_STATE() 함수가 -1을 반환했다면 트랜젝션이 커밋(commit) 할 수 없는 상태로있다는 것으로 Rollback Transaction 문을 실행해야 한다.
2. XACT_STATE() 함수가 1을 반환했다면 트랜젝션이 커밋할 수 있는 상태로 있다는 것으로 Commit Transaction 문을 실행해야 한다.
3. XACT_STATE() 함수가 0을 반환했다면 트랜젝션이 없다는 것으로 어떤 조치도 취할 필요가 없다.
Commit Transaction이나 Rollback Transaction 문을 Catch 블록에서 실해하기 전, 트랜젝션을 확인해보는 것은 어떤 처리의 일관성을 유지하는데 좋다.
# Example
먼저 sales.persons 테이블과 sales.deals 테이블을 생성한다.
CREATE TABLE sales.persons
(
person_id INT PRIMARY KEY IDENTITY,
first_name NVARCHAR(100) NOT NULL,
last_name NVARCHAR(100) NOT NULL
);
CREATE TABLE sales.deals (
deal_id INT PRIMARY KEY IDENTITY,
person_id INT NOT NULL,
deal_note NVARCHAR(100), FOREIGN KEY(person_id) REFERENCES sales.persons( person_id)
);
insert into sales.persons(first_name, last_name) values ('John','Doe'), ('Jane','Doe');
insert into sales.deals(person_id, deal_note) values (1,'Deal for John Doe');
다음으로 usp_report_error 프로시저를 만든다. 이 프로시저는 catch 블록에서 에러의 상세정보를 알려준다.
CREATE PROC usp_report_error
AS
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_LINE () AS ErrorLine
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_MESSAGE() AS ErrorMessage;
GO
그리고 sales.persons 테이블로 부터 행을 삭제하는 저장 프로시저를 만든다.
CREATE PROC usp_delete_person(
@person_id INT
) AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
-- delete the person
DELETE FROM sales.persons
WHERE person_id = @person_id;
-- if DELETE succeeds, commit the transaction
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- report exception
EXEC usp_report_error;
-- Test if the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT N'The transaction is in an uncommittable state.' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test if the transaction is committable.
IF (XACT_STATE()) = 1
BEGIN
PRINT N'The transaction is committable.' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH
END;
GO
XACT_STATE() 함수는 Catch 블록 내에서 Commit transaction 이나 Rollback Transaction 을 실행하기 전에 트랜젝션 상태를 확인한다.
이제 person id가 2인 사람을 삭제하는 usp_delete_person을 실행시켜 본다.
EXEC usp_delete_person 2;
예외가 발생하지 않는다. 마지막으로 person id가 1인 사람을 삭제하는 usp_delete_person을 실행시켜 본다.
EXEC usp_delete_person 1;
아래와 같은 예외가 발생한다.
'데이터베이스' 카테고리의 다른 글
테이블 변수(SQL Server Table Variables) (0) | 2020.06.11 |
---|---|
임시 테이블 Temporary Tables (0) | 2020.06.10 |
Merge (0) | 2020.06.08 |
Multi-statement table-valued functions in Sql Server (0) | 2020.06.06 |
클러스터 인덱스, 비 클러스터형인데스 차이 (0) | 2020.06.05 |