[MSSQL] 사용자 정의 함수의 종류

사용자 정의 함수의 종류

- 사용자 정의 스칼라 함수 : RETURN 문에 의해서 하나의 단일값을 반환하는 함수

- 사용자 정의 테이블 반환 함수 : RETURN 값이 테이블인 함수

사용자 정의 스칼라 함수

- 예시 : 출생년도를 입력하면 나이를 반환하는 함수

CREATE FUNCTION ufn_getAge(@byear INT)    -- 매개 변수를 정수로 받음
    RETURNS INT    -- 리턴값은 정수형
AS
    BEGIN
        DECLARE @age INT
        SET @age = YEAR(GETDATE()) - @byear + 1
        RETURN(@age)
    END
GO

사용자 정의 테이블 반환 함수

- 인라인 테이블 반환 함수

(형식)

CREATE FUNCTION 함수이름 (매개 변수)
    RETURNS TABLE
AS
    RETURN(단일 SELECT 문장)

- 예시 : 입력한 값보다 키가 큰 사용자들을 돌려주는 함수

-- 함수 생성
CREATE FUNCTION ufn_getUser(@ht INT)
    RETURNS TABLE
AS
    RETURN(SELECT userID AS [아이디], name AS [이름], height AS [키] FROM userTbl WHERE height > @ht)
GO
 
-- 함수 호출
SELECT * FROM dbo.ufn_getUser(177);

- 결과

- 다중 문 테이블 반환 함수

(형식)

CREATE FUNCTION 함수이름 ( 매개 변수 )
    RETURNS @테이블변수 TABLE
        ( 열이름과 데이터 형식 정의... )
AS
    BEGIN
        위(헤더)에서 정의한 테이블변수에 행을 INSERT시키는 작업들...
        RETURN
    END

- 예시 : 정수값을 매개 변수로 받고 출생년도가 매개 변수 이후인 고객들만 등급을 분류하며, 행이 하나도 없다면 '없음'으로 출력하는 함수

CREATE FUNCTION ufn_userGrade(@bYear INT)
    -- 리턴할 테이블의 정의(@retTable은 BEGIN ... END에서 사용될 테이블 변수)
    RETURNS @retTable TABLE
    (userID char(8),
    name nchar(10),
    grade nchar(5))
AS
    BEGIN
        DECLARE @rowCnt INT;
        -- 행 개수를 카운트
        SELECT @rowCnt = COUNT(*) FROM userTbl WHERE birthYear >= @bYear;
 
        -- 행이 하나도 없으면 '없음'이라고 입력하고 테이블을 리턴함
        IF (@rowCnt <= 0)
        BEGIN
            INSERT INTO @retTable VALUES('없음','없음','없음');
            RETURN;
        END;
 
        -- 행이 1개 이상 있다면
        INSERT INTO @retTable
            SELECT U.userID,U.name,
                CASE
                    WHEN (sum(price*amount) >= 1500) THEN N'최우수고객'
                    WHEN (sum(price*amount) >= 1000) THEN N'우수고객'
                    WHEN (sum(price*amount) >= 1) THEN N'일반고객'
                    ELSE N'유령고객'
                END
            FROM buyTbl AS B
            RIGHT OUTER JOIN userTbl U
            ON B.userID = U.userID
            WHERE birthYear >= @bYear
            GROUP BY U.userID, U.name;
        RETURN;
    END;
GO

- 호출 1

SELECT * FROM dbo.ufn_userGrade(1970);

- 결과

- 호출 2

SELECT * FROM dbo.ufn_userGrade(1990);

- 결과

---

참고문헌

우재남, 『뇌를 자극하는 SQL Server 2012』 한빛미디어 (2013-03-02 초판발행)