Oracle과 MySQL 문법 차이

구분	Oracle	MySQL	쿼리 비교
NULL값 확인함수	NVL	IFNULL	(Oracle) SELECT NVL(USER_ID,'') FROM DEPT; (MySQL) SELECT IFNULL(USER_ID,'') FROM DEPT;
현재날짜시간 확인	SYSDATE	NOW()	(Oracle) SELECT SYSDATE FROM DUAL; (MySQL) SELECT NOW() FROM DUAL;
날짜 String으로 변경	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;
요일변환 숫자범위	일~월(1~7)	일~월(0~6)	(Oracle) SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL; (MySQL) SELECT DATE_FORMAT(NOW(), '%w') FROM DUAL;
문자와 문자 병합	||	CONCAT()	(Oracle) SELECT USER_ID FROM DEPT WHERE USER_ID LIKE '%' || 'DEPT' || '%'; (MySQL) SELECT USER_ID FROM DEPT WHERE USER_ID LIKE CONCAT('%','DEPT','%');
형변환	TO_CHAR, TO_NUMBER	CAST	(Oracle) SELECT TO_CHAR(1234) FROM DUAL; (MySQL) SELECT CAST(1234 AS CHAR) FROM DUAL;
페이징처리	ROWNUM	LIMIT	(Oracle) SELECT * FROM (SELECT ROWNUM, A.* FROM (SELECT * FROM DEPT) A) WHERE ROWNUM BETWEEN 0 AND 10; (MySQL) SELECT * FROM DEPT LIMIT 0, 10;
시퀀스 다음번호	NEXTVAL	CURRVAL	(Oracle) 시퀀스명.NEXTVAL (MySQL) 시퀀스명.CURRVAL

 

참고

https://spidyweb.tistory.com/12