1. 사용자 정의 함수란

저장 프로시져(Stored Procedure)가 어떠한 처리를 위해 수행되는것과는 달리 함수는 짐작되다시피 어떤 특정한 값을 사용자에게 넘겨주는(리턴) 기능을 갖습니다. 예를 들어 GetDate() 함수는 현재의 날짜를 리턴해줍니다. SQL Server 2000에서는 사용자가 자신만의 함수를 만들 수 있는 기능을 제공합니다. 이것이 사용자 정의 함수(User Defined Function)입니다. 물론 저장 프로시져도 OUTPUT 매개변수를 이용하여 값을 넘겨줄수 있지만 값을 얻기위해 저장 프로시져를 사용하기는 적당하지 않은 부분이 많습니다. 사용자 정의함수는 특정한 값만을 리턴하는게 아니고 테이블을 리턴해주기도 합니다. 또한 쿼리문의 여러 부분에서 사용될 수 있어 업무에 많은 도움을 줍니다. 이 부분에 대해서는 나중에 확인하도록 하겠습니다.

사용자 정의 함수는 CREATE FUNCTION을 이용해서 만들고 DROP FUNCTION을 이용해서 제거되며 ALTER FUNCTION을 이용해서 변경되어집니다. CREATE FUNCTION은 만들어지는 사용자 정의 함수의 종류에따라 여러가지 방법이 있습니다. 우선 사용자 정의 함수의 종류를 알아보고 각 종류별로 만드는 방법과 사용예를 살펴보도록 하겠습니다.

사용자 정의 함수는 다음과 같이 분류할 수 있습니다.

o 스칼라 반환 사용자 정의 함수 (Scalar Functions)
o 인라인 테이블 반환 함수 (In-Line Table-valued Functions)
o 다중 문 테이블 반환 함수 (Multi-Statement Table-valued Functions)

1) 스칼라 반환 사용자 정의 함수(Scalar Functions)

GetDate()함수의 경우 리턴하는 값은 현재의 날짜입니다. 이렇듯 특정 값만을 리턴하는 함수를 Scalar Function 이라고 합니다. 만일 두 값의 합을 구하여 리턴해주는 사용자 정의 함수를 구현한다면 다음과 같이 만들수 있습니다.

[예제1]

CREATE FUNCTION fn_sum (@val01 int, @val02 int) -- 1) RETURNS int -- 2) AS BEGIN RETURN (@val01 + @val02) -- 3) END GO SELECT dbo.fn_sum(100, 200) -- 4)

o '--' 는 수행과는 아무 상관없는 Comments 를 위해 사용됩니다.
o 1)에서 CREATE FUNCTION을 사용했습니다. 이때 만들어지는 함수의 이름은 fn_sum이며, @val01과 @val02 두개의 int 타입 매개변수가 사용됨을 알 수 있습니다.
o 2)에서 fn_sum 함수가 int 타입의 값을 리턴한다고 지정하고 있습니다.
o 3)에서 함수에 전달된 @val01과 @val02의 값을 더한 결과를 RETURN하고 있습니다.
o 4)의 SELECT 결과는 100과 200의 합인 300이 됩니다. 사용자 정의 함수를 호출 할때는 이처럼 '소유자.함수이름(매개번수)' 형태를 취해야 합니다. 수유자가 dbo라 하더라도 생략할 수 없습니다.

[예제2]

CREATE FUNCTION fn_DateFormat(@indate datetime, @separator char(1)) RETURNS Nchar(20) AS BEGIN RETURN CONVERT(Nvarchar(20), datepart(mm,@indate)) + @separator + CONVERT(Nvarchar(20), datepart(dd, @indate)) + @separator + CONVERT(Nvarchar(20), datepart(yy, @indate)) END GO SELECT dbo.fn_DateFormat(GetDate(),'/')

o 날짜를 지정한 문자로 월,일,년을 분리하여 보시해주는 사용자 정의 함수의 예입니다.

※ 사용자 정의함수 호출 방법

스칼라 사용자 정의 함수를 호출할 때는 적어도 다음과 같이 두 부분(소유자.함수명)으로 된 이름을 제공해야 합니다.
SELECT *, MyUser.MyScalarFunction()
FROM MyTable

테이블 값을 리턴하는 함수는 다음과 같이 한 부분으로 된 이름을 사용하여 호출할 수 있습니다.
SELECT *
FROM MyTableFunction()

그러나 테이블을 반환하는 SQL Server 기본 제공 함수를 사용할 때는 함수 이름에 접두어 ::를 추가해야 합니다.
SELECT * FROM ::fn_helpcollations()

2) 인라인 테이블 반환 함수 (In-Line Table-valued Functions)

인라인 테이블 변환 함수는 함수 본문이 하나의 SELECT 문으로 정의된 테이블 반환 함수입니다.인라인 사용자 정의 함수는 다음 규칙을 따릅니다.

o RETURNS 절에는 키워드 table만 포함됩니다. 반환 변수 형식은 RETURN 절에 있는 SELECT 문의 결과 집합에서 설정되므로 사용자가 정의하지 않아도 됩니다.
o BEGIN과 END로 구분되는 function_body는 없습니다.
o RETURN 절에는 하나의 SELECT 문이 괄호 안에 포함됩니다. 함수에서 반환하는 테이블은 SELECT 문의 결과 집합으로 구성됩니다. 인라인 함수에서 사용되는 SELECT 문은 뷰에서 사용되는 SELECT 문과 같은 제한을 받습니다.

