[MSSQL] 저장 프로시저 (Stored Procedure) 사용법 정리

지난 포스팅에서 저장 프로시저에 대한 설명을 드렸었다.

혹시 안 보신 분은 아래 링크를 참고하자.

https://devkingdom.tistory.com/323

[MSSQL] 저장 프로시저 (Stored Procedure) 란?

 

저장프로시저는 아래의 형식을 따른다.

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] }
[;]

 

저장 프로시저의 예

 

저 형식을 바탕으로 간단하게 저장 프로시저를 실습해보자.

 

먼저 입력 매개변수가 하나일때의 예이다.

CREATE PROCEDURE select_user_by_name
	@userName NVARCHAR(10)
AS
	SELECT * FROM userTbl WHERE name = @userName;

GO

EXEC select_user_by_name '이승기';

 

다음은 입력 매개변수가 2개 이상일 때, 저장 프로시저의 예이다.

CREATE PROCEDURE select_user_by_first_name_and_height
	@userFirstName NVARCHAR(2),
	@userHeight INT
AS
	SELECT * FROM userTbl WHERE name LIKE @userFirstName+'%' AND height > @userHeight;
GO

EXEC select_user_by_first_name_and_height '김',170;

파라미터를 정의한 순서대로 값을 입력해주면되는데, 

순서가 바껴도 상관은 없으나 다만 순서가 바뀔시에는 파라미터 명을 함께 넣어서 실행해줘야한다.

EXEC select_user_by_first_name_and_height @userHeight= 170, @userFirstName ='김';

 

파라미터에 디폴트 값을 넣을 수도 있다.

CREATE PROCEDURE default_select_user_by_first_name_and_height
	@userFirstName NVARCHAR(2) ='김',
	@userHeight INT = 170
AS
	SELECT * FROM userTbl WHERE name LIKE @userFirstName+'%' AND height > @userHeight;
GO

EXEC default_select_user_by_first_name_and_height;

 

 

출력 파라미터를 설정하는 방법도 알아보자.

CREATE PROCEDURE output_table_identity
	@txtValue NCHAR(10),
	@outputValue INT OUTPUT
AS
	INSERT INTO outputTestTbl Values(@txtValue);
	SELECT @outputValue = IDENT_CURRENT('outputTestTbl'); -- 테이블의 현재 identity 값
GO

CREATE TABLE outputTestTbl ( id INT IDENTITY, txt NCHAR(10));
GO

위의 내용은 입력된 파라미터에 의해 출력 테이블에 데이터가 들어가는 걸 확인해 볼수 있는 예이다.

outputTestTbl 같은 경우에는 저장 프로시저의 지연된 이름 확인에 의해 생성되기전부터 정의가 가능하다.

DECLARE @myValue INT;
EXEC output_table_identity '텍스트', @myValue OUTPUT;
PRINT 'Current input Id value : ' +  CAST(@myValue AS CHAR(5));

실행될때마다 id 값이 증가되는 것을 확인할 수 있다.

 

 

저장 프로시저 안의 SQL 프로그래밍

 

저장 프로시저는 프로그래밍하듯 이용이 가능하다.

 

IF~ELSE

CREATE PROCEDURE if_else_test
	@userName NVARCHAR(10)
AS
	DECLARE @birthYear INT
	DECLARE @tempName NVARCHAR(10) = @userName
	SELECT @birthYear = birthYear FROM userTbl WHERE name = @userName;

		IF (@birthYear >= 1980 AND @birthYear < 1995)
			BEGIN
				PRINT @tempName + '님은 M 세대입니다.';
			END
		ELSE IF (@birthYear >= 1995 )
			BEGIN
				PRINT @tempName + '님은 Z 세대입니다.';
			END
		ELSE
			BEGIN
				PRINT @tempName + '님은 MZ 세대가 아닙니다.';
			END
GO

EXEC if_else_test '이승기';

 

CASE

CREATE procedure case_test
	@userName NVARCHAR(10)
AS
	DECLARE @birthYear INT
	DECLARE @animal NCHAR(3)
	SELECT @birthYear = birthYear FROM userTbl WHERE name = @userName;
	SET @animal =
			CASE
				WHEN(@birthYear%12 =0) THEN '원숭이'
				WHEN(@birthYear%12 =1) THEN '닭'
				WHEN(@birthYear%12 =2) THEN '개'
				WHEN(@birthYear%12 =3) THEN '돼지'
				WHEN(@birthYear%12 =4) THEN '쥐'
				WHEN(@birthYear%12 =5) THEN '소'
				WHEN(@birthYear%12 =6) THEN '호랑이'
				WHEN(@birthYear%12 =7) THEN '토끼'
				WHEN(@birthYear%12 =8) THEN '용'
				WHEN(@birthYear%12 =9) THEN '뱀'
				WHEN(@birthYear%12 =10) THEN '말'
				ELSE '양'
			END;
	
	PRINT @userName + '(은)는 ' + @animal+'띠 입니다.';

