[oracle] merge ibatis 자동 생성

Programing/Oracle

My Love Princess 2015. 9. 18. 15:13

insert, update 에 이이서 merge 자동 생성입니다.

수정 보안할점이 보이시면 답변 달아주세요.

SELECT MAX(쿼리)

FROM (SELECT 'MERGE INTO ' || TABLE_NAME || ' USING DUAL ON (' || TRIM(wm_concat(UP_COL) OVER(PARTITION BY TABLE_NAME

ORDER BY COLUMN_ID) ) || ') WHEN MATCHED THEN UPDATE SET ' ||TRIM(wm_concat(UP_COL) OVER(PARTITION BY TABLE_NAME

ORDER BY COLUMN_ID) ) || ' WHEN NOT MATCHED THEN ' 쿼리

FROM (select TABLE_NAME,

COLUMN_ID,

' ' ||COLUMN_NAME || '=' || ' #'||lower(SUBSTR(COLUMN_NAME, 1, 1))||SUBSTR(replace(INITCAP(lower(COLUMN_NAME)), '_', ''), 2) || '#' UP_COL,

'AND ' ||COLUMN_NAME || '=' || ' #'||lower(SUBSTR(COLUMN_NAME, 1, 1))||SUBSTR(replace(INITCAP(lower(COLUMN_NAME)), '_', ''), 2) || '#' WHERE_COL,

' ' ||COLUMN_NAME COLUMN_NAMES,

' #'||lower(SUBSTR(COLUMN_NAME, 1, 1))||SUBSTR(replace(INITCAP(lower(COLUMN_NAME)), '_', ''), 2) || '#' AS COL_VALUES

FrOm USER_TAB_COLS

where TABLE_NAME=upper(:TABLENAMES) ) )

UNION ALL

SELECT MAX(쿼리)

FROM (SELECT 'INSERT (' || TRIM(wm_concat(COLUMN_NAMES) OVER(PARTITION BY TABLE_NAME

ORDER BY COLUMN_ID) )|| ') VALUES (' || TRIM(wm_concat(COL_VALUES) OVER(PARTITION BY TABLE_NAME

ORDER BY COLUMN_ID) ) ||')' 쿼리