계층 쿼리

hierarchical-query-clause

hierarchical-query-clause를 포함하는 하위 선택을 계층 쿼리라고 합니다.

start-with-clause

connect-by-clause

start-with-clause

START WITH는 재귀의 시드(seed)를 표시합니다. start-with-clause는 계층 쿼리의 중간 결과 테이블 H₁을 지정합니다. 테이블 H₁은 search-condition이 참인 R의 행으로 구성됩니다. start-with-clause를 지정하지 않은 경우 H₁은 전체 중간 결과 테이블 R입니다. start-with-clause의 search-condition에 대한 규칙은 WHERE절 내의 규칙과 동일합니다.

connect-by-clause

CONNECT BY는 재귀 단계를 설명합니다. connect-by-clause는 검색 조건을 사용하여 H_n을 R과 조인함으로써 H_n에서 중간 결과 테이블 H_n+1을 생성합니다. NOCYCLE 키워드를 지정하는 경우 반복된 행은 중간 결과 테이블 H_n+1에 포함되지 않습니다. 오류가 리턴되지 않습니다. connect-by-clause 내의 search-condition에 대한 규칙은 WHERE절의 규칙과 동일합니다. 단, OLAP 스펙을 지정할 수 없습니다(SQLSTATE 42903).

첫 번째 중간 결과 테이블 H₁이 설정되면 후속 중간 결과 테이블 H₂, H₃ 등이 생성됩니다. 이후 작성되는 중간 결과 테이블은 조인 조건으로 connect-by-clause를 사용하여 H_n을 테이블 R과 조인함으로써 H_n+1을 생성하는 방식으로 생성됩니다. R은 하위 선택의 FROM절과 WHERE절에 있는 Join 술어의 결과입니다. H_n+1에서 빈 결과 테이블을 생성하면 프로세스가 중지됩니다. hierarchical-query-clause의 결과 테이블 H는 UNION ALL이 모든 중간 결과 테이블에 적용된 경우와 동일한 결과입니다.

   CONNECT BY MGRID = PRIOR EMPID

규칙

• 중간 결과 테이블 H_n+1이 임의 계층 경로의 R에서 이미 해당 계층 경로에 있는 R의 행과 동일한 행을 리턴한 경우 오류가 리턴됩니다(SQLSTATE 560CO).
• NOCYCLE 키워드를 지정한 경우 오류가 리턴되지는 않지만 반복된 행은 중간 결과 테이블 H_n+1에 포함되지 않습니다.
• 최대 64레벨의 재귀를 지원합니다(SQLSTATE 54066).
• 계층 쿼리인 하위 선택은 선택 목록에서 명시적 ORDER BY절, GROUP BY절, HAVING절 또는 DISTINCT 키워드를 사용하여 순서를 삭제하지 않는 한 중간 결과 세트를 부분 순서로 리턴합니다. 부분 순서는 특정 계층 구조에 대해 H_n+1에서 생성된 행이 그러한 행을 생성한 H_n의 행 바로 다음에 배치되도록 행을 리턴합니다. ORDER SIBLINGS BY절을 사용하여 동일한 상위에서 생성된 행 세트 내에 순서를 적용할 수 있습니다.
• 계층 쿼리는 구체화된 쿼리 테이블(SQLSTATE 428EC)에 지원되지 않습니다.
• CONNECT BY절은 XML 함수 또는 XQuery와 함께 사용할 수 없습니다(SQLSTATE 428H4).
• 다음 위치에서는 시퀀스에 대해 NEXT VALUE 표현식을 지정할 수 없습니다(SQLSTATE 428F9).
  • CONNECT_BY_ROOT 연산자 또는 SYS_CONNECT_BY_PATH 함수의 매개변수 목록
  • START WITH 및 CONNECT BY절

참고

