전자정부 3.7 ibatis -> Mybatis 변환일지 2탄(oracle -> mssql 변환)

작년에 직접변환하면서 만들었던 가이드 문서이다.

Oracle-MSSQL 변환가이드.pptx

앞서 ibatis를 mybatis로 변경하였고, 이젠 oracle 쿼리문을 모조리 mssql 쿼리문으로 변경하였다.

1. 타입맵핑

2.1 함수

함수설명	오라클 함수명	MSSQL 함수명
Input이 NULL일 경우, 지정한 값으로 세팅한 결과를 반환	NVL	ISNULL
position위치에 있는 char의 문자열부터 length갯수만큼 값을 반환	SUBSTR	SUBSTRING
두 문자열을 연결하여 그 결과를 반환	CONCAT   or    ||	+ (단, SQL 2012버전부터 CONCAT사용가능)
문자열 길이가  N개가 되도록 왼쪽부터 지정한 문자로 채운 결과를 반환	LPAD	REPLICATE('0',반복할사이즈 - LEN(숫자값|컬럼)) + CAST(숫자값|컬럼 AS VARCHAR) 형식으로 변경가능 dbo.LPAD( 사용자 정의 함수 생성필요 ) à
문자열 길이가  N개가 되도록 오른쪽부터 지정한 문자로 채운 결과를 반환	RPAD	CAST(숫자값|컬럼 AS VARCHAR) +REPLICATE('0',반복할사이즈 - LEN(숫자값|컬럼)) 형식으로 변경가능 dbo.RPAD( 사용자 정의 함수 생성필요 ) à
해당 문자열의 문자 수를 반환	LENGTH	LEN
숫자를 문자로 변환	TO_CHAR(숫자)	CONVERT(VARCHAR(길이), 숫자)

2.2 함수

함수설명	오라클 함수명	MSSQL 함수명
문자를 숫자로 변환	TO_NUMBER(문자열)	CONVERT(INT,문자열) or CAST(문자열 AS INT)
비교연산을 통한 참,거짓항목의 값을 반환	DECODE(컬럼명, ‘A’, ‘B‘, ’C’)	CASE 컬럼명 WHEN 'A' THEN 'B' ELSE 'C' END
Date형을 문자열로 반환	TO_CHAR(DATE타입 컬럼, 'YYYY-MM-DD')	CONVERT(CHAR(10), DATE타입 컬럼,126)
DBMS시스템 현재시간	sysdate	GETDATE()
입력문자열 중 가장 우선순위가 큰 문자열 찾기	GREATEST(‘A’,’B’,’C’)	dbo. GREATEST(‘A’,’B’,’C’)
입력문자열 중 가장 우선순위가 낮은 문자열 찾기	LEAST(‘A’,’B’,’C’)	dbo. LEAST(‘A’,’B’,’C’)
문자열을 DATETIME 타입으로 변경해주기	TO_DATE(STR, 'YYYY-MM-DD HH24:MI:SS') TO_DATE(STR, 'YYYY-MM-DD')	CONVERT (DATETIME, '2014-12-16 15:10:01.000') CONVERT (DATETIME, '2014-12-16 15:10:01') CONVERT (DATETIME, '2014-12-16')
문자열을 DATE 타입으로 변경해주기	TO_DATE(STR, 'YYYYMMDD')	CONVERT (DATETIME, '2014/12/16') CONVERT (DATETIME, '2014-12-16') CONVERT (DATETIME, '20141216')
문자열을 DATETIME 타입으로 변경해주기	TO_DATE(STR, 'YYYYMMDDHH24MISS')	CONVERT(DATETIME, STUFF(STUFF(STUFF('20141216 151001',13,0,':'),11,0,':'),9,0,' '))
	TO_CHAR(TO_DATE(EVENT_SVC_BGNDE, 'YYYYMMDD'),'YYYY-MM-DD')	STUFF(STUFF(EVENT_SVC_BGNDE,5,0,':'),8,0,':')

3.. 실제 작업사례1

