16-4.SQL Server의 프로시저

1.생성 및 호출

SQL Server의 프로시저도 개념 및 사용 목적은 오라클과 같지만 정의하는 구문은 많이 다르다. 변수나 제어문부터 문법이 다르니 어쩔 수 없는 부분이며 형식은 새로 익혀야 한다.

CREATE PROCEDURE 이름

[인수 목록]

[WITH 옵션]

AS

본체

이름은 자유롭게 붙일 수 있지만 SP_ 접두어는 시스템 프로시저를 의미하므로 접두를 붙이지 않거나 아니면 PROC_ 접두를 붙인다. 이름이 #이나 ##으로 시작하면 tempdb에 저장되는 임시 프로시저를 생성하는데 성능상의 이점이 없어 잘 사용하지 않는다.

다음은 인수로 전달한 도시의 인구수를 조사하여 출력하는 프로시저이다. 똑같은 동작을 하지만 오라클의 프로시저와 비교해 보면 많은 부분이 다르다.

CREATE PROCEDURE PROC_GetCityPopu

@p_name CHAR(10)

AS

BEGIN

SELECT popu FROM tCity WHERE name = @p_name;

END

인수 목록을 괄호로 감싸지 않으며 가변 길이 타입은 CHAR로만 쓸 수는 없고 CHAR(10)으로 길이를 다 밝혀야 한다. 값을 출력할 때는 굳이 변수에 대입할 필요 없이 SELECT 문으로 바로 출력할 수 있어 편리하다.

본체의 명령이 하나뿐이면 BEGIN~END 블록은 없어도 되지만 이후의 관리를 위해 블록으로 묶어 두는 것이 좋다. 프로시저를 호출할 때는 EXECUTE 또는 줄여서 EXEC 명령을 사용한다. 단독으로 호출할 때는 EXEC 명령 자체를 생략할 수 있지만 EXEC가 있으면 독립 배치로 실행하는 미세한 차이가 있다.

인수는 대입문 형식으로 값을 대입하거나 아니면 값만 나열해도 된다. 인수명 없이 값만 나열하는 것이 간편하지만 순서를 지켜야 한다. 대입문 형식은 순서에 상관없이 값을 전달할 수 있어 디폴트 인수를 건너뛸 때 편리하다. 다음 세 형식 모두 가능하다.

EXEC PROC_GetCityPopu @p_name = '서울';

EXEC PROC_GetCityPopu '서울';

PROC_GetCityPopu '서울';

다음 형식으로 호출하면 에러이다. 프로시저가 요구하는 인수를 전달하지 않으면 에러이며 인수를 괄호 안에 적는 형식도 지원하지 않는다.

EXEC PROC_GetCityPopu;

EXEC PROC_GetCityPopu('서울');

프로시저를 수정하는 원론적인 방법은 DROP PROCEDURE 명령으로 삭제한 후 새로 만드는 것이다. 공식적인 방법으로 ALTER PROCEDURE 명령이 있으나 표준은 아니고 SQL Server에서만 가능하다. 변수에 값을 대입한 후 변수를 출력하는 형식으로 수정해 보자.

ALTER PROCEDURE PROC_GetCityPopu

@p_name CHAR(10)

AS

DECLARE

@popu INT

BEGIN

SELECT @popu = popu FROM tCity WHERE name = @p_name;

PRINT @popu

END

오라클은 변수를 통해 값을 출력하는데 SQL Server도 비슷한 방법을 쓸 수 있다. 그러나 예제 수준에서나 결과를 출력해 볼 뿐 개발 중에는 프로시저가 값을 출력할 경우가 거의 없다. SQL Server에서는 그냥 SELECT 문으로 결과를 확인해 보면 된다.

SQL Server는 오라클과는 달리 프로시저를 정의할 때 객체의 유효성을 점검하지 않는다. 다음 프로시저는 존재하지 않는 tCity2 테이블을 참조하지만 이상 없이 컴파일된다. 물론 테이블이 존재하지 않아 실행시 에러가 발생한다.

ALTER PROCEDURE PROC_GetCityPopu

@p_name CHAR(10)

AS

BEGIN

SELECT popu FROM tCity2 WHERE name = @p_name;

END

