User-Defined Functions

반환 값은 스칼라 (단일) 값이거나 테이블 또는 result set.

• 모듈 식 프로그래밍을 허용합니다( allow modular programming.)
• 더 빠른 실행 허용 (I allow faster execution)
• 네트워크 트래픽을 줄일 수 있습니다(I can reduce network traffic)

활용

• SELECT문 같은 Transact-SQL 문에서 사용
• 함수를 호출하는 애플리케이션에서 사용
• 다른 사용자가 정의한 함수를 정의할 때 사용
• 뷰를 매개 변수화하거나 인덱싱 된 뷰의 기능을 개선하기 위해서 사용
• 테이블에서 열을 정의하기 위해서 사용
• 열에 대한 CHECK 제약 조건을 정의할 때 사용
• 저장된 procedure 를 바꾸기 위해
• 인라인 함수를 보안 정책에 대한 필터 조건 자로 사용하기 위해

유효한 문법

• DECLARE statements
• Assignments of values
• Cursor operations
• Control-of-flow statements
• SELECT statements.
• UPDATE, INSERT, and DELETE statements
• EXECUTE statements

종류 2가지

Scalar Function

단일 데이터 값 반환

Table-Valued Functions

테이블 형식을 반환

함수만들기 Syntax

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

기본 예제

create

create function getSum(@a int =20, @b int, @c int)
returns int
as
begin
declare @d as int
set @d = @a + @b + @c
return @d
end;
go

Incorrect syntax 'CREATE FUNCTION'must be the only statement in the batch.
이렇게 뜨면 위 아래에 go가 안들어갔는지 확인해서 넣어주면 된다. (batch 구역을 나누어주기 위해서)

이렇게 확인할 수 있다.

select dbo.getSum(3,5,7);
select dbo.getSum(default,5,7); --디폴트 쓸때

이렇게 실행시켜볼 수 있다.

alter(수정)

ALTER function [dbo].[getSum](@a int=30, @b int=20, @c int =10)
returns int
as
begin
declare @d as int
set @d = @a + @b + @c
return @d
end;

drop(삭제)

drop function dbo.getSum

활용 예제

1. CalculateArea() function gets radius as input parameter and return the area of the circle

IF OBJECT_ID('dbo.CalculateArea') IS NOT NULL DROP FUNCTION dbo.CalculateArea;
GO
create function dbo.CalculateArea(@radius as float)
returns float
as
begin
return PI()* power(@radius,2);
end;
go

select dbo.CalculateArea(3.7);

2. GetAge() function get the date of birth as input argument and returns the age in years

IF OBJECT_ID('dbo.GetAge') IS NOT NULL DROP FUNCTION dbo.GetAge;
GO

CREATE FUNCTION dbo.GetAge(@birthdate AS DATE)
RETURNS INT
AS
BEGIN
RETURN DATEDIFF(year, @birthdate, sysdatetime());

END;
GO

select dbo.GetAge('1995-10-10')
GO

3. fn_RectangleArea() gets the width and height as input arguments and returns area of the rectangle.

IF OBJECT_ID('dbo.rectangleArea') IS NOT NULL DROP FUNCTION dbo.rectangleArea;
GO

CREATE FUNCTION RectangleArea(@Width int, @Height int)
RETURNS int AS
BEGIN

RETURN ( @Width * @Height )
END
GO

select dbo.rectangleArea(4,8) as Area

4.ReverseCustName() function gets a string as input argument and returns the string in reverse order.

IF OBJECT_ID('dbo.reverseCustName') IS NOT NULL DROP FUNCTION dbo.reverseCustName;
GO

CREATE FUNCTION dbo.reverseCustName(@str varchar(100))
RETURNS varchar(100)
AS
BEGIN
DECLARE @custName varchar(100)
 set @custName = REVERSE(@str)
RETURN @custName
END
GO

select dbo.reverseCustName('hyunjune')
GO

5. 실제 테이블에서 값 가져오는 예제

USE AdventureWorks2019;  
GO 

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
DROP FUNCTION ufnGetInventoryStock;
GO

CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Retunrs
BEGIN
	DECLARE @ret int;
	SELECT @ret = SUM(p.Quantity)
	FROM Production.ProductInventory p
	WHERE p.ProductID = @ProductID
		AND p.LocationID = '6';
	 IF (@ret IS NULL)
		SET @ret = 0;
	RETURN @ret;
END;
GO

select dbo.ufnGetInventoryStock(4)