사용자 정의 함수

작성자 : 손호성 (NTFAQ.CO.KR)

http://www.ntfaq.co.kr or http://www.mcse.co.kr

　

---

　

그럼 이젠 테이블 함수에 대해서도 살펴보기로 합시다. 테이블 함수를 사용하기 

위해서는 SQL 서버 2000에 등장한 새로운 테이블형 변수에 대해서 알아야만 합니다. 

<리스트 13>의 코드를 수행해 보도록 하세요. 우선 첫번째 @test 변수의 경우 

테이블 형으로 선언되었고 그 안에는 테이블 스키마가 정의되어있는 것을 

볼 수 있을 것입니다. 마치 CREATE TABLE @test와 유사해 보입니다. 

일단 @test 변수가 선언된 다음에는 마치 임시 테이블을 사용하는 것과 같이 

@test 변수를 이용할 수 있습니다. 하지만 #으로 시작되는 임시 테이블에 비해서 

리소스 잠금 및 로깅에 대한 요구도 적고 일괄배치, 사용자 정의 함수, 

저장 프로시저 내부에서만 작동하므로 임시 테이블보다 훨씬 효율적으로 이용할 

수 있습니다. 테이블 형 변수는 궁극적으로는 로우세트(Rowset) 로 불리 우는 

검색된 결과의 로우 집합을 저장하는데 아주 편리한 구조라고 할 수 있습니다.

 

declare @test TABLE(id int, name varchar(10),email varchar(20),phone varchar(20)                                    , reg_date datetime)   insert @test values(1,'손호성','kind511@email.net','011-9118-1111',getdate())   select * from @test   <그림 4> 테이블 형 변수의 사용

<리스트 13> 테이블 형 변수의 사용

 

테이블 변수를 이해했다면 이제 테이블 변수를 사용하는 사용자 정의 함수를 생성해 

보도록 합시다. <리스트 14>는 인라인 테이블 함수라 불리 우는 것입니다. 

스칼라 함수 때와 비교했을 때 인라인 테이블 함수는 BEGIN~END로 시작되는 

구문이 없습니다. 단지 RETURN문 안에 모든 함수 본문이 정의될 뿐입니다. 

fn_regionOfCustomer 함수가 하는 일은 Northwind.dbo.customers 테이블에서 

Customer들의 region 정보에 기준해서 검색된 데이터를 반환하는 것입니다. 

입력 인자는 varchar(30)으로 된 region 정보이고 반환 형식은 로우세트인 

TABLE 형식입니다. 검색 문은 매우 간단하므로 더 이상 설명하지는 않겠습니다. 

사용방식은 특이하게도 FROM 다음에 함수를 사용하고 있습니다.

 

USE Northwind go   CREATE FUNCTION fn_regionOfCustomer(@region varchar(30)) RETURNS TABLE AS RETURN(              SELECT customerID,companyName              FROM customers              WHERE region = @region              )   go   SELECT * FROM dbo.fn_regionOfCustomer('WA') go   결과 <그림 5> fn_regionOfCustomer(‘WA’) 수행 결과

<리스트 14> 인라인 테이블 함수

 

마치 끝에 있는 (‘WA’)라는 인자가 없다면 테이블이나 뷰와 다르지 않죠. 

다음의 뷰를 실행하면 보다 확실히 비교할 수 있을 것입니다. <리스트 15>의 

뷰를 수행해 보십시오. <그림 5>와 <그림 6>의 수행 결과를 비교해 보십시오. 

수행 결과 사이에 틀린 점이 있습니까? 아닙니다. 두 개체 모두 동일한 결과 세트를 

반환하고 있습니다.

 

CREATE VIEW v_regionOfCustomer AS              SELECT customerID,companyName              FROM customers              WHERE region = 'WA' go   SELECT * FROM v_regionOfCustomer 결과 <그림 6> v_regionOfCustomer

<리스트 15> v_regionOfCustomer 검색 결과

 

하지만, <리스트 16>의 구문들을 수행해 보십시오. <그림 7>과 <그림 8>의 

수행 결과는 매우 다릅니다. 즉, 함수를 이용하는 fn_regionOfCustomer의 경우에는 

검색 값을 인자로 줄 수 있다는 것이고, 이에 반해서 뷰인 v_regionOfCustomer는 

