Oracle -> Mariadb 전환

By starseat
2022-10-13 10:17:10
db

Post Tags

예전에 oracle 전용 쿼리들을 mariadb 용 쿼리로 전환하는 작업을 진행하였다.
작업하면서 참고할만한 항목들을 정리하고자 한다.

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

- decode 
  - 컬럼이 비교값과 같을 경우 지정 값 1, 같이 않을 경우 지정 값 2  출력
  - decode(컬럼, 비교값, 지정 값 1, 지정 값 2)

```java
// java code 예시
// decode(type, "1", "참", "거짓")

if(type.equals("1") {
  return "참";
}
else {
  return "거짓";
}
```

```sql
-- 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
```

#### 1.2. NVL

- NVL
  - 값이 NULL인 경우 `지정 값` 출력, NULL이 아니면 원래 값 출력
  - `NVL(컬럼, NULL 일 경우 지정 값)`

- NVL2
  - 값이 NULL이 아닐 경우 경우 `지정 값 1` 출력,  NULL 이면 `지정 값 2` 출력
  - `NVL2(컬럼, "지정 값 1", "지정 값 2")`
  - 즉, `NVL2("값", "NOT NULL", "NULL") `

- Oracle 에서 NVL 을 사용했을 경우는 DB 별로 NULL 처리가 다르기에 해당 DB 에 맞게 바꾸어 줘야 한다.

```sql
-- Oracle - NVL
SELECT NVL(t.number, 0) FROM TEMP_TABLE t

-- MsSQL - ISNULL
SELECT ISNULL(t.number, 0) FROM TEMP_TABLE t

-- MySQL, MaraiDB - IFNULL
SELECT IFNULL(t.number, 0) FROM TEMP_TABLE t
```

### 2. to_number

```sql
-- 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. 숫자 -> 문자

```sql
-- 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 채우기

```sql
-- 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 채우기

```sql
-- 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

- [mysql date type](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html)

```sql
-- 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

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

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

### 6. (+)

오라클에서 `(+)` 는 `outer join` 을 뜻한다.

```text
SELECT * FROM a, b WHERE a.id = b.id(+) 는 
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id 와 같은 내용이다.
```

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

`(+)` 붙는 쪽이 null 일 수도 있어 확장한다는 뜻으로 생각하면 이해가 조금 더 빠를 것이다.

### 7. UNISTR

```sql
-- 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 가 필수로 있어야 한다.

```sql
-- 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 문 처리

```sql

-- 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#
```

Previous Post [MySQL] yum 설치

Next Post [DBeaver] 메모리 이슈 해결