[Oracle] 계층형 질의문

계층형 질의문은 서열이 있는 데이터에서 서열을 출력하는 SQL 문법이다.

사원 테이블에는 서열이 존재하기 때문에, 그 숨어있는 서열을 화면에 표시하는 것이다.

서열에 관련된 컬럼이 mgr(관리자 번호) 이다.

 

 

 

 

예제 1. 사원 이름, 서열, 월급, 직업을 출력

select ename, level, sal, job
    from emp
    start with ename = 'KING'
    connect by prior empno = mgr;

start with 절에는 시작되는 사원을 기술하면 된다.

 

 

 

예제 2. level 만큼 앞에 공백을 채워 넣어서 사원 이름, 서열, 직업, 월급을 출력

select rpad('   ', level*3) || ename as employee, level, job, sal
    from emp
    start with ename = 'KING'
    connect by prior empno = mgr;

rpad(’ ‘, 12) 라고 하면 '공백(’ ‘)을 12개 채워넣어라' 라는 뜻이다.

 

 

 

예제 3. 사원 테이블에서 KING 을 시작으로 서열을 출력하는데, BLAKE 는 제외하고 출력

select rpad('   ', level*3) || ename as employee, level, job, sal
    from emp
    where ename != 'BLAKE'
    start with ename = 'KING'
    connect by prior empno = mgr;

 

 

 

예제 4. 사원 테이블에서 KING 을 시작으로 서열을 출력하는데, BLAKE 와 BLAKE 의 팀원들을 전부 제외하고 출력

select rpad('   ', level*3) || ename as employee, level, job, sal
    from emp
    start with ename = 'KING'
    connect by prior empno = mgr and ename != 'BLAKE';

하나의 가지를 제거하고 싶다면 connect by 절에 조건을 주면 된다.

 

 

 

예지 5. KING 을 시작으로 서열을 부여한 결과를 출력하는데, 월급이 높은 사원부터 출력

select rpad(' ', level*3) || ename as employee, level, job, sal
    from emp
    start with ename = 'KING'
    connect by prior empno = mgr
    order by 4 desc;

order by 절을 그냥 썼더니 팀장이 누구고 그 팀장의 팀원들은 누구인지를 알 수 없게 되어버렸다.

만약에 그 서열을 유지시키면서 월급이 높은 순으로 정렬이 되게 하고 싶다면 다음과 같이 하면 된다.

 

 

select rpad(' ', level*3) || ename as employee, level, job, sal
    from emp
    start with ename = 'KING'
    connect by prior empno = mgr
    order siblings by 4 desc;

order 와 by 사이에 siblings 라는 옵션을 주게 되면 서열을 유지하면서 월급이 높은 사원 순으로 정렬해서 출력한다.

계층형 질의문에서 order by 절을 사용할 때는 siblings 가 필수다.

 

 

 

예제 6. KING 을 시작으로 서열을 출력하는데, 데이터를 가로로 출력

select ename, sys_connect_by_path(ename, '/') as path
    from emp
    start with ename = 'KING'
    connect by prior empno = mgr;

sys_connect_by_path 함수는 listagg 함수처럼 데이터를 가로로 출력하는 함수다.

 

 

 

예제 7. 숫자를 1부터 10까지 출력

select level
    from dual
    connect by level <= 10;

 

 

 

예제 8. 1부터 10까지 짝수 숫자들의 합을 출력

select sum(level)
    from dual
    where mod(level, 2)= 0
    connect by level <= 10;

 

 

 

예제 9. 주사위 10번 던지기 (1 ~ 6까지 중 하나를 랜덤으로 출력)

select round(dbms_random.value(0.5, 6.5))
    from dual
    connect by level <= 10;

dbms_random.value(0.5, 6.5) : 0.5 ~ 6.5 사이의 실수를 랜덤으로 출력한다.

주사위의 눈은 정수로 출력되므로 round 함수로 반올림한다.

connect by level <= 10 이라고 하고 select 절에 level 이라는 컬럼은 따로 없어도 된다.

대신 round(dbms_random.value(0.5, 6.5)) 가 10번 실행돼서 주사위를 10번 던지는 효과를 본다.