"오라클 DDL 생성 스트립트"의 두 판 사이의 차이

DB CAFE

2021년 3월 11일 (목) 10:28 기준 최신판

추천메뉴 바로가기

DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
튜닝 { 오라클 튜닝 목록 }
모델링 { 데이터 모델링 가이드 }

1 oracle ddl 생성스크립트[편집]

1.1 show schema of a table[편집]

WITH INFO AS (SELECT 'USER_NAME' AS username, 'ADM_USER' AS usertable FROM DUAL),
       PK AS (SELECT c.column_name
                   , CASE WHEN a.constraint_type = 'R' THEN 'Y' ELSE '' END    AS column_fk
                FROM sys.user_cons_columns c, sys.user_constraints a, INFO u
               WHERE     a.owner = c.owner
                 AND a.table_name = c.table_name
                 AND a.constraint_name = c.constraint_name
                 AND a.constraint_type IN ('P', 'R')
                 AND a.table_name = u.usertable
                 AND a.OWNER = u.username)
  SELECT PK,
         FK,
         column_name,
         data_type,
         data_length,
         nullable,
         data_default,
         comments
    FROM (SELECT CASE WHEN LENGTH (p.column_name) > 0 THEN 'PK' ELSE ' ' END
                     AS PK,
                 p.column_fk
                     AS FK,
                 c.column_name,
                 c.data_type,
                 CASE
                     WHEN c.data_type = 'VARCHAR2'
                     THEN
                         TO_CHAR (data_length)
                     WHEN    c.data_type = 'DATE'
                          OR c.data_type = 'FLOAT'
                          OR c.data_type = 'INTEGER'
                          OR c.data_type = 'BLOB'
                     THEN
                         ''
                     WHEN c.data_type = 'NUMBER'
                     THEN
                         (CASE
                              WHEN data_precision IS NULL
                              THEN
                                  ''
                              WHEN data_scale = 0
                              THEN
                                  TO_CHAR (data_precision)
                              ELSE
                                     TO_CHAR (data_precision)
                                  || ','
                                  || TO_CHAR (data_scale)
                          END)
                     ELSE
                         (CASE
                              WHEN data_precision IS NULL
                              THEN
                                  ''
                              WHEN data_scale = 0
                              THEN
                                  TO_CHAR (data_precision)
                              ELSE
                                     TO_CHAR (data_precision)
                                  || ','
                                  || TO_CHAR (data_scale)
                          END)
                 END
                     AS data_length,
                 c.COLUMN_ID,
                 c.NULLABLE,
                 c.data_default,
                 s.comments
            FROM USER_TAB_COLUMNS c,
                 PK               p,
                 INFO             u,
                 USER_COL_COMMENTS s
           WHERE     c.TABLE_NAME = u.usertable
                 AND c.column_name = p.column_name(+)
                 AND c.table_name = s.table_name
                 AND c.COLUMN_NAME = s.column_name)
ORDER BY column_id

2 샘플2[편집]

-- 1. sql 생성
SELECT TABLE_NAME Y
        ,0 X
        ,'CREATE TABLE ' ||RTRIM(TABLE_NAME) ||'('
  FROM DBA_TABLES
 WHERE OWNER = 'FDS330'
UNION
SELECT TC.TABLE_NAME Y
     , COLUMN_ID X
     , RTRIM(DECODE(COLUMN_ID,1,NULL,','))
       || RTRIM(COLUMN_NAME)|| ' '
       || RTRIM(DATA_TYPE)
       || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,'('),'('))
-- || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'CHAR',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'NVARCHAR2',DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)
       || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'RAW',DATA_LENGTH,'BLOB',DATA_LENGTH,'CLOB',DATA_LENGTH,'CHAR',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'VARCHAR2',DATA_LENGTH,'NVARCHAR2',DATA_LENGTH,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,TO_CHAR(DATA_PRECISION)
       || ','
       || TO_CHAR(DATA_SCALE)),'LONG',NULL,'******ERROR'))
       || RTRIM(DECODE(DATA_TYPE,'DATE',NULL,'LONG',NULL,'NUMBER',DECODE(TO_CHAR(DATA_PRECISION),NULL,NULL,')'),')'))
       || ' '
       || RTRIM(DECODE(NULLABLE,'N','NOT NULL',NULL))
  FROM DBA_TAB_COLUMNS TC
     , DBA_OBJECTS O
 WHERE O.OWNER = TC.OWNER
   AND O.OBJECT_NAME = TC.TABLE_NAME
   AND TC.TABLE_NAME NOT LIKE 'BIN%'
   AND O.OBJECT_TYPE = 'TABLE'
   AND O.OWNER = 'FDS330'
UNION
SELECT A.TABLE_NAME Y
      ,999999 X
      ,')' || CHR(10)
            -- ||' STORAGE(' || CHR(10)
            -- ||' INITIAL ' || INITIAL_EXTENT || CHR(10)
            -- ||' NEXT ' || NEXT_EXTENT || CHR(10)
            -- ||' MINEXTENTS ' || MIN_EXTENTS || CHR(10)
            -- ||' MAXEXTENTS ' || MAX_EXTENTS || CHR(10)
            -- ||' PCTINCREASE '|| PCT_INCREASE || ')' ||CHR(10)
            -- ||' INITRANS ' || INI_TRANS || CHR(10)
            -- ||' MAXTRANS ' || MAX_TRANS || CHR(10)
            -- ||' PCTFREE ' || PCT_FREE || CHR(10)
            -- ||' PCTUSED ' || PCT_USED || CHR(10)
            -- ||' PARALLEL (DEGREE ' || DEGREE || ') ' || CHR(10)
            -- ||' TABLESPACE ' || RTRIM(TABLESPACE_NAME) ||CHR(10)
            ||';'||CHR(10)||CHR(10)
            || 'COMMENT ON TABLE ' || A.TABLE_NAME || ' IS '||' '''|| B.COMMENTS ||''''
            ||';'||CHR(10)||CHR(10)
  FROM DBA_TABLES A
    , DBA_TAB_COMMENTS B
 WHERE A.TABLE_NAME = B.TABLE_NAME
   AND A.OWNER = B.OWNER
   AND A.TABLE_NAME NOT LIKE 'BIN%'
   AND A.OWNER = 'FDS330'
 ORDER BY 1,2
;

-- 2.comment 생성
SELECT 'COMMENT ON COLUMN ' || 'FDS330'||'.'||C.TABLE_NAME||'.'||COLUMN_NAME || ' IS ' || ''''|| COMMENTS ||'''' || ';'
  FROM DBA_COL_COMMENTS C
 WHERE OWNER = 'FDS330'
   AND C.TABLE_NAME NOT LIKE 'BIN%'
 ;