오라클 PL/SQL (Pipelined Table)

Pipelined Table 사용 이유

업무 진행 중 3개 이상의 테이블에서 아이템 정보를 뽑아와 리스트에 add하여 리스팅하는 레거시 코드를 발견하였습니다. 물론 틀린 방법은 아니지만 비즈니스 로직상 데이터를 뽑아오는 쿼리문이 꽤 무거워 페이지가 느리고 코드가 길어지는 문제가 생겼습니다. 그래서 또다른 방법이 없는지 찾아보던중 Table Function과 Pipelined Table에 대해 알아보게 되었습니다. 이 글은 제가 이해한대로 작성한 글이므로 많은 양해부탁드리고 잘못된 내용이 있는 경우 댓글 부탁드립니다.

Pipelined Table

Table Function과 유사하지만 한 행 단위로 바로바로 즉시값을 리턴하여 빠르다는 것이 장점이고 메모리 소비도 줄일 수 있다는 장점이 있습니다. Oracle 9i 이상부터 사용 가능합니다. 아래는 사용하기 위한 순서입니다.

-- 1.리턴 받을 행에 대한 OBJECT TYPE을 생성
CREATE OR REPLACE TYPE OBJECT_TYPE명 AS OBJECT (
	컬럼명1 타입(Ex.VARCHAR2(100)),
        컬렴명2 타입
);

-- 2.가상의 TABLE TYPE 객체를 생성
CREATE OR REPLACE TYPE 테이블명 AS TABLE OF 위의OJBECT_TYPE명;

-- 3. Table Function 작성
CREATE OR REPLACE FUNCTION Table_Function명(
      매개변수 IN VARCHAR2
) RETURN 2의테이블명 pipelined
	IS
        test 1의OBJECT_TYPE명 := 1의OBJECT_TYPE명();  -- 초기화(변수선언)

  BEGIN
      FOR test2 IN (SELECT A, B~~~~~~)
      LOOP
          test.A     := test2.컬럼명1;
          test.B    := test2.컬럼명2;
          PIPE ROW(test);  -- 검색값을 저장
      END LOOP;

      RETURN;
  END

-- 4. 사용 쿼리문(실제 사용 예시입니다.)
SELECT
    B.*
FROM
    테이블명 A
    , TABLE(Table_Function명(매개변수)) B
WHERE 1=1
    AND A.조인 컬럼명 = B.조인 컬럼명
    AND A.컬럼명 = #{parameter};

삭제하기 위한 방법

1,2 생성의 역순으로 DROP문을 돌려주면 됩니다.

DROP TYPE TABLE_TYPE; -- 가상 테이블 삭제
DROP TYPE OBJECT_TYPE; -- OBJECT TYPE 삭제