[MSSQL] 저장 프로시저 문법 및 사용 예시

저장 프로시저(Stored Procedure)란 무엇인가?

SQL Server에서 제공되는 프로그래밍 기능. 즉, 쿼리문의 집합으로써, 어떠한 동작을 일괄 처리하는 용도로 사용된다.

저장 프로시저의 정의 형식

--Transact-SQL Stored Procedure Syntax
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
 
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

저장 프로시저의 수정과 삭제

- 수정 : ALTER PROCEDURE…

- 삭제 : DROP PROCEDURE…

저장 프로시저 사용 예시

(1) 매개 변수가 1개인 저장 프로시저

-- 저장 프로시저 정의
CREATE PROCEDURE usp_users1
    @userName NVARCHAR(10)
AS
    SELECT * FROM userTbl WHERE name = @userName;
GO
-- 저장 프로시저 사용
EXEC usp_users1 '조관우';

- 결과

(2) 매개 변수가 2개인 저장 프로시저

-- 저장 프로시저 정의
CREATE PROCEDURE usp_users2
    @userBirth INT,
    @userHeight INT
AS
    SELECT * FROM userTbl WHERE birthYear > @userBirth AND height > @userHeight;
GO
-- 저장 프로시저 사용
EXECUTE usp_users2 1970,178;

- 결과

(3) 매개 변수가 2개인 저장 프로시저 매개 변수 순서 바꿔서 사용하기

-- 저장 프로시저 정의
CREATE PROCEDURE usp_users2
    @userBirth INT,
    @userHeight INT
AS
    SELECT * FROM userTbl WHERE birthYear > @userBirth AND height > @userHeight;
GO
-- 저장 프로시저 매개 변수 순서 바꿔서 사용하기
EXEC usp_users2 @userHeight = 178, @userBirth = 1970;

- 결과는 (2)와 같다.

(4) 매개 변수에 디폴트 값을 지정한 저장 프로시저

-- 저장 프로시저 정의
CREATE PROCEDURE usp_users3
    @userBirth INT = 1970,
    @userHeight INT = 178
AS
    SELECT * FROM userTbl WHERE birthYear > @userBirth AND height > @userHeight;
GO
-- 저장 프로시저 사용
EXEC usp_users3;

- 결과는 (2)와 같으며, 저장 프로시저를 사용할 때 매개 변수를 넣지 않은 것을 볼 수 있다.

(5) OUTPUT 매개 변수를 사용하는 저장 프로시저

-- 저장 프로시저 정의
CREATE PROCEDURE usp_users4
    @txtValue NCHAR(10),
    @outValue INT OUTPUT
AS
    INSERT INTO testTbl VALUES(@txtValue);                    -- 테스트용 테이블에 입력 받은 데이터 추가
    SELECT @outValue = IDENT_CURRENT('testTbl');            -- 테이블의 현재 identity 값
GO
 
-- 테스트용 테이블 생성
CREATE TABLE testTbl (id INT IDENTITY, txt NCHAR(10));
GO
 
-- 저장 프로시저 생성
DECLARE @myValue INT;                                        -- OUTPUT값을 받을 변수 선언
EXEC usp_users4 '테스트 값1', @myValue OUTPUT;                -- 저장 프로시저 호출
PRINT '현재 입력된 ID 값 ===> ' + CONVERT(CHAR(5),@myValue)    -- 결과 출력 (IDENTITY 값 출력)

- 결과 ( ※ 테이블이 아직 존재하지 않아도 저장 프로시저는 생성이 가능하다는 것을 확인해 볼 수 있다. )

(6) 저장 프로시저에서 IF…ELSE문을 사용

-- 저장 프로시저 정의
CREATE PROC usp_ifElse
    @userName NVARCHAR(10)
AS
    DECLARE @bYear INT        -- 출생년도를 저장할 변수
    SELECT @bYear = birthYear FROM userTbl
        WHERE name = @userName;
    IF(@bYear >= 1980)
        BEGIN
            PRINT N'아직 젊군요..';
        END
    ELSE
        BEGIN
            PRINT N'나이가 지긋하네요..';
        END
