h2.2.0 CONNECT BY~START WITH 보충설명
h4.2.0.1 구문설명
1)자료의 구조가 계층적으로 이루어진 경우 상위자료에서 부터 하위자료로의 순전개 및 하위자료에서 상위자료로의 역전개를 위해 사용되는 구문이다.
2)WHERE절 다음에 위치한다. CONNECT BY와 START WITH는 순서가 바뀌어도 상관없다.
3)START WITH
-계층구조에서 ROOT를 의미한다.
-만일 생략되면 테이블 내의 모든 로우를 루트로 하여 계층적 구조를 풀어낸다.
-서브쿼리문이 올 수 있으며 =,<=,>,>=,<>등의 모든 관계연산자와 IN,NOT IN 구문이 올 수 있다.
4)CONNECT BY
-자료 전개의 방향을 결정한다(순전개,역전개).
-AND 문을 사용하여 여러 개의 조건을 나열할 수 있다.
-WHERE 절에 사용된 조건은 테이블내의 그 로우만 영향받지만 CONNECT BY에 사용된 조건은 해당 로우와 해당 로우의 하위로우(순전개시) 혹은 상위로우(역전개시) 모두 영향받는다.
5)PRIOR
-계층구조 쿼리문에서 현재 선택된 로우에 대한 부모를 지칭한다.
-1항 연산자이다.
-SELECT문에 사용될 수 있다. (EX:SELECT DEPT_CD,PRIOR DEPT_CD FROM TDEPT)
-오라클 도큐먼트
PRIOR evaluates the connect_by_condition for the parent row of the current row in a hierarchical query.
PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators
h4.2.0.2 예시테이블
TDEPT
| DEPT_CD | DEPT_NM | P_DEPT |
|---|---|---|
| 000000 | 사장실 | |
| AA0001 | 경영지원 | 000000 |
| AB0001 | 재무 | AA0001 |
| AC0001 | 총무 | AA0001 |
| BA0001 | 기술지원 | 000000 |
| BB0001 | H/W지원 | BA0001 |
| BC0001 | S/W지원 | BA0001 |
| CA0001 | 영업 | 000000 |
| CB0001 | 영업기획 | CA0001 |
| CC0001 | 영업1 | CA0001 |
| CD0001 | 영업2 | CA0001 |
h4.2.0.3 사장실을 기준으로 하위부서를 도출하라
SELECT LPAD(DEPT_CD,LEVEL*5,' '),DEPT_NM
FROM TDEPT
CONNECT BY PRIOR DEPT_CD=P_DEPT
START WITH DEPT_CD='000000'
1. START WITH DEPT_CD='000000' 구문에 의해 맨 처음 사장실 로우가 선택된다.
2. CONNECT BY PRIOR DEPT_CD=P_DEPT 구문에 의해 현재 선택된 로우(사장실)의 DEPT_CODE(0000000)을 P_DEPT로 가지는 로우를 찾는다.
3. 0000000을 P_DEPT로 가지는 로우는 AA0001,BA0001,CA0001 세개가 있는데 먼저 SELECT되는 로우를 선택한다.(여기서는 AA0001라 가정한다.)
4. 이제 현재 선택된 로우는 AA0001이므로 현재 로우의 DEPT_CD(AA0001)을 P_DEPT로 가지는 로우를 찾는다.
5. AA0001을 P_DEPT로 가지는 로우는 AB0001,AC0001 두개가 있는데 먼저 SELECT되는 로우를 선택한다.(여기서는 AB0001)이라 가정한다.)
6. 이제 현재 선택된 로우는 AB0001이므로 현재 로우의 DEPT_CD(AB0001)을 P_DEPT로 가지는 로우를 찾는다.
7. 불행히도 그런 로우는 없으므로 탐색을 마치고 AA0001을 P_DEPT로 가지는 두번째 로우인 AC0001을 선택한다.
8. 역시 현재 선택된 로우인 AC0001을 P_DEPT로 가지는 로우가 없으므로 탐색을 마친다.
9. AA0001와 그 하위부서에 대한 탐색이 종료되었으므로 3번으로 돌아가 두번째 SELECT되는 BA0001을 선택하고 5~8번 과정을 반복한다.(이렇게 모든 로우에 대해 탐색을 한다)
DEPT_CD DEPT_NM
------------------------
0000000 사장실
AA0001 경영지원
AB0001 재무
AC0001 총무
BA0001 기술지원
BB0001 H/W지원
BC0001 S/W지원
CA0001 영업
CB0001 영업기획
CC0001 영업1
CD0001 영업2
h4.2.0.4 WHERE절과 CONNECT BY 조건문의 차이
<WHERE>
SELECT LPAD(DEPT_CD,LEVEL*5,' '),DEPT_NM
FROM TDEPT
WHERE DEPT_CD<>'CA0001'
CONNECT BY PRIOR DEPT_CD=P_DEPT
START WITH DEPT_CD='000000'
DEPT_CD DEPT_NM
------------------------
0000000 사장실
AA0001 경영지원
AB0001 재무
AC0001 총무
BA0001 기술지원
BB0001 H/W지원
BC0001 S/W지원
(없슴)
CB0001 영업기획
CC0001 영업1
CD0001 영업2
<CONNECT BY>
SELECT LPAD(DEPT_CD,LEVEL*5,' '),DEPT_NM
FROM TDEPT
CONNECT BY PRIOR DEPT_CD=P_DEPT
AND DEPT_CD<>'CA0001'
START WITH DEPT_CD='000000'
DEPT_CD DEPT_NM
------------------------
0000000 사장실
AA0001 경영지원
AB0001 재무
AC0001 총무
BA0001 기술지원
BB0001 H/W지원
BC0001 S/W지원
h4.2.0.5 역전개
SELECT LPAD(DEPT_CD,LEVEL*5,' '),DEPT_NM
FROM TDEPT
CONNECT BY PRIOR P_DEPT=DEPT_CD
START WITH DEPT_CD='CD0001'
DEPT_CD DEPT_NM
------------------------
CD0001 영업2
CA0001 영업
0000000 사장실
h4.2.0.6 SELECT절에 PRIOR 연산자 사용하기
SELECT LPAD(DEPT_CD,LEVEL*5,' '),DEPT_NM,P_DEPT,PRIOR DEPT_CD
FROM TDEPT
CONNECT BY PRIOR DEPT_CODE=PARENT_DEPT
START WITH DEPT_CODE='000000'
DEPT_CD DEPT_NM P_DEPT PRIOR DEPT_CD
-------------------------------------------------
00000 사장실
AA0001 경영지원 000000 000000
AB0001 재무 AA0001 AA0001
AC0001 총무 AA0001 AA0001
BA0001 기술지원 000000 000000
BB0001 H/W지원 BA0001 BA0001
BC0001 S/W지원 BA0001 BA0001
CA0001 영업 000000 000000
CB0001 영업기획 CA0001 CA0001
CC0001 영업1 CA0001 CA0001
CD0001 영업2 CA0001 CA0001
h2.2.1 예시 테이블(3-9 페이지참조)
*식별자관계
부품:#부품코드,부품명,규격,재질,안전재고,현재고,대체부품코드
부품구조:#부품코드,상위부품코드,소요량
h2.2.2 'PA101'부품의 하위 부품을 찾는 쿼리를 작성하라.
<잘못된 쿼리문:조인에는 CONNECT BY절을 사용할 수 없다>
SELECT LPAD(' ',2,*LEVEL)||X.부품코드,X.THDYFID,Y.부품명
FROM 부품구조 X,부품 Y
WHERE X.부품코드=Y.부품코드
CONNECT BY PRIOR X.부품코드=X.상위부품코드
START WITH X.부품코드='PA101'
<옳바른 쿼리문>
SELECT LPAD(' ',2*LV)||X.부푸코드,X.소요량,Y.부품명
FROM
(
SELECT LEVEL LV,부품코드,소요량
FROM 부품구조
CONNECT BY PRIOR 부품코드=상위부품코드
START WITH 부품코드='PA101'
) X,부품 Y
WHERE Y.부품코드=X.부품코드
h2.2.3 부품 PA101과 그 하위부품을 찾아 재고가 안전재고 이하이면 대체부품을 찾고 대체부품의 재고도 안전재고 이하이면 긴급구매로 출력하라.
SELECT
LPAD(' ',2*LVL)||DECODE(SW,'2',대체부품코드,원부품코드) 부품코드 --SW=2인 경우 대체부품을 출고한다
,소요량
,DECODE(SW,'2',대체부품명,원부품명) 부품명
,DECODE(SW,'1','원부품출고','2','대체부품출고','긴급구매') 조달상태
FROM
(
SELECT
LVL,X.부품코드 원부품코드,Z.부품코드 대체부품코드,소요량
,DECODE(
LEAST(Y.안전재고,Y.현재고),Y.안전재고,'1' --Y.안전재고 <= Y.현재고
,DECODE( --Y.안전재고 > Y.현재고 이므로 대체부품을 찾는다
LEAST(Z.안전재고,Z.현재고),Z.안전재고,'2' --Z.안전재고 <= Z.현재고
,'3' --Z.안전재고 > Z.현재고 이므로 긴급상황!!!
)
) SW
,X.부품명 원부품명
,Z.부품명 대체부품명
FROM
(
--PA101과 그 하위부품에 대한 순전개
SELECT
LEVEL LVL,부품코드,소요량
FROM 부품구조
CONNECT BY PRIOR 부품코드=상위부품코드
START WITH 부품코드='PA101'
) X,부품 Y,부품 Z
WHERE Y.부품코드=X.부품코드
--대체부품은 없을 수도 있으므로 아웃조인
AND Z.부품코드(+)=Y.대체부품코드
)
h2.2.4 대체부품이 여러개이고 이들간에 우선순위가 있을 때 어떤 부품의 재고가 부족한 경우 우선순위에 입각해서 출고 가능한 대체품목을 하나만 찾는 쿼리를 작성하라.
h4.2.4.1 잘못된 쿼리문
SELECT ......
FROM 부품 Y
(
SELECT .....
FROM 부품구성
CONNECT BY .....
START WITH .....
) X
,(
SELECT --INDEX(W 부품우선순위_DIX)
부품코드,NVL(MAX(현재고),0) 현재고
FROM 대체품목 W
WHERE 현재고>=안전재고
AND ROWNUM=1
) Z
WHERE Y.부품코드=X.부품코드
AND Z.부품코드(+)=Y.부품코드
1) Z.부품코드( + )=Y.부품코드 조건에 의하여 인라인뷰에 부품코드 조건이 파고 들어감으로써 불필요한 다른 부품은 엑세스하지 않는다.
2) 인라인뷰에서 현재고>=안전재고인 부품을 찾지만 모두 찾는 것이 아니라 INDEX(W 부품우선순위_DIX)와 AND ROWNUM=1 에 의해 우선순위가 높은 단 1개의 로우만 엑세스한다.
3) 그러나 예상은 이러하지만 인라인뷰의 ROWNUM=1 조건에 의해 실행계획상 인라인뷰가 먼저 실행된다. 그리고 ROWNUM=1 조건에 의해 현재고>=안전재고인 로우를 무작위로 하나만
SELECT 하게된다. 따라서 다행히 인라인뷰에서 SELECT된 부품이 부품과 부품구성의 조인에 의해 SELECT된 부품이라면 결과가 나오겠지만 그게 아니라면 SELECT 건 수가 없다.
4) 더구나 대체부품을 찾고자 하는 부품이 1개가 아니라 복수개일지라도 인라인뷰에서는 ROWNUM=1의 조건에 의해 무조건 하나의 로우만 SELECT 되는 문제점이 있다.
h4.2.4.2 옳바른 쿼리문???
1)예시테이블
아래와 같이 부품,대체부품 테이블이 있다. 부품테이블에서 현재고<안전재고인 부품에 대하여 대체부품 테이블에서 대체부품의 현재고>=안전재고인 대체부품을 찾아라.
단,대체부품을 엑세스할 때 부품테이블에서 현재고<안전재고인 부품의 대체부품만을 엑세스할 것이며 대체부품이 복수개인 경우 우선순위(ORDERING)에 입각하여
하나의 대체부품만을 찾아라.
결론적으로 부품테이블에서 A0,B0가 해당되며 그 대체부품으로 A3,B2가 나와야 한다.
CREATE TABLE PART_MST( --부품테이블
PART_CD VARCHAR2(10) CONSTRAINT PART_MST_PK PRIMARY KEY,
PART_NM VARCHAR2(10),
CURR_QTY NUMBER, --현재고
SAFE_QTY NUMBER --안전재고
)
--현재고<안전재고 인부품:A0,B0,C0
INSERT INTO PART_MST VALUES('A0','A0',5,10);
INSERT INTO PART_MST VALUES('B0','B0',5,10);
INSERT INTO PART_MST VALUES('C0','C0',7,10);
INSERT INTO PART_MST VALUES('D0','D0',15,10);
CREATE TABLE RPART_MST( --대체부품 테이블
RPART_CD VARCHAR2(10) CONSTRAINT RPART_MST_PK PRIMARY KEY,
RPART_NM VARCHAR2(10),
OPART_CD VARCHAR2(10), --원부품코드
CURR_QTY NUMBER, --현재고
SAFE_QTY NUMBER, --안전재고
ORDERING NUMBER --우선순위
)
CREATE INDEX ORDER_IDX ON SCOTT.RPART_MST(ORDERING)
CREATE INDEX OPART_IDX ON SCOTT.RPART_MST(OPART_CD)
--현재고>=안전재고인 부품:A3,A4,B2,B3,D1
INSERT INTO RPART_MST VALUES('A1','A1','A0',5,10,1);
INSERT INTO RPART_MST VALUES('A2','A2','A0',7,10,2);
INSERT INTO RPART_MST VALUES('A3','A3','A0',10,10,3);
INSERT INTO RPART_MST VALUES('A4','A4','A0',15,10,4);
INSERT INTO RPART_MST VALUES('B1','B1','B0',15,10,1);
INSERT INTO RPART_MST VALUES('B2','B2','B0',17,10,2);
INSERT INTO RPART_MST VALUES('B3','B3','B0',10,10,3);
INSERT INTO RPART_MST VALUES('C1','C1','C0',5,10,1);
INSERT INTO RPART_MST VALUES('C2','C2','C0',1,10,2);
INSERT INTO RPART_MST VALUES('C3','C3','C0',3,10,3);
INSERT INTO RPART_MST VALUES('D1','D1','D0',13,10,1);
ANALYZE TABLE PART_MST COMPUTE STATISTICS FOR ALL INDEXED COLUMNS
ANALYZE TABLE RPART_MST COMPUTE STATISTICS FOR ALL INDEXED COLUMNS
2) 첫번째 쿼리문
SELECT
A.PART_cd,A.CURR_QTY,A.SAFE_QTY
,B.RPART_CD,B.CURR_QTY,B.SAFE_QTY,B.ORDERING
FROM
(
SELECT
RPART_CD,RPART_NM,OPART_CD,CURR_QTY,SAFE_QTY,ORDERING
,ROW_NUMBER() OVER(PARTITION BY OPART_CD ORDER BY ORDERING) RM
FROM RPART_MST
WHERE CURR_QTY>=SAFE_QTY
) B,PART_MST A
WHERE A.CURR_QTY<A.SAFE_QTY
AND A.PART_CD=B.OPART_CD
AND A.PART_CD>' '
AND RM=1
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 4 7
HASH JOIN 4 288 7
TABLE ACCESS BY INDEX ROWID SCOTT.PART_MS 2 12 2
INDEX RANGE SCAN SCOTT.PART_MST_PK 4 1
VIEW 6 396 4
WINDOW SORT PUSHED RANK 6 60 4
TABLE ACCESS BY INDEX ROWID SCOTT.RPART_MST 6 60 2
INDEX RANGE SCAN SCOTT.OPART_IDX 11 1
1)
h2.2.5 개인연습문제
1) 책에서는 대체부품 테이블을 별도로 만드는 것으로 예시하고 있으나 어자피 구성칼럼은 똑같을 것이므로 하나의 테이블로 만든다.
2) 즉,부품테이블에 부품코드,대체부품코드 칼럼이 있으며 부품 A0에 대한 대체부품이 A1,A2,A3,A4이고 이 순서대로 우선순위를 갖는다면
A0-A1,A1-A2,A2-A3,A4-NULL 형식으로 구성한다면 별도의 테이블을 만들 필요가 없어진다.
3) 문제
아래와 같이 부품테이블과 부품주문 테이블이 있다.
A0-6개,B0-5개,C0-7개의 주문이 들어온 경우 해당 부품을 출고하되 해당부품의 갯수가 부족한 경우 우선순위에 입각한 대체부품을 출고한다.
대체부품까지 고려한 현재고가 주문갯수보다 많거나 같다면 '출고가능'으로 그렇지 않다면 '부품부족'(붉은색)으로 화면에 출력하라.
CREATE TABLE PART(
PART_CD VARCHAR2(10) CONSTRAINT PART_PK PRIMARY KEY,
PART_NM VARCHAR2(10),
REPL_PART VARCHAR2(10),
CURR_QTY NUMBER,
SAFE_QTY NUMBER
)
INSERT INTO PART VALUES('A0','A0','A1',1,10);
INSERT INTO PART VALUES('A1','A1','A2',3,10);
INSERT INTO PART VALUES('A2','A2','A3',5,10);
INSERT INTO PART VALUES('A3','A3','A4',1,10);
INSERT INTO PART VALUES('A4','A4',NULL,0,10);
INSERT INTO PART VALUES('B0','B0','B1',1,10);
INSERT INTO PART VALUES('B1','B1','B2',4,10);
INSERT INTO PART VALUES('B2','B2',NULL,0,10);
INSERT INTO PART VALUES('C0','C0','C1',1,10);
INSERT INTO PART VALUES('C1','C1','C2',4,10);
INSERT INTO PART VALUES('C2','C2',NULL,0,10);
INSERT INTO PART VALUES('D0','D0','D1',10,10);
INSERT INTO PART VALUES('D1','D1',NULL,4,10);
CREATE TABLE PORDER(
PART_CD VARCHAR2(10) CONSTRAINT PORDER_PK PRIMARY KEY,
ORD_QTY NUMBER
)
INSERT INTO PORDER VALUES('A0',6); //A0계열:총 10개
INSERT INTO PORDER VALUES('B0',5); //B0계열:총 5개
INSERT INTO PORDER VALUES('C0',7); //C0계열:총 4개
4.쿼리문
SELECT
ORD_PART "주문부품"
,ORD_QTY "주문수량"
,OUT_PART "출고부품"
,DECODE(LEAST(ACCUM_QTY,ORD_QTY),ACCUM_QTY,CURR_QTY,ORD_QTY-LAG(ACCUM_QTY,1) OVER(PARTITION BY GUBUN ORDER BY LV)) "출고수량"
,DECODE(SIGN(TOT_QTY-ORD_QTY),-1,'<FONT COLOR=RED>부품부족('||(TOT_QTY-ORD_QTY)||')</FONT>','출고가능') "상태"
FROM
(
SELECT LV
,B.PART_CD ORD_PART
,A.PART_CD OUT_PART
,GUBUN
,CURR_QTY
,ACCUM_QTY,ORD_QTY
,SUM(DECODE(SIGN(ACCUM_QTY-B.ORD_QTY),0,1,1,1,0)) OVER( PARTITION BY GUBUN ORDER BY LV) OK_FLAG
,TOT_QTY
FROM
(
SELECT
LEVEL LV
,PART_CD
,REPL_PART
,SUBSTR(PART_CD,1,1) GUBUN
,CURR_QTY
,SUM(CURR_QTY) OVER(PARTITION BY SUBSTR(PART_CD,1,1) ORDER BY LEVEL) ACCUM_QTY
,SUM(CURR_QTY) OVER(PARTITION BY SUBSTR(PART_CD,1,1)) TOT_QTY
FROM PART
CONNECT BY PRIOR REPL_PART=PART_CD
START WITH PART_CD IN(SELECT PART_CD FROM PORDER)
) A,PORDER B
WHERE A.PART_CD LIKE SUBSTR(B.PART_CD,1,1)||'%'
)
WHERE OK_FLAG<=1
주문부품 주문수량 출고부품 출고수량 상태
-------------------------------------------
A0 6 A0 1 출고가능
A0 6 A1 3 출고가능
A0 6 A2 2 출고가능
B0 5 B0 1 출고가능
B0 5 B1 4 출고가능
C0 7 C0 0 <FONT COLOR=RED>부품부족(-3)</FONT>
C0 7 C1 4 <FONT COLOR=RED>부품부족(-3)</FONT>
C0 7 C2 0 <FONT COLOR=RED>부품부족(-3)</FONT>