MSSQL Server 2000의 오류처리 기법 > db

=================================
MSSQL Server 2000의 오류처리 기법
=================================

출처 : 야시님의 홈페이지에서...

대개의 경우는 쿼리분석기에서 오류처리거나 프로그램 상에서 오류처리를 한다. 서버와
클라이언트의 시스템 사양은 많은 차이가 있다. 되도록이면 처리는 서버에서 이루어지고
결과만을 가지고 클라이언트는 의사결정을 하게 된다.

오류 메시지는 실제 사용자나 개발자에게 유용한 정보가 된다. MSSQL Server에서는 오류처리를 위하여 RAISERROR라는 것을 제공한다. 이것을 이용해서 어떻게 효율적으로 오류처리가 이루어져야 하는지를 살펴보도록 하자.

----------------------------
오류 메세지에 대한 기본 사항
----------------------------

[오류번호]
MSSQL SERVER에서 정의된 오류번호는 50000번 까지다. 이는 sysmessages 테이블에 저장된다. 그러므로 사용자 오류 메세지를 사용하기 위해서는 50001번부터 사용해야 한다. 또한 심각도 16이상의 오류 메세지는 이벤트 로그에 기록된다. 심각도 14는 '정보'로 기록되며, 심각도 15는 '경고'로 기록된다.

[이벤트 로그에 기록]
모든 사용자는 심각도 18까지를 지정할 수 있으며, 19 ~ 25 까지는 sysadmin 역할의 구성원만 사용 할 수 있으며, WITH LOG 옵션을 반드시 사용해야 한다. 심각도 20 이상이면 클라이언트 프로그램은 종료된다. 또한 sysmessages에 등록되지 않더라도 sp_addmessage의 with log옵션을 사용하면 이벤트 로그에 기록되게 할 수 있다.

[클라이언트로 오류 메세지 전송]
RAISERROR의 옵션중 WITH NOWAIT는 클라이언트로의 오류메세지를 전달하는 기능을 한다. NOWAIT 옵션을 사용하면 PRINT와 같은 오류메세지는 클라이언트로 전송된다.

[이벤트 로그에만 기록하기]
클라이언트에게 오류 메세지를 전송하지 않고 이벤트 로그에만 오류 메세지를 기록하고 싶다면 xp_logevent 확장 프로시저를 사용하면 된다. 이 확장 프로시저는 @@ERROR 자동변수에도 오류번호를 기록하지 않다.

[운영제제의 파일로 메세지 내보내기]
이벤트 로그에 기록하는게 싫다면 운영체제로 메세지를 기록할 수도 있다. 이것은 한가지 팁이다. xp_cmdshell 확장프로시저를 이용하여 운영체제의 명령어를 이용하는 방법이다. 다음은
그 예이다.

use tempdb
go

EXEC master..xp_cmdshell 'cd c:', NO_OUTPUT
EXEC master.. xp_cmdshell 'del log.txt', NO_OUTPUT

IF(OBJECT_ID('temp_xp_cmdshell')) IS NOT NULL
DROP PROC temp_xp_cmdshell
GO

CREATE PROCEDURE temp_xp_cmdshell
AS
BEGIN
--PRINT '이거 말입니까??'
--최초는 윈도우즈 명령어인 > 를 쓰고..다음부터는 >>
--윈도우즈 명령어에 관해서는 도움말이나 다른 서적을 참고하자.
EXEC master..xp_cmdshell 'cd c:', NO_OUTPUT
EXEC master..xp_cmdshell 'echo 이거말입니까? > log.txt', NO_OUTPUT
EXEC master..xp_cmdshell 'echo 그거냐? >> log.txt', NO_OUTPUT
END
GO

EXEC temp_xp_cmdshell
EXEC master..xp_cmdshell 'cd c:', NO_OUTPUT
EXEC master..xp_cmdshell 'type log.txt'

====
결과
====

output
---------------------
이거말입니까?
그거냐?
NULL

(3개 행 적용됨)

[부실하지만 그래도 없는거 보다는 낫다]
T-SQL의 오류 처리에 대한 능력은 정말 미약하다. 개발자가 오류처리 코드를 넣다는다고 해도 스크립트(즉, 인터프리터 방식)이기 때문에 에러 처리코드까지 도달하기 전에 에러가 난다면
어쩔 수 없는 것이다. 대부분의 에러는 데이터를 담는 그릇(테이블)에 문제가 있다. 여러가지
무결성을 지키고, 정규화를 잘 하는 것이 에러를 막는 지름길이다. 공사장을 가다보면 사고가
나기전에 처리하는 것보다 예방이 중요하다고 했다. 데이터베이스의 기본철학도 문제점이 일어나
지 않는 환경을 만드는 것이다.

----------------------
여러가지 오류처리 예제
----------------------

[일반적인 처리 형태]
이제 여러가지 오류 처리의 예제를 살펴보도록 하자. 오류처리 기법뿐만 아니라 코딩 기법도 배울 수 있을 것이다.

USE pubs

DECLARE @cnt INT

SELECT @cnt = COUNT(price) FROM titles
WHERE title_id = 'MC3026'

SELECT 1/@cnt