GO
-- 저장 프로시저 
EXEC usp_ifElse '조용필';

- 결과 ( '조용필'의 출생년도는 1950으로 1980보다 작다.)

(7) 저장 프로시저에서 CASE문을 사용

-- 저장 프로시저 정의
CREATE PROC usp_case
    @userName NVARCHAR(10)
AS
    DECLARE @bYear INT
    DECLARE @tti NCHAR(3) -- 띠
    SELECT @bYear = birthYear FROM userTbl
        WHERE name = @userName
    SET @tti =
    CASE
        WHEN (@bYear % 12 = 0) THEN '원숭이'
        WHEN (@bYear % 12 = 1) THEN '닭'
        WHEN (@bYear % 12 = 2) THEN '개'
        WHEN (@bYear % 12 = 3) THEN '돼지'
        WHEN (@bYear % 12 = 4) THEN '쥐'
        WHEN (@bYear % 12 = 5) THEN '소'
        WHEN (@bYear % 12 = 6) THEN '호랑이'
        WHEN (@bYear % 12 = 7) THEN '토끼'
        WHEN (@bYear % 12 = 8) THEN '용'
        WHEN (@bYear % 12 = 9) THEN '뱀'
        WHEN (@bYear % 12 = 10) THEN '말'
        ELSE '양'
    END
PRINT @userName + '의 띠 ===> ' + @tti;
GO
-- 저장 프로시저 사용
EXEC usp_case '성시경'

- 결과 ('성시경'의 출생년도는 1979이다.)

(8) 저장 프로시저에서 WHILE문 사용

- 고객의 총 구매 금액에 따른 등급 열 추가하기

-- 기존의 테이블에 등급 열 추가
ALTER TABLE userTbl
    ADD grade NVARCHAR(5);        -- 고객 등급 열 추가
GO
 
-- 저장 프로시저 생성
CREATE PROCEDURE usp_while
AS
    DECLARE userCur CURSOR FOR        -- 커서 선언
        SELECT U.userid,sum(price*amount)
        FROM buyTbl AS B
            RIGHT OUTER JOIN userTbl U
            ON B.userID = U.userID
        GROUP BY U.userID, U.name
 
        OPEN userCur            -- 커서 열기
 
        DECLARE @id NVARCHAR(10)        -- 사용자 아이디를 저장할 변수
        DECLARE @sum BIGINT                -- 총 구매액을 저장할 변수
        DECLARE @userGrade NCHAR(5)        -- 고객 등급 변수
 
        FETCH NEXT FROM userCur INTO @id, @sum        -- 촉 행 값을 대입
        WHILE (@@FETCH_STATUS = 0)        -- 행이 없을 때까지 반복(즉, 모든 행 처리)
        BEGIN
            SET @userGrade =
                CASE
                WHEN (@sum >= 1500) THEN N'최우수고객'
                WHEN (@sum >= 1000) THEN N'우수고객'
                WHEN (@sum >= 1) THEN N'일반고객'
                ELSE '유령고객'
                END
            UPDATE userTbl SET grade = @userGrade WHERE userID = @id
            FETCH NEXT FROM userCur INTO @id, @sum -- 다음 행 값을 대입
        END
 
        CLOSE userCur    -- 커서 닫기
        DEALLOCATE userCur -- 커서 해제
    GO
 
 
-- 저장 프로시저 호출 후 확인
EXEC usp_while;
SELECT * FROM userTbl;

[ 저장 프로시저 호출 전]

[ 저장 프로시저 호출 후 ]

(9) RETURN 문을 이용하여 저장 프로시저의 성공 여부 확인하기

- 조회하여 해당 이름의 데이터가 존재하면 성공, 존재하지 않다면 실패 하는 저장 프로시저

-- 저장 프로시저 생성
CREATE PROC usp_return
    @username nvarchar(10)
