[MSSQL] 사용자 정의 함수, 테이블 반환 함수, 스칼라 함수

[MSSQL] 사용자 정의 함수, 테이블 반환 함수, 스칼라 함수

 

도움이 되셨다면 광고 한 번 클릭 부탁드립니다. 한 번의 클릭이 제게 큰 힘이 된답니다!^^

반응형

 

사용자 정의 함수

- 사용자가 직접 함수를 정의한 함수

- 일반적인 프로그래밍 언어에서 사용되는 함수같이 복잡한 프로그래밍이 가능하다.

- return문을 사용하여 특정한 값을 반환할 수 있다.

- 저장 프로시저는 'EXECUTE' or 'EXEC'에 키워드로 실행되지만, 함수는 주로 'SELECT' 문에 포함되어 실행(호출)된다.

  (스칼라 함수의 경우 저장 프로시저처럼 EXECUTE로 실행할 수 있다.)

 

 

사용자 정의 함수

사용자 정의 함수는 다음과 같이 정의한다. 

CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type
	   	[ = default ] [ READONLY ] }
        [ , ...n ]
    ]
)
RETURNS return_data_type
	[ WITH <finction_option> [ , ...n ] ]
    [ AS ]
    BEGIN
    	function_body
        
        RETURN scalar_expression
    END

 

 

사용자 정의 함수 예제

 

1. 출생년도를 입력하면 나이가 출력되는 함수를 생성해 보자.

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

※ 설명 ※

CREATE FUNTION fn_getAge_test(@byear INT)

└▶ 함수를 fn_getAge_test명으로 하고, 매개 변수를 1개, 매개 변수 이름은 @byear 자료형은 INT로 한다.

RETURNS INT

└▶ 반환 타입은 INT이다. 함수에서 return할 때 INT를 반환한다.

AS BEGIN ~ END

└▶함수가 정의되는 시작점, 끝점

DECLARE @AGE INT

└▶INT 타입의 @AGE라는 변수 이름을 선언한다.

SET @AGE = YEAR(GETDATE()) - @byear

└▶ GETDATE() 현재날짜를 구하는 함수를 사용해서, YEAR() 함수를 사용해 년도를 구하고, @byear 에 입력된 매개 변수 값을 빼주어 @AGE 변수에 값을 세팅한다.

RETURN(@AGE)

└▶ @AGE 값을 반환한다.

 

현재의 연도(YEAR(GETDATE()) 에서 입력된 출생년도를 뺀 값(즉, 나이)을 돌려주는 함수다.

 

 

 

2. 함수를 호출해 보자.

 

1) 함수를 정의할 때 스키마 이름을 지정하지 않았으므로 디폴트 스키마인 dbo를 붙여줘야 한다.

SELECT dbo.fn_getAge_test(1991);

 

2) EXECUTE 문을 사용해서 저장 프로시저를 실행하듯이 사용할 수도 있다. 하지만 좀 불편하다.

DECLARE @retVal INT;
EXEC @retVal = dbo.fn_getAge_test 1991;
PRINT @retVal;

 

3. 함수를 수정할 땐 CREATE 키워드 대신 ALTER 키워드를 사용한다.

 

우리나라 나이로 계산되도록 수정한다.

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

실행

 

 

4. 함수 삭제 키워드 DROP

함수를 삭제한다.

DROP FUNCTION fn_getAge_test;

 

 

함수의 종류

1. 사용자 정의 스칼라 함수 : 사용자 정의 스칼라 함수 RETURN 문에 의해서 하나의 단일값을 돌려주는 함수

└▶ 지금까지 위에서 살펴본 함수

 

2. 사용자 정의 테이블 반환 함수 : 그냥 '테이블 함수'라고도 한다. 반환하는 값이 하나가 아닌 '테이블'인 함수

테이블 함수에는 2가지 종류가 있다.

 - 인 라인 테이블 반환 함수(Inline Table-valued Function)

 - 다중 문 테이블 반환 함수(Multistatement Table-valued Function)

 

1) 인 라인 테이블 반환 함수

간단히 테이블을 돌려주는 함수이며, 매개 변수가 있는 뷰와 비슷한 역할을 한다. 인 라인 테이블 반환 함수에는 별도의 내용이 없으며 단지 SELECT 문만 사용되어 그 결과 집합을 리턴할 뿐이다.

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

예시)

인 라인 테이블 반환 함수의 예시이다. 다음과 같은 테이블이 있다.

 

 

함수의 매개 변수에 입력하는 숫자 보다 큰 사람들의 정보를 띄우는 함수를 만든다.

 

함수 생성

CREATE FUNCTION fn_getUser(@ht INT)
RETURNS TABLE
AS
RETURN (
    SELECT id AS 아이디, name AS 이름, height AS 키
    FROM user_t_tb
    WHERE height > @ht
)
GO

함수 호출

 

키가 175 보다 크던 이승기, 강호동의 데이터 행만 검색한 것을 볼 수 있다.

 

 

 

2) 다중 문 테이블 반환 함수

다중 문 테이블 반환 함수의 내용은 BEGIN - END 로 정의되며, 그 내부에 일련의 T-SQL을 이용해서 반환될 테이블에 행 값을 INSERT 하는 형식을 가진다.

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

 

 

다중 문 테이블 반환 함수는 간단한 예시로 보자.

 

ALTER FUNCTION fn_getUser()
RETURNS @TABLE TABLE
( 
	NAME VARCHAR(10),
	AGE INT
)
AS
BEGIN
	INSERT INTO @TABLE VALUES ('박지성' , 35)
	INSERT INTO @TABLE VALUES ('이영표' , 40)
	INSERT INTO @TABLE VALUES ('안정환' , 40)
	INSERT INTO @TABLE VALUES ('이천수' , 34)
	INSERT INTO @TABLE VALUES ('호날두' , 37)

	RETURN
END
GO

select * from dbo.fn_getUser()

 

반환하려고 하는 테이블에(@TABLE) 값을 넣어서(INSERT 문...) 리턴한다고 보면 된다.

 

결과를 보자.

 

빠른 이해를 돕기 위해 간단한 예제로 나타냈다.

---

도움이 되셨다면 광고 한 번 클릭 부탁드립니다. 한 번의 클릭이 제게 큰 힘이 된답니다!^^