DATOR


PIPELINED TABLE FUNCTION의 개념과 활용 사례(1) SQL


Oracle 9i 부터 지원되는 Pipelined Table Function 이라는 개념이 있다.

이전까지의 Function은 return되는 값이 단 하나의 scalar value 인데 비해, Pipelined Table Function 은 여러 Column과 Row로 구성된 data set을 return한다.

 

문자열을 특정 구분자로 잘라서 row로 return 하는 가장 간단한 사례를 살펴보자.

 

먼저 TYPE 객체를 하나 생성한다. 

CREATE OR REPLACE TYPE TBL_VARCHAR2_4000 AS TABLE OF VARCHAR2(4000);

 

그리고, 이 TYPE 객체를 rerurn type으로 가지는 function을 생성한다.

CREATE OR REPLACE FUNCTION SPLIT
(
    I_VALUE VARCHAR2,
    I_DELIMETER VARCHAR2 := ','
)
RETURN TBL_VARCHAR2_4000
PIPELINED
IS
    L_IDX    PLS_INTEGER;
    L_REST_VALUE    VARCHAR2(32767) := I_VALUE;
BEGIN
    LOOP
        L_IDX := INSTR(L_REST_VALUE, I_DELIMETER);
        IF L_IDX > 0 THEN
            PIPE ROW(TRIM(SUBSTR(L_REST_VALUE, 1, L_IDX - 1)));
            L_REST_VALUE := SUBSTR(L_REST_VALUE, L_IDX + LENGTH(I_DELIMETER));
        ELSE
            PIPE ROW(TRIM(L_REST_VALUE));
            EXIT;
        END IF;
    END LOOP;
    RETURN;
END SPLIT;

구분자를 지정하여 아래와 같이 사용할 수 있고,
SELECT *
  FROM TABLE(SPLIT('BGT_LCL_RT', '_'));
 
COLUMN_VALUE
------------
BGT
LCL
RT

기본 구분자를 사용할 수도 있다.

SELECT *
  FROM TABLE(SPLIT('12, 13, 14, 15'));
 
COLUMN_VALUE
------------
12
13
14
15

 

테이블명 또는 컬럼명을 분해하여 표준화 준수 검사를 하는데 활용할 수도 있겠다.

 

참고로, MS-SQL Server에서도 동일한 개념을 table-valued function 이라는 이름으로 제공하고 있다.

출처: http://msdn.microsoft.com/en-us/library/ms186755.aspx

USE AdventureWorks2008R2;
GO
IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P 
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

 

사용예는 다음과 같다.

SELECT * FROM Sales.ufn_SalesByStore (602);
 

 

※ 읽을 거리

ORACLE Documents:

- Overview of Pipelined Table Functions
   http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2345

 

- Optimizing Multiple Calls to Pipelined Table Functions
   http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2371

TAG

Leave Comments


profile기술 너머의 통찰 

Recent Trackback