[MSSQL] 사용자 정의 함수 (ft. 스칼라 함수, 테이블 함수)

728x90

안녕하세요. 오늘은 라이프사이클 재무에 대한 모든 것_글라이드 유튜브 강의를 듣고 참고하여 MSSQL의 사용자 정의 함수에 대해 포스팅을 작성하도록 하겠습니다.

함수(Function)

SQL 내장함수

MSSQL에서 기본적으로 제공하는 함수들로 데이터 집계, 변환, 계산 등을 쉽게 수행할 수 있게 해줌
SUM(), AVG(), MAX(), MIN(), COUNT(), LEN(), GETDATE() 등의 함수가 있음

사용자 정의 함수(User-Defined Function)

사용자가 직접 정의하여 특정 작업을 수행하는 함수이며, 반복적으로 사용되는 복잡한 로직을 함수로 정의하여 코드의 재사용성을 높일 수 있음
사용자 정의 함수 종류에는 스칼라 함수(Scalar Function)와 테이블 함수(Table-Valued Function)가 있음

사용자 정의 함수

스칼라 함수

하나 이상의 인자를 받아 SUM이나 AVG처럼 단일 값을 반환하는 함수
예를 들면, 두 숫자를 더하거나 특정 조건에 따라 결과를 반환할 때 사용

스칼라 함수 문법

CREATE FUNCTION 함수이름 (@매개변수1 데이터타입, 매개변수2 테이터타입, ...)
RETURNS 반환데이터타입
AS
BEGIN
    -- 함수 로직 수행
    DECLARE @결과값 반환데이터타입;
    
    -- ex: 간단한 계산
    SET @결과값 = <SQL 표현식 또는 계산>;
    
    -- 결과값 반환
    RETURN @결과값;
END

예시1. F_plus 이름의 덧셈 함수 만들기

CREATE FUNCTION F_plus(@value1 INT, @value2 INT) -- 숫자 형태의 데이터를 가짐
RETURNS INT -- 반환 타입 INT
AS
BEGIN
    DECLARE @sum_value INT;	            -- 선언
    SET @sum_value = (@value1 + @value2);   -- 할당
    RETURN @sum_value;
END;
GO

-- F_plus 함수 이용
SELECT DBO.F_plus(10, 20);

예시2

-- F_return 함수 만들기
-- - 시작일, 종료일, 기업코드 입력 시 해당 날짜 사이의 수익률을 구해주는 함수
-- - @ 뒤에 오는 매개변수는 총 3개
-- 1) 시작일의 startdate
-- 2) 종료일의 enddate
-- 3) 기업코드를 의미하는 ID
-- - 각 뒤에는 데이터 형태를 나타내는 DATE, INT 등이 있음
CREATE FUNCTION F_return(
    @startdate DATE,
    @enddate DATE,
    @id INT
)
RETURNS DECIMAL(12, 10) -- 함수의 결과값을 소수점 이하 10번째 자리까지 보이는 설정
AS
BEGIN
    DECLARE @startprice NUMERIC -- startprice 변수를 숫자형 데이터로 선언한다는 뜻
    DECLARE @endprice NUMERIC

    SET @startdate = (SELECT MIN(DATE_) FROM Stockprice WHERE ID = @id AND DATE_ >= @startdate)
    -- @startdate가 휴일인 경우 가격이 없으므로 가장 가까운 다음 거래일을 찾기 위한 코드
    SET @enddate = (SELECT MAX(DATE_) FROM Stockprice WHERE ID = @id AND DATE_ <= @enddate)
    SET @startprice = (SELECT CLOSE_ FROM Stockprice WHERE ID = @id AND DATE_ = @startdate)
    -- CLOSE_ 가격을 가져와서 수익률을 계산
    SET @endprice = (SELECT CLOSE_ FROM Stockprice WHERE ID = @id AND DATE_ = @enddate)

    RETURN (@endprice / @startprice - 1.0)
END;
GO

-- F_return 함수에 Companyinfo에 존재하는 모든 종목 적용하기
SELECT C.*
, DBO.F_return('2020-10-01', '2020-10-12', ID) AS ret
FROM Companyinfo C;

테이블 함수

테이블 형식의 데이터를 반환하는 함수
주로 복잡한 쿼리 결과를 함수로 정의하여 쉽게 재사용하고자 할 때 유용
테이블 함수 종류에는 인라인 테이블 함수(Inline Table-Valued Function)와 다중문 테이블 함수(Multi-Statement Table-Valued Function)가 있음

