2011년 6월 1일 수요일

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

댓글 없음:

댓글 쓰기

ETL 솔루션 환경

ETL 솔루션 환경 하둡은 대용량 데이터를 값싸고 빠르게 분석할 수 있는 길을 만들어줬다. 통계분석 엔진인 “R”역시 하둡 못지 않게 관심을 받고 있다. 빅데이터 역시 데이터라는 점을 볼때 분산처리와 분석 그 이전에 데이터 품질 등 데이...