PL/SQL_6 - 테이블 함수

테이블 함수

• 여러 로우를 가진 컬렉션을 반환하는 함수
  • 컬렉션 타입을 반환, 연관 배열은 반환하지 못하고 중첩 테이블이나 VARRAY만 반환 가능
• 기본 함수는 하나의 값만 반환이 가능하지만 테이블 함수로는 여러 행, 열의 반환이 가능
  • Oracle Table Function은 Multi Column + Multi Row로 값을 return할 수 있는 PL/SQL Function

 

문법

• 일반 함수와 다를 건 없지만, 대상이 되는 컬렉션 타입이 먼저 만들어져 있어야 함.
• 다음 예시는 DEPT와 EMP 테이블을 예를 들어, DEPT 번호를 파라미터로 전달하여 부서 및 직원 정보를  가져오는 쿼리를 TABLE 함수 형태로 변환

1. OBJECT 타입 생성
   
   1. 행을 리턴받는 역할
   2. 함수에서 반환하는 레코드의 스키마
2. (임시) TABLE TYPE 객체 생성
   1. 함수에서 반환하는 레코드의 집합(테이블) 정의
3. Table Function 생성 (테이블 Return)
4. "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부터 사용

1. OBJECT 타입 생성
2. (임시) TABLE TYPE 객체 생성
3. Pipelined Table Function 생성
4. 조회

-- 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));