[예제1]

USE pubs GO CREATE FUNCTION SalesByStore (@storeid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storeid and t.title_id = s.title_id)

o 진하게 표시된 부분을 자세히 살펴보시기 바랍니다.
o 다른 사용자 정의 함수와는 달리 BEGIN...END문이 없습니다.
o RETURN 문의 괄호안에 하나의 SELECT문이 포함되어 있습니다.
o 다음의 뷰(VIEW)를 보면 위 SalesByStore 함수와 같은 내용을 표시해 주지만 여러가지 경우에 따른 변화된 내용을 보여주지는 못합니다. 이런 뷰의 단점을 인라인 테이블 반환 함수는 해결해 줍니다.

USE pubs GO CREATE VIEW vw_Sales AS SELECT title, qty FROM sales s, titles t WHERE t.title_id = s.title_id

다음은 위의 사용자 정의 함수와 뷰를 이용하여 검색하는 예입니다. 처음의 사용자 정의 함수는 Stor_id가 7067인 것만을 표시하지만 뷰는 전체를 다 표시하게 됩니다.

SELECT * FROM SalesByStore(7067) SELECT * FROM vw_Sales

3) 다중 문 테이블 반환 함수 (Multi-Statement Table-valued Functions)

다중 문 테이블 변환 함수는 뷰(VIEW)와 저장 프로시져(Stored Procedure)의 복합형태로 생각히시면 됩니다. 즉, 뷰처럼 테이블을 반환해주고, 저장 프로시져처럼 여러가지 처리를 함수 내부에서 하게됩니다. 이런 이유로 함수를 만드는 형태가 꽤나 복잡합니다.

table을 반환하는 사용자 정의 함수에서는 다음을 수행합니다.

o RETURNS 절에서 함수가 반환한 테이블에 로컬 반환 변수 이름을 정의합니다.
o RETURNS 절에서 테이블 형식도 정의합니다. 로컬 반환 변수 이름의 범위는 함수 내에서 로컬입니다.
o 함수 본문에 있는 Transact-SQL 문에서 행을 작성하여 RETURNS 절에서 정의된 반환 변수에 삽입합니다.
o RETURN 문이 실행될 때 변수에 삽입된 행은 함수의 테이블 형식 출력으로 반환됩니다. RETURN 문에서는 인수를 사용할 수 없습니다.
o 테이블을 반환하는 함수에 있는 어떠한 Transact-SQL 문도 사용자에게 직접 결과 집합을 반환할 수 없습니다.
o 함수에서 사용자에게 반환할 수 있는 유일한 정보는 함수에서 반환된 table입니다.

두가지 예제를 보면서 만드는 방법을 확인하도록 하겠습니다. 첫번째 예제는 MOC 교재에 수록된 것이며, 두번째 예제는 Books Online에 수록된 예제입니다.

[예제1]

USE Northwind GO CREATE FUNCTION fn_Employees (@length nvarchar(9)) RETURNS @fn_Employees table (EmployeeID int PRIMARY KEY NOT NULL, [Employee Name] nvarchar(61) NOT NULL) AS BEGIN IF @length = 'ShortName' INSERT @fn_Employees SELECT EmployeeID, LastName FROM Employees ELSE IF @length = 'LongName' INSERT @fn_Employees SELECT EmployeeID, (FirstName + ' ' + LastName) FROM Employees RETURN END GO SELECT * FROM dbo.fn_Employees('LongName') -- 또는 SELECT * FROM dbo.fn_Employees('ShortName')

o 진하게 표시된 부분을 자세히 살펴보시기 바랍니다.
o 만드는 방법이 다소 복잡하게 보입니다.
o RETURNS 문에 @fn_Employees 테이블이 컬럼 구조와 함께 선언되어 있습니다.
o 이 @fn_Employees 테이블에 SELECT결과를 INSERT해주고 있습니다.
o @fn_Employees 테이블의 내용이 실제 함수가 리턴해주는 결과입니다.

[예제2]

CREATE FUNCTION LargeOrderShippers (@FreightParm money) RETURNS @OrderShipperTab TABLE ( ShipperID int, ShipperName nvarchar(80), OrderID int, ShippedDate datetime, Freight money ) AS BEGIN INSERT @OrderShipperTab SELECT S.ShipperID, S.CompanyName, O.OrderID, O.ShippedDate, O.Freight FROM Shippers AS S INNER JOIN Orders AS O ON S.ShipperID = O.ShipVia WHERE O.Freight > @FreightParm RETURN END

o [예제1]과 형식이 똑 같습니다.
o 다른 것은 함수 이름과 테이블 구조와 어떤 결과가 이 테이블에 기록되어 리턴되는가 하는 것뿐입니다.

4) 기타

사용자 정의 함수는 SQL Server 2000에서 처음 선보이는 기능이므로 이전 버젼 사용자에게는 꽤 낯설게 보일 수 있지만 이 기능을 활용하면 많은 도움을 받을 수 있습니다. 사용자 정의 함수는 WHERE 절에도 사용이 가능하며 FROM 절에도 사용이 가능합니다. Books Online에 사용자 정의 함수에 대한 많은 설명이 포함되어 있습니다. Books Online의 내용을 꼭 확인하여 보시기 바랍니다.

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

출처 : http://www.sqlworld.pe.kr