1. Mybatis CRUD DML 생성 SQL
/*
경고: 이 쿼리를 실행하기 전에 group_concat_max_len 설정을 확인하고 필요시 늘려야 합니다.
예: SET SESSION group_concat_max_len = 1000000;
이 쿼리는 MySQL 8.0 이상 버전이 필요합니다 (윈도우 함수 사용).
*/
SELECT
-- SELECT Script Generation
CONCAT('SELECT ',
GROUP_CONCAT(DISTINCT
CONCAT('A.', RPAD(T.COLUMN_NAME, T.MAX_LENGTH, ' '), ' AS ', RPAD(T.COLUMN_NAME, T.MAX_LENGTH, ' '), ' -- ', IFNULL(T.COMMENTS, ''), '') -- NULL 코멘트 처리 추가
ORDER BY T.COL_ORDER_POS -- ORDINAL_POSITION 기준 정렬
SEPARATOR '\n , '
),
'\n FROM ', UPPER(#{OWNER}), '.', UPPER(#{TABLE_NAME}), ' A\n WHERE ',
GROUP_CONCAT(DISTINCT
CASE WHEN T.LOG_YN = '0' AND T.PK_YN = '1' THEN CONCAT('A.', T.COLUMN_NAME, ' = ', '#', '{', T.COLUMN_NAME, '}') ELSE NULL END
ORDER BY T.COL_ORDER_POS
SEPARATOR '\n AND '
)
) AS SEL_SCRIPT,
-- INSERT Script Generation
CONCAT('INSERT INTO ', UPPER(#{OWNER}), '.', UPPER(#{TABLE_NAME}), ' (\n ',
GROUP_CONCAT(DISTINCT T.COLUMN_NAME ORDER BY T.COL_ORDER_POS SEPARATOR '\n , '),
'\n) VALUES (\n ',
GROUP_CONCAT(DISTINCT
CASE
WHEN T.COLUMN_NAME IN ('INPT_DTTM', 'UPDT_DTTM') THEN 'NOW()' -- MySQL 현재 시간 함수
ELSE CONCAT('#', '{',
CASE
WHEN T.COLUMN_NAME IN ('INPT_ID', 'UPDT_ID') THEN 'SYSTEM_ID'
WHEN T.COLUMN_NAME IN ('INPT_PBIP', 'UPDT_PBIP') THEN 'SYSTEM_PBIP'
WHEN T.COLUMN_NAME IN ('INPT_PROG', 'UPDT_PROG') THEN 'SYSTEM_PROG'
ELSE T.COLUMN_NAME
END,
'}')
END
ORDER BY T.COL_ORDER_POS SEPARATOR '\n , '
),
'\n)'
) AS INS_SCRIPT,
-- UPDATE Script Generation
CONCAT('UPDATE ', #{OWNER}, '.', #{TABLE_NAME}, '\n SET ',
GROUP_CONCAT(DISTINCT
CASE
WHEN T.COLUMN_NAME NOT IN ('INPT_ID', 'INPT_PBIP', 'INPT_DTTM', 'INPT_PROG') AND T.PK_YN = '0' -- 입력 관련 컬럼과 PK 제외
THEN CONCAT(RPAD(T.COLUMN_NAME, T.MAX_LENGTH, ' '), ' = ',
CASE
WHEN T.COLUMN_NAME IN ('INPT_DTTM', 'UPDT_DTTM') THEN 'NOW()' -- MySQL 현재 시간 함수
ELSE CONCAT('#', '{',
CASE
WHEN T.COLUMN_NAME IN ('INPT_ID', 'UPDT_ID') THEN 'SYSTEM_ID'
WHEN T.COLUMN_NAME IN ('INPT_PBIP', 'UPDT_PBIP') THEN 'SYSTEM_PBIP'
WHEN T.COLUMN_NAME IN ('INPT_PROG', 'UPDT_PROG') THEN 'SYSTEM_PROG'
ELSE T.COLUMN_NAME
END,
'}')
END
) ELSE NULL END
ORDER BY T.COL_ORDER_POS SEPARATOR '\n , '
),
'\n WHERE ',
GROUP_CONCAT(DISTINCT
CASE WHEN T.PK_YN = '1' THEN CONCAT(T.COLUMN_NAME, ' = ', '#', '{', T.COLUMN_NAME, '}') ELSE NULL END
ORDER BY T.COL_ORDER_POS SEPARATOR '\n AND '
)
) AS UPD_SCRIPT,
-- DELETE Script Generation
CONCAT('DELETE \n FROM ', #{OWNER}, '.', #{TABLE_NAME}, '\n WHERE ',
GROUP_CONCAT(DISTINCT
CASE WHEN T.PK_YN = '1' THEN CONCAT(T.COLUMN_NAME, ' = ', '#', '{', T.COLUMN_NAME, '}') ELSE NULL END
ORDER BY T.COL_ORDER_POS SEPARATOR '\n AND '
)
) AS DEL_SCRIPT
FROM (
-- Inner Subquery (Metadata Retrieval - MySQL version)
SELECT
A.ORDINAL_POSITION AS COL_ORDER_POS, -- 컬럼 순서 (GROUP_CONCAT 정렬용)
A.TABLE_SCHEMA, -- Oracle OWNER 대체
A.TABLE_NAME,
A.COLUMN_NAME,
A.COLUMN_COMMENT AS COMMENTS, -- Oracle COMMENTS 대체
IF(K.CONSTRAINT_NAME = 'PRIMARY', '1', '0') AS PK_YN, -- PK 여부 확인
CASE WHEN A.COLUMN_NAME IN ('INPT_ID', 'INPT_PBIP', 'INPT_DTTM', 'INPT_PROG', 'UPDT_ID', 'UPDT_PBIP', 'UPDT_DTTM', 'UPDT_PROG') THEN '1' ELSE '0' END AS LOG_YN,
-- 최대 컬럼명 길이 계산 (윈도우 함수, MySQL 8.0+ 필요)
MAX(CASE WHEN A.COLUMN_NAME IN ('INPT_ID', 'INPT_PBIP', 'INPT_DTTM', 'INPT_PROG', 'UPDT_ID', 'UPDT_PBIP', 'UPDT_DTTM', 'UPDT_PROG') THEN 0 ELSE LENGTH(A.COLUMN_NAME) END)
OVER (PARTITION BY A.TABLE_SCHEMA, A.TABLE_NAME) AS MAX_LENGTH
FROM information_schema.COLUMNS A
LEFT JOIN information_schema.KEY_COLUMN_USAGE K -- PK 정보 조인
ON A.TABLE_SCHEMA = K.TABLE_SCHEMA
AND A.TABLE_NAME = K.TABLE_NAME
AND A.COLUMN_NAME = K.COLUMN_NAME
AND K.CONSTRAINT_NAME = 'PRIMARY' -- PK 제약조건 필터
WHERE A.TABLE_SCHEMA = #{OWNER}
AND A.TABLE_NAME = #{TABLE_NAME}
) T
2. 활용예시
SELECT A.AUTH_CD AS AUTH_CD -- 권한코드
, A.UP_AUTH_CD AS UP_AUTH_CD -- 상위권한코드
, A.AUTH_NM AS AUTH_NM -- 권한명
, A.AUTH_GBCD AS AUTH_GBCD -- 권한구분코드
, A.IDNT_TARG_GBCD AS IDNT_TARG_GBCD -- 신분대상구분코드
, A.NOTE AS NOTE -- 비고
, A.INPT_ID AS INPT_ID -- 입력ID
, A.INPT_PBIP AS INPT_PBIP -- 입력공인IP
, A.INPT_VTIP AS INPT_VTIP -- 입력사설IP
, A.INPT_MAC AS INPT_MAC -- 입력MAC
, A.INPT_DTTM AS INPT_DTTM -- 입력일시
, A.INPT_PROG AS INPT_PROG -- 입력프로그램
, A.UPDT_ID AS UPDT_ID -- 수정ID
, A.UPDT_PBIP AS UPDT_PBIP -- 수정공인IP
, A.UPDT_VTIP AS UPDT_VTIP -- 수정사설IP
, A.UPDT_MAC AS UPDT_MAC -- 수정MAC
, A.UPDT_DTTM AS UPDT_DTTM -- 수정일시
, A.UPDT_PROG AS UPDT_PROG -- 수정프로그램
FROM OWNER.CS_AUTH A
WHERE A.AUTH_CD = #{AUTH_CD}
INSERT INTO OWNER.CS_AUTH (
AUTH_CD
, UP_AUTH_CD
, AUTH_NM
, AUTH_GBCD
, IDNT_TARG_GBCD
, NOTE
, INPT_ID
, INPT_PBIP
, INPT_VTIP
, INPT_MAC
, INPT_DTTM
, INPT_PROG
, UPDT_ID
, UPDT_PBIP
, UPDT_VTIP
, UPDT_MAC
, UPDT_DTTM
, UPDT_PROG
) VALUES (
#{AUTH_CD}
, #{UP_AUTH_CD}
, #{AUTH_NM}
, #{AUTH_GBCD}
, #{IDNT_TARG_GBCD}
, #{NOTE}
, #{SYSTEM_ID}
, #{SYSTEM_PBIP}
, #{INPT_VTIP}
, #{INPT_MAC}
, NOW()
, #{SYSTEM_PROG}
, #{UPDT_VTIP}
, #{UPDT_MAC}
)
UPDATE OWNER.CS_AUTH
SET UP_AUTH_CD = #{UP_AUTH_CD}
, AUTH_NM = #{AUTH_NM}
, AUTH_GBCD = #{AUTH_GBCD}
, IDNT_TARG_GBCD = #{IDNT_TARG_GBCD}
, NOTE = #{NOTE}
, INPT_VTIP = #{INPT_VTIP}
, INPT_MAC = #{INPT_MAC}
, UPDT_ID = #{SYSTEM_ID}
, UPDT_PBIP = #{SYSTEM_PBIP}
, UPDT_VTIP = #{UPDT_VTIP}
, UPDT_MAC = #{UPDT_MAC}
, UPDT_DTTM = NOW()
, UPDT_PROG = #{SYSTEM_PROG}
WHERE AUTH_CD = #{AUTH_CD}
DELETE
FROM OWNER.CS_AUTH
WHERE AUTH_CD = #{AUTH_CD}'DB > MYSQL' 카테고리의 다른 글
| [MYSQL] ORACLE의 TABLE FUNCTION 구현 (0) | 2025.04.22 |
|---|