Oracle to Mysql 마이그레이션 문법

Oracle to Mysql (MariaDB) 쿼리변환 작업

Oracle 쿼리를 Mysql 쿼리로 변환하는 프로젝트에 참여했었다. 작업했던 내용들을 기억하기 위해 작성하는 쿼리 전환 작업 문법 정리

1. NVL() -> IFNULL()

IFNULL( expression1, expression2 ) : expression1이 NULL이 아니면 expression1을, NULL이면 expression2를 반환

• ORACLE

SELECT NVL(USER_ID, ‘’) FROM TABLE

• MYSQL

SELECT IFNULL(USER_ID, ‘’) FROM TABLE

 

2. NVL2() -> CASE WHEN ~ THEN ~ END

• ORACLE

NVL2(문자열,’ ‘)

• MYSQL

CASE WHEN 문자열 IS NULL THEN ‘Y’ ELSE ‘N’ END
CASE WHEN 문자열 IS NOT NULL THEN ‘N’ ELSE ‘Y’ END

 

3. SYSDATE -> SYSDATE(), NOW()

현재 날짜, 시간 확인

• ORACLE

SELECT SYSDATE FROM DUAL

• MYSQL 

SELECT NOW() FROM DUAL

 

4. TO_CHAR() -> DATE_FORMAT()

날짜 포멧 변환

• ORACLE

SELECT TO_CHAR(REG_DATE, ‘YYYYMMDD HH24MISS’) FROM DUAL

• MYSQL

SELECT DATE_FORMAT(REG_DATE,’%Y%m%d%H%i%s’) FROM DUAL

 

5.  요일 변경

요일변환의 숫자범위

• ORACLE

일~토 : 1~7 (결과값: 오늘이 수요일인 경우 4를 반환)

• MYSQL

일~토 : 0~6 (결과값: 오늘이 수요일인 경우 3을 반환)

 

6.  || -> CONCAT()

문자와 문자 합치는 방법

• ORACLE

SELECT USER_ID FORM TABLE WHERE USER_ID LIKE ‘%’ || ‘JIEUN’ || ’%’

• MYSQL

SELECT USER_ID FROM TABLE WHERE USER_ID LIKE CONCAT(‘%’,’JIEUN’,’%’)

 

7.  TO_CHAR, TO_NUMBER -> CAST

형변환 방법

숫자에서 문자

• ORACLE

 SELECT TO_CHAR(123) FROM DUAL

• MYSQL

SELECT CAST(123 AS CHAR) FROM DUAL

문자에서 숫자

• ORACLE 

SELECT TO_NUMBER(‘123’) FROM DUAL

• MYSQL

SELECT CAST(‘123’ AS INTEGER) FROM DUAL

 

8.  ROWNUM -> LIMIT

rownum이 조건으로 사용될 경우 limit 사용

• ORACLE - ROWNUM>=5
• MYSQL - LIMIT 5

rownum이 컬럼으로 사용될 경우 (MariaDB)

• ORACLE - ROWNUM()
• MYSQL - ROW_NUMBER() OVER()

 

9. TRUN() -> DATE()

날짜 데이터 시,분,초 제외 한 년,월,일만 출력

• ORACLE 

TRUN(SYSDATE)

• MYSQL 

TRUNCATE(NOW(),1) ( MariaDB는 DATE(SYSDATE()) )

 

10. 시퀀스.NEXTVAL -> NEXTVAL(‘시퀀스’)

시퀀스의 다음 값 출력

• ORACLE

SELECT SEQ.NEXTVAL FROM DUAL

• MYSQL

SELECT NEXTVAL(‘SEQ’) FROM DUAL (MARIADB는 ‘’ 제거하고 사용)

 

11. TO_DATE() -> STR_TO_DATE()

• ORACLE

TO_DATE(‘18/05/2021 00:00:00’, ‘MM/DD/YYYY HH24:MI:SS’)

• MYSQL

STR_TO_DATE(‘18/05/2021 00:00:00’, ‘%m/%d/%Y %H:%i:%s’)

 

12. Subquery alias

서브쿼리에 alias가 없다면 붙여준다

• ORACLE

SELECT A, B FROM (SELECT A, B FROM TABLE)

• MYSQL

SELECT T.A, T.B FROM (SELECT A, B FROM TABLE) T

 

13. DELETE -> DELETE FROM

ORACLE에서는 FROM을 생략 가능하지만, MYSQL에서는 필수

• ORACLE

DELETE TABLE WHERE ~

• MYSQL

DELETE FROM TABLE WHERE ~

 

14. DECODE문 -> CASE문

문자열을 비교대상과 비교하여 참일 때와 거짓일 때의 값을 표시

• ORACLE - DECODE(문자열,비교대상,참일때 값, 거짓일때 값)
• MYSQL - CASE WHEN 문자열=비교대상 THEN 참일때 값 ELSE 거짓일때 값 END

 

15. SUBSTR() 0 -> 1

시작이 0일 경우 1로 변환

• ORACLE - SUBSTR(문자열,0,3)
• MYSQL - SUBSTR(문자열,1,3)

 

16. 암호화, 복호화

암호화

• ORACLE - DAMO.ENCRYPT_VAR(‘AES128_FULL’,’평문 값’,’’)
• MYSQL - DAMO.ENC_B64(‘AES256_FULL’,’평문 값)

복호화

• ORACLE - DAMO.DECRYPT_VAR(‘AES128_FULL’,’암호화 값’,’’)
• MYSQL - DAMO.DEC_B64(‘AES256_FULL’,’암호화 값’)

 

17. LISTAGG() -> GRUP_CONCAT()

컬럼의 ROW 데이터를 한줄로 나열한 뒤 구분자로 구분하여 표시

• ORACLE

LISTAGG(나열할 컬럼,’구분자’) WITHIN GROUP(ORDER BY 컬럼)

• MYSQL

GROUPB_CONCAT(나열할 컬럼 ORDER BY 컬럼 SEPARATOR ‘구분자’)

 

18. OUTER JOIN 변경

LEFT OUTER JOIN

• ORACLE 

 SELECT * FROM T1,T2 WHERE T1.I1=T2.I2(+)

• MYSQL

SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.I1=T2.I2

RIGHT OUTER JOIN

• ORACLE

SELECT * FROM T1,T2 WHERE T1.I1(+)=T2.I2

• MYSQL

SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.I1=T2.I2

 

19. START WITH CONNECT BY -> RECURSIVE

계층형 함수로 만들기 위해 WITH RECURSIVE를 사용하여 테이블 생성

 

20. MERGE INTO -> INSERT, UPDATE

조건이 맞는지 (MATCHED)에 대한 SELECT 문을 실행시켜서 결과값이 있으면 UPDATE문 실행, 결과값이 없으면 INSERT문 실행

• ORACLE

MERGE INTO TABLE USING DUAL ON (MATCHED 조건) 
THEN 조건이 참일때 수행할 쿼리(INSERT OR UPDATE) 
WHEN NOT MATCHED THEN 조건이 거짓일때 수행할 쿼리 (INSERT OR UPDATE)

• MYSQL 

INSERT문 ON DUPLICATE KEY UPDATE (UPDATE 할 항목들)

 

MYSQL에서는 INSERT INTO 구문을 수행한 뒤 KEY 값이 중복된 경우 UPDATE문이 실행된다. 
PK 값이 중복되어야만 UPDATE문이 실행되므로 INSERT가 2번 되면 PK가 정상적으로 등록이 되었는지 확인하여야 한다.