1. 2.0 CONNECT BY~START WITH 보충설명
2. 2.1 예시 테이블(3-9 페이지참조)
3. 2.2 'PA101'부품의 하위 부품을 찾는 쿼리를 작성하라.
4. 2.3 부품 PA101과 그 하위부품을 찾아 재고가 안전재고 이하이면 대체부품을 찾고 대체부품의 재고도 안전재고 이하이면 긴급구매로 출력하라.
5. 2.4 대체부품이 여러개이고 이들간에 우선순위가 있을 때 어떤 부품의 재고가 부족한 경우 우선순위에 입각해서 출고 가능한 대체품목을 하나만 찾는 쿼리를 작성하라.
6. 2.5 개인연습문제

2.0 CONNECT BY~START WITH 보충설명

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

2.0.2 예시테이블

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'

DEPT_CD          DEPT_NM
------------------------
0000000         사장실
    AA0001       경영지원
      AB0001     재무
      AC0001     총무
   BA0001        기술지원
      BB0001     H/W지원
      BC0001     S/W지원
   CA0001        영업
      CB0001     영업기획
      CC0001     영업1
     CD0001     영업2

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지원

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     사장실

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

2.1 예시 테이블(3-9 페이지참조)

식별자관계

• 부품:#부품코드,부품명,규격,재질,안전재고,현재고,대체부품코드
• 부품구조:#부품코드,상위부품코드,소요량

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.부품코드

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.대체부품코드
)

2.4 대체부품이 여러개이고 이들간에 우선순위가 있을 때 어떤 부품의 재고가 부족한 경우 우선순위에 입각해서 출고 가능한 대체품목을 하나만 찾는 쿼리를 작성하라.

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.부품코드

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 	 	      	             	 

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>