[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) 최종 생성 객체 확인

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

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

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

저작자표시비영리변경금지

'4. Database > 4_3 Oracle 기본' 카테고리의 다른 글

[Oracle] PL/SQL 기초8 - 레코드(RECORD) (0)	2020.07.25
[Oracle] PL/SQL 기초7 - 컬렉션(Collection) : VARRAY, Nested Table, 연관 배열, 메소드 등 (0)	2020.07.25
[Oracle] PL/SQL 기초6 - 함수(Function) 두번째 : 테이블 함수(Table Function, Pipeline Table Function) (0)	2020.07.24
[Oracle] PL/SQL 기초5 - 함수(Function) 첫번째 : 사용자 정의 함수(User Defined Function) (3)	2020.07.12
[Oracle] 전역 임시 테이블 (Global Temporary Table) (0)	2019.12.17
[Oracle] PL/SQL 기초4 - 프로시저(Procedure) (6)	2018.12.23

갓대희의 작은공간

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