Oracle -> Mariadb 전환

요즘 oracle 전용 쿼리들을 mariadb 로 전환하는 작업중이다.
작업하면서 나중에도 필요할 수 있는 항목들을 정리하고자 한다.

1. decode -> CASE WHEN ~ 으로 변경

-- As-IS(oracle)
select DECODE(type, 1, '1000', '0000') as type from dual

-- To-Be
select CASE WHEN type = 1 THEN '1000' ELSE '0000' END as type from dual

2. to_number

-- As-IS(oracle)
select to_number('1') as num from dual

-- To-Be
select CAST('1' AS INTEGER) as num from dual

3. to_char
   3.1. 숫자 -> 문자

-- As-IS(oracle)
select to_char(1) as level from dual

-- To-Be
select CAST('1' AS VARCHAR(1)) as level from dual

3.2. 숫자를 문자로 변환 후 0 채우기

-- As-IS(oracle)
select to_char(1, '09') as level from dual

-- To-Be
select LPAD(1, 2, '0') as level from dual

3.3. 숫자를 문자로 변환 후 오른쪽에 0 채우기

-- As-IS(oracle)
select N'USER_' || TO_CHAR(11, 'FM00') as user_number from dual

-- To-Be
select CONCAT(N'USER_', RPAD(11, 2, '00')) as user_number from dual

4. to_date

-- As-IS(oracle)
select TO_DATE('20201010", 'YYYYMMDD') as cdate from dual

-- To-Be
select CAST('20201010' AS DATE) as cdate from dual

5. || -> CONCAT

-- As-IS(oracle)
select 'AA' || 'BB' as str from dual

-- To-Be
select CONCAT('AA', 'BB') as str from dual

6. (+)
   오라클에서 (+) 는 outer join 을 뜻한다.
   SELECT * FROM a, b WHERE a.id = b.id(+) 는
   SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id 와 같은 내용이다.

-- oracle query
SELECT * FROM a, b WHERE b.id(+)  = a.id

-- ansi query
SELECT * FROM a LEFT OUTER JOIN b ON b.id = a.id

-- 즉
-- a.id = b.id(+)  => LEFT OUTER
-- a.id(+) = b.id  => RIGHT OUTER

7. UNISTR

-- As-IS(oracle)
select UNISTR('가나다') as text from dual

-- To-Be
select CHAR('가나다' USING UCS2) as text from dual

이 경우는 둘다 select N'가나다' as text from dual 로 써도 됨.

8. from 절 sub query
   oracle 인 경우는 from 절의 sub query 는 alias 없이 실행 가능하지만
   mariadb 는 from 절의 sub query 의 alias 가 필수로 있어야 한다.

-- As-IS(oracle)
select * from (select id, name, phone from temp_user)  -- 가능

-- To-Be
select * from (select id, name, phone from temp_user)  -- 에러
select tuser.* from (select id, name, phone from temp_user) as tuser -- 성공

9. listagg
   이건... 개발 언어가 java라서 java 소스로 해결. (String.split)
   참고로 oracle 에서 listagg 는 4000 byte 까지만 되는것 같다.

10. connect by
    이것도.. 답없음.. query 를 새로 짜는 수밖에...

11. merge
    11.1. merge 대부분이 있으면 update, 없으면 insert 처리 이므로
    merge 문 호출 전에 select 로 체크하여 query 분기 태움.

    11.2. sql mapper 에 parameter 로 db type 을 주어 각각의 merge 문 처리

-- oracle
MERGE INTO TB_TEMP temp
USING ( SELECT id, name ) utemp ON (temp.id = utemp.id)
WHEN MATCHED THEN
     UPDATE SET name = #name# 
WHEN NOT MATCHED THEN
     INSERT (id, name) VALUES (#id#, #name#)
     
-- mariadb
INSERT INTO TB_TEMP (id, name) VALUES (#id#, #name#)
ON DUPLICATE KEY UPDATE name = #name#