이를 지연된 이름 확인이라고 한다. 그래서 프로시저를 먼저 만들어 두고 테이블을 나중에 만들어도 상관없으며 중간에 테이블이 잠시 사라져도 별 문제 없다. 실행하기 전에 테이블을 만들어 놓기만 하면 된다.

2.인수와 리턴

인수는 필요한 만큼 받을 수 있고 디폴트값을 지정할 수도 있다. 다음은 p_member에게 p_bonus를 지급하는 프로시저이다.

CREATE PROCEDURE PROC_GrantBonus

@p_member CHAR(20),

@p_bonus INT = 100

AS

BEGIN

UPDATE tMember SET money = money + @p_bonus WHERE member = @p_member;

END;

두 개의 인수를 받으며 p_bonus는 디폴트값 100을 지정했다. 두 인수를 순서대로 다 전달하거나 아니면 뒤쪽 인수는 생략해도 디폴트값 100이 적용된다.

PROC_GrantBonus '춘향', 2000;

PROC_GrantBonus '춘향';

인수의 이름을 밝히면 호출문이 길어지지만 순서에 상관없이 전달할 수 있는 이점이 있다. 다음 두 구문 모두 잘 동작한다.

PROC_GrantBonus @p_member = '춘향', @p_bonus = 2000;

PROC_GrantBonus @p_bonus = 2000, @p_member = '춘향';

출력용 인수는 뒤에 OUTPUT 키워드를 붙인다. 다음 프로시저는 도시의 인구수를 조사하여 출력용 인수로 리턴한다.

CREATE PROCEDURE PROC_OutCityPopu

@p_name CHAR(10),

@o_popu INT OUTPUT

AS

BEGIN

SELECT @o_popu = popu FROM tCity WHERE name = @p_name;

END;

호출할 때는 변수를 선언하여 출력용 인수로 전달하되 OUTPUT 키워드를 밝힌다. 프로시저가 인수에 값을 대입해 주면 이 값을 호출측에서 사용한다.

DECLARE @popu INT;

EXECUTE PROC_OutCityPopu '서울', @popu OUTPUT;

PRINT '서울의 인구는 ' + CAST(@popu AS VARCHAR(10)) + '만명입니다.';

프로시저가 호출원으로 값을 돌려주는 일반적인 방법은 출력용 인수이다. 여러 개의 출력용 인수로 많은 양의 정보를 리턴할 수 있고 타입의 제약도 없다. 하지만 호출원이 변수를 선언해야 한다는 점에서 번거로운데 정수값 하나를 리턴할 때는 RETURN 문이 간편하다.

RETURN 명령은 프로시저의 나머지 명령을 무시하고 즉시 종료하며 정수값 하나를 리턴한다. 오로지 정수만 리턴할 수 있으며 임의 타입을 리턴할 수는 없다. 다음 프로시저는 도시의 인구수를 조사하여 리턴한다.

CREATE PROCEDURE PROC_RetCityPopu

@p_name CHAR(10)

AS

DECLARE

@popu INT;

BEGIN

SELECT @popu = popu FROM tCity WHERE name = @p_name;

RETURN @popu;

END;

호출예는 다음과 같다. 프로시저의 실행문 자체를 변수에 대입하여 사용한다.

DECLARE @popu INT;

EXECUTE @popu = PROC_RetCityPopu '서울';

PRINT '서울의 인구는 ' + CAST(@popu AS VARCHAR(10)) + '만명입니다.';

프로시저가 값을 리턴하더라도 식내에서 프로시저를 사용할 수는 없다. 다음 두 명령은 모두 에러이다. 이런 용도로 쓰고 싶다면 함수를 만들어야 한다.

PRINT '서울의 인구는 ' + CAST(PROC_RetCityPopu '서울' AS VARCHAR(10)) + '만명입니다';

SELECT PROC_RetCityPopu '서울';

프로시저의 RETURN 문은 결과값 리턴보다는 에러 처리용이다. 프로시저 제작자가 발생 가능한 에러 코드를 정의하고 에러 상황시 코드를 리턴하는 식이다. 호출원은 프로시저의 리턴값을 점검하여 에러를 처리한다.

