Oracle-MSSQL 변환가이드 2017.06.09 이씨스 기술연구소 선임 김태용.

Presentation on theme: "Oracle-MSSQL 변환가이드 2017.06.09 이씨스 기술연구소 선임 김태용."— Presentation transcript:

1 Oracle-MSSQL 변환가이드 이씨스 기술연구소 선임 김태용

2 함수 NVL -> ISNULL SUBSTR -> SUBSTRING CONCAT or || -> +
함수설명 오라클 함수명 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(길이), 숫자)  NVL -> ISNULL SUBSTR -> SUBSTRING CONCAT or || -> + LPAD -> UF_LPAD( 사용자 정의 함수 생성필요 ) RPAD -> UF_RPAD( 사용자 정의 함수 생성필요 ) LENGTH -> LEN TO_CHAR(숫자) -> CONVERT(VARCHAR(길이), 숫자)  TO_NUMBER(문자열) -> CONVERT(INT,문자열) or CAST(문자열 AS INT) DECODE(컬럼명, 'A', 'B') -> CASE 컬럼명 WHEN 'A' THEN 'B' ELSE 'C' END TO_CHAR(DATE타입 컬럼, 'YYYY-MM-DD') -> CONVERT(CHAR(10), DATE타입 컬럼,126)

3 함수 함수설명 오라클 함수명 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, ' :10:01.000') CONVERT (DATETIME, ' :10:01') CONVERT (DATETIME, ' ') 문자열을 DATE 타입으로 변경해주기 TO_DATE(STR, 'YYYYMMDD') CONVERT (DATETIME, '2014/12/16') CONVERT (DATETIME, ' ') TO_DATE(STR, 'YYYYMMDDHH24MISS') CONVERT(DATETIME, STUFF(STUFF(STUFF(' ',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,':') ADD_MONTHS(SYSDATE, 3) DATEADD(month, 3, GETDATE())

4 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')) TO_CHAR((SYSDATE - 210), 'YYYYMMDD') CONVERT(VARCHAR, GETDATE()-210, 112)

5 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 /* 위와 같이 _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( 로 변환한다. 끝 TRIM(문자열) RTRIM(LTRIM( 문자열 ) )

6 TO_CHAR(DATE타입 컬럼, 'YYYY-MM-DD') -> CONVERT(CHAR(10), DATE타입 컬럼,126)
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008 SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy SELECT convert(varchar, getdate(), 106) -- dd mon yyyy SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

7 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

8 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Description 1 접속정보 입력 2 연결진행중 로그 3 마이그레이션 할 대상 DB 4 접속시도중 로그 5 진행률(더디게 보여도 동작하니 기다려야함) 실제 예시 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)

9 쿼리문 일괄 변경하기 변경 대상검색하기

10 쿼리문 일괄 변경하기 똑같은 대상 확인하면 해당 키워드(아래와 같이)라인 통째로 복사한다.
똑같은 대상 확인하면 해당 키워드(아래와 같이)라인 통째로 복사한다. TO_CHAR(a.FRST_REGIST_PNTTM, 'YYYY-MM-DD') as FRST_REGIST_PNTTM 위의 키워드로 다시 검색한다.

11 쿼리문 일괄 변경하기 검색된 결과를 아래의 키워드로 모두 일결변경하기
CONVERT(VARCHAR(10), a.FRST_REGIST_PNTTM,120) as FRST_REGIST_PNTTM

12 쿼리문 일괄 변경하기 마찬가지로 TO_CHAR(a.FRST_REGIST_PNTTM, 'YYYY-MM-DD HH:MI:SS')  CONVERT(VARCHAR, a.FRST_REGIST_PNTTM,120) 로 변경함. 그담에 또 덜변경된 걸 찾아야하니 TO_CHAR로 검색하고, 위 과정을 반복함.

13 Mybatis 변경하기 ibatis Mybatis
<isEqual prepend="AND" property="searchCondition" compareValue="1"> TEST = #searchCondition# </isEqual> <if test="searchCondition== 1"> AND TEST = #{searchCondition} </if> <typeAlias alias="egovMap" type="egovframework.rte.psl.dataaccess.util.EgovMap" /> Mybatis-config.xml 파일내에 이동하여 반드시 정의 <configuration> <typeAliases> </typeAliases> </configuration> <sqlMap namespace="SysLogDAO"> </sqlMap> <mapper namespace="SysLogDAO"> </mapper> <resultMap id="SysLogVO" class="egovframework.com.sym.log.lgm.service.SysLog"> <resultMap id="SysLogVO" type="egovframework.com.sym.log.lgm.service.SysLog"> parameterClass , resultClass parameterType , resultMap #something# , $something$ #{something} , ${something} <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" " <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "

14 Mybatis 변경하기 ibatis Mybatis columnIndex="8“
…. 등으로 검색하여 일괄 삭제하는게 빠름(폴더 범위 지정하여 ex) selected resources) 삭제해야함. 더 이상 안씀 , 아래와 같이 맵핑을 하삼. <result property="userSe" column="userSe"/>

15 이외에도 차이점이 있을 것이나, 제가 변환하면서 필요한 내용을 정리한 것입니다. 도움이 되었으면 좋겠습니다.
 Oracle to MSSQL 쿼리 변환 시 검토할 점 : 참고 사이트 : 15. TRUNC(Number) 함수는 ROUND(Number, 0, 1)로 대체 16. 숫자 연산 시 MSSQL은 기본이 Integer 형이므로 소수점 이하를 표시하기 위해서는 DECIAML로 캐스팅 해줘야 함     예) SELECT 10 / 3 => 3           SELECT 10 / CASE(3 AS DECIMAL) => 17. MSSQL에서는 'YYYYMMDDHH24MISS' 형식의 날짜 포멧이 지원되지 않음 18. (+) 형태의 구문은 OUTER JOIN 형태로 대체     예) ORACLE - SELECT * FROM Table A, Table B WHERE A.Field = B.Field(+)           MSSQL  - SELECT * FROM Table A LEFT OUTER JOIN Table B ON A.Field = B.Field 19. ORDER BY 결과에 NULL 값의 위치가 다름     예) ORACLE - ORDER BY Field 결과에 NULL 값이 가장 나중에 조회           MSSQL  - ORDER BY Field 결과에 NULL 값이 가장 빠르게 조회 20. TO_CHAR 함수는 CONVERT로 변환     예) ORACLE - TO_CHAR(Number)           MSSQL  - CONVERT(VARCHAR, Number) 이외에도 차이점이 있을 것이나, 제가 변환하면서 필요한 내용을 정리한 것입니다. 도움이 되었으면 좋겠습니다. 1. Sub Query에 Alies는 필수 2. Sub Query 내에 ORDER BY 절을 사용할 수 없음 3. NVL 함수는 ISNULL로 대체 4. SUBSTR 함수는 SUBSTRING으로 대체 5. 문자열 연결 ||는 +로 대체 6. TRIM 함수는 LTRIM(RTRIM(FieldName))으로 대체 7. STDDEV 함수는 STDEV로 대체 8. CEIL 함수는 CEILING으로 대체 9. ROWNUM     - ORDER BY 절 없이 WHERE 절에서 ROWNUM = 1 사용 시 SELECT 절에서 Top1으로 대체      - ORDER BY  절 사용 시, SELECT 절에 ROWNUMBER() OVER (ORDER BY Field1...FieldN) 으로 대체 10. MOD 함수는 %로 대체     예) ORACLE - MOD(10, 3)           MSSQL  - 10%3 11. MINUS 함수는 EXCEPT로 대체 12. DECODE 문은 CASE...WHEN...THEN 문으로 대체 13. LENGTH 함수는 LEN으로 대체 14. ROUND 함수는 두 번째 자리수까지 써줘야 됨     예) ORACLE - ROUND(Number) 가능.            MSSQL  - ROUND(Number, 0)까지 써줘야 함

16 끝 감사합니다.

17 샘플 샘플1 샘플2 샘플3 샘플4 Description 1 접속정보 입력 2 연결진행중 로그 3 마이그레이션 할 대상 DB 4
5 6 7 8 9 10 11 12 13 14 Description 1 접속정보 입력 2 연결진행중 로그 3 마이그레이션 할 대상 DB 4 접속시도중 로그 5 진행률(더디게 보여도 동작하니 기다려야함) 샘플 샘플1 샘플2 샘플3 샘플4