Oracle User Defined Object을 이용한 Table Return Function

출처 : http://blog.naver.com/inter999/140048698535

아래와 같은 Query을 Function으로 만들어서 Return 해야 한다면 일반적인 oracle datatype으로는 Table 형태의 결과를 Return 할 수 없고 User Defined Object을 생성하여 사용하면 된다.

select     d.DEPTNO, d.DNAME, e.EMPNO, e.ENAME from dept d, emp e where d.DEPTNO = e.DEPTNO

 

실행결과(간략)

DEPTNO	DNAME	EMPNO	ENAME
20	RESEARCH	7369	SMITH
30	SALES	7499	ALLEN
30	SALES	7521	WARD
20	RESEARCH	7566	JONES
30	SALES	7654	MARTIN

 

먼저 User Defined Object을 생성한다.

CREATE OR REPLACE TYPE  deptUser AS OBJECT (             deptno number(2),             deptname varchar(14),             empno number(4),             ename varchar(10) );

 

다음으로 Function에서 Return Type으로 사용할 User Defined Table을 생성한다.

CREATE OR REPLACE TYPE deptUserTable AS TABLE OF deptUser;

 

Oracle Function을 만든다.

CREATE OR REPLACE FUNCTION f_deptUserList RETURN deptUserTable  PIPELINED is begin     declare               Type deptuserrecord_type is RECORD(             v_deptno    dept.DEPTNO%type,             v_dname     dept.DNAME%type,             v_empno     emp.EMPNO%type,             v_ename     emp.ENAME%type         );                 deptuserrecord deptuserrecord_type;                 cursor deptusercursor is         select         d.DEPTNO, d.DNAME, e.EMPNO, e.ENAME         from dept d, emp e         where d.DEPTNO = e.DEPTNO;                              begin                                                  OPEN deptusercursor;                                  LOOP                  FETCH deptusercursor into deptuserrecord;                  EXIT WHEN deptusercursor%NOTFOUND;                     PIPE ROW (                         deptUser(                             deptuserrecord.v_deptno,                             deptuserrecord.v_dname,                             deptuserrecord.v_empno,                             deptuserrecord.v_ename                         )                     );                                                  END LOOP;                       CLOSE deptusercursor;             RETURN;             end; end;

 

실행 Query

select * from table(f_deptUserList);

 

실행 결과(간략)

DEPTNO	DEPTNAME	EMPNO	ENAME
20	RESEARCH	7369	SMITH
30	SALES	7499	ALLEN
30	SALES	7521	WARD
20	RESEARCH	7566	JONES
30	SALES	7654	MARTIN