반응형
출처 : 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 |
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
반응형
'IT > MS SQL' 카테고리의 다른 글
| MSSQL 한글 검색이 안되는경우 ( Varchar 와 nVarchar의 차이 ) (0) | 2022.10.17 |
|---|---|
| MSSQL 임시테이블 # ##, 테이블변수 @ 차이와 사용방법 (0) | 2022.07.21 |
| MSSQL 개발시 유의 또는 참고 사항 (0) | 2022.07.21 |
| MSSQL 형변환 오류 벗어나기 TRY_CONVERT (0) | 2022.07.21 |
| MSSQL WITH(NOLOCK) , SELECT시 잠금무시 (0) | 2022.07.21 |
댓글