[Oracle] PL/SQL 기초6 - 함수(Function) 두번째 : 테이블 함수(Table Function, Pipeline Table Function)

[Oracle] PL/SQL 기초6 - 함수(Function) 두번째 : 테이블 함수(Table Function, Pipeline Table Function)

 

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ [Oracle] PL/SQL 기초 - 함수(2) - 테이블 함수 ] 입니다. : ) 

 

0.Oracle  Table Function

▶ 1. Table 함수란?

 - 여러 로우를 가진 컬렉션을 반환하는 함수

  (컬렉션 타입을 반환하는데, 연관 배열은 반환하지 못하고, 중첩 테이블이나 VARRAY만 반환 가능하다.)

 - 기본 함수는 하나의 값만 반환이 가능하지만 테이블 함수로는 여러 행, 열의 반환이 가능 하다. 
   (Oracle Table Function은 Multi column + Multi Row로 값을 Return할 수 있는 PL/SQL Function 이다.)

 

▶ 2. Table  Function 문법

※ 일반 함수(사용자정의 함수)와 다를 것은 없지만, 대상이 되는 컬렉션 타입이 먼저 만들어져 있어야 한다.

※ 다음 예시는 DEPT와 EMP테이블을 예를들어, DEPT번호를 파라미터로 전달하여 부서 및 직원정보를 가져오는 쿼리를 TABLE 함수 형태로 변환 하는 내용이다.

 

1) OBJECT 타입 생성

 - 행을 Return 받는 역할

 - 함수에서 반환하는 레코드의 스키마

-- 1. OBJECT 타입 생성
CREATE OR REPLACE TYPE DEPT_TYPE AS OBJECT (
    DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13),
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10)
)

 

2) (임시) TABLE TYPE 객체 생성

 - 함수에서 반환하는 레코드의 집합(테이블) 정의

-- 2. TABLE 타입 생성
CREATE OR REPLACE TYPE DEPT_TABLE AS TABLE OF DEPT_TYPE;

 

3) Table Function 생성 (테이블 Return)

-- 3. 함수 생성 (테이블 리턴)
CREATE OR REPLACE FUNCTION GET_DEPT_FN(P_DEPT_NO IN NUMBER)
    RETURN DEPT_TABLE
IS
    V_RSLT DEPT_TABLE;
BEGIN
    SELECT  DEPT_TYPE(A.DEPTNO, A.DNAME, A.LOC, B.EMPNO, B.ENAME)
    BULK COLLECT INTO V_RSLT
    FROM    DEPT A
            , EMP B
    WHERE   A.DEPTNO = B.DEPTNO
    AND     A.DEPTNO = P_DEPT_NO;
    RETURN V_RSLT;
END GET_DEPT_FN;

 

4) 조회

 - "Table" 키워드를 사용하여 조회

-- 실행예시
SELECT * FROM TABLE(GET_DEPT_FN(30));

 

5) 최종 생성 객체 확인

 - 함수, 개체, 컬렉션 타입이 생성 된 것을 볼 수 있다.

 - 이로써 기본 Table Function 사용 방법을 알아 보았다.

 

▶ 3. Pipelined Table  Function

 - Table Function과 거의 유사하지만, 한 행 단위로 바로바로 즉시 값을 리턴하는 함수

   즉, 한 Row씩 처리하므로 바로 결과 값들이 출력되기 시작 한다.하다.

 - Oracle 9i 이상부터 사용 가능

 

1) OBJECT 타입 생성 (위 예시와 동일)

-- 1. OBJECT 타입 생성
CREATE OR REPLACE TYPE DEPT_TYPE AS OBJECT (
    DEPTNO NUMBER(2),
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13),
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10)
)

 

2) (임시) TABLE TYPE 객체 생성 (위 예시와 동일)

-- 2. TABLE 타입 생성
CREATE OR REPLACE TYPE DEPT_TABLE AS TABLE OF DEPT_TYPE;

 

3) Pipelined Table Function 생성

-- 3. Pipelined 함수 생성 (테이블 리턴)
CREATE OR REPLACE FUNCTION GODDAEHEE.GET_DEPT_PIPE_FN(P_DEPT_NO IN NUMBER)
    RETURN DEPT_TABLE
    PIPELINED
IS
    V_RSLT DEPT_TYPE;
BEGIN
    FOR DEPT_CUR IN (
    SELECT  A.DEPTNO, A.DNAME, A.LOC, B.EMPNO, B.ENAME
    FROM    DEPT A
            , EMP B
    WHERE   A.DEPTNO = B.DEPTNO
    AND     A.DEPTNO = P_DEPT_NO
    )
    LOOP
        V_RSLT := DEPT_TYPE(DEPT_CUR.DEPTNO, DEPT_CUR.DNAME, DEPT_CUR.LOC, DEPT_CUR.EMPNO, DEPT_CUR.ENAME);
        PIPE ROW(V_RSLT);
    END LOOP;
    RETURN;
END;

 

4) 조회

 - "Table" 키워드를 사용하여 조회

-- 실행예시
SELECT * FROM TABLE(GET_DEPT_PIPE_FN(30));

5) 최종 생성 객체 확인

 - 함수, 개체, 컬렉션 생성

 

그러고 보니 컬렉션 생성, 사용 방법에 관련하여서 따로 포스팅 한적이 없었던 것 같다.

다음 포스팅에서는 컬렉션과 관련된 내용을 살펴보자.