테이블로 VO, Insert, Update문 만들기

1. VO 만들기

--VO 만들기

select
    'private String ' || lower(SUBSTR(a.COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(a.COLUMN_NAME)), '_',''), 2) || ' = "";'
    || '    //' || NVL(b.COMMENTS, '')
    as 빈소스
FROM USER_TAB_COLS a
   , USER_COL_COMMENTS b
WHERE a.TABLE_NAME = b.TABLE_NAME
and a.COLUMN_NAME = b.COLUMN_NAME
AND a.TABLE_NAME=upper('TB_BOARD');

 

2. SELECT

--SELECT 만들기
select  decode(rownum, 1, 'SELECT ' ||  CHR(13))
     || '' || upper(COLUMN_NAME)     
     || ' as ' ||lower(SUBSTR(COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(COLUMN_NAME)), '_',''), 2)    
     || decode(rownum, (COUNT(*) OVER()), CHR(13)
            || ' FROM '|| TABLE_NAME ||'  ' ||'  ;', ',   ')
    as 조회쿼리
FrOm USER_TAB_COLS --ALL_TAB_COLS, USER_TAB_COLS
where  TABLE_NAME=upper('TB_BOARD');-- and OWNER='DBKIM';

 

3. INSERT

--INSERT 쿼리 만들기
select
    TABLE_NAME,
    '          '||decode(rownum, 1,'',',')||COLUMN_NAME as 컬럼,
    '          '||decode(rownum, 1,'',',')||'#'||lower(SUBSTR(COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(COLUMN_NAME)), '_',''), 2) || '#' as 세팅부
FrOm USER_TAB_COLS
where  TABLE_NAME=upper('TB_BOARD');

 

4. UPDATE

--UPDATE 쿼리 만들기
select
    TABLE_NAME||' Z',
    '          '||'<isNotEmpty prepend="," property="'||lower(SUBSTR(COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(COLUMN_NAME)), '_',''), 2)||'">'||
    'Z.'||COLUMN_NAME || '='||
    '#'||lower(SUBSTR(COLUMN_NAME, 1,1))||SUBSTR(replace(INITCAP(lower(COLUMN_NAME)), '_',''), 2) || '#' ||
    '</isNotEmpty>'
     as 수정세팅부
FrOm USER_TAB_COLS
where  TABLE_NAME=upper('TB_BOARD');