• 계층 쿼리 지원은 다음과 같은 방식으로 하위 선택에 영향을 줍니다.
  • 하위 선택의 절은 다음 순서로 처리됩니다.
    1. FROM절
    2. hierarchical-query-clause
    3. WHERE절
    4. GROUP BY절
    5. HAVING절
    6. SELECT절
    7. ORDER BY절
    8. FETCH FIRST절
  • WHERE절의 술어를 처리하는 순서에는 특수 규칙이 적용됩니다. search-condition은 AND 조건(접속사)과 함께 술어의 인수가 됩니다. 술어가 내재된 Join 술어인 경우(즉, FROM절에서 둘 이상의 테이블을 참조함) 술어는 hierarchical-query-clause가 적용되기 전에 적용됩니다. FROM절에서 최대 하나의 테이블을 참조하는 술어는 hierarchical-query-clause의 중간 결과 테이블에 적용됩니다.

    조인을 포함하는 계층 쿼리를 작성하는 경우, WHERE절 술어 적용에 대해 혼동하지 않도록 ON절을 사용한 명시적 조인된 테이블을 사용하십시오.

  • ORDER SIBLINGS BY절을 지정할 수 있습니다. 이 절은 계층 구조 내의 동위에만 순서 지정이 적용되도록 지정합니다.
• 의사 컬럼은 특정 컨텍스트에서 의미를 가지며 컬럼 및 변수와 동일한 네임스페이스를 공유하는 규정된 또는 규정되지 않은 ID입니다. 규정되지 않은 ID가 컬럼 또는 변수를 식별하지 않으면 해당 ID로 의사 컬럼이 식별되는지 여부를 검사합니다.

  LEVEL은 계층 쿼리에서 사용할 의사 컬럼입니다. LEVEL 의사 컬럼은 행이 생성된 계층 구조에서 재귀 단계를 리턴합니다. START WITH절에 의해 생성된 모든 행은 값 1을 리턴합니다. CONNECT BY절의 첫 번째 반복을 적용하여 생성된 행은 2를 리턴합니다. 컬럼의 데이터 유형은 INTEGER NOT NULL입니다.

  계층 쿼리의 컨텍스트에서 LEVEL을 지정해야 합니다. START WITH절에서 CONNECT_BY_ROOT 연산자의 인수로 또는 SYS_CONNECT_BY_PATH 함수의 인수로 LEVEL을 지정할 수 없습니다.

• 계층 쿼리를 지원하는 단항 연산자는 CONNECT_BY_ROOT 및 PRIOR입니다.
• 계층 쿼리를 지원하는 함수는 SYS_CONNECT_BY_PATH 스칼라 함수입니다.

예

