ORACLE쿼리를 MYSQL쿼리로 바꿔야할 상황이 생겼다.

건너 건너 감사히 빌려쓰던 ORACLE DB서버를 갑자기 못쓰게 되었다. 

그래서 전에 저장해 두었던 query에 의존하여 CAFE24에 호스팅한 MySQL로 옴겨야하는 상황이 발생했다.

잘 됐어! 공부하면서 바꾸자!

MYSQL 서버에 TABLE을 만들고, ORACLE에서 쓰던 기존의 QUERY를 그대로 쓰면서 발생한 

에러메지에 의존하여 인터넷과 주변인들의 도움을 받아 하나씩 바꿔나간 것을 정리한 것입니다.

////////////////////////////////////////////////// SPRING //////////////////////////////////////////////////

////////////////////////////////////////////////// 메이븐설정

ojdbc가 아닌 이제는 mysql Connector에 의존하자

////////////////////////////////////////////////// 스프링 CONTEXT 설정

<!-- ORACLE -->

<!-- 

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />

<property name="url" value="jdbc:oracle:thin:@www.hahaha.com:1521:XE" />

<property name="username" value="id" />

<property name="password" value="pw" />

</bean> 

-->

<!-- MYSQL -->

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">

<property name="driverClassName" value="com.mysql.jdbc.Driver" />

<property name="url" value="jdbc:mysql://www.hohoho.com:3306/스키마이름?characterEncoding=EUCKR" />

<property name="username" value="id" />

<property name="password" value="pw" />

</bean>

////////////////////////////////////////////////// QUERY ( ORACLE -> MYSQL ) //////////////////////////////////////////////////

////////////////////////////////////////////////// TYPE

///// varchar2 ->varchar 

///// number -> int 

///// date -> datetime

> date는 날짜만 / time은 시간만 / datetime은 날짜와 시간

 

////////////////////////////////////////////////// FUNCTION

///// DEFAULT sysdate -> 트리거를 쓰라는데 일단 보류

///// sysdate -> now()     // 버전5.5 이하

///// sysdate -> CURRENT_TIMESTAMP     // 버전5.6 이상

///// SUBSTR() -> SUBSTRING()

 

///// TO_NUMBER(data) -> CAST(data as unsigned)

ORACLE : ORDER BY TO_NUMBER(data) ASC

MYSQL -> ORDER BY CAST(data as unsigned) ASC

> binary / char / date / time / datetime / signed  / unsigned

 

///// TO_CHAR() -> DATE_FORMAT()

ORACLE : TO_CHAR(PAPERTIMESTART, '24hh')

MYSQL -> DATE_FORMAT(PAPERTIMESTART, '%H')

///// TO_DATE() -> STR_TO_DATE()

ORACLE : TO_DATE('${planPeriod}', 'yyyy-mm-dd')

MYSQL -> STR_TO_DATE('${planPeriod}', '%Y-%m-%d')

ORACLE : TO_DATE('${paperTimeEnd}','YYYYMMDDHH24MI')

MYSQL -> STR_TO_DATE('${paperTimeStrart}','%Y%m%d%T')

> %Y  해(4자리) / %y  해(2자리) / %m 달 / %b 달(영어표기) / %d 날 / %D 날(영어표기) / %T 시:분:초 / %H 시(24) / %h 시(12) / %i 분 / %s  초 / %j 는 뭐지???

 

///// NVL()  -> IFNULL()

///// NVL()  -> COALESCE()

ORACLE : NVL(MAX(MEMSEQ+1),1)

MYSQL -> COALESCE(MAX(MEMSEQ+1),1)

///// rownum -> LIMIT

ORACLE : SELECT test, rownum FROM TESTTABLE WHERE rownum BETWEEN 6 AND 15;

MYSQL -> SELECT test FROM TESTTABLE LIMIT 5, 10;

> LIMIT의 1번째 숫자는 0부터 시작하는 index를 의미합니다. 5번 index부터 10개를 가져와라라는 말입니다.

///// rownum -> @rn:=@rn+1

ORACLE : SELECT test, rownum FROM TESTTABLE WHERE rownum BETWEEN 6 AND 15;

MYSQL -> SELECT test, @rn:=@rn+1 as rownum FROM TESTTABLE, (select @rn:=0) as r WHERE rownum BETWEEN 6 AND 15;

> 또는 비슷한 구조로 하고 싶을 경우는 이렇게!!! @rn: 이런식으로 변수를 다룰 수 있는 모양입니다. 이를 응용해서도 가능합니다.

 

///// (+) -> LEFT OUTER JOIN

ORACLE : 

MYSQL ->

////////////////////////////////////////////////// ERROR 

///// 대소문자의 구분이 ORACLE에 비해 민감하다!!!

-> 전부 대문자화 시킴

///// 알리아스가 필요하다는 에러메세지가 뜨길래

-> 서브쿼리에 반드시 알리아스 추가

 

///// DATETIME형식의 DB데이터를 DATE_FORMAT함수로 SELECT하고, JAVA에서 OBJECT로 받아 JSON형식으로 만들 때 DATE타입으로 인식 한다.

ORACLE에서는 DATE형식의 DB데이터를 TO_CHAR함수로 SELECT하고, JAVA에서 OBJECT로 받아 JSON형식으로 만들 때 문자로 인식했었음.

-> CAST나 CONVERT 함수로 형변화를 시켜주면 된다.

ORACLE : SELECT  TO_CHAR(PAPERTIMESTART, 'hh')

MYSQL -> SELECT  CAST(DATE_FORMAT(PAPERTIMESTART, '%H') as unsigned)

 

///// You can't specify target table 'PP_MEMBERS' for update in FROM clause 

-> 한 층 더 서브쿼리를 만들고 알리아스도 챙겨서 기입해주기

ORACLE : (SELECT  COALESCE(MAX(MEMSEQ+1),1) FROM PP_MEMBERS) 

MYSQL -> (SELECT * FROM (SELECT COALESCE(MAX(MEMSEQ+1),1) FROM PP_MEMBERS) as TEMP)