① 인라인 테이블 함수

하나의 'SELECT' 문을 사용하여 결과 테이블을 반환하는 함수이며 매우 간단하고 성능이 좋으며 뷰(View)와 유사하게 동작함
별도의 테이블 변수를 선언하거나 데이터를 삽입하는 과정이 없음

인라인 테이블 함수 문법

CREATE FUNCTION 함수이름 (@매개변수1 데이터타입, @매개변수2 데이터타입, ...)
RETURNS TABLE
AS
RETURN
(
    SELECT 컬럼1, 컬럼2, ...
    FROM 테이블이름
    WHERE 조건
);

예시

CREATE FUNCTION F_info (
    @id INT
)
RETURNS TABLE -- 테이블을 반환하는 함수로 명명
AS
RETURN
(
    SELECT R.FIN_NAME
         , R.FIN_VAL
    	 , R.FDATE
    	 , R.FPRD
    	 , A.NAME
    FROM Companyinfo A	    -- companyinfo A로부터 IDMap I와 Ratios R을 차례대로 조인
    JOIN IDMap I            -- - 먼저 A와 I를 조인하되, ID를 기준으로 조인
        ON A.ID = I.ID          -- -> A와 I의 공통 key는 ID
    JOIN Ratios R           -- 그 후 I와 R을 조인, FIN_ID를 기준으로 함
        ON I.FIN_ID = R.FIN_ID  -- -> I와 R의 공통 key는 FIN_ID
    WHERE A.ID = @id        -- A.ID에 대한 필드가 존재하는 데이터만 추출하라는 뜻
)

GO
SELECT * FROM DBO.F_info(40853);

② 다중문 테이블 함수

여러 개의 SQL 문을 사용하여 테이블을 구성하고 반환할 수 있는 함수이며 인라인 테이블 함수보다 복잡한 로직을 처리할 때 유용함
테이블 변수를 선언하고 함수 내에서 이 테이블 변수에 데이터를 INSERT 함
인라인 테이블 함수에 비해 성능이 상대적으로 떨어질 수 있음

다중문 테이블 함수 문법

CREATE FUNCTION 함수이름 (@매개변수1 데이터타입, @매개변수2 데이터타입, ...)
RETURNS @반환테이블 TABLE (컬럼1 데이터타입, 컬럼2 데이터타입, ...)
AS
BEGIN
    -- 여러 SQL 문을 통해 테이블 구성
    INSERT INTO @반환테이블
    SELECT 컬럼1, 컬럼2, ...
    FROM 테이블이름
    WHERE 조건;
    
    -- 추가적인 SQL 문이 필요하면 작성
    ...
    
    RETURN;
END

예시

CREATE FUNCTION F_info2 (
    @id INT
)
RETURNS @return_table TABLE
(
    FIN_NAME VARCHAR(300)
  , FIN_VAL FLOAT
  , FDATE DATE
  , FPRD INT
  , NAME NVARCHAR(3000)
)
AS
BEGIN
    INSERT INTO @return_table -- SELECT 문에 쓴 다수의 데이터를 INSERT 하라는 뜻
    SELECT R.FIN_NAME
         , R.FIN_VAL
         , R.FDATE
         , R.FPRD
         , A.NAME
    FROM Companyinfo A
    JOIN IDMap I
        ON A.ID = I.ID
    JOIN Ratios R
        ON I.FIN_ID = R.FIN_ID
    WHERE A.ID = @id
    RETURN
END;
GO

SELECT * FROM DBO.F_info2(40853);

✅ 인라인 테이블 함수와 다중 테이블 함수의 차이

다중문 테이블 함수는 데이터를 담을 임시 테이블(테이블 변수)을 만들어서 데이터를 'INSERT'하고, 그 테이블을 최종적으로 반환하는 방식이며, 반면 인라인 테이블 함수는 임시 테이블 없이 단일 'SELECT' 문으로 결과를 바로 반환함

그 외 문법

삭제 문법

DROP FUNCTION [스키마 이름].[함수 이름];

예시

DROP FUNCTION F_info;
DROP FUNCTION F_info2;

수정 문법

ALTER FUNCTION [스키마 이름].[함수 이름]
(
    -- 매개변수 목록
    @매개변수1 데이터타입,
    @매개변수2 데이터타입,
    ...
)
RETURNS 반환타입
AS
BEGIN
    -- 함수 본문
    ...
    RETURN 반환값;
END;