[oracle] 오라클 table Function , Pipelined Table Function

** 오라클 table Function , Pipelined Table Function  ******

 

1. Table 함수란
 - 여러 Row를 가진 컬렉션을 반환하는 함수(연관배엹x, 중첩테이블o, Varray o)
 - 기본 함수는 하나의 값만 반환이 가능하지만 테이블함수로는 여러행,열의 반환이 가능하다

2. Table Function 문법
  1. object type 생성:: 행을 return 받는 역할, 함수에서 반환하는 레코드 스키마
  2. 해당 오브젝트를 이용한 table type객체 생성 :: 함수에서 반환하는 레코드의 집합(테이블)정의
  3. table :: function
  4. 조회 :: Table 키워드를 사용하여 조회

3. Pipelined Table Function
  - Table Function과 거의 유사하지만, 현행 단위로 바로 즉시 값을 리턴하는 함수, 즉 한 Row 씩 처리하므로 바로 결과 값들이 출력되기 시작한다.
  - Oracle 9i이상부터 사용가능
   1. object type 생성 :: 행을 return 받는 혁할, 함수에서 반환하는 레코드의 스키마
   2. 해당 오브젝트를 이용한 table type 객체 생성 :: 함수에서 반환하는 레코드의 집합(테이블)정의
   3. Pipelined Table Function 생성 :: return table
   4. 조회 :: Table 키워드를 사용하여 조회

 

활용 ]
--1. object  type생성

CREATE OR REPLACE TYPE SCOTT.TP_ROW_EMP AS OBJECT
( EMP_NAME VARCHAR2(100),
 EMP_YN VARCHAR2(1),
 SUCC_MSG VARCHAR2(2000),
);

-- 2. collection type 만들기

CREATE OR REPLACE TYPE SCOTT.TP_TBL_EMP AS TABLE OF TP_ROW_EMP;

-- 3. function으로 사용

* 단행으로 리턴
CREATE OR REPLACE FUNTION SCOTT.FN_EMP_CHK
(I_EMPNO IN VARCHAR2 ) RETURN TP_TBL_EMP PIPELINED
IS
 V_EMP_NAME VARCHAR2(100);
 V_EMP_YN VARCHAR2(1);
 V_SUCC_MSG VARCHAR2(2000);
BEGIN

    IF I_EMPNO IS NULL THEN
       RETURN;  //null이 리턴된다
    END IF; 

    SELECT EMP_NAME , EMP_YN, SUCC_MSG
       INFO V_EMP_NAME
               , V_EMP_YN
              ,  V_SUCC_MSG
     FROM EMP
    WHERE EMPNO = I_EMPNO;

   PIPE ROW(TP_ROW_EMP(V_EMP_NAME, V_EMP_YN, V_SUCC_MSG ));
   RETURN;
END;

* 여러행으로 리턴
CREATE OR REPLACE FUNTION SCOTT.FN_EMP_CHK
(I_EMPNO IN VARCHAR2 ) RETURN TP_TBL_EMP PIPELINED
IS
BEGIN
    IF I_EMPNO IS NULL THEN
       RETURN;  //null이 리턴된다
    END IF; 

    FOR CUR IN 
        SELECT EMP_NAME , EMP_YN, SUCC_MSG
          FROM EMP
        WHERE EMPNO = I_EMPNO;   
    LOOP
        PIPE ROW(TP_ROW_EMP(CUR.EMP_NAME, CUR.EMP_YN, CUR.SUCC_MSG ));
    END LOOP;
    
    RETURN;
END;

--4. 쿼리에서 호출

SELECT EMP_NAME ,
       EMP_YN ,
       SUCC_MSG
FROM TABLE(FN_EMP_CHK('21'));

 

 

참고) gurubee.net/lecture/2238