Oracle, mariaDB 차이점 정리

오라클에서 마리아DB로 이관하면서 아래에 정리된 차이점을 정말정말 많이 봤다..

Oracle과 MySql 쿼리문 문법 비교

 

Oracle에 익숙한 필자는.. 이번 프로젝트에서 생전 처음으로 MariaDB를 쓰면서 MySql 문법을 익혀야 했다.

지금부터 알게된 모든 차이점들을 하나씩 하나씩 차근차근 적어나갈 생각이다.

규칙은, 기준이 Oracle이고 그 비교되는 Mysql 문법을 아래에 적는다.

 

1. 공백치환 함수 ( NVL -> IFNULL )

Oracle : SELECT NVL('컬럼명', '') FROM DUAL;

MySql : SELECT IFNULL('컬럼명', '') FROM DUAL;

 

2. 현재 날짜시간

Oracle : SYSDATE

Mysql  : NOW() 

 

3. 날짜포멧 

Oracle : TO_CHAR(sysdate,'MMDDYYYYHH24MISS')

Mysql  : DATE_FORMAT(now(),'%Y%m%d%H%i%s')  -> 여기서 대문자Y는 4자리 년도, 소문자 y는 2자리 년도

 

4. 날짜 포멧 : 요일

Oracle : 요일이 1~7로 인식함  -> TO_CHAR(SYSDATE - 1, 'D') 

Mysql : 요일이 0~6으로 인식   -> DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 DAY), '%w')

* 참고로 자바스크립트가 0~6으로 인식하기에 Oracle 쿼리에서 -1을 해서 맞추는 경우가 많음

 

5. Like절 '%' 사용법

Oracle : Like '%'||'문자'||'%' 이런식으로 컬럼명 앞뒤로 '%'를 붙여주면 된다

Mysql : LIKE CONCAT('문자','%') 이런식으로 CONCAT 함수 사용

 

6. 형변환

Oracle : To_char, To_number 등

Mysql : CAST

SELECT TO_CHAR(1234) FROM DUAL 

-> SELECT CAST(1234 AS CHAR) FROM DUAL

 

7. 대소문자 구분함

Oracle : 구분없음

Mysql : 기본적으로 구분하나, 설정으로 변경 가능함

 

8. ROWNUM

Oracle : where 절에 rownum > 5 and rownum =< 10 

Mysql : where절 없이 limit 5,10

 

9. Sequence(시퀀스)는 둘 다 사용자함수를 만들어서 아래와 같이 사용

Oracle : 시퀀스명.nextval

Mysql : 시퀀스명.currval (또는 autoIncrement)

10. 문자열 자르기

Oracle: SUBSTR(문자열, 1, 10)

Mysql: SUBSTRING(문자열, 1,10), LEFT(문자열, 3), RIGHT(문자열, 3)

 

11. 문자열 합치기 ( - 문자열을 연결한다고 가정)

Oracle: 문자열(또는 컬럼) ||' - '

Mysql: CONCAT(문자열(또는 컬럼), ' - ')

 

12. 예약어가 컬럼명일 때