IF @@ERROR <> 0
PRINT ''
PRINT '0으로 나누는 오류냐?'

====
결과
====
경고: 집계 또는 다른 SET 연산에 의해 Null 값이 제거되었습니다.
서버: 메시지 8134, 수준 16, 상태 1, 줄 8
0으로 나누기 오류가 발생했습니다.

0으로 나누는 오류냐?

이 예제는 몇 가지 살펴볼 것들이 있다. 먼저 COUNT() 함수이다. 아래의 두 쿼리를 보도록 하자.

USE pubs
SELECT COUNT(*) FROM titles
SELECT COUNT(price) FROM titles

====
결과
====

-----------
18

(1개 행 적용됨)

-----------
16

(1개 행 적용됨)

경고: 집계 또는 다른 SET 연산에 의해 Null 값이 제거되었습니다.

경고 메세지가 나왔다. 즉, COUNT(*)는 널값을 가지고 있어도 카운트하지만, 임의의 컬럼값을 COUNT()함수의 매개변수로 넣는다면 널값은 제외된다. 가끔 보면 널값을 제외시키기 위해 아래와 같이 ISNULL()함수를 사용하는 경우도 있다. 매개변수가 뭐가 들어가느냐에 따른 차이를
이해하고 ISNULL()함수의 남발을 막아야 한다.

이 스크립트에서는 @cnt가 0이 되어서 나타나는 문제이다. 그러므로 0으로 나눈다는 오류메세지를 받지 않으려면 @cnt에 대한 처리가 필요하다.

USE pubs

DECLARE @cnt INT

SELECT @cnt = COUNT(price) FROM titles
WHERE title_id = 'MC3026'

SELECT CASE WHEN @cnt > 0 THEN 1/@cnt END

IF @@ERROR <> 0 BEGIN
PRINT ''
PRINT '0으로 나누는 오류냐?'
END

====
결과
====
경고: 집계 또는 다른 SET 연산에 의해 Null 값이 제거되었습니다.

-----------
NULL

(1개 행 적용됨)

IF로 할 수도 있었으나 CASE WHEN을 사용하였다. 필자가 실험한 바로는 IF보다는 CASE WHEN이
더 빨랐다(1백만건을 스캔해서 처리하는데 4~7초가 빨랐다.)

[sp_execsql을 이용한 오류 메세지의 전달]
실제로 sp_executesql을 사용하여 아래와 같이 스크립트를 작성하였다. 이것은 에러와 프로시저를 고립시킨 것으로 매우 유용한 예제이다. 오류 메세지를 만들기 위해서 COUNT()함수에 'T'를 하나 더 붙였다. 이러한 기법은 연결된 서버로 작업을 할 때 매우 유용하다.
왜냐하면 오류로 인한 일괄처리가 중지되는 것을 막을 수 있기 때문이다.

USE pubs
GO

CREATE PROC PROC_ERR_TEST
AS
DECLARE @err INT
EXEC @err = sp_executesql N'SELECT COUNTT(*) FROM titles'
IF @err <> 0 BEGIN
PRINT 'ERROR!!'
RETURN(@err)
END
GO

DECLARE @er INT
EXEC @er = PROC_ERR_TEST
SELECT @er

====
결과
====
서버: 메시지 102, 수준 15, 상태 1, 줄 1
'*' 근처의 구문이 잘못되었습니다.
ERROR!!

-----------
102

(1개 행 적용됨)

만약 sp_executesql을 사용하지 않는다면 프로시저는 에러메세지의 손아귀에서 벗어날 수 없다.
당연히 구문분석하면서부터 에러이고, 컴파일 될 수없다. 대신 EXEC()를 사용할 수도 있는데, 권
장사항은 아니다. EXEC()대신 sp_executesql을 사용할 것을 권장한다. 왜냐하면 매개변수 쿼리가
가능하기 때문에 하드 파싱을 막을 수 있다.

CREATE PROC PROC_ERR_TEST2
AS
SELECT COUNTT(*) FROM titles

IF @@ERROR <> 0 BEGIN
PRINT 'ERROR!!'
RETURN(@@ERROR)
END
GO

CREATE PROC PROC_ERR_TEST2
AS
EXEC('SELECT COUNTT(*) FROM titles')

IF @@ERROR <> 0 BEGIN
PRINT 'ERROR!!'
RETURN(@@ERROR)
END
GO

DECLARE @er INT
EXEC @er = PROC_ERR_TEST2
SELECT @er

[SET XACT_ABORT]
SET XACT_ABORT ON은 런타임시 오류가 발생했을 때 트랜잭션을 중단한다. 그러나 여러 서적에서
는 이 옵션이 모든 트랜잭션에 대한 처리를 할 수 있는게 아니라고 한다. 즉, 처리할 수 없는 오류도 존재한다는 것이다.

----
결론
----
트랜잭션 처리에 대한 거의 모든 책임은 개발자에게 있다. 트랜잭션 자체가 하나의 작업이므로 하나의 트랜잭션내에 많은 작업을 하는 것은 잠재적인 문제를 포함하고 있다. 최대한 트랜잭션을
짧게 가져가는 것이 잠재적인 문제를 최소화 시키는 좋은 방법이다.