[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 |
댓글0