Oracle With구문 ->Mysql 변환

처음에는 무조건 안되는줄 알고 with구문을 변경하려고 난리를 쳤었다.(ㅠㅠ내 시간)

해당 버전확인이 먼저 되어야할듯함. 8.0이후에서는 with구문을 동일하게 사용가능.

 

MySQL 8.0 이전 버전에서는 WITH 구문이 지원되지 않기 때문에 WITH 구문을 변환하려면 다음 선택지 중 하나를 택할 수 있다.

• Temporary Table(임시 테이블)
• Derived Table
• Inline View

본 글에서는 inline view를 이용하여 Oracle WITH 구문을 변경해 볼 것이다.

 

WITH 구문을 Inline View로 변환하는 예제

편의상 간단한 쿼리를 예제로 들었다.

 

ℹ️Oracle WITH:

WITH SAMPLE_WITH AS (SELECT ID FROM SAMPLE_TBL)

SELECT A.ID
FROM ANOTHER_TBL A,
     SAMPLE_WITH B
WHERE A.ID = B.ID

 

ℹ️MySQL Inline View:

SELECT A.ID
FROM ANOTHER_TBL A,
     (SELECT ID FROM SAMPLE_TBL) B
WHERE A.ID = B.ID

 

MyBatis의 sql, include와 함께 사용하기

MyBatis 사용 시 WITH 구문과 inline view는 MyBatis의 <sql>과 <include>를 사용해서 반복 코드를 줄이고 깔끔하게 정리할 수 있다.

 

ℹ️Oracle WITH:

<sql id="info">
 SELECT AGT_ID FROM SAMPLE_TBL
</sql>

<select id="get..." parameterType="..." resultType="...">
  WITH SAMPLE_WITH AS (<include refid="info" />)

SELECT A.ID
  FROM ANOTHER_TBL A,
       SAMPLE_WITH B
  WHERE A.ID = B.ID 
</select>

 

ℹ️MySQL Inline View:

<sql id="info">
 SELECT AGT_ID FROM SAMPLE_TBL
</sql>

<select id="get..." parameterType="..." resultType="...">
  SELECT A.ID
  FROM ANOTHER_TBL A,
       (<include refid="info" />) B
  WHERE A.ID = B.ID
</select>

 

[참고] Oracle WITH 구문

• WITH 구문 내의 쿼리 결과(서브쿼리)가 여러 번 사용될 때 유용하다.
• 서브쿼리 블럭에 이름을 지정할 수 있다.
• 오라클 옵티마이저는 쿼리를 인라인뷰나 임시 테이블로 여긴다.
• Oracle 9 이상 지원

 

[참고] MySQL Inline View

• FROM 절에서 사용되는 서브쿼리를 인라인 뷰 라고한다.
• 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다.
• 인라인 뷰는 SQL 문이 실행될 때만 임시로 생성되는 동적뷰이다.
• 인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같다.

 

출처: <https://atoz-develop.tistory.com/entry/Oracle-WITH-%EA%B5%AC%EB%AC%B8%EC%9D%84-MySQL%EB%A1%9C-%EB%B3%80%ED%99%98%ED%95%98%EA%B8%B0>

 

##기타사항##

최신버전의 MariaDB로의 이관이라서 해당부분을 그대로 사용 할 수 있었으나. 기존 오라클에서 사용할때는 rownum 사용하는 부분이 존재해서 곤란했다...

이전글 다음글을 불러오는 로직이였는데

임시테이블(with구문)에서 선언하던 rownum부분을 빼 버리고 사용할때마다 세팅해주는 형태로 변경함.

맨 위에서(서브쿼리x) order by를 호출하는게 아니라면 해당 orderby를 무시해버리는것 때문에 with에서 생성한 테이블을 사용할때 order by를 해주는형태로 변경함

WITH prevnext AS ( 
		SELECT rn.*
		FROM (
				SELECT A.SNP AS snp, A.SNP_BIZ AS snpBiz,
				(SELECT DET_NM FROM snip_vnet_v2.BIZ_MGR WHERE SNP = A.SNP_BIZ) AS bizNm,
				A.BIZ_DET_CD AS bizDetCd,
				(SELECT PRV_NM FROM snip_vnet_v2.CO_CODE_PRV WHERE PRV_CD = A.BIZ_DET_CD) AS bizDetNm,
				A.TITLE AS title, A.CONTENT_HPAGE AS contents, A.HTML_YN AS htmlYn,
				A.CONTENT_TXT AS contentTxt, DATE_FORMAT(A.START_DT, '%Y-%m-%d') AS startDt,
				DATE_FORMAT(A.END_DT, '%Y-%m-%d') AS endDt, 
				A.REQ_TARGET AS reqTarget, A.DOWN_AUTH AS downAuth,
				A.MGR_ID AS mgrId, snip_vnet_v2.FN_DEPT_NM_BY_MGR(A.MGR_ID) 
				AS userNm, DATE_FORMAT(A.REG_DT, '%Y-%m-%d') AS regDt,
				DATE_FORMAT(A.REG_DT, '%Y-%m-%d %H:%i:%s') AS regDt2,
				A.FILE_SEQ AS fileSeq, A.READ_CNT AS readCnt,
				A.ONLINE_YN AS onlineYn, A.END_TM AS endTm 
				FROM snip_vnet_v2.BIZ_GONGGO a
				WHERE 1=1 AND A.REG_DT > '2016-01-01' AND USE_YN = 'Y'
		) rn 
)
select 
(@`PREV` := 'PREV') as prevnext,
a.TITLE AS title,
a.snp
from (select *, @rownum:=@rownum+1 as rnum from prevnext,(select @rownum:=0) newtmp1 order by regDt2 desc) a
left join (select *, @newnum:=@newnum+1 as newnum from PREVNEXT,(select @newnum:=0) newtmp2 order by regDt2 desc) bb
on a.rnum = bb.newnum-1 where bb.snp='pk값'
UNION ALL
select 
(@`NEXT` := 'NEXT') as prevnext,
a.TITLE AS title,
a.snp
FROM (select *, @rnumone:=@rnumone+1 as rnumone from PREVNEXT,(select @rnumone:=0) ntmp1 order by regDt2 desc) a
left join (select *, @rnumtow:=@rnumtow+1 as rnumtow from PREVNEXT,(select @rnumtow:=0) ntmp2 order by regDt2 desc) bb
on a.rnumone = bb.rnumtow +1 where bb.snp='pk값';