MS-SQL 함수란? (스칼라, 테이블 반환 함수 등..)

시스템 즉 DBMS 가 제공하는 시스템 함수 말고

개발자들이 만들 수 있는 사용자 정의 함수를 살펴 보자

 

사용자 정의 함수는 2가지 유형 - 테이블 반환 함수,스칼라 반환 함수

 

 

1. 스칼라 함수

 

단일 데이터를 리턴하는 함수 입니다. 가장 많이 사용되고 있죠. 개인적으로는 이 유형만 씁니다.

시스템 함수들 대부분도 스칼라 함수입니다. ( AVG(),SUM() 등 )

CREATE FUNCTION 함수이름
      ( 파라미터 데이터타입, 파라미터 데이터타입,… ) 
RETURNS 리턴값의 데이터타입
[WITH 함수_옵션 ] 
[AS] 
BEGIN
    FUNCTION BODY
    RETURN 단일한 리턴값
END

 

리턴값의 데이터 타입 중 text,ntext,timestamp 등은 해당되지 않습니다.

컴파일 때 에러 나옵니다. ( 생성 때는 빨간 줄이 안그어 지네요  ^^ )

 

뭐 앞에 포스팅된 사용자 함수들이 다 스칼라 함수입니다.

 

예를 들어 보겠습니다. 아주 간단한 걸로 ^^

생일을 년월일(YYYYMMDD) 을 입력 받아 오늘 이후에 몇일이 지났나 보는 간단한 함수를 보겠습니다.

( 뭐 "데이터 검증이 없네요" 라고 하던가 "하루 +,- 해야 하네" 이런 테클은 안받겠습니다.. 걍 예제입니다. )

 

생성

--------------------------------------------------------------------------

CREATE FUNCTION fn_GetDayFromBirthDate

(

@iBirthDate  VARCHAR(8)

) 
RETURNS INT

AS
BEGIN
     RETURN DATEDIFF(D,@iBirthDate,GETDATE())

END

 

컴파일 하시고 걍 호출해서 쓰시면 됩니다.

호출은 DB명.스키마명,함수(파라미터값) 으로 하면 됩니다. ( 이전 2000 에는 스키마명 대신 소유자명이였죠 )

 

 

2. 테이블 반환 함수

 

테이블 반환 함수는 말 그대로 리턴값이 테이블 입니다. ( 스칼라 함수처럼 하나의 값이 아닌 테이블이죠 )

리턴되는 테이블에 따라

"인 라인 테이블 반환 함수(Inline Table valued Function)" 과 "다중 문 데이블 반환 함수(Multistatement Table valued Function)"으로 나눌수 있습니다.

 

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

 

개인적으로 거의 쓴적이 없습니다. 만드실 때 BEGIN  END 를 빼셔야 합니다.

제가 습관적으로 만들고 (BEGIN, END 로 구문을 포함시켰죠) 한참 이유를 몰라서 헤멘...쩝..

 

SELECT 문만 사용되어 결과 집합만 리턴하는 구조입니다. ( View 같네요 )

 

CREATE FUNCTION 함수이름
      ( 파라미터 데이터타입, 파라미터 데이터타입,… ) 
RETURNS TABLE

[WITH 함수_옵션 ] 
[AS] 
    RETURN ( 단일 SELECT 문 )

 

View 와 비슷한데 조건을 함수 파라미터로 넣으니 더 깔끔한것 같기도 합니다만.....

( 단 입력 파라미터는 꼭 입력해야하니 전체가 나오기가..물론.. 응용하면 되겠지만..^^;; )

그러나 저는 통일성 및 일관성을 위해 차라리 View 로 구현하겠습니다. ( 개인 생각 입니다. )

 

예제를 보시죠.

Dummy 테이블 많이 들 쓰시죠? 숫자와 '0' + 숫자 인 문자로 구성된.. 여튼..

Dummy 테이블을 Select 하도록 하겠습니다.

 

생성

--------------------------------------------------------------------------

CREATE FUNCTION fn_GetDummyTableUsingInlineFunction

(

         @iNum           INT

) 
RETURNS TABLE

AS
     RETURN ( SELECT Num,Seq FROM TbDummy WHERE Num >= @iNum )

 

이제 호출합니다.

--------------------------------------------------------------------------

