1. Mybatis CRUD DML 생성 SQL
SELECT /*+ RULE */
SELECT_SCRIPT
, INSERT_SCRIPT
, UPDATE_SCRIPT
, DELETE_SCRIPT
FROM (
SELECT /* SELECT */
TO_CLOB('SELECT ') || SUBSTR(XMLAGG(XMLELEMENT(X, '', CHR(13) || ' , ' || ('A.' || RPAD(COLUMN_NAME, MAX_LENGTH, ' ')) || ' AS ' || RPAD(COLUMN_NAME, MAX_LENGTH, ' ') || ' /* ' || COMMENTS || ' */') ORDER BY COL_MAX_ID).EXTRACT('//text()').GETCLOBVAL(), 9)
|| TO_CLOB(CHR(13) || ' FROM ' || MAX(OWNER) || '.'|| MAX(TABLE_NAME) || ' A' || CHR(13) || ' WHERE ')
|| SUBSTR(XMLAGG(XMLELEMENT(X, '', CASE WHEN LOG_YN = '0' AND PK_YN = '1' THEN CHR(13) || ' AND ' || 'A.' || RPAD(COLUMN_NAME, PK_MAX_LENGTH, ' ') ||' = '|| '#'||'{'|| COLUMN_NAME || '}' END) ORDER BY COL_MAX_ID).EXTRACT('//text()').GETCLOBVAL(), 9)
|| TO_CLOB(CHR(13) || ' ORDER BY ')
|| SUBSTR(XMLAGG(XMLELEMENT(X, '', CASE WHEN PK_YN = '1' THEN CHR(13) || ' , A.' || RPAD(COLUMN_NAME, PK_MAX_LENGTH, ' ') END) ORDER BY COL_MAX_ID).EXTRACT('//text()').GETCLOBVAL(), 12)
AS SELECT_SCRIPT
/* INSERT */
, TO_CLOB('INSERT INTO ' || MAX(OWNER) || '.'|| MAX(TABLE_NAME) ||' ('||CHR(13))
|| ' ' || SUBSTR(XMLAGG(XMLELEMENT(X, '', CHR(13) || ' , ' || COLUMN_NAME) ORDER BY COL_MAX_ID).EXTRACT('//text()').GETCLOBVAL(), 6)
|| TO_CLOB(CHR(13)||') VALUES ('||CHR(13))
|| ' ' || SUBSTR(XMLAGG(XMLELEMENT(X, '', CHR(13) || ' , ' || CASE WHEN COLUMN_NAME IN ('INPT_DTTM', 'UPDT_DTTM')
THEN 'SYSDATE'
ELSE '#'||'{'|| CASE WHEN COLUMN_NAME IN ('INPT_ID' , 'UPDT_ID') THEN 'SYSTEM_ID'
WHEN COLUMN_NAME IN ('INPT_PBIP', 'UPDT_PBIP') THEN 'SYSTEM_PBIP'
WHEN COLUMN_NAME IN ('INPT_PROG', 'UPDT_PROG') THEN 'SYSTEM_PROG'
ELSE COLUMN_NAME
END
|| '}'
END) ORDER BY COL_MAX_ID).EXTRACT('//text()').GETCLOBVAL(), 6)
|| CHR(13)||')'
AS INSERT_SCRIPT
/* UPDATE */
, TO_CLOB('UPDATE ' || MAX(OWNER) || '.'|| MAX(TABLE_NAME) || CHR(13)
||' SET ')
|| SUBSTR(XMLAGG(XMLELEMENT(X, '', CASE WHEN COLUMN_NAME NOT IN ('INPT_ID', 'INPT_PBIP', 'INPT_DTTM', 'INPT_PROG') AND PK_YN = '0'
THEN CHR(13) || ' , ' || RPAD(COLUMN_NAME, MAX_LENGTH, ' ') || ' = ' || CASE WHEN COLUMN_NAME IN ('INPT_DTTM', 'UPDT_DTTM')
THEN 'SYSDATE'
ELSE '#'||'{' || CASE WHEN COLUMN_NAME IN ('INPT_ID' , 'UPDT_ID') THEN 'SYSTEM_ID'
WHEN COLUMN_NAME IN ('INPT_PBIP', 'UPDT_PBIP') THEN 'SYSTEM_PBIP'
WHEN COLUMN_NAME IN ('INPT_PROG', 'UPDT_PROG') THEN 'SYSTEM_PROG'
ELSE COLUMN_NAME
END
|| '}'
END
END) ORDER BY COL_MAX_ID).EXTRACT('//text()').GETCLOBVAL(), 9)
|| CHR(13)|| ' WHERE '
|| SUBSTR(XMLAGG(XMLELEMENT(X, '', CASE WHEN PK_YN = '1' THEN CHR(13) || ' AND ' || RPAD(COLUMN_NAME, PK_MAX_LENGTH, ' ') || ' = ' || '#'||'{' || COLUMN_NAME || '}' END) ORDER BY COL_MAX_ID).EXTRACT('//text()').GETCLOBVAL(), 9)
AS UPDATE_SCRIPT
/* DELETE */
, TO_CLOB('DELETE '|| CHR(13) || ' FROM ' || MAX(OWNER) || '.'|| MAX(TABLE_NAME)
|| CHR(13)|| ' WHERE ')
|| SUBSTR(XMLAGG(XMLELEMENT(X, '', CASE WHEN PK_YN = '1' THEN CHR(13) || ' AND ' || RPAD(COLUMN_NAME, PK_MAX_LENGTH, ' ') || ' = ' || '#'||'{' || COLUMN_NAME || '}' END) ORDER BY COL_MAX_ID).EXTRACT('//text()').GETCLOBVAL(), 9)
AS DELETE_SCRIPT
FROM (
SELECT A.OWNER AS OWNER
, A.COLUMN_ID AS COLUMN_ID
, A.TABLE_NAME AS TABLE_NAME
, A.COLUMN_NAME AS COLUMN_NAME
, LENGTH(A.COLUMN_NAME) AS COLUMN_LENGTH
, A.DATA_TYPE AS DATA_TYPE
, A.COLUMN_ID AS COL_MAX_ID
, CASE WHEN C.COLUMN_NAME IS NOT NULL THEN '1' ELSE '0' END AS PK_YN
, COMMENTS AS COMMENTS
, 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
, 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_NAME) AS MAX_LENGTH
, MAX(CASE WHEN A.COLUMN_NAME IN ('INPT_ID', 'INPT_PBIP', 'INPT_DTTM', 'INPT_PROG'
,'UPDT_ID', 'UPDT_PBIP', 'UPDT_DTTM', 'UPDT_PROG')
OR C.COLUMN_NAME IS NULL
THEN 0
ELSE LENGTH(A.COLUMN_NAME)
END) OVER (PARTITION BY A.TABLE_NAME) AS PK_MAX_LENGTH
FROM ALL_TAB_COLUMNS A
LEFT OUTER JOIN ALL_CONSTRAINTS B
ON A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND 'P' = B.CONSTRAINT_TYPE
LEFT OUTER JOIN ALL_CONS_COLUMNS C
ON B.OWNER = C.OWNER
AND B.TABLE_NAME = C.TABLE_NAME
AND A.COLUMN_NAME = C.COLUMN_NAME
AND B.CONSTRAINT_NAME = C.CONSTRAINT_NAME
INNER JOIN ALL_COL_COMMENTS D
ON A.OWNER = D.OWNER
AND A.TABLE_NAME = D.TABLE_NAME
AND A.COLUMN_NAME = D.COLUMN_NAME
WHERE A.OWNER = 'OWNER' -- USER
AND A.TABLE_NAME = 'TABLE_NAME' -- TABLE_NAME
ORDER BY COLUMN_ID
)
)
;
2. 환경에 맞게 변경할 컬럼명
| 컬럼명 | 설명 |
| INPT_ID | 테이블 시스템 컬럼(입력ID) |
| INPT_PBIP | 테이블 시스템 컬럼(입력IP) |
| INPT_DTTM | 테이블 시스템 컬럼(입력일시) |
| INPT_PROG | 테이블 시스템 컬럼(입력프로그램) |
| UPDT_ID | 테이블 시스템 컬럼(수정ID) |
| UPDT_PBIP | 테이블 시스템 컬럼(수정IP) |
| UPDT_DTTM | 테이블 시스템 컬럼( 수정일시) |
| UPDT_PROG | 테이블 시스템 컬럼(수정프로그램) |
| SYSTEM_ID | 프레임워크에서 넣어주는 시스템 ID |
| SYSTEM_PBIP | 프레임워크에서 넣어주는 시스템 IP |
| SYSTEM_PROG | 프레임워크에서 넣어주는 시스템 프로그램명 |
| SYSDATE | 오라클 SYSDATE |
3. 활용예시
SELECT A.ACCN_YEAR AS ACCN_YEAR /* 회계년도 */
, A.ACCN_GBCD AS ACCN_GBCD /* 회계구분코드 */
, A.ACCN_HRSMN AS ACCN_HRSMN /* 회계기수 */
, A.FRDT AS FRDT /* 시작일자 */
, A.TODT AS TODT /* 종료일자 */
, A.DECI_CNRL_DT AS DECI_CNRL_DT /* 결의통제일자 */
, A.NOTE AS NOTE /* 비고 */
, A.INPT_ID AS INPT_ID /* 입력ID */
, A.INPT_PBIP AS INPT_PBIP /* 입력공인IP */
, 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_DTTM AS UPDT_DTTM /* 수정일시 */
, A.UPDT_PROG AS UPDT_PROG /* 수정프로그램 */
FROM ADMI.AA_SCHE_MNG A
WHERE A.ACCN_YEAR = #{ACCN_YEAR}
AND A.ACCN_GBCD = #{ACCN_GBCD}
ORDER BY A.ACCN_YEAR
, A.ACCN_GBCD
INSERT INTO ADMI.AA_SCHE_MNG (
ACCN_YEAR
, ACCN_GBCD
, ACCN_HRSMN
, FRDT
, TODT
, DECI_CNRL_DT
, NOTE
, INPT_ID
, INPT_PBIP
, INPT_DTTM
, INPT_PROG
, UPDT_ID
, UPDT_PBIP
, UPDT_DTTM
, UPDT_PROG
) VALUES (
#{ACCN_YEAR}
, #{ACCN_GBCD}
, #{ACCN_HRSMN}
, #{FRDT}
, #{TODT}
, #{DECI_CNRL_DT}
, #{NOTE}
, #{SYSTEM_ID}
, #{SYSTEM_PBIP}
, SYSDATE
, #{SYSTEM_PROG}
, #{SYSTEM_ID}
, #{SYSTEM_PBIP}
, SYSDATE
, #{SYSTEM_PROG}
)
UPDATE ADMI.AA_SCHE_MNG
SET ACCN_HRSMN = #{ACCN_HRSMN}
, FRDT = #{FRDT}
, TODT = #{TODT}
, DECI_CNRL_DT = #{DECI_CNRL_DT}
, NOTE = #{NOTE}
, UPDT_ID = #{SYSTEM_ID}
, UPDT_PBIP = #{SYSTEM_PBIP}
, UPDT_DTTM = SYSDATE
, UPDT_PROG = #{SYSTEM_PROG}
WHERE ACCN_YEAR = #{ACCN_YEAR}
AND ACCN_GBCD = #{ACCN_GBCD}
DELETE
FROM ADMI.AA_SCHE_MNG
WHERE ACCN_YEAR = #{ACCN_YEAR}
AND ACCN_GBCD = #{ACCN_GBCD}'DB > ORACLE' 카테고리의 다른 글
| [ORACLE] 다단 SELECT SQL (0) | 2025.01.21 |
|---|---|
| [ORACLE] 다른 테이블에서 특정 코드 데이터가 있는지 확인하는 FUNCTION (0) | 2023.09.27 |
| [ORACLE] 텍스트 균등분할(균등정렬) FUNCTION (0) | 2023.08.18 |
| [ORACLE] VIRTUAL COLUMN(가상 열) 예제 (0) | 2023.07.04 |
| [ORACLE] BETWEEN 주의사항 (0) | 2022.07.11 |