검색 조건이 고정되어있다는 점입니다. 함수는 동적이고 뷰는 정적으로 쿼리를 

저장하는 것입니다. 그래서 인라인 함수를 흔히 동적인 뷰 혹은 

파라미터된 뷰라고도 이야기합니다. 이러한 인라인 함수를 이용하면 검색 조건이 

자주 변하는 쿼리의 경우 하나의 함수 개체로 처리할 수 있다는 점이 장점이라고 

할 수 있습니다. 그리고 개인적으로는 좀더 매니아틱 해보여서 좋아하기도 합니다.^^;

SELECT * FROM dbo.fn_regionOfCustomer('SP') <그림 7> dbo.fn_regionOfCustomer('SP')   SELECT * FROM v_regionOfCustomer <그림 8> v_regionOfCustomer

<리스트 16> dbo.fn_regionOfCustomer('SP')와 v_regionOfCustomer

 

인라인 함수를 조인 쿼리와 같이 이용하는 것도 재미있는 주제일 수 있습니다. 

함수 fn_orderByEmployee와 fn_orderByEmployee2 함수는 각각 로우세트 형태의 

테이블을 반환하는 인라인 함수들입니다. fn_orderByEmployee 함수는 고객 아이디를 

입력 받아서 ORDERS 테이블에서 해당 고객 아이디를 처리한 EmployeeID들을 

반환합니다. fn_orderByEmployee2 함수는 앞의 동작과 함께 RETURN 문에서 

IN 명령어를 이용해서 EMPLOYEES 테이블을 검색해 냅니다. 

주석 처리되어있는 Query1, Query2, Query3는 모두 동일한 결과를 반환합니다. 

<그림 17>이 바로 그것입니다. 하지만, 각각의 실행 방법들은 사뭇 다른데요.

 

Query1은 IN 절 안에 fn_ordyerByEmployee(‘VINET’)과 같이 ‘VINET’을 인자로 해서 

로우세트를 반환하고 이를 SELECT로 검색해 내서 IN 절에 매칭하고 있습니다.

(직접적인 매칭은 사용할 수 없고 반드시 SELECT문으로 재검색 해 주어야만 합니다.)

Query2는 FROM 절에서 Employees 테이블과 fn_orderByEmployee(‘VINET’) 함수와 

INNER JOIN 하고 있습니다. 이때의 키는 물론 EmployeeID입니다. 이 경우에는

마치 인라인 함수를 테이블인양 조인하실 수 있습니다.

Query3는 아예 IN 절을 함수 반환문 안에 내포시킨 경우입니다. 

Query2에서처럼 JOIN을 하실 수도 있습니다.

 

CREATE FUNCTION fn_orderByEmployee(@customerID varchar(20)) RETURNS TABLE AS RETURN(              SELECT DISTINCT EmployeeID  FROM ORDERS WHERE CustomerID = @customerID              ) go   CREATE FUNCTION fn_orderByEmployee2(@customerID varchar(20)) RETURNS TABLE AS RETURN(              SELECT * FROM EMPLOYEES WHERE EmployeeID IN (              SELECT DISTINCT EmployeeID  FROM ORDERS                   WHERE CustomerID = @customerID)              ) go   -- Query 1 SELECT LastName,FirstName FROM EMPLOYEES WHERE EmployeeID IN(                          SELECT * FROM fn_orderByEmployee('VINET')                         )   -- Query 2 SELECT E.LastName,E.FirstName FROM EMPLOYEES E INNER JOIN fn_orderByEmployee('VINET') F ON E.employeeID = F.employeeID   -- Query 3 SELECT LastName,FirstName FROM fn_orderByEmployee2('VINET')   결과 <그림 8> 인라인 함수에서 조인 쿼리

<리스트 17> 인라인 함수에서 조인 쿼리

 

테이블 함수 중 인라인 함수를 제외한 또 다른 것이 바로 다중 문 테이블 값을 

반환하는 함수인데요. 상당히 용어가 길기는 하지만, 몇 가지 점을 제외하면 

별다를 것이 없습니다. 이 경우에는 단순히 검색된 TABLE 변수를 반환하는 

것이 아니라, TABLE 변수의 스키마를 정의해서 이 변수에다가 

INSERT/UPDATE/DELTE, 즉 데이터를 조작해서 반환해 줄 수 있습니다. 

