오라클 -> 마리아DB oracle -> mariaDB 쿼리 변경

---

ORACLE(TIBERO) -> MARIA 쿼리 변경 TIP

---

SQL 오류(1146): Table '' doesn't exist

ORACLE

오라클은 테이블명 대소문자 가리지 않는다.

MARIA

마리아는 테이블명 대소문자 가린다. 분명 테이블이 있는데 Table doesn't exist 이라고 나오면 테이블명 대소문자를 체크해봐야함.

HeidiSQL 좌측 테이블 스키마에는 대문자로 전부 나오는데 쿼리 부분에서는 대소문자 가린다..

---

LIKE 검색시

ORACLE

AND EMP_NAME LIKE '%'|| #{EMP_NAME} || '%'

MARIA

AND EMP_NAME LIKE CONCAT('%',#{EMP_NAME},'%')

---

INSERT UPDATE

ORACLE

MERGE INTO AA A USING DUAL ON 
	(AAA_DATE = TO_CHAR(SYSDATE, 'YYYYMMDD') AND EMP_NO = #{EMP_NO} AND AAA_TYPE_CD = 'T1100')
WHEN MATCHED THEN UPDATE
SET AAA_GUBUN = #{AAA_GUBUN}
	, AAA_START = #{AAA_START}
WHEN NOT MATCHED THEN 
INSERT (
	  IDX
	, EMP_NO
	, AAA_TYPE
	, AAA_GUBUN
	, AAA_TYPE_CD
	
) VALUES (
	  USR._AAA_SEQ.NEXTVAL
	, #{EMP_NO}
	, '00'
	, #{AAA_GUBUN}
	, 'A'
)

MARIA

INSERT INTO aa( 
	 EMP_NO       
	,YYYYMM
	,LIST_FILE
	,REG_DATE
	,REG_EMP_NO
	,MODF_DATE
	,MODF_EMP_NO
)
VALUES (
	 #{EMP_NO}
	,#{SDATE}
	,#{LIST_FILE}
	,date_format(now(), '%Y%m%d')
	,#{EMP_NO}
	,date_format(now(), '%Y%m%d')
	,#{EMP_NO}
)
on duplicate key update
LIST_FILE=#{LIST_FILE}, MODF_DATE=date_format(now(), '%Y%m%d'), MODF_EMP_NO=#{EMP_NO}

1. 저장하고자 하는 테이블에 PK(또는 UNIQUE 제약)이 설정되어 있는 경우에 insert 수행
2. 결과 unique violation 에러가 발생하면 update 수행

---

DDL CREATE NUMBER형

ORACLE

IDX NUMBER NOT NULL

MARIA

IDX BIGINT NOT NULL

AAA_MINUTE INT

---

MARIA comment

ORACLE

COMMENT ON TABLE USR._AAA IS '근무'; COMMENT ON COLUMN USR._AAA.EMP_NAME IS '이름';

MARIA

CREATE TABLE `AAA` (
	`IDX` INT(11) NOT NULL COMMENT '키',
	`EMP_NAME` VARCHAR(100) NOT NULL COMMENT '이름'	
	PRIMARY KEY (`IDX`)
)
COMMENT='테이블'

---

현재시간 TO_CHAR

ORACLE

TO_CHAR(SYSDATE,'YYYYMMDD')

TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')

MARIA

DATE_FORMAT(NOW(), '%Y%m%d')

DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')

---

시간더하기

ORACLE

TO_DATE(SUBSTR(PASSWORD_UPD_DATE, 1, 8), 'YYYYMMDD') + 9

MARIA

DATE_ADD( SUBSTR(PASSWORD_UPD_DATE,1,8) , INTERVAL 9 DAY )

---

시간더하기 포맷

ORACLE

TO_DATE(SUBSTR(PASSWORD_UPD_DATE, 1, 8), 'YYYYMMDD') + 9

MARIA

Maria는 위의 결과가 2019-07-12 로 나와서 20190712 로 나오게 하기 위해 FORMATTING을 해준다.

DATE_FORMAT(DATE_ADD( SUBSTR(PASSWORD_UPD_DATE,1,8) , INTERVAL 7 DAY ), "%Y%m%d")

---

포맷 대문자 사용 주의

ORACLE

오라클은 쿼리를 대문자로만 짜도 된다. 그래서 이클립스에서 퀴리를 블럭지정해서 대문자로 바꾸는 단축키 Ctrl+Shift+X 혹은 HeidiSQL에서 Ctrl+u 를 눌러 전체를 대문자로 바꿔도 괜찮지만

TO_DATE(SUBSTR(PASSWORD_UPD_DATE, 1, 8), 'YYYYMMDD') + 9

MARIA

마리아 에서는 포맷이 있는 경우 퀴리를 블럭지정해서 대문자로 바꾸는 단축키 Ctrl+Shift+X 혹은 HeidiSQL에서 Ctrl+u 를 하면 엉뚱한 값이 출력되니 주의해야 한다.

DATE_FORMAT(DATE_ADD( SUBSTR(PASSWORD_UPD_DATE,1,8) , INTERVAL 7 DAY ), "%Y%M%D") - > 2019June12th

DATE_FORMAT(DATE_ADD( SUBSTR(PASSWORD_UPD_DATE,1,8) , INTERVAL 7 DAY ), "%Y%m%d") - > 20190612

---

SUBSTR

ORACLE

SUBSTR(PASSWORD_UPD_DATE, 0, 8)
SUBSTR(PASSWORD_UPD_DATE, 1, 8)
오라클은 시작점을 0을 써도 된다.

MARIA

SUBSTR(PASSWORD_UPD_DATE, 1, 8) 마리아는 0을 쓰면 안되고 1만 써야 한다.

---

NVL NULL 체크

ORACLE

NVL(A.EMP_NAME, ' ') AS EMP_NAME

MARIA

IFNULL(A.EMP_NAME, ' ') AS EMP_NAME

---

SELECT UPDATE

ORACLE

UPDATE USR.EMP A
SET A.MEMO=(SELECT MEMO FROM AAA WHERE DATE='20190430' AND A.EMP_NO=EMP_NO)

UPDATE USR.AAA
SET (AAA_START,AAA_END,AAA_MINUTE,STATUS)=
(SELECT AAA_START,AAA_END,AAA_MINUTE,STATUS
	FROM USR.AAA
    WHERE IDX=#{IDX}
)
WHERE IDX=#{IDX}

MARIA

UPDATE AAA A, BBB B
SET A.MEMO=B.MEMO
WHERE A.EMP_NO=B.EMP_NO

---

함수만들기

ORACLE

MARIA

delimiter //

create function F14() returns tinyint
begin
declare x tinyint;
set x=42;

return x;
end

//

---

시퀀스 만들기

ORACLE

ORACLE은 시퀀스 있음.

MARIA

1. 채번 테이블 생성
drop table _SEQUENCE;

CREATE TABLE `SEQUENCE` (
	`SEQ` BIGINT(64) NOT NULL AUTO_INCREMENT PRIMARY KEY
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

2. 시퀀스 함수 생성
delimiter //

drop function if exists _AAA_SEQ_NEXTVAL;

create function _AAA_SEQ_NEXTVAL() returns bigint

begin
declare x bigint; 
insert into _SEQUENCE(seq) values (0);
select last_insert_id() into x;

if(x % 10000 = 0) then
delete from _SEQUENCE where seq < x;
end if;

return x;
end

//

---

시퀀스

ORACLE

CREATE SEQUENCE AA.AAA 
INCREMENT BY 1 START WITH 1
MAXVALUE 99999999999
MINVALUE 1 CYCLE CACHE 40 NOORDER ;

사용방법 SELECT AA.AAA.NEXTVAL FROM DUAL

MARIA

마리아 DB(10.1)는 시퀀스가 없음. 채번 테이블을 사용해야함. 10.3 부터는 시퀀스가 있다고 함.

---

요일

ORACLE

TO_CHAR(TO_DATE(A.AAA_DATE),'DY') DY

MARIA

CASE DAYOFWEEK(DATE_FORMAT(A.AAA_DATE, '%Y%m%d')) 
						WHEN '1' THEN '일'
						WHEN '2' THEN '월'
						WHEN '3' THEN '화'
						WHEN '4' THEN '수'
						WHEN '5' THEN '목'
						WHEN '6' THEN '금'
						WHEN '7' THEN '토'
						END AS DY

---

ORACLE

CASE WHEN TO_CHAR(SYSDATE, 'd') = 2 THEN  DAY1
		                 WHEN TO_CHAR(SYSDATE, 'd') = 3 THEN  DAY2
		                 WHEN TO_CHAR(SYSDATE, 'd') = 4 THEN  DAY3
		                 WHEN TO_CHAR(SYSDATE, 'd') = 5 THEN  DAY4
		                 WHEN TO_CHAR(SYSDATE, 'd') = 6 THEN  DAY5

MARIA

CASE WHEN DAYOFWEEK(NOW()) = 2 THEN  DAY1
		                 WHEN DAYOFWEEK(NOW()) = 3 THEN  DAY2
		                 WHEN DAYOFWEEK(NOW()) = 4 THEN  DAY3
		                 WHEN DAYOFWEEK(NOW()) = 5 THEN  DAY4
		                 WHEN DAYOFWEEK(NOW()) = 6 THEN  DAY5

---

nvl2 처리

ORACLE

nvl2( , , )

MARIA

CASE WHEN 절로 처리함

---

TRUNC

ORACLE

TRUNC()

MARIA

TRUNCATE( , )

---

decode

ORACLE

DECODE(컬럼, 값, 조건 일치, 조건불일치)

MARIA

IF(컬럼 조건 값, 조건 일치, 조건불일치)

---

날짜형으로 변경

ORACLE

TO_DATE()

MARIA

STR_TO_DATE()
ex) DATE_FORMAT(DATE_SUB(STR_TO_DATE(concat(A.AAA_DATE,REPLACE(A.AAA_START,':','')),'%Y%m%d%H%i'), INTERVAL 30 MINUTE), '%H%i') AS AAA_START_URGENT_USE

---

특정 문자를 자르기

ORACLE

SUBSTR(#{IP_ADDR}, 1, INSTR(#{IP_ADDR}, '.', -1)-1)

MARIA

SUBSTRING_INDEX( , , )
ex) IP_ADDR_RANGE = SUBSTRING_INDEX(#{IP_ADDR}, '.', 3)

---

TO_CHAR(SYSDATE, 'd')