• 다음 보고서 체인 예는 connect-by 재귀를 보여 줍니다. 예는 다음 데이터로 작성되고 채워진 MY_EMP 테이블에 기반합니다.

  CREATE TABLE MY_EMP(
    EMPID  INTEGER NOT NULL PRIMARY KEY,
    NAME   VARCHAR(10),
    SALARY DECIMAL(9, 2),
    MGRID  INTEGER);
  
  INSERT INTO MY_EMP VALUES ( 1, 'Jones',    30000, 10);
  INSERT INTO MY_EMP VALUES ( 2, 'Hall',     35000, 10);
  INSERT INTO MY_EMP VALUES ( 3, 'Kim',      40000, 10);
  INSERT INTO MY_EMP VALUES ( 4, 'Lindsay',  38000, 10);
  INSERT INTO MY_EMP VALUES ( 5, 'McKeough', 42000, 11);
  INSERT INTO MY_EMP VALUES ( 6, 'Barnes',   41000, 11);
  INSERT INTO MY_EMP VALUES ( 7, 'O''Neil',  36000, 12);
  INSERT INTO MY_EMP VALUES ( 8, 'Smith',    34000, 12);
  INSERT INTO MY_EMP VALUES ( 9, 'Shoeman',  33000, 12);
  INSERT INTO MY_EMP VALUES (10, 'Monroe',   50000, 15);
  INSERT INTO MY_EMP VALUES (11, 'Zander',   52000, 16);
  INSERT INTO MY_EMP VALUES (12, 'Henry',    51000, 16);
  INSERT INTO MY_EMP VALUES (13, 'Aaron',    54000, 15);
  INSERT INTO MY_EMP VALUES (14, 'Scott',    53000, 16);
  INSERT INTO MY_EMP VALUES (15, 'Mills',    70000, 17);
  INSERT INTO MY_EMP VALUES (16, 'Goyal',    80000, 17);
  INSERT INTO MY_EMP VALUES (17, 'Urbassek', 95000, NULL);

  The following query returns all employees working for Goyal, as well as some additional information, such as the reports-to-chain:

  
    1 SELECT NAME,
    2        LEVEL,
    3        SALARY,
    4        CONNECT_BY_ROOT NAME AS ROOT,
    5        SUBSTR(SYS_CONNECT_BY_PATH(NAME, ':'), 1, 25) AS CHAIN
    6   FROM MY_EMP
    7   START WITH NAME = 'Goyal'
    8   CONNECT BY PRIOR EMPID = MGRID
    9   ORDER SIBLINGS BY SALARY;

    NAME       LEVEL       SALARY      ROOT  CHAIN
    ---------- ----------- ----------- ----- ---------------
    Goyal                1    80000.00 Goyal :Goyal
    Henry                2    51000.00 Goyal :Goyal:Henry
    Shoeman              3    33000.00 Goyal :Goyal:Henry:Shoeman
    Smith                3    34000.00 Goyal :Goyal:Henry:Smith
    O'Neil               3    36000.00 Goyal :Goyal:Henry:O'Neil
    Zander               2    52000.00 Goyal :Goyal:Zander
    Barnes               3    41000.00 Goyal :Goyal:Zander:Barnes
    McKeough             3    42000.00 Goyal :Goyal:Zander:McKeough
    Scott                2    53000.00 Goyal :Goyal:Scott

  Lines 7 and 8 comprise the core of the recursion: The optional START WITH clause describes the WHERE clause that is to be used on the source table to seed the recursion. In this case, only the row for employee Goyal is selected. If the START WITH clause is omitted, the entire source table is used to seed the recursion. The CONNECT BY clause describes how, given the existing rows, the next set of rows is to be found. The unary operator PRIOR is used to distinguish values in the previous step from those in the current step. PRIOR identifies EMPID as the employee ID of the previous recursive step, and MGRID as originating from the current recursive step.

  The LEVEL pseudocolumn in line 2 indicates the current level of recursion.

  CONNECT_BY_ROOT is a unary operator that always returns the value of its argument as it was during the first recursive step; that is, the values that are returned by an explicit or implicit START WITH clause.

  SYS_CONNECT_BY_PATH() is a binary function that prepends the second argument to the first and then appends the result to the value that it produced in the previous recursive step. The arguments must be character types.

  Unless explicitly overridden, connect-by recursion returns a result set in a partial order; that is, the rows that are produced by a recursive step always follow the row that produced them. Siblings at the same level of recursion have no specific order. The ORDER SIBLINGS BY clause in line 9 defines an order for these siblings, which further refines the partial order, potentially into a total order.

• Return the organizational structure of the DEPARTMENT table. Use the level of the department to visualize the hierarchy.

  
     SELECT LEVEL, CAST(SPACE((LEVEL - 1) * 4) || '/' || DEPTNAME
         AS VARCHAR(40)) AS DEPTNAME
       FROM DEPARTMENT
       START WITH DEPTNO = 'A00'
       CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPT

  The query returns:

  LEVEL       DEPTNAME                                
  ----------- ----------------------------------------
            1 /SPIFFY COMPUTER SERVICE DIV.       
            2     /PLANNING                       
            2     /INFORMATION CENTER             
            2     /DEVELOPMENT CENTER             
            3         /MANUFACTURING SYSTEMS      
            3         /ADMINISTRATION SYSTEMS     
            2     /SUPPORT SERVICES               
            3         /OPERATIONS                 
            3         /SOFTWARE SUPPORT           
            3         /BRANCH OFFICE F2           
            3         /BRANCH OFFICE G2           
            3         /BRANCH OFFICE H2           
            3         /BRANCH OFFICE I2           
            3         /BRANCH OFFICE J2