오라클 WITH문과 계층형 쿼리

WITH절

WITH절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우 자주 실행되는 경우 한번만 Parsing되고 Plan 계획이 수립되므로 쿼리의 성능향상에 도움이 된다.

WITH절 사용법

WITH APPLE AS 
(
 SELECT '사과' AS FRUIT_NAME
 FROM FRUIT
 )
 
 SELECT * FROM APPLE 

• 위 사진과 같이 APPLE이라는 임시 테이블이 형성되어 조회된 결과를 확인 할 수 있다.

다중 WITH문

WITH APPLE AS (
 SELECT  '사과' FRUIT_NAME FROM FRUIT
 UNION ALL
 SELECT  '풋사과' FRUIT_NAME FROM FRUIT
 ), 
 'BANANA' AS (
  SELECT '바나나' FRUIT_NAME FROM FRUIT
  UNION ALL 
  SELECT FRUIT_NAME FROM APPLE 
 )
 
 SELECT * FROM BANANA

• 위 사진과 같이 APPLE이라는 임시테이블을 만들어 BANANA라는 임시테이블을 만들때 UNION ALL을 함으로써 BANANA임시 테이블에 APPLE 데이터 값이 함께 출력되는 것을 볼 수 있다.

예제1

WITH FRUIT_SET AS (
SELECT * FROM FRUIT
WHERE FRUIT_QTY BETWEEN 2 AND 5

SELECT * FROM FRUIT_SET WHERE FRUIT_NAME ='사과'
UNION ALL 
SELECT * FROM FRUIT_SET WHERE FRUIT_NAME ='풋사과'
UNION ALL 
SELECT * FROM FRUIT_SET WHERE FRUIT_NAME ='바나나'

• WITH문의 큰장점은 한번 WITH절의 내용을 한번에 올려놓고 계속 재사용하는 것에 큰 의미가 있기에 WITH절에 구문을 여러번 참조하는 쿼리를 만들수록 그 효과가 배로 증가한다.

계층형 쿼리

계층형 구조는 상하 수직관계의 트리형태의 구조로 이루어진 형태이다.
오라클에서의 계층형 쿼리는 START WITH CONNECT BY 절로 생성할 수 있으며 계층형 정보로 표현할 수 있다.

위 사진과 같은 계층으로 임의로 테이블과 데이터를 생성했다.

CREATE TABLE TEMP_STORE (
     KIND_CD NUMBER NOT NULL, 			-- 종류코드
     PARENT_CD NUMBER,        			-- 상위코드
     FRUIT_NAME VARCHAR2(100) NOT NULL,         -- 부서이름
     PRIMARY KEY (KIND_CD)
);

INSERT INTO TEMP_STORE VALUES ( 101, NULL, '가게');
INSERT INTO TEMP_STORE VALUES ( 102, 101, '과일');
INSERT INTO TEMP_STORE VALUES ( 103, 101, '고기');
INSERT INTO TEMP_STORE VALUES ( 104, 101, '야채');

INSERT INTO TEMP_STORE VALUES ( 105, 102, '복숭아');
INSERT INTO TEMP_STORE VALUES ( 106, 103, '돼지고기');
INSERT INTO TEMP_STORE VALUES ( 107, 103, '소고기');
INSERT INTO TEMP_STORE VALUES ( 108, 105, '천도복숭아');
INSERT INTO TEMP_STORE VALUES ( 111, 104, '오이');
INSERT INTO TEMP_STORE VALUES ( 109, 106, '삼겹살');
INSERT INTO TEMP_STORE VALUES ( 100, 106, '돼지목살');

계층형 쿼리 START WITH CONNECT BY 사용법

SELECT 
FRUIT_NAME,
KIND_CD,
PARENT_CD,
LEVEL
FROM TEMP
START WITH PARENT_CD IS NULL          --최상위 설정,
CONNECT BY PRIOR KIND_CD = PARENT_CD  --부모노드와 자식노드 연결
ORDER BY LEVEL 

• 위의 예제에서는 최상위 코드의 PARENT_CD가 NULL값이므로 PARENT_CD IS NULL로 조건을 주었다.
• CONNECT BY조건에서는 계층형 구조처럼 종류코드(KIND_CD)에 상위코드(PARENT_CD)를 가지고 있으므로 KIND_CD = PARENT_CD로 표현하였다.
• 마지막 컬럼에 나열한 LEVEL컬럼은 계층형 정보를 표현할때 그 계층의 레벨을 나타내준다.