[Oracle] 테스트 데이터 생성 / myBatis 구문 생성 / 멤버변수 생성

-- SELECT 절

select CASE WHEN ROWNUM = 1 THEN ''

            ELSE                 ','

       END

       ||COL.column_name

       ||' AS '

       ||replace(lower(substr(COL.column_name, 1, 1))||substr(initcap(replace(lower(COL.column_name), '_', ' ')), 2), ' ', '')

       ||' /* '

       ||CMM.COMMENTS

       ||' */'

from all_tab_cols COL

LEFT JOIN ALL_COL_COMMENTS CMM

ON COL.OWNER = CMM.OWNER

AND COL.TABLE_NAME = CMM.TABLE_NAME

AND COL.COLUMN_NAME = CMM.COLUMN_NAME

where COL.owner = '오너명'

and   COL.table_name = '테이블명'

;

-- 멤버 변수 만들기

select 'private String '

       ||replace(lower(substr(COL.column_name, 1, 1))||substr(initcap(replace(lower(COL.column_name), '_', ' ')), 2), ' ', '')

       ||'; /*'

       ||CMM.COMMENTS

       ||' */'

from all_tab_cols COL

LEFT JOIN ALL_COL_COMMENTS CMM

ON COL.OWNER = CMM.OWNER

AND COL.TABLE_NAME = CMM.TABLE_NAME

AND COL.COLUMN_NAME = CMM.COLUMN_NAME

where COL.owner = '오너명'

and   COL.table_name = '테이블명'

;

-- get/set 테스트 값 만들기

select 'obj.get'

       ||replace(initcap(replace(lower(column_name), '_', ' ')), ' ', '')

       ||'();'

from all_tab_cols

where owner = '오너'

and   table_name = '테이블명'

;

-- myBatis insert 조건 생성

select 

         chr(9)

       ||','

       ||'#{'

       ||LOWER(COL.column_name)

       ||'}' 

       ||chr(9)||chr(9)||'/*'

       ||CMM.COMMENTS

       ||' */'

from all_tab_cols COL

LEFT JOIN ALL_COL_COMMENTS CMM

ON COL.OWNER = CMM.OWNER

AND COL.TABLE_NAME = CMM.TABLE_NAME

AND COL.COLUMN_NAME = CMM.COLUMN_NAME

where COL.owner = 'OCMWAS'

and   COL.table_name = 'T_MCE_TRGG_TBL'

;

-- myBatis 수정 조건 생성

select 

         '<if test="'||LOWER(COL.column_name)||'">'

       ||chr(13)

       ||chr(9)

       ||','

       ||COL.column_name

       ||' = #{'

       ||LOWER(COL.column_name)

       ||'} /*'

       ||CMM.COMMENTS

       ||' */'

       ||chr(13)

       ||'</if>'

from all_tab_cols COL

LEFT JOIN ALL_COL_COMMENTS CMM

ON COL.OWNER = CMM.OWNER

AND COL.TABLE_NAME = CMM.TABLE_NAME

AND COL.COLUMN_NAME = CMM.COLUMN_NAME

where COL.owner = '오너'

and   COL.table_name = '테이블명'

;

-- 테스트 값 만들기 List<VO>

select 'select ''빈이름 obj''||ROWNUM||'' = new 빈이름();'''

from dual

union all

select   ',''obj''||ROWNUM||'''

       ||'.set'

       ||replace(initcap(replace(lower(column_name), '_', ' ')), ' ', '')

       ||'("''||'

       ||column_name

       ||'||''");'''

from all_tab_cols

where owner = '오너'

and   table_name = '테이블명'

union all

select ',''list.add(obj''||ROWNUM||'');'''

from dual

union all 

select 'from 테이블명'

from dual

;

-- 테스트 값 만들기 List<Map<?, ?>>

select 'select ''List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();'' from dual; '

       ||'select ''Map<String, Object> obj''||ROWNUM||'' = new HashMap<String, Object>();'''

from dual

union all

select   ',''obj''||ROWNUM||'''

       ||'.put("'

       ||lower(column_name)

       ||'", "''||'

       ||column_name

       ||'||''");'''

from all_tab_cols

where owner = '오너명'

and   table_name = '테이블명'

union all

select ',''list.add(obj''||ROWNUM||'');'''

from dual

union all 

select 'from 테이블명'

from dual

;