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하면
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 ) ;
