[oracle] 유용한 쿼리문 만들기

[oracle] 유용한 쿼리문 만들기 

 

 

--BEAN CLASS 만들기

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('ez_user');

 

--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('CORP_OPER_DAY');-- and OWNER='MYEZWEL';

 

--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('EZ_EC_ACTING');

 

--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('EZ_EC_ACTING');

 

출처 : ilovehsk.tistory.com/106