IT/MS SQL

MSSQL 프로시저 총정리

heavenLake 2022. 7. 21. 16:43
반응형

출처 : https://gameserverengineer-k.tistory.com/7

 

 

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

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

 

 

저장 프로시저의 정의 형식

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--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 ]
 cs

 

 

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

- 수정 : ALTER PROCEDURE…

- 삭제 : DROP PROCEDURE…

 

 

저장 프로시저 사용 예시

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

1
2
3
4
5
6
7
8
-- 저장 프로시저 정의
CREATE PROCEDURE usp_users1
    @userName NVARCHAR(10)
AS
    SELECT * FROM userTbl WHERE name = @userName;
GO
-- 저장 프로시저 사용
EXEC usp_users1 '조관우';
 cs
- 결과

 

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

1
2
3
4
5
6
7
8
9
-- 저장 프로시저 정의
CREATE PROCEDURE usp_users2
    @userBirth INT,
    @userHeight INT
AS
    SELECT * FROM userTbl WHERE birthYear > @userBirth AND height > @userHeight;
GO
-- 저장 프로시저 사용
EXECUTE usp_users2 1970,178;
 cs
- 결과

 

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

1
2
3
4
5
6
7
8
9
-- 저장 프로시저 정의
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;
 cs

 

- 결과는 (2)와 같다.

 

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

1
2
3
4
5
6
7
8
9
-- 저장 프로시저 정의
CREATE PROCEDURE usp_users3
    @userBirth INT = 1970,
    @userHeight INT = 178
AS
    SELECT * FROM userTbl WHERE birthYear > @userBirth AND height > @userHeight;
GO
-- 저장 프로시저 사용
EXEC usp_users3;
 cs
- 결과는 (2)와 같으며, 저장 프로시저를 사용할 때 매개 변수를 넣지 않은 것을 볼 수 있다.
 
(5) OUTPUT 매개 변수를 사용하는 저장 프로시저
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 저장 프로시저 정의
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 값 출력)
 cs
 
- 결과 ( ※ 테이블이 아직 존재하지 않아도 저장 프로시저는 생성이 가능하다는 것을 확인해 볼 수 있다. )

 

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 저장 프로시저 정의
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 '조용필';
 cs
 
- 결과 ( '조용필'의 출생년도는 1950으로 1980보다 작다.)

 

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 저장 프로시저 정의
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 '성시경'
 cs

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

 

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- 기존의 테이블에 등급 열 추가
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;
 cs

 

[ 저장 프로시저 호출 전]

 

[ 저장 프로시저 호출 후 ]

 

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 저장 프로시저 생성
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;
 cs

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

 

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

 

 

저장 프로시저 에러

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 저장 프로시저 생성 ( 테이블에 데이터를 추가하는 프로시저 )
-- 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
 cs
- 실행 구문
1
2
3
4
DECLARE @errNum INT;
EXEC @errNum = usp_error 'WDT',N'우당탕';
IF(@errNum != 0)
    SELECT @errNum;
 cs

 

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

 

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE PROC usp_tryCatch
    @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;
    BEGIN TRY
        INSERT INTO userTbl(userID,name,birthYear,addr,mobile1,mobile2,height,mDate)
        VALUES(@userid,@name,@birthYear,@addr,@mobile1,@mobile2,@height,@mdate);
    END TRY
 
    BEGIN CATCH
        SELECT ERROR_NUMBER()
        SELECT ERROR_MESSAGE()
    END CATCH
GO
 cs

- 실패시 결과

 

 

현재 저장된 프로시저의 이름 및 내용 확인 방법

(1) sys.objects 및 sys.sql_modules 카탈로그 뷰를 사용한 조회

- 카탈로그 뷰에서 프로시저의 타입은 'P'이다.

1
2
3
4
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';
 cs

- 결과

 

(2) 시스템 저장 프로시저인 sp_helptext 사용하여 프로시저 내용 확인하기

- 형식

EXCUTE sp_helptext 프로시저_이름

1
EXECUTE sp_helptext usp_error;
 cs

 

- 결과

 

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

          가능하다.

 

 

저장 프로시저 암호화

- WITH ENCRYTION

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

1
2
3
4
5
6
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;
 cs

- 결과

- 코드 확인 결과 (EXEC sp_helptext usp_Encrypt;)

 

 

임시 저장 프로시저

- 접두사 '#' : 로컬 임시 저장 프로시저 , tempdb에 생성되며, 생성한 사용자만 사용 가능 , 쿼리 종료시 소멸

- 접두사 '##' : 전역 임시 저장 프로시저 , tempdb에 생성되며 , 외부에서도 사용 가능 , 외부에서 접속한 사용자가 없고 쿼리 종료시 소멸

1
2
3
4
5
6
7
-- 저장 프로시저 생성
CREATE PROC #usp_temp
AS
    SELECT * FROM userTbl;
GO
-- 저장 프로시저 
EXEC #usp_temp;
 cs
※ Tip : 단 , 한 번만 사용될 저장 프로시저를 생성할 목적이라면, 다음과 같이 'sp_excutesql' 시스템 저장 프로시저를 활용하는 것이 시스템 성능을 위해 더 낫다.
1
EXEC sp_executesql N'SELECT * FROM userTbl';

 

 

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

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

1
2
3
4
5
6
7
CREATE TYPE userTblYtpe AS Table
(
    userID        char(8),
    name        nvarchar(10),
    birthYear    int,
    addr        nchar(2)
)
 cs

 

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

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

 

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

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

 

-결과

 

 

참고문헌

 

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

 

 

출처 : https://gameserverengineer-k.tistory.com/7

 

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

저장 프로시저(Stored Procedure)란 무엇인가? SQL Server에서 제공되는 프로그래밍 기능. 즉, 쿼리문의 집합으로써, 어떠한 동작을 일괄 처리하는 용도로 사용된다. 저장 프로시저의 정의 형식 1 2 3 4 5 6

gameserverengineer-k.tistory.com

 

반응형