Oracle쿼리문을 MySQL쿼리로 변환 방법
Posted by Albert 4002Day 14Hour 45Min 58Sec ago [2013-05-14]
Oracle을 쓰다가 MySQL을 사용하는 경우는 많지 않습니다. 워낙 강력한 DBMS라 Oracle을 버리기 어렵습니다.
대부분 변경한다고 하면 License문제로 변경하거나 솔루션에 DB를 추가로 적용 가능하게 하는 정도?
어째든 이번에 Oracle을 MySQL로 변경 할 일이 생겨 정리하여 블로그에 올립니다. ^^
1. NVL 함수를 IFNULL 함수로 변경한다.
2. SYSDATE 함수를 NOW() 함수로 변경한다.
3. trunc(sysdate)를 하면 시간만 잘린 일자가 나온다.
MySQL에서 날자만 반환해주는 CURDATE()를 쓴다.
4. Sequence는 사용자 함수를 만들어 사용한다.
사용자 함수 만드는 방법
SELECT zon_sq01.nextval FROM dual; -> SELECT get_next_seq_val('zon_sq01') FROM dual;
SELECT zon_sq01.currval FROM dual; -> SELECT get_curr_seq_val('zon_sq01') FROM dual;
sequence 추가가 필요할시 mysql_seq_table에 seq_nm(시퀀스명), last_cached_val(마지막 사용된 값)를 입력한다.
5. 인덱스 힌트 제거한다.
있더라도 큰 영향 없으므로 대상에서 제외
6. Date 함수 관련
6-1. TO_CHAR()를 DATE_FORMAT()으로 변환한다.
TO_CHAR(sysdate,'MMDDYYYYHH24MISS')의 경우
DATE_FORMAT(now(),'%m%d%Y%H%i%s')로 변환 -- '%Y%m%d' 대소문자 주의!!! Y는 4자리, y는 2자리, %m은 12기준 %M은 24기준
6-2. 요일 변경
자바스크립트의 요일은(0-6), MySQL의 요일은(0-6), ORACLE의 요일은(1,7)
그러므로 자바스크립트와 oracle의 요일차이로 TO_CHAR(SYSDATE-1, 'D')를
그대로 변경 시키면 아래구문이지만
=> DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%w')
MySQL에서는 -1을 할 필요가 없으므로
=> DATE_FORMAT(NOW(), '%w')
6-3. TO_DATE()를 STR_TO_DATE()으로 변환한다.
TO_DATE('10/30/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')의 경우
=> STR_TO_DATE('10/30/2012 00:00:00', '%m/%d/%Y %H:%i:%s')로 변환
TRUNC(to_date('20130227', 'YYYYMMDD')+30) 의 경우
=> DATE_ADD(str_to_date('20130227', '%Y%m%d'), INTERVAL 30 DAY) -- '%Y%m%d' 대소문자 주의!!! Y는 4자리, y는 2자리, %m은 12기준 %M은 24기준
6-4. 날짜 가감
oracle은 날짜형 데이터에 +1을 하면 1일이 추가되나 mysql은 이상하게 변함.
sysdate + 1
=> DATE_ADD(now(), interval 1 DAY)
DATE_ADD(date,INTERVAL expr unit) -- 날짜 가산
DATE_SUB(date,INTERVAL expr unit) -- 날짜 감산
DATE_ADD, DATE_SUB를 사용 할 경우 '0000-00-00 00:00:00'나 '0000/00/00 00:00:00'의 형태만 맞춰 주면 문자도 자동으로 변경하여 연산됨.
unit Value expr 형태
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND 'MINUTES:SECONDS'
HOUR_MICROSECOND 'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE 'HOURS:MINUTES'
DAY_MICROSECOND 'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS'
DAY_SECOND 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE 'DAYS HOURS:MINUTES'
DAY_HOUR 'DAYS HOURS'
YEAR_MONTH 'YEARS-MONTHS'
예)
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
-> '1899-12-30 14:00:00' -- 34시간이 감산됨
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '1 -10' DAY_HOUR);
-> '1900-01-02 10:00:00' -- 34시간이 가산됨
6-5. 날짜와 날짜의 차이 구하기
SELECT to_date('20130314', 'YYYYMMDD')- to_date('20130313', 'YYYYMMDD') FROM DUAL -- 1일 이 조회됨
-> SELECT TO_DAYS(str_to_date('20130314', '%Y%m%d'))- TO_DAYS(str_to_date('20130313', '%Y%m%d')) FROM DUAL
7. Outerjoin 변경
[Oracle]
left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+);
right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2;
[MySQL]
left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2;
right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2;
% From절에서 테이블명 중간에 , 가 없다는것과 ON 구문이 From절 바로 뒤에 오게
해야하는것에 주의
8. rownum 변환
8-1. rownum이 조건으로 사용될 경우 limit를 사용
rownum >= 5 와 rownum <= 10 이면 limit 4, 5
ex) 리스트 페이징에서 11번째부터 20번째까지 limit 10, 10
처음부터 10개 ... LIMIT 10
5번째부터 10개 ... LIMIT 4, 10
5번째부터 마지막까지 ... LIMIT 4, -1
이때 주의 할 점은 첫번째 레코드의 시작은 “0”
즉 “LIMIT 1, 3 - 2번째부터 3개” 의 결과를 출력
예)
select * from table LIMIT 1, 3;
8-2. rownum이 컬럼으로 사용될 경우 변수 사용
select @rownum := @rownum + 1 as rownum
, table.*
from table
JOIN (select @rownum := 0) r;
8-3. rownum이 조건 및 컬럼으로 모두 사용될 경우 2.를 적용한 서브쿼리로 작성
select *
from (
select @rownum := @rownum + 1 as rownum
, table.*
from table
JOIN (select @rownum := 0) r
)
where rownum < 3;
9. Subquery에서 alias가 없다면 붙인다.
select a, b from ( select a, b from ttt)
--> select t.a, t.b from ( select a, b from ttt) t
10. DELETE 테이블명 쿼리문을 DELETE FROM 테이블명 쿼리문으로 바꾸어준다.
oracle에서는 FROM을 생략해도 되지만 MySQL에서는 반드시 필요
11. Merge into의 경우는 쿼리문을 분리하여 처리로직으로 변경해준다.
변경방법은 아래 두가지중에 하나를 선택해도 되고 다른 방법을 써도 된다.
11-1 조건이 맞는지(MATCHED)에 대한 SELECT문을 실행시켜서 결과값이 있으면 UPDATE문을 실행시켜 주고 결과값이 없으면 INSERT문을 실행시켜준다.
11-2 UPDATE문을 실행해서 UPDATE가 일어나면 그대로 가고 UPDATE가 일어나지 않으면 INSERT문을 실행시킨다.
12. decode문 case로 변경
decode( 1,1,'true','false') -> case when 1=1 then 'true' else 'false' end
13. start with connet by 문은 with를 사용한 recursive 쿼리로 변경
recursive 쿼리 작성 방법
14. mysql은 대소문자 구분함. 모든 컬럼명과 테이블명의 대소문자 확인
15. 오라클의 ||(Vertical Bar) 사용할 수없음. CONCAT으로 변경
TO_CHAR(NOW(), 'yymmdd') || LPAD(get_next_seq_val('ord_cl_info_sq01'), 9, '0')
--> CONCAT(DATE_FORMAT(NOW(), '%y%m%d') ,LPAD(get_next_seq_val('ord_cl_info_sq01'), 9, '0'))
16. TO_CHAR, TO_NUMBER -> CAST로 변경
SELECT TO_CHAR( 11 ) AS AA FROM DUAL
--> SELECT CAST( 11 AS CHAR ) AS AA FROM DUAL
SELECT TO_NUMBER( '-11' ) AS AA FROM DUAL
--> SELECT CAST( '-11' AS SIGNED ) AS AA FROM DUAL
[출처] Oracle쿼리문을 MySQL쿼리로 변환 방법|작성자 강철고양이