AS
    DECLARE @userID char(8)
    SELECT @userID = userID FROM userTbl
        WHERE name = @username;
    IF(@userID <> '')
        RETURN 0;        -- 성공일 경우, 그냥 RETURN만 써도 0을 돌려준다.
    ELSE
        RETURN -1;        -- 실패일 경우 (즉, 해당 이름의 ID가 없을 경우)
GO
 
-- 성공인 경우의 저장 프로시저
DECLARE @retVal INT;
EXEC @retVal = usp_return '은지원';
SELECT @retVal;
 
-- 실패인 경우의 저장 프로시저
DECLARE @retVal INT;
EXEC @retVal = usp_return '나몰라';
SELECT @retVal;

- 성공인 경우의 저장 프로시저 결과

- 실패인 경우의 저장 프로시저 결과

저장 프로시저 에러

(1) @@ERROR 함수를 사용한 오류 처리

-- 저장 프로시저 생성 ( 테이블에 데이터를 추가하는 프로시저 )
-- userID와 이름외에 모든 매개 변수는 디폴트 값 설정
CREATE PROC usp_error
    @userid char(8),
    @name nvarchar(10),
    @birthYear int = 1900,
    @addr nchar(2) = N'서울',
    @mobile1 char(3) = NULL,
    @mobile2 char(8) = NULL,
    @height smallint = 170,
    @mdate date = '2013-11-11'
AS
    DECLARE @err INT;
    INSERT INTO userTbl(userID,name,birthYear,addr,mobile1,mobile2,height,mDate)
    VALUES(@userid,@name,@birthYear,@addr,@mobile1,@mobile2,@height,@mdate);
 
    SELECT @err = @@ERROR;
    IF @err != 0
    BEGIN
        PRINT '###' + @name + '을(를) INSERT에 실패했습니다. ###'
    END;
 
    RETURN @err;
GO

- 실행 구문

DECLARE @errNum INT;
EXEC @errNum = usp_error 'WDT',N'우당탕';
IF(@errNum != 0)
    SELECT @errNum;

- 결과 : 처음 실행하면 성공적으로 데이터가 추가 된다. 하지만 한번 더 실행하면 아래와 같은 결과가 나온다.

(2) (1)의 저장 프로시저에 TRY…CATCH 사용하기

SELECT o.name, m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o
ON m.object_id = o.object_id AND o.type = 'P';

- 결과

※ Tip : [Ctrl + T] 키를 눌러 '텍스트로 결과 표시'로 변경하여 텍스트 형식으로 확인이 가능하며, [Ctrl + D] 키를 눌러 다시 '표로 결과 표시'로 변경이 

          가능하다.

저장 프로시저 암호화

- WITH ENCRYTION

- 다른 사용자가 소스 코드를 확인할 수 없게한다.

CREATE PROC usp_Encrypt WITH ENCRYPTION
AS
    SELECT SUBSTRING(name,1,1) + 'OO' as [이름], birthYear as N'출생년도', height AS N'키'
    FROM userTbl;
GO
EXEC usp_Encrypt;

사용자 정의 데이터 형식을 매개 변수로 하는 저장 프로시저 ( 여기서는 사용자 정의 데이터 형식으로 테이블 사용 )

- 사용자 정의 데이터 형식 생성

CREATE TYPE userTblYtpe AS Table
(
    userID        char(8),
    name        nvarchar(10),
    birthYear    int,
    addr        nchar(2)
)

- 저장 프로시저 생성 ( 1970년 이전 출생자만 출력 )

CREATE PROC usp_tableTypeParameter
    @tblPara userTblYtpe READONLY        -- 테이블 형식의 매개 변수는 READONLY를 붙여야 한다.
AS
    BEGIN
        SELECT * FROM @tblPara WHERE birthYear < 1970;
    END

- 테이블 형식의 변수를 선언하고, 데이터를 입력 시킨 후, 저장 프로시저 호출

DECLARE @tblVar userTblYtpe;
INSERT INTO @tblVar
    SELECT userID,name,birthYear,addr FROM userTbl;    -- 테이블 변수에 데이터 입력

-결과

---

참고문헌

우재남, 『뇌를 자극하는 SQL Server 2012』 한빛미디어 (2013-03-02 초판발행)