MSSQL 함수(Function) 정의 및 설명

MSSQL 함수는 매개변수를 받아서 함수 내에서 데이터 검색, 수정, 삭제 및 연산 과정을 수행 후 결과를 반환받을 때 사용합니다.

 

MSSQL 함수 종류 설명

시스템 함수 종류 및 설명.

• SQL Server에서 지원되는 함수를 시스템 정의 함수라고 합니다.
• 문자열 함수는 문자열 타입의 Data를 변경하는
• 데 CHARINDEX(), REPLACE(), LTRIM(), RTRIM(), LEN(), LEFT(), RIGHT(), LOWER() 등이 있습니다.
• 집계 함수는 주로 Data의 연산을 수행하는 COUNT(), MAX(), MIN(), SUM() 등이 있습니다.
• 날짜함수 및 시간 함수는 날짜와 시간의 Data를 변경할 때 사용합니다.
• 함수의 자세한 내용은 MSSQL 날짜함수, 시간 할 수 사용법 정리 및 설명을 참고하세요.
• https://aurumguide.tistory.com/28
• 이외에도 SQL Server에서는 커서 함수, 그래프 함수, JSON 함수, 수학함수 등을 지원하고 있습니다.

사용자 정의 함수.

• 사용자 정의 함수는 Data를 반환하는 형태에 따라서 스칼라함수(Scalar Function)와 테이블 반환 함수(Table-Valued Function)로 분류합니다.
• 스칼라함수 단일 스칼라값을 반환하는 함수입니다.
• 예를 들면 문자열 열의 길이를 반환하는 LEN() 함수, 문자열을 변경할 수 있는 REPLACE() 함수가 대표적입니다.
• 테이블 반환 함수는 데이터 형식을 반환하는데 인라인 테이블 반환 함수, 다중 문장 테이블 반환 함수로 분류합니다.
• Data를 테이블 형태로 반환할 때 사용하는데, 아래 예제를 참고하시면 싶게 이해할 수가 있습니다.

 

MSSQL 함수 사용법 및 예제

시스템 함수 사용법.

• 시스템 함수 사용예제.

DROP TABLE IF EXISTS AurumGuideForFunction;
CREATE TABLE AurumGuideForFunction (
    UserId int,
    UserNm varchar(255) NOT NULL,
    UserAge  int     NULL,
    DeptId varchar(500)   NULL
);
 

INSERT INTO dbo.AurumGuideForFunction(UserId,UserNm,UserAge,DeptId) 
VALUES (272, N'Ken',10,100)	 
      ,(274, N'Stephen',20,100)
      ,(275, N'Michael',30,200)
      ,(276, N'Linda',40,200)
      ,(277, N'Lee',40,100);
 
-- 시스템 함수 사용법.
--문자열함수
SELECT LEN('AurumGuide') as lenFunc
       ,LOWER('AurumGuide')  as lowerFunc 
;
-- 집계함수
SELECT SUM(UserAge) as sumFunc
      ,MIN(UserAge)  as minFunc
      ,MAX(UserAge)  as maxFunc
  FROM dbo.AurumGuideForFunction
;

-- 로그인 함수(유저의 마지막 패스워드 변경 시간.)
SELECT LOGINPROPERTY('sa', 'PasswordLastSetTime');

-- 시스템 테이블 반환함수(권한 확인)
SELECT * 
FROM sys.fn_my_permissions(NULL, 'DATABASE');

 

사용자 정의 스칼라함수.

• 스칼라 함수 사용예제.

-- 사용자 정의 스칼라함수 
CREATE FUNCTION dbo.AurumGuide_scalarFunction (@PARAM1 INT, @PARAM2 INT)
RETURNS INT
AS
BEGIN
    RETURN @PARAM1 + @PARAM2
END;
go
-- EXCUTE 
select dbo.AurumGuide_scalarFunction(10,20);

 

인라인 테이블 반환 함수.

• 인라인 테이블 반환 사용예제.

-- 인라인 테이블 반환
CREATE FUNCTION fn_AurumGuide_InlineTableFunction (@DeptId INT)
RETURNS TABLE
as 
RETURN 
(
	 
	SELECT UserId,UserNm,UserAge,DeptId
	FROM
		AurumGuideForFunction t1
	WHERE
		t1.DeptId = @DeptId
)	 
;
go
-- EXCUTE
select * 
from fn_AurumGuide_InlineTableFunction(100);

 

다중 문장 테이블 반환 함수.

• 다중문장 테이블 반환 소스 상세 설명.

테이블 반환 함수 소스 설명 입니다.

• 다중문장 테이블 반환 사용예제.

-- 다중 문장 테이블 반환 함수
CREATE FUNCTION fn_AurumGuide_TableFunction (@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 UserId,UserNm,UserAge,DeptId
	FROM AurumGuideForFunction t1
	WHERE t1.DeptId = @DeptId;

	RETURN;
END;

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

 

MSSQL 함수 주의사항

• 사용자 정의 함수를, 소스를 간결하게 사용할 수 있는 장점이 있습니다. 그러나 상황에 맞지 않게 사용하면 성능상 문제를 발생시킬 수 있습니다.
• 대표적으로 테이블 반환할 수를 사용할 때는 반환되는 DATA를 확인하고 사용하기를 추천해 드립니다.
• 많은 데이터를 반복적으로 반환하게 되면 시스템 장애의 원인이 됩니다.