Oracle: 컬럼명을 따옴표(")로 감싸기 (예: select "column" from tab)

Mysql: 컬럼명을 TAB 키 위에 있는 ` 키 ( Single quotation )로 감싸기

 

13. 저장프로시저 있는지 여부 파악해서 Create 하기

Oracle: CREATE OR REPLACE PROCEDURE 프로시저명

Mysql: DROP PROCEDURE IF EXISTS 프로시저명; 을 한 뒤에 CREATE PROCEDURE 프로시저명

 

1. (+)
   오라클에서 (+) 는 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

 

15.

 

오라클 To_date, mysql STR_TO_DATE()

<오라클에서 사용되는 문장>

 

SELECT A.AGENT_ID, A.AGENT_NM, B.CHECK_DATE, B.IS_ACTIVE AS IS_LOG_ACTIVE

FROM SCM_AGENT A , SCM_AGENT_LOG B

WHERE A.AGENT_ID = B.AGENT_ID

AND A.AGENT_ID = '1'

AND B.CHECK_DATE BETWEEN TO_DATE('201512200000', 'YYYY-MM-DD HH24:MI') 

        AND TO_DATE('201612200000', 'YYYY-MM-DD HH24:MI')

  AND B.IS_ACTIVE = 'N'

  ORDER BY B.CHECK_DATE DESC

 

<MySQL과 Maria에서 사용되는 문장>

 

SELECT A.AGENT_ID, A.AGENT_NM, B.CHECK_DATE, B.IS_ACTIVE AS IS_LOG_ACTIVE

FROM SCM_AGENT A , SCM_AGENT_LOG B

WHERE A.AGENT_ID = B.AGENT_ID

AND A.AGENT_ID = '1'

AND B.CHECK_DATE BETWEEN STR_TO_DATE('201512200000', '%Y%m%d%s') 

AND STR_TO_DATE('201612200000', '%Y%m%d%s')

AND B.IS_ACTIVE = 'N'

ORDER BY B.CHECK_DATE DESC

 

<정리>

TO_DATE('201512200000', 'YYYY-MM-DD HH24:MI') 

를 사용 하려면

STR_TO_DATE('201512200000', '%Y%m%d%s') 

과 같이 사용하면 된다.

201512200000 는 '파라미터'이다.

 

출처: https://stepping.tistory.com/27 [디딤돌]

 

1. TRUNC ---> TRUNCATE

■ 오라클 TRUNC

 

EX)

 

SELECT TRUNC(123456.12, 1);

-> 123456.1

 

SELECT TRUNC(123456.12, -1);

-> 123450

 

SELECT TRUNC(123456.12, 3);

-> 123456.120

 

SELECT TRUNC(123456.12, -3);

-> 123000

 

 

 

■ MySQL(MariaDB) TRUNCATE

 

EX)

 

SELECT TRUNCATE(123456.12, 1);

-> 123456.1

 

SELECT TRUNCATE(123456.12, -1);

-> 123450

 

SELECT TRUNCATE(123456.12, 3);

-> 123456.120

 

SELECT TRUNCATE(123456.12, -3);

-> 123000

 

출처: <https://m.blog.naver.com/PostView.nhn?blogId=chodambee&logNo=220937603038&proxyReferer=https:%2F%2Fwww.google.com%2F>

 

 

1.  오라클WM_CONCAT, Mysql GROUP_CONCAT

 

출처: <https://m.blog.naver.com/cacung82/220219070134>

 

1. 오라클 DECODE  --> mysql IF

 

ORACLE DECODE 함수 문법

?

1	DECODE(컬럼,값,'조건이 일치할 경우의 VALUE','조건이 일치하지 않을경우의 VALUE')

오라클의 decode 문법은 위와 같습니다.

 

 

 

 

 

위 실행 화면은 임의로 존재하는 MEMBERTABLE이 있는데 

이 테이블을보면 PASSWORD 컬럼의 값을 비교 하려고 합니다.

비밀번호가 '1234'일경우 '위험한비밀번호' 아닐경우 '안전한비밀번호' 라는 가상의 조건으로 

진행을 해보도록 하겠습니다.

 

ORACLE DECODE 함수 적용한 실행쿼리

?

1 2 3

SELECT id,name,password,        DECODE(password,'1234','위험한비밀번호','안전한비밀번호') result FROM MEMBERTABLE

위와 같이 작성한후 실행해보도록 하겠습니다.

DECODE 실행결과

 

 

 

확인결과 TRUE FASLE의 조건이 정상적으로 이루어 졌습니다.

MySQL IF함수문법

?

1	IF(조건,'조건이 일치할 경우의 VALUE','조건이 일치하지 않을경우의 VALUE')

IF함수는 DECODE함수보다 입력값이 하나가 적습니다.

DECODE함수의 조건은 EQUAL(=) 만 지원하지만 

MySQL의 경우 조건문을 첫번째 입력값에 적어줄수있습니다.

 

MySQL역시 기존에 존재하는 테이블을 아무거나 하나 보도록 하겠습니다.

 

저같은 경우에는 ORACLE과 동일한 테이블명 + 데이터를 가진 테이블을 보도록 하겠습니다.

 

 

 

 

이번 MySQL의 조건은 ORACLE이랑은 살짝 다르게 정해보도록 하겠습니다.

 

가상조건

 

membertable의 password 컬럼에 들어있는 글자수를 비교하여 

4이하일 경우는 '위험한 비밀번호' 4보다 클경우는 ''안전한 비밀번호'라는 결과값을 출력

 

이라는 조건으로 실행쿼리를 작성해보겠습니다

 

 

 

MySQL IF함수 적용 실행쿼리

?

1 2 3

select id,name,password,        IF(length(password) <= 4,'위험한비밀번호','안전한비밀번호') result   from membertable

실행결과를 한번 보도록 하겠습니다.

 

 

실행결과

 

 

 

MySQL의 IF 함수를 이용하여 역시 참/거짓 조건에 맞추어 결과값이 result 컬럼에 정상적으로 출력되었습니다.

 

이로써 단일조건과 다중조건에 대해서 모두 알아보았습니다.

 

출처: https://hellogk.tistory.com/22 [IT Code Storage]