CREATE PROCEDURE GetWantedValue 인수목록 AS IF (인수값이 잘못됐음) RETURN 1 IF (테이블이 없음) RETURN 2 IF (권한이 없음) RETURN 3 값을 구하는 처리 RETURN 0

DECLARE @Result INT; EXEC @Result=GetWantedValue; IF @Result = 0 THEN 하고 싶은 일; ELSE IF @Result = 1 THEN PRINT '인수가 잘못됐잖아!'; ELSE IF @Result = 2 THEN PRINT '어! 테이블이 없네'; ELSE IF @Result = 3 THEN PRINT '넌 이 값을 읽을 자격이 없어';

프로시저의 코드를 정확히 작성했더라도 항상 제대로 실행되는 것은 아니다. 프로시저를 모듈로서 활용하려면 안정성을 확보해야 한다. 외부 환경의 변화나 무효한 인수로 인해 예외가 발생할 가능성은 항상 있다. 다음 프로시저는 tCity에 서울이 뻔히 있는데 또 삽입하여 일부러 예외를 유발한다.

CREATE PROCEDURE PROC_InsertSeoul

AS

BEGIN

INSERT INTO tCity VALUES ('서울',605,974,'y','경기');

END

당연히 실패하며 에러 메시지를 출력한다. 에러가 발생하도록 내 버려 두는 것보다는 프로시저 내부에서 에러를 처리하고 더 친절한 메시지를 출력하는 것이 좋다. @@ERROR 시스템 함수는 최후 실행한 쿼리의 결과값이며 성공시는 0, 오류 발생시는 에러 번호값을 가진다.

ALTER PROCEDURE PROC_InsertSeoul

AS

BEGIN

INSERT INTO tCity VALUES ('서울',605,974,'y','경기');

IF @@ERROR != 0

BEGIN

PRINT('새 레코드를 삽입하지 못했습니다.');

END

END

삽입 명령 실행 후 @@ERROR를 점검해 보고 0이 아니면 에러가 발생했음을 상세히 알린다. 그러나 이 메시지를 출력하기 전에 이미 예외가 발생하며 메시지는 그 후에 출력된다. 더 적극적으로 처리하려면 예외 처리 구문을 사용한다.

ALTER PROCEDURE PROC_InsertSeoul

AS

BEGIN

BEGIN TRY

INSERT INTO tCity VALUES ('서울',605,974,'y','경기');

END TRY

BEGIN CATCH

PRINT '에러 번호 : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));

PRINT '에러 메시지 : ' + ERROR_MESSAGE();

END CATCH

END

예외가 발생할만한 구문을 BEGIN TRY 블록에서 실행하고 BEGIN CATCH 블록에서 예외의 종류를 판별하여 원하는 처리를 수행한다. 적어도 시스템이 뿜어 내는 불친절하고 무시무시한 메시지보다는 공손하고 상세한 메시지를 보여줄 수 있다.

프로시저에 중요한 규칙이나 보안상 치명적인 정보가 있다면 암호화해야 한다. WITH ENCRYPTION 옵션은 프로시저 본문을 암호화하여 실행은 되지만 내용을 다시 보거나 수정할 수 없다. 심지어 만든 사람도 볼 수 없어 원래의 본문을 따로 잘 보관해 두어야 한다.

성능을 위해 프로시저는 미리 컴파일해 두는데 인수에 따라 최적화 방법이 바뀌거나 외부의 변화에 의해 컴파일해 놓은 실행 계획이 부적절해지기도 한다. 이럴 때는 WITH RECOMPILE 옵션을 사용한다. 프로시저 정의문에 이 옵션을 지정하면 실행시마다 재컴파일하며 EXEC 명령에 이 옵션을 주면 재컴파일 후 실행한다.

또는 sp_recompile 프로시저로 재컴파일이 필요한 프로시저임을 지정해 놓을 수도 있고 DBCC FREEPROCCACHE 명령으로 캐시를 비워 다음 실행시 강제로 재컴파일하도록 한다. 재컴파일 옵션은 성능에 영향을 미치는 힌트일 뿐 동작에는 영향을 주지 않는다.

3.시스템 프로시저

