"오라클 DDL 생성 스트립트"의 두 판 사이의 차이
DB CAFE
(새 문서: oracle ddl 생성스크립트 -- 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....) |
|||
2번째 줄: | 2번째 줄: | ||
oracle ddl 생성스크립트 | oracle ddl 생성스크립트 | ||
-- 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 | -- 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 === | ||
+ | -- 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%' | ||
+ | ; |
2018년 8월 13일 (월) 19:10 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
oracle ddl 생성스크립트 -- 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[편집]
-- 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%' ;