테이블 레이아웃 출력

이번에는 테이블명을 입력받아 테이블 레이아웃을 출력하는 프로시저를 만들어 보자. 테이블에 대한 정보는 ALL_TABLES, ALL_TAB_COLS 뷰를 참조해 얻을 수 있는데, 이 정보를 활용해 table_layout_prc라는 프로시저를 만들어 보자.

입력

    CREATE OR REPLACE PACKAGE BODY my_util_pkg IS
    ...
    ...

      -- 테이블 Layout 출력
      PROCEDURE table_layout_prc ( ps_table_name IN VARCHAR2)
      IS
        vs_table_name VARCHAR2(50) := UPPER(ps_table_name);
        vs_owner      VARCHAR2(50);
        vs_columns    VARCHAR2(300);
      BEGIN
      BEGIN
        -- 테이블이 있는지 검색
        SELECT OWNER
          INTO vs_owner
          FROM ALL_TABLES
         WHERE TABLE_NAME = vs_table_name;
        -- 해당 테이블이 없으면 빠져 나간다.
        EXCEPTION WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.PUT_LINE(vs_table_name || '라는 테이블이 존재하지 않습니다');
           RETURN;
        END;
        -- 테이블명 출력
        DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------');
        DBMS_OUTPUT.PUT_LINE('테이블: ' || vs_table_name || ' , 소유자 : ' || vs_owner);
        DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------');

        -- 컬럼정보 검색 및 출력
        FOR C_CUR IN ( SELECT column_name, data_type, data_length, nullable, data_default
                         FROM ALL_TAB_COLS
                        WHERE table_name = vs_table_name
                        ORDER BY column_id;
                      )
        LOOP
           -- 컬럼 정보를 출력한다. 줄을 맞춰 출력되도록 RPAD 함수를 사용한다.
           vs_columns := RPAD(C_CUR.column_name, 20) || RPAD(C_CUR.data_type, 15) || RPAD(C_CUR.data_length, 5) || RPAD(C_CUR.nullable, 2) || RPAD(C_CUR.data_default, 10);
           DBMS_OUTPUT.PUT_LINE( vs_columns);
        END LOOP;
      END table_layout_prc;

결과

    PACKAGE BODY MY_UTIL_PKG이(가) 컴파일되었습니다.

이 프로시저는 테이블 명을 매개변수로 입력받아 먼저 ALL_TABLES 뷰를 참조해 해당 테이블이 있는지 검색한다. 없으면 오류 메시지를 출력한 후 프로시저를 빠져 나오고, 테이블이 존재하면 다시 ALL_TAB_COLS 뷰를 검색해 해당 테이블의 컬럼 정보(컬럼명, 데이터 타입, 데이터 길이, NULL 허용여부, DEFAULT 값)를 읽어 출력하고 있다. 출력하는 컬럼명의 길이가 제각각이므로 줄을 맞춰 출력하기 위해 RPAD 함수를 사용하고 있다. 그럼 프로시저를 실행해 보자.

입력

    BEGIN
      -- 부서 테이블명 입력
      my_util_pkg.table_layout_prc ('departments');
    END;

결과

    -------------------------------------------------------
    테이블: DEPARTMENTS , 소유자 : ORA_USER
    -------------------------------------------------------
    DEPARTMENT_ID         NUMBER         22    N
    DEPARTMENT_NAME       VARCHAR2       80    N
    PARENT_ID             NUMBER         22    Y
    MANAGER_ID            NUMBER         22    Y
    CREATE_DATE           DATE            7    Y SYSDATE
    UPDATE_DATE           DATE            7    Y SYSDATE

컬럼 외에 인덱스 정보까지 출력하고 싶다면 ALL_INDEXES와 ALL_IND_COLUMNS 뷰를 참조하면 출력할 수 있다. 참고로 만약 테이블 생성 스크립트를 얻고자 한다면 DBMS_METADATA.GET_DDL 함수를 사용하면 된다.