MSSQL 함수(Function) 생성, 수정, 삭제 설명 및 사용법

MSSQL 시스템에서 지원되는 함수를 시스템 함수도 있지만 사용자가 필요시 함수를 생성, 수정, 삭제할 수 있습니다.

 

MSSQL 함수 생성, 수정, 삭제 설명

• 사용자가 직접 생성하는 함수를 보통 사용자 정의 함수라고 합니다.
• 시스템 함수는 변경할 수 없습니다.
• 사용자 정의 함수는 스칼라함수, 테이블 반환 함수 형태로 생성, 수정, 삭제할 수 있습니다.
• 사용자 정의 함수를 사용하면 반복되는 QUERY 문을 하나의 함수로 만들어 필요시 호출하면 QUERY 문을 간결해지고 가독성이 좋게 만들 수 있습니다.
• 테이블 반환 함수는 DATA 반환 시 테이블 형식으로 반환하는 함수입니다.
• 스칼라함수는 단일 값을 반환할 수입니다.

 

MSSQL 함수 사용법

사용자 정의 함수 흐름도.

함수 생성, 수정, 삭제 설명.

사용자 정의 함수 생성.

• 사용자 정의 함수 생성

--  스칼라 함수 생성.
CREATE FUNCTION dbo.AurumGuide_scalarFunc (@PARAM1 varchar(10), @PARAM2 varchar(10))
RETURNS varchar(100)
AS
BEGIN
    declare @returnStr varchar(100);
    set @returnStr  = 'Hello World ' + @PARAM1 + @PARAM2;
    RETURN @returnStr
END;
go
-- EXCUTE 
select dbo.AurumGuide_scalarFunc('Aurum','Guide');

 

• 테이블 반환 함수 생성.

-- CREATE TABLE
USE sampleDB;
DROP TABLE IF EXISTS AurumGuideForFunc;
CREATE TABLE AurumGuideForFunc (
    UserId int,
    UserNm varchar(255) NOT NULL    
);
INSERT INTO dbo.AurumGuideForFunc(UserId,UserNm ) 
VALUES (272, N'Ken' )	 
      ,(274, N'Stephen' )
      ,(275, N'Michael' )
      ,(276, N'Linda' )
      ,(277, N'Lee' ) 
   ;
DROP TABLE IF EXISTS AurumGuideForFuncJoin;
CREATE TABLE AurumGuideForFuncJoin (
    UserId int,    
    UserAge  int     NULL,
	DeptId varchar(500)   NULL
); 
INSERT INTO dbo.AurumGuideForFuncJoin(UserId,UserAge,DeptId) 
VALUES (272 ,10,100)	 
      ,(274 ,20,100)
      ,(275 ,30,200)
      ,(276 ,40,200)
      ,(277 ,40,100) 
; 
 --   테이블 반환 함수
CREATE FUNCTION fn_AurumGuide_ReturnUserFunc (@DeptId INT)
RETURNS @Return_AurumGuide  TABLE
(
	UserId int,
	UserNm varchar(255)    NULL,
	UserAge  int           NULL,
	DeptId varchar(500)    NULL
)
AS
BEGIN
	INSERT @Return_AurumGuide
	SELECT af1.UserId,af1.UserNm,af2.UserAge,af2.DeptId
	FROM AurumGuideForFunc af1
	join AurumGuideForFuncJoin af2
	on af1.UserId = af2.UserId
	WHERE af2.DeptId = @DeptId;
	RETURN;
END;

go
-- EXCUTE
select * 
from fn_AurumGuide_ReturnUserFunc(100);

사용자 정의 함수 수정.

• 함수 수정 예제.

--  MSSQL 함수 수정
GO
ALTER FUNCTION dbo.AurumGuide_scalarFunc (
@PARAM1 varchar(10),
@PARAM2 varchar(10),
@PARAM3 varchar(10)
)
RETURNS varchar(100)
AS
BEGIN
	declare @returnStr varchar(100);
	set @returnStr  = 'Hello World ' + @PARAM1 + @PARAM2 + ' ' + @PARAM3;
	RETURN @returnStr
END;
go
-- EXCUTE 
select dbo.AurumGuide_scalarFunc('Aurum','Guide','Good');

사용자 정의 함수 삭제.

• 함수 삭제 예제.

-- MSSQL 함수 삭제
DROP FUNCTION dbo.AurumGuide_scalarFunc;
-- CHECK
select dbo.AurumGuide_scalarFunc('Aurum','Guide','Good');

 

MSSQL 함수 언제 사용하는가?

• 반복되는 SQL 문이 있는 경우 Function으로 만들 수 있는지 고민할 수 있습니다.
• 함수를 생성할 때 스칼라함수로 만들 수 있으면 좋습니다.
• 함수 내부에서 data를 select 해서 반환하면 성능상 문제가 될 수 있습니다.