MS SQL_사용자 정의 함수 사용법

1. 사용자 정의 함수

개발자가 직접 정의하여 사용할 수 있는 함수 

2 사용자 정의 함수 종류

- 스칼라반환함수: RETURN 문에서 하나의 단일값을 반환하는 함수
- 테이블반환함수: RETURN 값이 반환값이 테이블인 함수 (인라인 / 다중문)

3. 사용자 정의 함수 생성

- 스칼라반환함수 사용법

CREATE FUNCTION 함수명(
@매개변수1  타입,
@매개변수 2 타입,
,,,
)
RETURNS 타입
AS
BEGIN 
	RETURN
END

- 테이블반환함수사용법

•  인라인 테이블 반환함수:  단일 SELECT 문장

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

•   다중문 테이블 반환함수:  행을 INSERT 시키는 문장

CREATE FUNCTION 함수이름(매개변수)
    RETURNS @테이블변수 TABLE
    (컬럼명 타입 정의,
    ,,,
    )
AS
BEGIN
    INSERT 작업문
    RETURN
END

다중문 테이블 반환함수 예제 - 문자열 구분자로 구분하여 INSERT

CREATE FUNCTION [dbo].[FNC_SPLIT]( 
	@STRING   NVARCHAR(MAX), -- 문자열
	@SEPARATOR  NVARCHAR(10) -- 문자열 구분자
)
RETURNS @RTN 
	TABLE ( NUM INT, VAL NVARCHAR(MAX) )
AS 
BEGIN
	DECLARE  @START INT = 1, @IDX  INT = 1, @NUM INT = 0, @TMP NVARCHAR(MAX)
	WHILE(@IDX > 0) 
		BEGIN 
			SET @IDX = CHARINDEX(@SEPARATOR, @STRING, @START) 
			IF @IDX = 0 
				BEGIN
					SET @TMP = RIGHT(@STRING, LEN(@STRING) - @START + 1)
				END 
			ELSE 
				BEGIN
					SET @TMP = SUBSTRING(@STRING, @START, @IDX - @START)
				END
			IF LEN(@TMP) > 0 
				BEGIN
					INSERT INTO @RTN
					VALUES ( @NUM, RTRIM(LTRIM(@TMP)) )
				END
			SET @START = @IDX + 1
			SET @NUM = @NUM + 1
		END
	RETURN 
END

• CHARINDEX(): 문자열에서 지정학 식의 시작 위치를 반환
• CHARINDEX('찾을문자열A','지정문자열B',검색 시작할 위치 INDEX)
• LEN() : 문자열 길이
• LTRIM()  / RTRIM(): 문자열의 왼쪽 / 오른쪽에 있는 공백 제거

함수 실행 결과

-- 함수 실행 결과를 담을 테이블
CREATE TABLE Alphabet(
    RESULT    VARCHAR(5),
    INS_DATE  DATE
)

--실행예시
DECLARE @ALPHABET  VARCHAR(200)
SET @ALPHABET = 'A|B|C|D' 

INSERT INTO Alphabet(RESULT, INS_DATE)
SELECT VAL, GETDATE() FROM DBO.FNC_SPLIT (@ALPHABET, '|')

 

4. 사용자 정의 함수 수정

ALTER FUNCTION 함수명

5. 사용자 정의 함수 삭제

 

DROP FUNCTION [함수명]

6. 사용자 정의 함수 실행

SELECT 함수명('매개변수')