113 lines
3.0 KiB
Markdown
113 lines
3.0 KiB
Markdown
|
```SQL
|
|||
|
|
|
|||
|
|
truncate table tbl_info
|
|||
|
|
|
|||
|
|
insert into tbl_info
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
select distinct tbl_id, tbl_user, tbl_id, tbl_nm, tbl_desc
|
|||
|
|
from tbl_info dde
|
|||
|
|
order by cast(seq as integer)
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- 테이블 삭제
|
|||
|
|
select distinct concat('Drop table ', tbl_user, '.', tbl_id, ';') as drpotbl
|
|||
|
|
from tbl_info dde
|
|||
|
|
order by cast(seq as integer)
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- 테이블 생성
|
|||
|
|
select tnm, ty, nullyn, def, com
|
|||
|
|
from (
|
|||
|
|
select distinct 0 as sor
|
|||
|
|
, 0 as idx
|
|||
|
|
, tbl_id
|
|||
|
|
, concat('Create table ', tbl_user, '.', tbl_id, ' (') as tnm
|
|||
|
|
, '' ty
|
|||
|
|
, '' nullyn
|
|||
|
|
, '' def
|
|||
|
|
, '' com
|
|||
|
|
from tbl_info dde
|
|||
|
|
union all
|
|||
|
|
select 1 as sor
|
|||
|
|
, seq as idx
|
|||
|
|
, tbl_id
|
|||
|
|
, col_id as col_lend
|
|||
|
|
, concat(col_ty, (case when col_len is null then '' else concat('(', col_len, ')') end )) as ty
|
|||
|
|
, case when defv is not null then concat( ' default '
|
|||
|
|
, case when col_ty = 'VARCHAR'
|
|||
|
|
then concat('''', defv, '''') -- varchar인경우
|
|||
|
|
when col_ty = 'TIMESTAMP' and instr(defv, ' \n') > 0 -- 하드코딩
|
|||
|
|
then concat('to_date(''', replace(defv, ' \n', ''), '''', ', ''yyyy-mm-dd hh24:mi:ss'')')
|
|||
|
|
when col_ty = 'NUMBER'
|
|||
|
|
then replace(defv, ',', '') -- 천단위 콤마제거
|
|||
|
|
else nvl(defv, '')
|
|||
|
|
end
|
|||
|
|
) else null end as def
|
|||
|
|
, case when nullyn = 'O' then ' Not NULL ' else '' end as nullyn
|
|||
|
|
, ',' as com
|
|||
|
|
from tbl_info dde
|
|||
|
|
union all
|
|||
|
|
select distinct 2 as sor
|
|||
|
|
, 9999 as idx
|
|||
|
|
, tbl_id
|
|||
|
|
, ' ); \n\n' as tnm
|
|||
|
|
, '' ty
|
|||
|
|
, '' nullyn
|
|||
|
|
, '' def
|
|||
|
|
, '' com
|
|||
|
|
from tbl_info dde
|
|||
|
|
) c
|
|||
|
|
-- where tbl_id = 'SACP_MOTION'
|
|||
|
|
order by tbl_id, sor, idx
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- pk
|
|||
|
|
SELECT concat('ALTER TABLE ',
|
|||
|
|
tbl_user,
|
|||
|
|
'.',
|
|||
|
|
tbl_id,
|
|||
|
|
' ADD CONSTRAINT PK_',
|
|||
|
|
tbl_id,
|
|||
|
|
' PRIMARY KEY (',
|
|||
|
|
GROUP_CONCAT(col_id SEPARATOR ', '),
|
|||
|
|
');' ) AS tbl_pk
|
|||
|
|
from tbl_info dde
|
|||
|
|
where pkyn = 'O'
|
|||
|
|
-- and tbl_id = 'SACP_MOTION'
|
|||
|
|
group by tbl_id
|
|||
|
|
order by cast(tbl_no as integer)
|
|||
|
|
|
|||
|
|
-- TABLE COMMENT
|
|||
|
|
select distinct concat('COMMENT ON table ',
|
|||
|
|
tbl_user, '.', tbl_id,
|
|||
|
|
' IS ',
|
|||
|
|
'''',
|
|||
|
|
tbl_nm,
|
|||
|
|
nvl2(tbl_desc, ' (', ''),
|
|||
|
|
tbl_desc,
|
|||
|
|
nvl2(tbl_desc, ')', ''),
|
|||
|
|
'''',
|
|||
|
|
';') as col_comment
|
|||
|
|
from tbl_info dde
|
|||
|
|
where tbl_id = 'SACP_MOTION'
|
|||
|
|
order by cast(seq as integer)
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
-- COLUMN COMMENT
|
|||
|
|
select *
|
|||
|
|
from (
|
|||
|
|
select concat('COMMENT ON COLUMN ',
|
|||
|
|
concat(tbl_user, '.', tbl_id,'.', col_id),
|
|||
|
|
' IS ',
|
|||
|
|
concat('''', concat(col_nm, nvl2(replace(bigo, '''', '＇'), ' (', ''), replace(nvl(bigo,''), '''', '＇'), nvl2(bigo, ')', '')), '''')
|
|||
|
|
, ';') as col_comment
|
|||
|
|
from tbl_info dde
|
|||
|
|
order by cast(seq as integer)
|
|||
|
|
) a
|
|||
|
|
where col_comment is not null
|
|||
|
|
|
|||
|
|
```
|