좀더 유연하게 동작할 수 있죠. 사용자 정의 함수 중에서 가장 복잡한 것 중의 

하나입니다. 한가지 예를 들어보죠. <리스트 18>은 Northwind 데이터베이스의 

Shippers 테이블에서 운송비용이 일정액 이상인 로우들을 반환하는 사용자 정의 

함수입니다.

 

<리스트 18>이 상당히 길기는 하지만, 몇 가지 흥미로운 상황들이 있습니다. 

우선 인라인 함수에서는 사용되지 않았던 BEGIN~END 함수 본문이 있습니다. 

그리고 RETURNS 문 다음에는 반환할 테이블 스키마도 선언되어있고요. 

@overShipper 테이블 변수는 몇 가지 컬럼들로 이루어져 있는데, 이는 그다지 

중요한 요소는 아닙니다. 그보다는 BEGIN~END안에 있는 함수 본문을 주의해서 

보아주시길 바랍니다. 우선 INSERT SELECT 문을 사용해서 테이블 변수에 

데이터를 인서트 하고 있습니다. 스칼라 함수에서 함수 안에 INSERT 문을 

사용할 수 없다고 말한 적이 있는데요. 이는 비확정적 요인으로 데이터베이스 

및 서버 자원들을 임의로 변경시킬 요인이기 때문이라고 설명했었습니다. 

하지만 테이블 변수에 대한 INSER/UPDATE/DELETE 문들은 이러한 비확정적 

요인들은 아니므로 자유로이 사용할 수 있습니다. @freightCost 보다 

큰 주문들을 @orderShipper 테이블 변수에 인서트하는 군요.

　

그리고는 @avgFreight 변수에는 @orderShipper 테이블 변수의 평균 Freight 값을 

할당합니다. 그런 후 DELETE 문을 통해서 @orderShipper 테이블 변수에서 

평균값 이하의 모든 로우들을 삭제합니다. 그리고 마지막에는 INSERT 문을 

통해서 임의로 @orderShipper 테이블에 기존 평균값을 가지는 새로운 로우를 

인서트하고 있습니다. 실제로 이러한 작업은 테스트를 위한 가공의 상황일 뿐입니다. 

중요한 것은 반환할 로우세트를 함수 안에서 자유로이 조작할 수 있다는 것이지요. 

이렇게 하면, 어떤 일련의 검색 결과들을 함수 안에서 조작 및 필터링 하여 

사용할 수 있다는 뜻이 됩니다. 반환된 결과는 테이블 형태이므로 마치 일종의 

테이블 개체나 뷰 개체처럼 조인이나 검색할 수 있게 됩니다. 다중 문 테이블 값

함수에서는 RETURN 다음에 아무런 구문도 존재할 수 없습니다. RETURN은 

RETURNS 다음에 지정된 테이블 변수를 반환하는 데에 사용됩니다.

 

USE Northwind go   CREATE FUNCTION fn_overCostShipper(@freightCost money) RETURNS @orderShipper TABLE              (              ShipperID int,              ShipperName nvarchar(80),              OrderID int,              ShippedDate datetime,              Freight   money              ) AS BEGIN                           declare @avgFreight money                                        INSERT @orderShipper              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 > @freightCost                SELECT @avgFreight = AVG(Freight) FROM @orderShipper                DELETE @orderShipper WHERE freight <= @avgFreight                           INSERT @orderShipper VALUES(-1,'SYSTEM_AVG_FREIGHT','00000',NULL                                                               ,@avgFreight)                           RETURN END go   SELECT * FROM dbo.fn_overCostShipper($600) ORDER BY ShippedDate DESC 결과 <그림 9> fn_overCostShipper 함수 실행 결과

<리스트 18> fn_overCostShipper 함수 실행 결과

 

이렇게 해서 사용자 정의 함수의 세가지 즉, 스칼라 함수, 인라인 테이블 함수, 

다중 문 테이블 값 함수를 모두 다루었습니다. 조금은 기초적인 수준에서 

다루었지만, 저장 프로시저와 마찬가지로 응용 방법에 따라서는 굉장한 일들도 

처리할 수 있을 것입니다. 이제는 몇 가지 사용자 정의 함수와 관련된 주제들도 

설명해 드리기로 하죠.

[출처] http://sqler.pe.kr/FriendLec/shon/shon0303.asp