[SQL Server] Chapter 15. 사용자 정의 함수

*본 포스팅은 개인의 학습 내용을 정리한 것입니다.

 

- 사용자 정의 함수 : 사용자가 직접 함수를 정의하여 사용할 수 있는 기능. 다음과 같이 세가지 유형이 있다.

 1) 스칼라함수 : 단일 데이터 값을 반환하는 함수

 2) 인라인테이블반환함수 : 함수의 본문 없이 단일 SELECT문의 결과를 반환하는 함수

 3) 다중 문 테이블 반환함수 : 반환될 테이블 변수에 여러 작업을 수행 후 결과를 반환하는 함수

- 사용자 정의 함수는 테이블의 데이터를 수정할 수 없다.

 

1. 사용자 정의 함수 생성, 수정 및 삭제구문

 (1) 스칼라함수

CREATE FUNCTION [<소유자명>.] <함수명>    ( [ {<@파라미터명> [AS] <파라미터타입> [= <기본값>] } [, ... n] ] )  RETURNS <반환할 데이터타입>    [ WITH <함수옵션> [ , ... n] ] [AS]  BEGIN    <함수 body>    RETURN <반환할 값>  END

 

 (2) 인라인테이블반환함수

CREATE FUNCTION [<소유자명>.] <함수명>     ([{<@파라미터명> [AS] <파라미터타입> [=<기본값> ] } [, ...n] ] )  RETURNS TABLE     [ WITH <함수옵션> [, ... n] ] [AS]  RETURN [ () <select구문> [] ]

 

 (3) 다중문테이블 반환함수

CREATE FUNCTION [<소유자명>.] <함수명>     ([{<@파라미터명> [AS] <파라미터타입> [=<기본값>]} [, ...n] ] )  RETURN <@반환변수> TABLE <table_type_definition>     [ WITH <function_option>[ , ... n] ] [AS]  BEGIN       <함수본문>       RETURN  END  <table_type_definition> = { ENCRYPTION | SCHEMABINDING}  <function_option> = ({column_definition | table_constraint}[, ...n])

 

 (4) 함수의 수정과 삭제 : ALTER FUNCTION, DROP FUNCTION으로 수정/삭제가 가능하다.

 

 (5) 사용자 정의 함수에 가능한 구문 : 아래에 기술된 구문 외에는 포함할 수 없다

   1) 변수와 커서를 위한 DECLARE

   2) 변수에 값 대입을 위한 SET, SELECT

   3) 흐름제어

   4) 로컬커서 사용을 위한 DECLARE,OPEN,FETCH,CLOSE,DEALLOCATE. 단, FETCH는 INTO구문으로 대입을 위해서만 사용가능하다. FETCH로 데이터 반환 불가능

   5) 함수에서 로컬로 사용되는 변수를 수정하는 UPDATE,INSERT,DELETE문

   6) 반환값을 가지지 않은 확장 저장 프로시저를 호출하는 EXECUTE

 

 (6) 정의 함수에 사용할 수 없는 구문

   - 사용자 정의 함수에서는 비확정적함수(특정 입력값에 대해 호출시마다 다른 결과 반환)는 사용할 수 없다.  

@@CONNECTIONS	@@PACK_SENT	@@CPU_BUSY	@@IDLE	@@IO_BUSY	@@PACKET_ERRORS
@@MAX_CONNECTINS	@@TIMETICKS	@@TOTAL_READ	GETDATE	NEWID	@@PACK_RECEIVED
@@TOTAL_ERRORS	GetUTCDate	@@TOTAL_WRITE	RAND	TEXTPTR

 

2. 사용자 정의함수 호출

 (1) 스칼라 사용자 정의함수

[<DB명>.]<사용자명>.<함수명>()

    만약 다른 DB의 함수일 경우 DB명도 포함해야 한다.

 

 (2) 테이블 값의 함수

[::]<함수명>()

    만약 SQL Server에서 기본 제공하는 함수에서 테이블 값의 함수 호출은 접두어 ::를 붙여야 한다.

 

-출처 : SQL Server advanced programming