No. 10373

TIPS(21) : 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하면
     SELECT RPAD( ' ', LEVEL * 5 ) || NAME FROM UNIVERSE 
      CONNECT BY PRIOR NAME = PARENT START WITH PARENT IS NULL; 
다음과 같은 결과를 얻게 된다.     
    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 ) ;