반응형
사용자 정의 함수 (User-defined Function, UDF)
- 프로그래밍 언어의 함수와 동일
- 매개 변수를 입력 받아 복잡한 계산과 같은 작업을 수행
- 해당 작업의 결과를 값으로 반환
- 반드시 하나의 반환 값이 있어야 함
- 반환 값은 단일 스칼라 값이나 결과 집합
- 단일 스칼라 값 : 단일 데이터 값을 의미
- 결과 집합 : 열 또는 행의 일부
함수 사용의 이점
- 모듈식 프로그래밍
- 함수를 한 번 만들어 데이터베이스에 저장한 후에는 프로그램에서 여러 번 호출 할 수 있음
- 함수는 프로그램 원본 코드에 관계없이 수정 할 수 있음
- 빠른 실행
- 저장 프로시저와 마찬가지로 함수는 작업을 캐싱하고 반복 실행을 위해 다시 사용하여 컴파일 비용을 줄임
- 즉, 사용 시 재분석 및 다시 최적화할 필요가 없음
- 네트워크 트래픽 감소
- 필터링을 필요로 하는 작업의 경우 함수를 호출하여 전송되는 행과 데이터를 줄일 수 있음
종류
- 스칼라 함수 (Scalar User-defined Function, scalar UDF)
- RETURNS절에 정의된 유형의 단일 데이터 값을 반환
- 단일 스칼라 함수 : 단일 스칼라 값을 반환하는 함수
- 반환된 스칼라 값이 단일 문의 결과
- 단순한 수학식을 사용, 내장 함수에 사용하여 정의
- 단일 실행 단위로 처리, 다중 스칼라 함수보다 더 효율적으로 실행
- 다중 스칼라 함수 : 단일 스칼라 값을 반환하고 여러개의 SQL문으로 구성된 함수
- 함수 본문에는 단일 값을 반환하는 SQL문이 포함 될 수 있음
- 반환 형식 : text, ntext, image, cursor, timestamp를 제외한 모든 데이터 형식
- 반복문, 조건문, 변수 등의 제어 구조를 사용하여 다중 계산 및 데이터 조작을 포함한 복잡한 연산을 수행
- 실행하기 위해 추가적인 처리 오버헤드가 필요해서 단일 스칼라 함수보다 효율성 저하
- 테이블 반환 함수 (Table-valued Function, TVF)
- 데이블 데이터 형식을 반환
- 인라인 테이블 반환 함수의 경우 함수의 본문이 없음
- 테이블은 단일 SELECT 문의 결과 집합
- SELECT문, JOIN절 또는 하위 쿼리에서 테이블과 동일하게 사용 가능
- 시스템 함수 (System Function)
- 사용 할 수 있는 많은 시스템 기능을 제공
- 수정 불가
요약
- 스칼라 함수 : 단일 데이터 반환
- 단일은 간단한 작업 할 때, 다중 스칼라는 반복, 조건 등 여러가지 작업을 할 때
- 테이블 반환 함수 : 테이블로 여러 행의 데이터 반환
- 시스템 함수 : 시스템 관리
매개변수
- 0개 이상의 입력 매개 변수 사용
- 스칼라 값이나 테이블을 반환
- 입력 매개 변수는 최대 1024개
- 기본값이 존재하는 매개 변수는 함수 호출 시 DEFAULT 키워드 사용
- 이는 매개 변수를 생략해도 기본 값을 의미하는 프로시저와 다름! 주의!
- 출력 매개 변수 없음
구문
스칼라 함수
단일 스칼라 함수 (Single-statement Scalar Function)
CREATE FUNCTION function_name
(
@parameter_name1 data_type1,
@parameter_name2 data_type2,
...
)
RETURNS data_type
AS
BEGIN
DECLARE @local_variable_name data_type
...
-- Function code goes here
...
RETURN expression
END단일 스칼라 함수 사용 예시
- 입력한 값의 제곱 값 반환
CREATE FUNCTION dbo.square (@input INT)
RETURNS INT
AS
BEGIN
DECLARE @result INT
SET @result = @input * @input
RETURN @result
END다중 스칼라 함수 (Multi-statement Scalar Function)
CREATE FUNCTION function_name
(
@parameter_name1 data_type1,
@parameter_name2 data_type2,
...
)
RETURNS data_type
AS
BEGIN
DECLARE @local_variable_name1 data_type1
DECLARE @local_variable_name2 data_type2
...
-- Function code goes here
...
RETURN expression
END다중 스칼라 함수 사용 예시
- 입력한 값의 팩토리얼 값을 반환
CREATE FUNCTION dbo.factorial (@input INT)
RETURNS BIGINT
AS
BEGIN
DECLARE @counter INT = 1
DECLARE @result BIGINT = 1
WHILE (@counter <= @input)
BEGIN
SET @result = @result * @counter
SET @counter = @counter + 1
END
RETURN @result
END테이블 반환 함수 (Table-valued Function, TVF)
CREATE FUNCTION function_name
(
@parameter_name1 data_type1,
@parameter_name2 data_type2,
...
)
RETURNS TABLE
AS
RETURN
(
SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition
)테이블 반환 함수 사용 예시
- 테이블의 가장 큰 수 3개 출력 (준비된 테이블(iNumberTable)은 1부터 10까지의 수가 저장되어 있음)
SELECT * FROM iNumberTable
CREATE FUNCTION dbo.GetTop3Number (@minValue INT)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 3 number
FROM iNumberTable
WHERE number > @minValue
ORDER BY number DESC
)
호출
스칼라 함수
SELECT function_name(parameter1, parameter2, ...)
- 팩토리얼 함수 호출 예시
select dbo.factorial(3)
테이블 반환 함수
SELECT Col1, Col2, Col3 ...
FROM function_name(parameter1, parameter2, ...)
- 가장 큰 수 3개 출력 함수 호출 예시 (1부터 10까지의 값 저장되어 있는 테이블(iNumberTable))
SELECT * FROM iNumberTable
SELECT number FROM dbo.GetTop3Number(3)
참고
반응형