요즘 oracle 전용 쿼리들을 mariadb 로 전환하는 작업중이다.
작업하면서 나중에도 필요할 수 있는 항목들을 정리하고자 한다.
-- 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
-- As-IS(oracle)
select to_number('1') as num from dual
-- To-Be
select CAST('1' AS INTEGER) as num from dual
-- 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
-- As-IS(oracle)
select TO_DATE('20201010", 'YYYYMMDD') as cdate from dual
-- To-Be
select CAST('20201010' AS DATE) as cdate from dual
-- As-IS(oracle)
select 'AA' || 'BB' as str from dual
-- To-Be
select CONCAT('AA', 'BB') as str from dual
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
-- 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
로 써도 됨.
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 -- 성공
listagg
이건... 개발 언어가 java
라서 java
소스로 해결. (String.split
)
참고로 oracle 에서 listagg 는 4000 byte 까지만 되는것 같다.
connect by
이것도.. 답없음.. query
를 새로 짜는 수밖에...
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#