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
|
||
|
||
``` |