SELECT * FROM dbo.fn_GetDummyTableUsingInlineFunction(10)

 

결과입니다.

--------------------------------------------------------------------------

 

정말 View 하고 비슷 하군요 조건쪽만 빼면..

간단한 형태라 그점은 정말 매력적입니다.

 

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

다중 문 테이블 반환 함수는 인라인테이블 반환함수와 틀리게 BEGIN ~ END 로 묶어줘야 됩니다.

함수는 BEGIN END 가 있어야. 함수 답습니다. ㅋㅋ ( 개인적인 생각이 넘 들어가는 군요 )

 

다중 문이라고 멀티 레코드셋 집합을 리턴하는것이 아닙니다. 이름 때문에 많이 헷깔린 ( 저만 그런가요. ^^;;;; 영어를 못해서 )

 

다중 문 테이블 반환 함수는 테이블 변수를 선언하고 테이블 형식을 미리 정의해야 합니다. 임시 테이블 생성 처럼요.

이 테이블 변수 값이 결과 레코드 셋으로 리턴되는 겁니다.

 

CREATE FUNCTION 함수이름
      ( 파라미터 데이터타입, 파라미터 데이터타입,… ) 
RETURNS @테이블변수 TABLE

    ( 테이블 스키마 정의 )

[WITH 함수_옵션 ] 
[AS] 
BEGIN
    @테이블변수에 Insert 하는 작업

    RETURN

END

 

이건 Stored Procedure ( 저장프로시져 : SP ) 와 비슷하군요.

저는 이렇게 함수를 안만들고 SP를 쓸 겁니다. ( SP 를 가장 좋아 합니다. 함수는 스칼라리턴만...선호하고.. )

 

또 예제를 봅시다.

이번에도 dummy 테이블을 이용할 겁니다. dummy 테이블에는 1 ~ 31 의 값이 들어있습니다.

( 위에서는 dummy 테이블 select 했으니 설명안했는데 아래의 예제는 조건이 들어가서.. ㅎㅎ )

 

제가 구현할 시나리오는

정해진 숫자보다 높은 레코드들이 있으면 결과 집합을 반환하고 아니면 99 값으로 반환하도록 할겁니다.

 

 

 

생성

--------------------------------------------------------------------------

CREATE FUNCTION fn_GetDummyTableUsingInlineFunction

(

         @iNum           INT

) 
RETURNS @returnTable TABLE

   (

         Num  INT,
           Seq  VARCHAR(2)

   )

AS

BEGIN
      IF EXISTS ( SELECT * FROM TbDummy WHERE Num >= @iNum )
      BEGIN
               INSERT INTO @returnTable ( Num, Seq )
               SELECT Num,Seq FROM TbDummy WHERE Num >= @iNum
      END
      ELSE
      BEGIN
               INSERT INTO @returnTable ( Num, Seq )
               VALUES ( 99, '99' )
      END 
 
      RETURN   

END

 

여기도 호출해 봅시다.

--------------------------------------------------------------------------

SELECT * FROM dbo.fn_GetDummyTableUsingMultistatementFunction(5)
SELECT * FROM dbo.fn_GetDummyTableUsingMultistatementFunction(100)

 

결과

두개를 호출하니 두개의 레코드 셋이 같이 나왔군요. ( 순서대로 결과입니다. )

--------------------------------------------------------------------------

 

 

내용으로 보면

"다중 문 테이블 반환 함수"는 "인라인 테이블 반환 함수"에 조건을 넣어 다른 처리를 해줄 수 있게 하는 뭐 확장된

함수 인듯 합니다.

 

포스팅을 하면서 거의 안쓰던 내용( 테이블 반환 함수 )을 만들어 사용해 보니 이것도 이것 나름대로 매리트가 있는듯 도 합니다.

그러나 테이블 반환 관련 해서는  View 나 SP를 주로 사용해서 쓸일이...( 스칼라함수는 잘 씁니다. )

 

응용을 하자면 View 나 SP안에서 호출하면 꽤 괜찮을 것도 같습니다. 뭐 취향입니다.

 

여튼 함수 기본 정의에 대한  포스팅을 마칠까 합니다.

 

출처: https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=rocabilly&logNo=140133522180

MSSQL 일반 - 함수란? ( 스칼라,테이블 반환함수( 인라인, 다중 문 ) )