Oracle User Defined Object을 이용한 Table Return Function

아래와 같은 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