SQL Server는 시스템 관리를 위해 미리 정의한 시스템 프로시저를 제공한다. 주로 환경을 설정하거나 조사하고 DB 오브젝트를 관리하는 목적으로 사용한다. 관리 스튜디오의 master/프로그래밍 기능/저장 프로시저 노드를 보면 sp_로 시작하는 수많은 시스템 프로시저가 있으며 팝업에서 수정을 선택하면 소스도 볼 수 있다.

이 코드를 분석해 보면 프로시저 제작과 SQL Server의 구조에 대해 많은 것을 배울 수 있다. SQL Server 설치 직후부터 등록되어 있어 언제든지 간편하게 사용할 수 있다. 이 중 자주 사용하는 실용적인 프로시저 몇 가지를 알아보자.

프로시저	설명
sp_databases	서버의 데이터베이스 목록을 나열한다.
sp_tables	현재 DB의 테이블 목록을 나열한다. 인수를 전달하면 특정 테이블에 대한 정보만 보여준다.
sp_help	DB 오브젝트에 대한 상세한 정보를 조사하여 출력한다. sp_help 'tCity'는 도시 정보 테이블의 구조와 제약 사항을 상세히 보여준다.
sp_helptext	프로시저, 함수, 트리거 등의 소스 코드를 출력한다. sp_helptext 'PROC_GetCityPopu' 명령을 실행해 보자.
sp_configure	SQL Server의 전역 설정 상태를 조사하거나 @configname, @configvalue 인수로 옵션과 값을 지정하여 변경한다.
sp_helpsort	정렬 순서와 문자 집합을 조사한다.
sp_rename	DB 오브젝트의 이름을 변경한다. 제약이나 권한을 그대로 유지한채 이름만 변경한다.

시스템 프로시저는 일종의 유틸리티이며 편의 기능을 제공한다. 이 외에도 수많은 시스템 프로시저가 정의되어 있으며 도움말도 상세하게 잘 정리되어 있다. 수시로 레퍼런스를 찾아 보고 쓸만한 프로시저를 익혀 보자.

확장 프로시저는 SQL Server 외부의 작업을 처리한다. 서버의 용량이 부족하거나 치명적인 에러가 발생하면 관리자에게 핸드폰 문자나 메일로 이 사실을 알려야 한다. SQL은 데이터만 관리하는 언어여서 이런 명령까지는 처리할 수 없다. 그래서 네이티브 언어로 작성한 확장 프로시저의 도움을 받아야 한다. 확장 프로시저는 SQL Server API를 사용하여 C언어로 만들며 DLL 형태로 동작한다.

확장 프로시저는 시스템 명령까지 수행할 수 있어 SQL Server의 기능을 무한히 확장할 수 있지만 잘못 사용하면 오히려 성능을 저해하거나 안정성을 위협할 수도 있다. 그래서 요즘은 닷넷 프레임워크의 메서드를 호출하는 CLR 프로시저를 더 권장한다. 시스템 기능을 확장하는 용도여서 사용자 DB에는 쓸 수 없고 master DB에만 작성할 수 있다.

4.사용자 정의 함수

사용자 정의 함수는 리턴값의 종류에 따라 단일 값을 리턴하는 스칼라 함수와 결과셋을 리턴하는 테이블 함수로 구분되며 테이블 함수는 인라인 테이블과 다중문 테이블로 다시 세분화된다. 다음은 가장 간단한 스칼라 함수이다.

CREATE FUNCTION FN_AddInt(@a INT, @b INT) RETURNS INT

AS

BEGIN

RETURN @a + @b;

END

함수명은 관습적으로 접두 FN_을 붙이며 괄호 안에 인수 목록을 적고 RETRUNS 키워드로 리턴할 값의 타입을 지정한다. 함수 본체에서는 값을 계산한 후 RETURN 명령으로 값을 리턴한다. 본체에서는 제어문, 쿼리문 등을 자유롭게 기술할 수 있지만 다음 동작은 할 수 없다.

l 테이블을 읽을 수는 있어도 삭제하거나 변경할 수는 없다.

l 딱 하나의 결과만 리턴한다.

l 예외 처리 구문은 사용할 수 없다.

l 외부 변수의 값을 SET 문으로 변경할 수 없다.

