[ORACLE/오라클] 딕셔너리 뷰를 이용해 SELECT문 만들기

기본제공되는 SCOTT 스키마로 SELECT 구문을 만들기 위해서 (SELECT ~ FROM ~)

ALL_TAB_COLS, ALL_TAB_COMMENTS, ALL_COL_COMMENTS 를 이용한다. 

 

※ DBA_* 로 시작하는 딕셔너리 뷰는 데이터베이스 내에 생성된 모든 스키마의 오브젝트를 조회할 수 있고, ALL_*로 시작되는 뷰는 현재 사용자의 조회 권한 범위 내의 스키마를 조회할 수 있으며, USER_*로 시작되는 뷰는 현재 접속한 사용자의 스키마 정보를 확인할 수 있다.

/* SCOTT 스키마의 테이블 정보 조회 */
SELECT * FROM ALL_TAB_COLS WHERE 1=1 AND OWNER = 'SCOTT';

/* SCOTT 스키마의 테이블 정보 조회 */
SELECT * FROM ALL_TAB_COMMENT WHERE 1=1 AND OWNER = 'SCOTT';

/* SCOTT 스키마의 컬럼 정보 조회 */
SELECT * FROM ALL_COL_COMMENT WHERE 1=1 AND OWNER = 'SCOTT';

ALL_TAB_COLS.COLUMN_ID 컬럼을 보면 각테이블에서 생성된 컬럼의 정렬순서를 뜻한다는 것을 알 수 있다. 이를 통해 각 테이블의 첫번째 컬럼과 마지막 컬럼을 아래의 쿼리로 식별할 수 있다.

SELECT A.OWNER AS OWNER
     , A.TABLE_NAME AS TABLE_NAME
     , A.COLUMN_NAME AS COLUMN_NAME
     , A.COLUMN_ID AS COLUMN_ID 
     , RANK()OVER(PARTITION BY A.OWNER, A.TABLE_NAME ORDER BY A.COLUMN_ID DESC) AS COLUMN_ID_REV --순위를 COLUMN_ID의 내림차순 순으로 메긴다
  FROM ALL_TAB_COLS A
 WHERE 1=1
   AND A.OWNER = 'SCOTT'
   AND A.TABLE_NAME = 'EMP'
 ORDER BY A.COLUMN_ID ASC

테이블 COMMENTS와 컬럼 COMMENTS를 조인하여 컬럼명과 테이블명을 가져온다.

SELECT A.OWNER AS OWNER
     , A.TABLE_NAME AS TABLE_NAME
     , B.COMMENTS AS TABLE_H_NAME
     , A.COLUMN_NAME AS COLUMN_NAME
     , C.COMMENTS AS COLUMN_H_NAME
     , A.COLUMN_ID AS COLUMN_ID
     , RANK()OVER(PARTITION BY A.OWNER, A.TABLE_NAME ORDER BY A.COLUMN_ID DESC) AS COLUMN_ID_REV
  FROM ALL_TAB_COLS A
       JOIN ALL_TAB_COMMENTS B
         ON A.OWNER = B.OWNER
        AND A.TABLE_NAME = B.TABLE_NAME
       JOIN ALL_COL_COMMENTS C
         ON A.OWNER = C.OWNER
        AND A.TABLE_NAME = C.TABLE_NAME
        AND A.COLUMN_NAME = C.COLUMN_NAME
 WHERE 1=1
   AND A.OWNER = 'SCOTT'
   AND A.TABLE_NAME = 'EMP'
 ORDER BY A.COLUMN_ID ASC;
 
 SCOTT,EMP,사원정보,EMPNO,사원번호,1,8
SCOTT,EMP,사원정보,ENAME,사원명,2,7
SCOTT,EMP,사원정보,JOB,직급,3,6
SCOTT,EMP,사원정보,MGR,상위직급사원번호,4,5
SCOTT,EMP,사원정보,HIREDATE,입사일,5,4
SCOTT,EMP,사원정보,SAL,수령액,6,3
SCOTT,EMP,사원정보,COMM,인센티브,7,2
SCOTT,EMP,사원정보,DEPTNO,소속부서코드,8,1

해당 쿼리를 인라인뷰로 감싸서 조건문으로 처리한다. 이 때, COLUMN_ID, COLUMN_ID_REV로 테이블의 첫번째 컬럼과 마지막 컬럼을 구분한다.

 

SELECT CASE WHEN A.COLUMN_ID = 1 THEN 'SELECT '||A.COLUMN_NAME||' AS '||A.COLUMN_NAME||'--'||A.COLUMN_H_NAME
            WHEN A.COLUMN_ID_REV = 1 THEN '     , '||A.COLUMN_NAME||' AS '||A.COLUMN_NAME||'--'||A.COLUMN_H_NAME||CHR(10)||
                                        '   FROM '||'--'||A.TABLE_H_NAME||CHR(10)||'       '||A.OWNER||'.'||A.TABLE_NAME
            ELSE '     , '||A.COLUMN_NAME||' AS '||A.COLUMN_NAME||'--'||A.COLUMN_H_NAME
        END
  FROM (SELECT AA.OWNER                                                                    AS OWNER
             , AA.TABLE_NAME                                                               AS TABLE_NAME
             , AB.COMMENTS                                                                 AS TABLE_H_NAME
             , AA.COLUMN_NAME                                                              AS COLUMN_NAME
             , AC.COMMENTS                                                                 AS COLUMN_H_NAME
             , AA.COLUMN_ID                                                                AS COLUMN_ID
             , RANK() OVER (PARTITION BY AA.OWNER, AA.TABLE_NAME ORDER BY AA.COLUMN_ID DESC) AS COLUMN_ID_REV
        FROM ALL_TAB_COLS AA
                 JOIN ALL_TAB_COMMENTS AB
                      ON AA.OWNER = AB.OWNER
                          AND AA.TABLE_NAME = AB.TABLE_NAME
                 JOIN ALL_COL_COMMENTS AC
                      ON AA.OWNER = AC.OWNER
                          AND AA.TABLE_NAME = AC.TABLE_NAME
                          AND AA.COLUMN_NAME = AC.COLUMN_NAME
        WHERE 1 = 1
          AND AA.OWNER = 'SCOTT'
          AND AA.TABLE_NAME = 'EMP') A
 ORDER BY A.COLUMN_ID ASC;

SCOTT 스키마의 EMP 테이블을 조회하는 결과쿼리가 아래와 같이 만들어진다. 물론 아래와 같이 결과값이 나올려면 테이블의 코멘트와 컬럼의 코멘트가 잘 작성되어 있어야 한다.

SELECT EMPNO AS EMPNO--사원번호
      , ENAME AS ENAME--사원명
      , JOB AS JOB--직급
      , MGR AS MGR--상위직급사원번호
      , HIREDATE AS HIREDATE--입사일
      , SAL AS SAL--수령액
      , COMM AS COMM--인센티브
      , DEPTNO AS DEPTNO--소속부서코드
   FROM --사원정보
       SCOTT.EMP