테이블 함수
- 여러 로우를 가진 컬렉션을 반환하는 함수
- 컬렉션 타입을 반환, 연관 배열은 반환하지 못하고 중첩 테이블이나 VARRAY만 반환 가능
- 기본 함수는 하나의 값만 반환이 가능하지만 테이블 함수로는 여러 행, 열의 반환이 가능
- Oracle Table Function은 Multi Column + Multi Row로 값을 return할 수 있는 PL/SQL Function
문법
- 일반 함수와 다를 건 없지만, 대상이 되는 컬렉션 타입이 먼저 만들어져 있어야 함.
- 다음 예시는 DEPT와 EMP 테이블을 예를 들어, DEPT 번호를 파라미터로 전달하여 부서 및 직원 정보를 가져오는 쿼리를 TABLE 함수 형태로 변환
- OBJECT 타입 생성
- 행을 리턴받는 역할
- 함수에서 반환하는 레코드의 스키마
- (임시) TABLE TYPE 객체 생성
- 함수에서 반환하는 레코드의 집합(테이블) 정의
- Table Function 생성 (테이블 Return)
- "Table" 키워드를 사용하여 조회
예시
-- 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 타입 생성
CREATE OR REPLACE TYPE DEPT_TABLE AS TABLE OF DEPT_TYPE;
-- 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;
-- 실행예시
SELECT * FROM TABLE(GET_DEPT_FN(30));
Pipelined Table Function
- Table Function과 거의 유사, 한 행 단위로 바로바로 즉시 값을 리턴하는 함수
- 한 Row씩 처리하므로 결과값들이 바로 출력되기 시작
- Oracle 9i부터 사용
- OBJECT 타입 생성
- (임시) TABLE TYPE 객체 생성
- Pipelined Table Function 생성
- 조회
-- 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 타입 생성
CREATE OR REPLACE TYPE DEPT_TABLE AS TABLE OF DEPT_TYPE;
-- 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;
-- 실행예시
SELECT * FROM TABLE(GET_DEPT_PIPE_FN(30));'Study > Oracle' 카테고리의 다른 글
| PL/SQL 기본_5 - 함수 (0) | 2021.05.17 |
|---|---|
| PL/SQL 기본_4 - 프로시저 (0) | 2021.05.10 |
| PL/SQL 기본_3 - 커서 (0) | 2021.05.10 |
| PL/SQL 기본_2 - 반복문과 제어문 (0) | 2021.05.10 |
| PL/SQL 기본_1 (0) | 2021.05.10 |