GO

EXEC case_test '이승기';

 

WHILE

GO
ALTER TABLE userTbl
	ADD userLevel NVARCHAR(5);
GO
CREATE PROCEDURE while_test
AS
	DECLARE userCur CURSOR FOR -- 커서 선언
		SELECT U.userId, sum(price* amount)
		FROM buyTbl B
			RIGHT OUTER JOIN userTbl U
			ON B.userId = U.userId
		GROUP BY U.userId, U.name

	OPEN userCur -- 커서 오픈

	DECLARE @userId NVARCHAR(10) -- 사용자 아이디
	DECLARE @sum BIGINT -- 구매 합
	DECLARE @userLevel NCHAR(5) -- 유저별 등급

	FETCH NEXT FROM userCur INTO @userId, @sum -- 첫 행 값을 대입

	WHILE (@@FETCH_STATUS=0) -- 행이 없을 때까지 반복
	BEGIN
		SET @userLevel =
			CASE 
				WHEN (@sum >=1500) THEN 'VIP'
				WHEN (@sum >= 1000) THEN 'GOLD'
				WHEN (@sum >= 50) THEN 'SILVER'
				ELSE 'BRONZE'
			END
		UPDATE userTbl SET userLevel = @userLevel WHERE userId = @userId
		FETCH NEXT FROM userCur INTO @userId, @sum -- 다음행 값 대입
	END

	CLOSE userCur -- 커서 닫기
	DEALLOCATE userCur -- 커서 해제
GO

EXEC while_test;
SELECT * FROM userTbl;

RETURN

CREATE PROC return_test
	@userName NVARCHAR(10)
AS
	DECLARE @userId char(8);
	SELECT @userId = userId FROM userTbl WHERE name = @userName;
	IF (@userId <>'')
		RETURN 0;
	ELSE
		RETURN -1;
GO

DECLARE @retVal1 INT;
EXEC @retVal1 = return_test '이승기';
SELECT @retVal1;

DECLARE @retVal2 INT;
EXEC @retVal2 = return_test '김연아';
SELECT @retVal2;

 

ERROR 처리 (로직에서 처리)

CREATE PROC error_test
	@userId char(8),
	@name NVARCHAR(10),
	@birthYear INT = 2021,
	@addr NCHAR(2) = '성남',
	@mobile1 char(3) = NULL,
	@mobile2 char(8) = NULL,
	@height smallInt = 170,
	@mDate date = '2021-02-05'
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 = error_test 'KBA', '강뽀야';
if (@errNum != 0)
		SELECT @errNum;

한번 더 수행하면...

DECLARE @errNum INT;
EXEC @errNum = error_test 'KBA', '강뽀야';
if (@errNum != 0)
		SELECT @errNum;

 

ERROR 처리 ( TRY ~ CATCH)

CREATE PROC try_catch_test
	@userId char(8),
	@name NVARCHAR(10),
	@birthYear INT = 2021,
	@addr NCHAR(2) = '성남',
	@mobile1 char(3) = NULL,
	@mobile2 char(8) = NULL,
	@height smallInt = 170,
	@mDate date = '2021-02-05'
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

EXEC try_catch_test 'KJS', '강정성';

한번 더 수행하면...

EXEC try_catch_test 'KJS', '강정성';

 

그외 자주 사용되는 것

 

저장 프로시저 이름 및 내용 조회

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

저장 프로시저의 소스코드 확인

EXECUTE sp_helptext if_else_test;

 

소스코드 암호화

CREATE PROC encrypt_test WITH ENCRYPTION
AS
	SELECT * FROM userTbl;
GO

EXECUTE encrypt_test;

EXECUTE sp_helptext encrypt_test;

 

사용자 정의 데이터 형식의 파라미터 사용

파라미터에 READONLY를 꼭 붙여줘야한다.

CREATE TYPE userTblType AS TABLE
(
	userId char(8),
	name NVARCHAR(10),
	birthYear int,
	addr NCHAR(2)
)
GO

CREATE PROC test_custom_data
	@customTblParam userTblType READONLY
AS
	BEGIN
		SELECT * FROM @customTblParam WHERE birthYear < 1970;
	END
GO

DECLARE @tempVar userTblType;
INSERT INTO @tempVar
		SELECT userId, name, birthYear, addr FROM userTbl;
EXEC test_custom_data @tempVar;

임시 저장 프로시저 만들기

프로시저 명 앞에 #, ## 붙여서 생성한다.

 

끝.

 

ref.

-우재남, 『이것이 SQL Server다』, 한빛미디어(2020)