함수의 본분은 값을 계산하는 것이지 동작을 처리하는 것이 아니어서 값을 구하는 것 이외의 동작을 해서는 안된다. FN_AddInt 함수는 두 개의 정수 @a, @b를 전달받아 두 정수를 더한 합을 리턴한다. 함수는 정의한 사람만 호출할 수 있으며 호출할 때 스키마명을 붙인다. DB명까지는 굳이 붙이지 않아도 상관없다. 인수는 괄호 안에 전달한다.

SELECT Study.dbo.FN_AddInt(2, 3);

리턴값을 확인하려면 SELECT 문이나 PRINT 명령 다음에 함수를 호출한다. 스칼라 함수 호출문은 하나의 값을 리턴하므로 타입만 일치한다면 필드나 변수가 올 수 있는 모든 곳에 사용할 수 있다. 그러나 테이블은 아니어서 FROM 절에는 올 수 없다.

테이블 함수는 리턴 타입을 TABLE로 지정하며 본체는 결과셋을 생성하는 SELECT 문 하나로 구성한다. 다음 함수는 지역의 이름을 전달받아 이 지역의 도시 목록을 결과셋으로 만들어 리턴한다.

CREATE FUNCTION FN_GetCity(@region CHAR(10))

RETURNS TABLE

AS

RETURN SELECT * FROM tCity WHERE region = @region;

본체는 SELECT 결과를 리턴하는 명령 하나만 올 수 있으며 그래서 BEGIN, END 조차 쓸 필요 없다. 함수가 리턴하는 결과셋은 일종의 임시 테이블이다. 뷰와 비슷하지만 뷰는 정의할 때 결과셋이 미리 결정되는데 비해 함수는 인수에 따라 결과셋이 달라진다는 차이점이 있다.

다음 쿼리는 강원도의 도시 목록을 출력한다. 테이블 함수는 스키마를 붙이지 않아도 상관없지만 가급적 일관되게 붙이는 것이 좋다. 함수는 SELECT 문으로 조사한 결과셋을 리턴하며 호출원은 함수를 마치 테이블처럼 사용한다.

SELECT * FROM dbo.FN_GetCity('강원');

함수가 리턴한 테이블에 대해 일부 필드만 선택적으로 출력하거나 조건을 지정할 수도 있다. 다음 쿼리는 강원도 도시중 인구가 10만을 넘는 도시만 조사하며 춘천을 출력한다.

SELECT * FROM dbo.FN_GetCity('강원') WHERE popu > 10;

조인문을 함수로 정의해 두면 함수 내부의 조인문이 아무리 복잡해도 리턴 결과는 하나의 테이블이며 마치 원래부터 있던 테이블처럼 사용할 수 있다.

인라인 테이블 함수는 SELECT 문 하나로 구성되며 쿼리 결과를 리턴할 뿐 변수나 제어문 같은 복잡한 코드는 쓸 수 없다. 이에 비해 다중문 테이블 함수는 코드를 사용할 수 있으며 아예 새로운 테이블을 만들어 리턴한다.

리턴할 테이블을 RETURNS 절에서 선언한다. 문법은 CREATE TABLE과 비슷하며 괄호 안에 필드 목록을 나열한다. 본체에서는 이 테이블에 레코드를 채워 리턴한다. 다음 함수는 인수로 전달받은 지역의 도시명과 인구수만으로 테이블을 만들어 리턴한다.

CREATE FUNCTION FN_GetCityTable(@region CHAR(10))

RETURNS @result TABLE

(name VARCHAR(10),popu INT)

AS

BEGIN

INSERT INTO @result SELECT name, popu FROM tCity WHERE region = @region;

RETURN;

END

@result 테이블 변수를 이름과 인구만 가지는 형태로 정의하고 본체에서 tCity의 레코드를 읽어 이 테이블을 채웠다. 그리고 만들어진 테이블 자체를 리턴한다. 다음 쿼리문은 경기도 지역의 도시 목록을 출력하되 원본과 달리 이름과 인구수만 나타난다.

SELECT * FROM dbo.FN_GetCityTable('경기');

함수에 의해 새로운 테이블이 만들어진다. 뷰와 유사하지만 DB에 영구적으로 저장되지 않으며 함수가 끝나면 삭제되는 임시 테이블이다. 함수 호출시마다 생성했다가 사라져 성능상 불리하다.