③ DBMS_SQL 패키지

13장 [현장의 노하우]에서 살펴봤듯이 DBMS_SQL 패키지를 이용하면 컬럼 데이터를 모두 로우로 떨어 뜨릴 수 있다. 자세한 내용은 13장을 참조하기 바란다.

④ 파이프라인 테이블 함수

이 장에서 배웠던 파이프라인 테이블 함수를 사용하면 컬럼 데이터를 세로로 쭉 늘어뜨린 형태로 출력할 수 있다. 먼저 출력용으로 사용할 OBJECT 타입과 컬렉션 타입을 만들어 보자.

입력

    CREATE OR REPLACE TYPE ch14_obj_subject AS OBJECT (
           YEARS     VARCHAR2(4),   -- 연도
           GUBUN     VARCHAR2(30),  -- 구분(중간/기말)
           SUBJECTS  VARCHAR2(30),  -- 과목
           SCORE     NUMBER         -- 점수
          );

결과

    TYPE CH14_OBJ_SUBJECT이(가) 컴파일되었습니다.

입력

    CREATE OR REPLACE TYPE ch14_subject_nt IS TABLE OF ch14_obj_subject;

결과

    TYPE CH14_SUBJECT_NT이(가) 컴파일되었습니다.

이제 ch14_score_col_table 테이블을 읽어 로우 형태로 반환하는 파이프라인 테이블 함수를 만들어 보자.

입력

    CREATE OR REPLACE FUNCTION fn_ch14_pipe_table3
      RETURN ch14_subject_nt
      PIPELINED
    IS

      vp_cur  SYS_REFCURSOR;
      v_cur   ch14_score_col_table%ROWTYPE;

      -- 반환할 컬렉션 변수 선언 (컬렉션 타입이므로 초기화를 한다)
      vnt_return  ch14_subject_nt :=  ch14_subject_nt();
    BEGIN
      -- SYS_REFCURSOR 변수로 ch14_score_col_table 테이블을 선택해 커서를 오픈
      OPEN vp_cur FOR SELECT * FROM ch14_score_col_table ;

      -- 루프를 돌며 입력 매개변수 vp_cur를 v_cur로 패치
      LOOP
        FETCH vp_cur INTO v_cur;
        EXIT WHEN vp_cur%NOTFOUND;

        -- 컬렉션 타입이므로 EXTEND 메소드를 사용해 한 로우씩 신규 삽입
        vnt_return.EXTEND();
        -- 컬렉션 요소인 OBJECT 타입에 대한 초기화
        vnt_return(vnt_return.LAST) := ch14_obj_subject(null, null, null, null);

        -- 컬렉션 변수에 커서 변수의 값 할당
        vnt_return(vnt_return.LAST).YEARS     := v_cur.YEARS;
        vnt_return(vnt_return.LAST).GUBUN     := v_cur.GUBUN;
        vnt_return(vnt_return.LAST).SUBJECTS  := '국어';
        vnt_return(vnt_return.LAST).SCORE     := v_cur.KOREAN;
        PIPE ROW ( vnt_return(vnt_return.LAST));                 -- 국어 반환

        vnt_return(vnt_return.LAST).SUBJECTS  := '영어';
        vnt_return(vnt_return.LAST).SCORE     := v_cur.ENGLISH;
        PIPE ROW ( vnt_return(vnt_return.LAST));                 -- 영어 반환

        vnt_return(vnt_return.LAST).SUBJECTS  := '수학';
        vnt_return(vnt_return.LAST).SCORE     := v_cur.MATH;
        PIPE ROW ( vnt_return(vnt_return.LAST));                 -- 수학 반환

        vnt_return(vnt_return.LAST).SUBJECTS  := '과학';
        vnt_return(vnt_return.LAST).SCORE     := v_cur.SCIENCE;
        PIPE ROW ( vnt_return(vnt_return.LAST));                 -- 과학 반환

        vnt_return(vnt_return.LAST).SUBJECTS  := '지리';
        vnt_return(vnt_return.LAST).SCORE     := v_cur.GEOLOGY;
        PIPE ROW ( vnt_return(vnt_return.LAST));                 -- 지리 반환

        vnt_return(vnt_return.LAST).SUBJECTS  := '독일어';
        vnt_return(vnt_return.LAST).SCORE     := v_cur.GERMAN;
        PIPE ROW ( vnt_return(vnt_return.LAST));                 -- 독일어 반환

      END LOOP;
      RETURN;
    END;

결과

    function fn_ch14_pipe_table3이(가) 컴파일되었습니다.

컴파일이 성공했다. 이제 TABLE 연산자를 사용해 fn_ch14_pipe_table3 함수를 실행하고 그 결과를 확인해 보자.

입력

    SELECT *
      FROM TABLE ( fn_ch14_pipe_table3 );

결과

    YEARS GUBUN    SUBJECTS   SCORE
    ----- -------- ---------- ------
    2014  중간고사  국어         92
    2014  중간고사  영어         87
    2014  중간고사  수학         67
    2014  중간고사  과학         80
    2014  중간고사  지리         93
    2014  중간고사  독일어       82
    2014  기말고사  국어         88
    2014  기말고사  영어         80
    2014  기말고사  수학         93
    2014  기말고사  과학         91
    2014  기말고사  지리         89
    2014  기말고사  독일어       83

단일 SQL문을 사용하는 것보다는 좀 복잡하지만 컬럼을 로우로 변환하는 소기의 목적은 달성하였다.

지금까지 로우를 컬럼으로 변환하는 3가지 방법과 컬럼을 로우로 변환하는 4가지 방법을 살펴봤는데, 이 외에도 아이디어를 내면 더 다양한 방법을 찾을 수 있을 것이다. 독자 여러분도 새로운 방법을 찾아보길 바란다.