root@Twelo.com

Explanation & Example

   Hierarchical query를 구현할 때 ORDER BY 절을 사용하는 것은
   Oracle 7.1 버젼부터 가능한 것이었다.
   그러나, 순서대로 ordering되지 않고 특정 컬럼(emp table의 ename)을
   기준으로 ordering하기를 원한다면 <Bulletin:10373>처럼 procedure를
   작성하여야만 하였다.
   그러나, Oracle 9i 에서는 ORDER BY 절 대신에 ORDER SIBLINGS BY 절을
   사용할 수 있어 user-defined stored procedure를 만들 필요가 없게 되었다. 

1) Ordering 하기 전의 emp table의 Hierarchical query

SQL> @a

ename                      EMPNO    MGR JOB
------------------------- ------ ------ ---------------
     KING                   7839        PRESIDENT
          JONES             7566   7839 MANAGER
               SCOTT        7788   7566 ANALYST
                    ADAMS   7876   7788 CLERK
               FORD         7902   7566 ANALYST
                    SMITH   7369   7902 CLERK
          BLAKE             7698   7839 MANAGER
               ALLEN        7499   7698 SALESMAN
               WARD         7521   7698 SALESMAN
               MARTIN       7654   7698 SALESMAN
               TURNER       7844   7698 SALESMAN

ename                      EMPNO    MGR JOB
------------------------- ------ ------ ---------------
               JAMES        7900   7698 CLERK
          CLARK             7782   7839 MANAGER
               MILLER       7934   7782 CLERK

14 rows selected.

Ordering 하기 전의 a.sql 은 다음과 같다.

   col ename format a25
   col empno format 99999
   col mgr format 99999
   col job format a15
   select rpad(' ', LEVEL*5) || ename "ename", empno, mgr, job
   from emp
   start with job='PRESIDENT'
   connect by prior empno=mgr;

   /

2) 9i의 new feature인 Hierarchical query를 사용하여 Ordering한 경우

SQL> @new_a

ename                      EMPNO    MGR JOB
------------------------- ------ ------ ---------------
     KING                   7839        PRESIDENT
          BLAKE             7698   7839 MANAGER
               ALLEN        7499   7698 SALESMAN
               JAMES        7900   7698 CLERK
               MARTIN       7654   7698 SALESMAN
               TURNER       7844   7698 SALESMAN
               WARD         7521   7698 SALESMAN
          CLARK             7782   7839 MANAGER
               MILLER       7934   7782 CLERK
          JONES             7566   7839 MANAGER
               FORD         7902   7566 ANALYST

ename                      EMPNO    MGR JOB
------------------------- ------ ------ ---------------
                    SMITH   7369   7902 CLERK
               SCOTT        7788   7566 ANALYST
                    ADAMS   7876   7788 CLERK

14 rows selected.

Ordering하기 위해 사용한 new_a.sql 은 다음과 같다.

   col ename format a25
   col empno format 99999
   col mgr format 99999
   col job format a15
   select rpad(' ', LEVEL*5) || ename "ename", empno, mgr, job
   from emp
   start with job='PRESIDENT'
   connect by prior empno=mgr
   order siblings by ename;
   /

HIERARCHICAL QUERY DATA의 SORT와 ORDERING

ORACLE 6에서의 HIERARCHICAL QUERY에서는 SORT를 하기 위한 ORDER BY 절을 사용
할 수 없었다. 그러나, ORACLE 7.1이상 VERSION에서는 USER-DEFINED STORED
PROCEDURE를 이용하여 HIERARCHY 순서로 출력되면서 ORDERING할 수 있게 되었다.

세계의 지역에 관한 자료를 예로 보자.

      CREATE TABLE UNIVERSE
      ( PARENT VARCHAR2(30) REFERENCES UNIVERSE, 
      NAME  VARCHAR2(30) PRIMARY KEY );
     
     REM SOME TEST DATA
     INSERT INTO UNIVERSE VALUES ( NULL, 'WORLD' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'WORLD', 'EUROPE' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'EUROPE', 'ENGLAND' ) ;
     INSERT INTO UNIVERSE VALUES ( 'EUROPE', 'THE NETHERLANDS' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'EUROPE', 'GERMANY' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'WORLD', 'ASIA' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'ASIA', 'JAPAN' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'ASIA', 'CHINA' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'WORLD', 'AMERICA' ) ;
     INSERT INTO UNIVERSE VALUES ( 'AMERICA', 'UNITED STATES' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'AMERICA', 'MEXICO' ) ;
     INSERT INTO UNIVERSE VALUES ( 'WORLD', 'AFRICA' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'AFRICA', 'EGYPT' ) ; 
     INSERT INTO UNIVERSE VALUES ( 'AFRICA', 'MOROCCO' ) ;

위의 자료를 다음과 같이 보고자 하는 경우
WORLD
        AFRICA
                EGYPT
                MOROCCO
        AMERICA
                MEXICO
                UNITED STATES
        ASIA
                CHINA
                JAPAN
        EUROPE
                ENGLAND
                GERMANY
                THE NETHERLANDS

만약,ORDER BY절이 없이 QUERY하면

다음과 같은 결과를 얻게 된다.    
    WORLD
        EUROPE
                ENGLAND
                GERMANY
                THE NETHERLANDS
        ASIA
                JAPAN
                CHINA
        AMERICA
                UNITED STATES
                MEXICO
        AFRICA
                EGYPT
                MOROCCO

만약, 위 문장에 ORDER BY 절을 사용하면

      SELECT RPAD( ' ', LEVEL * 5 ) || NAME FROM UNIVERSE
      CONNECT BY PRIOR NAME = PARENT START WITH PARENT IS NULL 
      ORDER BY NAME;

다음과 같은 원치 않는 결과를 얻게 된다.    
        AFRICA
        AMERICA
        ASIA
                CHINA
                EGYPT
                ENGLAND
        EUROPE
                GERMANY
                JAPAN
                 MEXICO
                MOROCCO
                THE NETHERLANDS
                UNITED STATES
     WORLD

7. 1이상 VERSION에서는 다음과 같이 USER DEFINED FUNCTION을  이용하여

원하는 자료를 얻을 수 있다.

CREATE OR REPLACE FUNCTION UNIVERSESORTORDER( PKEY UNIVERSE.NAME%TYPE ) 
     RETURN VARCHAR2 IS
        PATH VARCHAR2(2000);
     BEGIN
        PATH := PKEY;
        -- INSERT ALL PREVIOUS PARENT RECORDS LIKE A DIRECTORY STRUCTURE
        -- E.G. WORLD/EUROPE/...
        FOR CREC IN ( SELECT PARENT FROM UNIVERSE 
                      CONNECT BY PRIOR PARENT = NAME
                      START WITH NAME = PKEY ) LOOP
           PATH := CREC.PARENT || '/' || PATH;
        END LOOP;
        RETURN PATH;
     END;
     /
     
SELECT SUBSTR( RPAD( ' ', LEVEL * 5) || NAME, 1, 40) "THE UNIVERSE"
       FROM UNIVERSE
       CONNECT BY PRIOR NAME = PARENT
       START WITH PARENT IS NULL
       ORDER BY UNIVERSESORTORDER( NAME ) ;