ORACLE에서 MSSQL 마이그레이션 시 참고사항

1. 인덱스 힌트를 제거한다. 

2. NVL 함수를  ISNULL 혹은 IFNULL 함수로 변경한다. 

3. SYSDATE 함수를 NOW() 함수로 변경한다. 

4. TRUNC(SYSDATE)를 CONVERT(DATE,GETDATE(),12)로 변경한다. 
   trunc(sysdate)를 하면 시간이 잘린 연,월,일이 나온다.  예)19/05/09

   이와 같은 역할을 하는 CONVERT를 사용한다.

5. 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; 

[MSSQL] 
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절 바로 뒤에 오게 해야하는것에 주의  !!


6. TO_CHAR()를 CONVERT()으로 변환한다. 

    TO_CHAR(sysdate,'YYYYMMDD')의 경우 

    CONVERT(VARCHAR,GETDATE(),12)로 변환  .

 

아래는 추가로 다양한 형식에 맞게 사용

 

SELECT CONVERT(VARCHAR,getdate(),(120))   --//2019-05-09 2019-05-09 11:55:35
,CONVERT(VARCHAR,getdate(),(102))  --//2019.05.09 
,CONVERT(VARCHAR,getdate(),(12))  --//190509
,CONVERT(VARCHAR,getdate(),(23)) --//2019-05-09
,LEFT(CONVERT(VARCHAR,getdate(),(23)),7) --//2019-05
,LEFT(CONVERT(VARCHAR,getdate(),(3)),2) //09
;
  


7. TO_DATE()를 CONVERT()으로 변환한다. 
SELECT CONVERT(DATE,getdate(),(120))   --//2019-05-09 2019-05-09 11:55:35
,CONVERT(DATE,getdate(),(102))  --//2019.05.09 
,CONVERT(DATE,getdate(),(12))  --//190509
,CONVERT(DATE,getdate(),(23)) --//2019-05-09
,LEFT(CONVERT(DATE,getdate(),(23)),7) --//2019-05
,LEFT(CONVERT(DATE,getdate(),(3)),2) //09


8. Where rownum <= 10 을 ROW_NUMBER()OVER() 로 변환 
  이때 주의 할 점은 SELECT 절에 TOP 100 PERCENT 붙여주어야 누락없이 다 뽑음


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 테이블명 쿼리문으로 바꾸어준다. 

11. Merge into의 경우는 쿼리문을 분리하여 DAO단에서 처리로직으로 변경해준다. 

     변경방법은 아래 두가지중에 하나를 선택해도 되고 다른 방법을 써도 된다. 


  

      (1) 조건이 맞는지(MATCHED)에 대한 SELECT문을 실행시켜서 결과값이 

           있으면 UPDATE문을 실행시켜 주고 결과값이 없으면 INSERT문을 

           실행시켜준다. 

           % rs.next()로 결과값이 있는지 판단 

  

      (2) UPDATE문을 실행해서 UPDATE가 일어나면 그대로 가고 UPDATE가 

           일어나지 않으면 INSERT문을 실행시킨다. 

           % ps.executeUpdate() > 0으로 UPDATE 판단 



o 오라클 MergeInto 구문 

MERGE INTO SERVICE_LOG FSL1 
USING 
(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') AS DAYS, 'LS003' AS SERVICE_TYPE FROM DUAL ) FSL2 
ON (FSL1.DAYS = FSL2.DAYS AND FSL1.SERVICE_TYPE = FSL2.SERVICE_TYPE) 
WHEN MATCHED THEN 
UPDATE SET FSL1.AGE_10_CNT = FSL1.AGE_10_CNT + 1 
WHEN NOT MATCHED THEN 
INSERT (FSL1.DAYS, FSL1.SERVICE_TYPE, FSL1.AGE_10_CNT) 
VALUES (FSL2.DAYS, FSL2.SERVICE_TYPE, 1) 

-------------------------------------------------------------------------------------- 

o MySQL 구문으로 변환처리 1 
  - DAO처리에서 조건이 맞는지(MATCHED) 안맞는지(NOT MATCHED) 비교하는 SELECT 쿼리문을 실행하고 
    그 결과에 따라서 업데이트와 인서트 처리를 해준다. SELECT 결과값이 있다면 업데이트, 
    SELECT 결과값이 없다면 인서트(rs.next()로 결과값이 있는지 판단) 
  
if(getDAO().existServiceLog(conn, serviceType)) { 
    return getDAO().updateServiceLog(conn, memberInfo, serviceType); 
}else{ 
    return getDAO().insertServiceLog(conn, memberInfo, serviceType); 
} 

1) public boolean existServiceLog(Connection conn, String serviceType) 

   SELECT SERVICE_TYPE 
   FROM SERVICE_LOG USE INDEX(PRIMARY) 
   WHERE SERVICE_TYPE='LS003' AND DAYS=DATE_FORMAT(NOW(), '%Y%m%d') 

