[SQL Server] 저장 프로시저

저장 프로시저

 

쿼리문의 집합, 어떤 동작을 일괄 처리할 때 사용된다.

-- =============================================
-- Author:		KYH
-- Create date: 2020.06.11. 15:35
-- Description:	유저 테이블을 조회하는 사용자 저장 프로시저(SP)
-- =============================================
CREATE OR ALTER PROCEDURE usp_User

AS
BEGIN
	SELECT * FROM userTbl;
END
GO

EXEC usp_user;

BBK     	바비킴	1973	서울	010	0000000 	176	2013-05-05
EJW     	은지원	1972	경북	011	8888888 	174	2014-03-03
JKW     	조관우	1965	경기	018	9999999 	172	2010-10-10
JYP     	조용필	1950	경기	011	4444444 	166	2009-04-04
KBS     	김범수	1979	경남	011	2222222 	173	2012-04-04
KKH     	김경호	1971	전남	019	3333333 	177	2007-07-07
LJB     	임재범	1963	서울	016	6666666 	182	2009-09-09
LSG     	이승기	1987	서울	011	1111111 	182	2008-08-08
SSK     	성시경	1979	서울	NULL	NULL	186	2013-12-12
YJS     	윤종신	1969	경남	NULL	NULL	170	2005-05-05

 

 

USE [sqlDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_User]    Script Date: 2020-06-11 오후 4:06:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		KYH
-- Create date: 2020.06.11. 15:35
-- Description:	유저 테이블을 조회하는 사용자 저장 프로시저(SP)
-- =============================================
ALTER PROCEDURE [dbo].[usp_User]
	@userID VARCHAR(8) = 'SSK', -- 디폴트값
	@userHeight INT = 186,
	@outValue INT OUTPUT
AS
BEGIN
	SELECT @outValue = birthYear FROM userTbl 
	WHERE userID = @userID
	  AND height >= @userHeight;
END

DECLARE @curBirth INT;
EXEC usp_user 'BBK',170, @curBirth OUTPUT;
PRINT '현재 사용자의 출생년도는 ==>' + CAST(@curBirth AS CHAR(4));

 

 

CREATE OR ALTER PROC usp_ages
	@userName NVARCHAR(10)
AS
BEGIN
	DECLARE @mYear INT; -- 출생년도 저장 변수
	SELECT @mYear = birthYear FROM userTbl
	 WHERE name = @userName;

	 IF(@mYear >= 1980)
	 BEGIN
		SELECT '아직젊군여';
	 END
	 ELSE
	 BEGIN
		SELECT '다 됐습니다';
	 END
END
GO

EXEC usp_ages '성시경';

 

 

단점

 

비즈니스 로직(BL)을 C#에서도 건드려야하고 DB에서도 건드려야 되서

일이 더욱 많아짐. 그래서 최근엔 저장 프로시저를 거의 안쓰는 추세

 

CREATE OR ALTER PROCEDURE usp_Case
	@userName NVARCHAR(10)
AS

	DECLARE @Year INT
	DECLARE @Zodiac NCHAR(3)
	 SELECT @Year = birthYear FROM userTbl
	  WHERE name = @userName;
	SET @Zodiac = 
			CASE
				WHEN (@Year%12 = 0) THEN '원숭이'
				WHEN (@Year%12 = 1) THEN '닭'
				WHEN (@Year%12 = 2) THEN '개'
				WHEN (@Year%12 = 3) THEN '돼지'
				WHEN (@Year%12 = 4) THEN '쥐'
				WHEN (@Year%12 = 5) THEN '소'
				WHEN (@Year%12 = 6) THEN '호랑이'
				WHEN (@Year%12 = 7) THEN '토끼'
				WHEN (@Year%12 = 8) THEN '용'
				WHEN (@Year%12 = 9) THEN '뱀'
				WHEN (@Year%12 = 10) THEN '말'
				ELSE '양'
			END;
	PRINT @userName +'의 띠 ==> ' +@Zodiac;
GO

EXEC usp_Case '성시경';

 

장점

 

SQL Server의 성능을 향상시킬 수 있다.

 

유저관리가 간편하다.

 

모듈식 프로그래밍이 가능하다.

 

보안을 강화할 수 있다.

 

스칼라 함수

RETURN문에 의해 하나의 단일값을 돌려주는 함수

 

사용자 정의 함수

SELECT 문에 포함되어서 실행된다.

CREATE FUNCTION ~

RETURN

 

CREATE or ALTER FUNCTION ufn_getAge
(
	@bYear	INT
)
RETURNS INT
AS
BEGIN
	
	DECLARE @age INT

	SET @age = YEAR(GETDATE()) - @bYear;

	RETURN @age;

END
GO

SELECT name, birthYear, 
       dbo.ufn_getAge(birthYear) +1 AS [나이]
  FROM userTBL

바비킴	1973	48
은지원	1972	49
조관우	1965	56
조용필	1950	71
김범수	1979	42
김경호	1971	50
임재범	1963	58
이승기	1987	34
성시경	1979	42
윤종신	1969	52

 

사용자 정의 함수를 쓸려면 스키마 dbo를 붙여야함

 

제약사항

사용자 정의 함수 내부에 TRY ... CATCH 문을 사용할 수 없다.

사용자 정의 함수 내부에 CREATE ALTER DRAOP 문을 사용할 수 없다

오류가 발생하면 즉시 실행이 멈추고 값을 반환하지 않는다.

 

 

 

인라인 테이블 반환 함수

CREATE OR ALTER FUNCTION ufn_getUser
(	
	@height INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT userID AS'아이디',
		   name AS '이름',
		   height AS '키'
	  FROM userTBL
	 WHERE height >= @height
)
GO

SELECT * FROM dbo.ufn_getUser(180);

LJB     	임재범	182
LSG     	이승기	182
SSK     	성시경	186