Oracle	MSSQL
TO_CHAR(a.FRST_REGIST_PNTTM, 'YYYYMMDDHH24MI')	REPLACE(CONVERT(VARCHAR(8), GETDATE(), 112)+CONVERT(VARCHAR(6), GETDATE(), 114), ':','')
TO_CHAR(a.FRST_REGIST_PNTTM, 'YYYYMMDDHH24MISS')	REPLACE(CONVERT(VARCHAR(8), a.FRST_REGIST_PNTTM, 112)+CONVERT(VARCHAR(8), a.FRST_REGIST_PNTTM, 114), ':','')
TO_CHAR(a.FRST_REGIST_PNTTM, 'YYYY-MM-DD HH:MI:SS')	CONVERT(VARCHAR, a.FRST_REGIST_PNTTM,120)
TO_CHAR(a.FRST_REGIST_PNTTM, 'YYYY-MM-DD')	CONVERT(VARCHAR(10), A.FRST_REGIST_PNTTM,120)
TO_CHAR(a.FRST_REGIST_PNTTM, 'YYYYMMDD')	CONVERT(VARCHAR, a.FRST_REGIST_PNTTM,112)
TO_CHAR(c.FRST_REGIST_PNTTM, 'YYYYMMDD')	CONVERT(VARCHAR, c.FRST_REGIST_PNTTM,112)
TO_CHAR(d.FRST_REGIST_PNTTM, 'YYYYMMDD')	CONVERT(VARCHAR, d.FRST_REGIST_PNTTM,112)
TO_CHAR(SYSDATE - 1, 'YYYYMMDD')	CONVERT(VARCHAR, GETDATE()-1,112)
TO_CHAR(sysdate, 'YYYYMMDD')	CONVERT(VARCHAR, GETDATE(),112)
TO_CHAR(sysdate, 'YYYY-MM-DD')	CONVERT(VARCHAR(10), GETDATE(),120)
TO_CHAR(A.FRST_REGIST_PNTTM,'YYYYMMDD')	CONVERT(VARCHAR, A.FRST_REGIST_PNTTM,112)
TO_CHAR(CREAT_DT, 'YYYYMMDD')	CONVERT(VARCHAR, CREAT_DT,112)
TO_CHAR(FRST_REGIST_PNTTM,'YYYYMMDD')	CONVERT(VARCHAR, FRST_REGIST_PNTTM,112)
TO_CHAR(A.FRST_REGIST_PNTTM, 'YYYY')	CONVERT(VARCHAR(4), A.FRST_REGIST_PNTTM,112) --CONVERT(VARCHAR(4), A.FRST_REGIST_PNTTM,120)
TO_CHAR(A.FRST_REGIST_PNTTM, 'YYYY‘MM)	CONVERT(VARCHAR(6), A.FRST_REGIST_PNTTM,112) --CONVERT(VARCHAR(6), A.FRST_REGIST_PNTTM,120)
TO_CHAR(A.LAST_UPDT_PNTTM, 'YYYY-MM-DD HH24:MI:SS')	CONVERT(VARCHAR, A.LAST_UPDT_PNTTM,120)
TO_CHAR(GETDATE(), 'YYYYMMDD'))	CONVERT(VARCHAR, GETDATE(),112)
TO_CHAR((SYSDATE - 210), 'YYYYMMDD')	CONVERT(VARCHAR, GETDATE()-210, 112)

4. 실제 작업사례2

Oracle	MSSQL
rownum rn	ROW_NUMBER() OVER(ORDER BY BATCH_SCHDUL_ID ASC) rn ROW_NUMBER() OVER(ORDER BY XXXXSORTXXXXXXXX ) RNUM
ORDER BY A.BATCH_SCHDUL_ID ASC	삭제하고, 위와 같이 OVER의 인자로 넣어줌 (단, 이때A. 과 같은 alias참조를 제거하고 넣어줌.)
) TB ) WHERE rn BETWEEN #firstIndex# + 1 AND #firstIndex# + #recordCountPerPage#	) TB ) _TMP WHERE rn BETWEEN #firstIndex# + 1 AND #firstIndex# + #recordCountPerPage# /* 위와 같이 _TMP란 alias를 줘야함. */
WHERE ROWNUM < 5	TOP 5
NVL(  로 검색한다.	ISNULL( 로 변환한다. 끝
DECODE(B.EMPLYR_ID, NULL, 'N', 'Y') AS REG_YN	CASE B.EMPLYR_ID WHEN NULL THEN 'N' ELSE 'Y' END AS REG_YN
	CASE WHEN B.EMPLYR_ID IS NULL THEN 'N' ELSE 'Y' END AS REG_YN
dbms_lob.SUBSTRING(  로 검색한다.	SUBSTRING(  로 변환한다. 끝

5. 실제 작업사례3

TO_CHAR(DATE타입 컬럼, 'YYYY-MM-DD') -> CONVERT(CHAR(10), DATE타입 컬럼,126)

•SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

•SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

•SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

•SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

•SELECT convert(varchar, getdate(), 112) -- yyyymmdd

•SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

•SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

•SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

•SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

•SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

6. 실제 작업사례4

•TO_CHAR(A.FRST_REGIST_PNTTM, 'YYYY-MM-DD HH24:MI:SS') AS FRST_REGIST_PNTTM

à CONVERT(VARCHAR, A.FRST_REGIST_PNTTM,120)

 == CONVERT(VARCHAR(19), A.FRST_REGIST_PNTTM,120)

주로 아래의 쿼리변경이 많음, 그래서 이럴땐 그냥

통짜로 바꿀수 있는데까지 바꿔버림. 다음 페이지 참고

CONVERT(VARCHAR, A.LAST_UPDT_PNTTM,120)

CONVERT(VARCHAR, A.FRST_REGIST_PNTTM,120)

CONVERT(VARCHAR, A.CREAT_DT,120)

---

실제 적용한 소스는 아래와 같다.(프로젝트내 DML, DDL도 포함)

• 오라클 버전 https://github.com/ktyuzz/egov3.7_mybatis_jpa_springdatarest

• MSSQL 버전 https://github.com/ktyuzz/egov3.7_mybatis_jpa_springdatarest/tree/oracle2mssql