2) public boolean updateServiceLog(Connection conn, Member memberInfo, String serviceType) 

   UPDATE SERVICE_LOG 
   SET AGE_10_CNT = AGE_10_CNT+1 
   WHERE SERVICE_TYPE='LS003' AND DAYS=DATE_FORMAT(NOW(), '%Y%m%d') 

3) public boolean insertServiceLog(Connection conn, Member memberInfo, String serviceType) 

   INSERT SERVICE_LOG(DAYS, SERVICE_TYPE, AGE_10_CNT, AGE_20_CNT, AGE_30_CNT, AGE_40_CNT, AGE_50_CNT) 
   VALUES(DATE_FORMAT(NOW(), '%Y%m%d'), 'LS003', 1, 0, 0, 0, 0) 

--------------------------------------------------------------------------------------------------------- 
o MySQL 구문으로 변환처리 2 
  - DAO처리에서 업데이트가 되었는지를 비교하는 구문을 실행해서 업데이트가 일어나면 그대로 진행되고 
    업데이트 일어나지 않으면 인서트를 실행해 준다(ps.executeUpdate() > 0으로 UPDATE 판단) 

if(!getDAO.updateServiceLog(conn, memberInfo, serviceType)){ 
    getDAO.insertServiceLog(conn, memberInfo, serviceType); 
} 


1) public boolean updateServiceLog(Connection conn, Member memberInfo, String serviceType) 

   UPDATE SERVICE_LOG 
   SET AGE_10_CNT = AGE_10_CNT+1 
   WHERE SERVICE_TYPE='LS003' AND DAYS=DATE_FORMAT(NOW(), '%Y%m%d') 
   을 실행해서  ps.executeUpdate() > 0;을 결과로 리턴한다. 업데이트가 
   일어나지 않으면 false를 반환하고 insert가 일어난다. 

2) public boolean insertServiceLog(Connection conn, Member memberInfo, String serviceType) 

   INSERT SERVICE_LOG(DAYS, SERVICE_TYPE, AGE_10_CNT, AGE_20_CNT, AGE_30_CNT, AGE_40_CNT, AGE_50_CNT) 
   VALUES(DATE_FORMAT(NOW(), '%Y%m%d'), 'LS003', 1, 0, 0, 0, 0) 


+ 자료형 변환 

MySQL 을 Oracle 로 이전하기     

MySQL의 내용을 Oracle로 옮기면서 사용한 내용을 정리함 



1. 데이터 타입을 바꾼다. 
  MySQL 메뉴얼 : 7.2 Column types 참고 
  
int(11) =>  number(11) 
varchar(30) => varchar2(30) 
char(30) => char(30) 

TINYINT => number(3) : 255 
SMALLINT => number(5) : 65535 
MEDIUMINT => number(8) : 16777215 
INT => number(10): 4294967295 
BIGINT => number(20): 18446744073709551615 
FLOAT => : 1.175494351E-38 to 3.402823466E+38 
DOUBLE => : 2.2250738585072014E-308 to 1.7976931348623157E+308 
DECIMAL => 
DATE => : 'YYYY-MM-DD' 
DATETIME => : '1000-01-01 00:00:00' to '9999-12-31 23:59:59' 
TIMESTAMP => number(11) 
TIME => : 'HH:MM:SS' 
YEAR => : 1901 to 2155 
CHAR => CHAR 
VARCHAR => VARCHAR2 
TINYBLOB 
TINYTEXT => CHAR(255) 
TEXT => VARCHAR2(65535) : (2^16 - 1) 
BLOB 
MEDIUMBLOB 
MEDIUMTEXT => VARCHAR2(16777215) : (2^24 - 1) 
LONGBLOB 
LONGTEXT => VARCHAR2(4294967295) : (2^32 - 1) 
ENUM 
SET 

2. auto_increment 인 경우 sequence 값을 만든다. 
  - "테이블이름_seq" 형식으로 만든다. 
    한 테이블에 여러개가 있으면 column_seq 로 추가한다. 
  - user 테이블의 no 인 경우는 user_seq 가 된다. 

CREATE sequence tablename_seq 
INCREMENT BY 1      -- 증가치 
START WITH 1        -- 시작 
MAXVALUE 9999999    -- 종점 
NOCACHE             -- 
NOCYCLE; 

예) 
create sequence SEQ_TEST increment by 1 start with 1 
maxvalue 99999999 nocache nocycle; 

insert into table_name (auto_inc_field) values(SEQ_TEST.NEXTVAL); 

3. key 값 

  "PRIMARY KEY  (no)" 는 그대로 설정해도 된다. 
  
4. NOT NULL 과 DEFAULT 의 위치를 바꾼다. 
  서로 순서가 다른거 같다. 

  - MySQL 
    NOT NULL default '', 
   
  - Oracle 
    DEFAULT '' NOT NULL , 
   

5. \\\' 나 \\\" 로 되어 있는것을 '' 로 바꿔야 한다.