"오라클 DDL 생성 스트립트"의 두 판 사이의 차이
DB CAFE
(→샘플2) |
(→oracle ddl 생성스크립트) |
||
(같은 사용자의 중간 판 2개는 보이지 않습니다) | |||
1번째 줄: | 1번째 줄: | ||
=== oracle ddl 생성스크립트 === | === oracle ddl 생성스크립트 === | ||
− | + | ==== show schema of a table ==== | |
+ | <SOURCE LANG=SQL> | ||
+ | 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 | ||
+ | </SOURCE> | ||
=== 샘플2 === | === 샘플2 === | ||
− | < | + | <source lang=sql> |
-- 1. sql 생성 | -- 1. sql 생성 | ||
SELECT TABLE_NAME Y | SELECT TABLE_NAME Y | ||
66번째 줄: | 144번째 줄: | ||
AND C.TABLE_NAME NOT LIKE 'BIN%' | AND C.TABLE_NAME NOT LIKE 'BIN%' | ||
; | ; | ||
− | </ | + | </source> |
+ | |||
+ | [[category:oracle]] |
2021년 3월 11일 (목) 10:28 기준 최신판
thumb_up 추천메뉴 바로가기
- 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%'
;