SQL> CREATE TABLE employee (
2 employee_id INTEGER,
3 manager_id INTEGER,
4 first_name VARCHAR2(10) NOT NULL,
5 last_name VARCHAR2(10) NOT NULL,
6 title VARCHAR2(20),
7 salary NUMBER(6, 0)
8 );
Table created.
SQL>
SQL> select * from employee;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
1 0 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000
13 rows selected.
SQL>
SQL>
SQL> SELECT LEVEL,
2 LPAD(' ', 2 * LEVEL - 1) || first_name || ' ' ||
3 last_name AS employee
4 FROM employee
5 START WITH employee_id = (
6 SELECT employee_id
7 FROM employee
8 WHERE first_name = 'Kevin'
9 AND last_name = 'Black'
10 )
11 CONNECT BY PRIOR employee_id = manager_id;
LEVEL EMPLOYEE
---------- -------------------------
1 Kevin Black
2 Keith Long
2 Frank Howard
2 Doreen Penn
SQL>
연습내용........................................................................................................................................................
START WITH ~ CONNECT BY 연습
계층구조화 되어 있는 테이블의 내용을 출력할 때 사용할 필요가 있는 Query
Oracle의 기본 테이블 중에서 EMP 테이블은 그 내용안에 사원의 상사를 표현한 MGR(Manager)컬럼이 포함되어 있다.
그리고 MGR 컬럼은 사원의 사번(EMPNO)이 저장되어 있기 때문에 어떤 사원의 상사를 확인할 수 있게 작성되어 있다.
또한 이런 계층구조로 작성된 데이터를 보여줄 때 START WITH ~ CONNECT BY 문장을 이용하면 쉽게 계층구조화된
데이터를 시각적으로 표현할 수 있다.
PRIOR empno=mgr 은 레코드를 위/아래로 연결할 때 empno와 mgr의 값이 같은 레코드를 위/아래로 연결하되 이들 2개의 레코드 중에서 empno를 가진 레코드를 먼저 위에 출력하고 아래에는 mgr 값을 가진 레코드를 출력하라는 의미이다. 따라서 상급자를 위에, 그 아래에는 직속 하급자를 이어서 출력하라는 의미이다.
예를 들어, 사장부터 말단 사원까지 한 눈에 계층구조가 파악될 수 있도록 출력하는 경우를 들 수가 있다. 또한, 인터넷의
게시판 글에도 계층구조가 적용될 수가 있는데, 부모글 아래에 댓글, 댓글 아래에 또 댓글이 달려 있는 경우를 들 수가 있다.
오라클의 기본 테이블, EMP를 이용하여 이 문장을 연습을 해 보자.
.....................................................................................................................................................................................................................
연습 1) KING부터 말단 사원까지 사원의 이름(ENAME)을 위에서 아래로 순서대로 나열해 본다.
.....................................................................................................................................................................................................................
SQL> select ename from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr;
ENAME
----------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
14 개의 행이 선택되었습니다.
.....................................................................................................................................................................................................................
연습 2) START WITH 문장을 사용할 때는 임시컬럼인 LEVEL도 자동으로 생성되어 사용할 수가 있다.
LEVEL은 계층구조상의 단계(구조상의 위치, 정점은 1 이고 단계가 내려갈수록 숫자가 증가함)
각 사원의 이름 왼쪽에 그 사윈의 계층구조상 위치를 출력하기 위해 LEVEL을 사용해 보자.
.....................................................................................................................................................................................................................
SQL> select LEVEL, ename from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr;
LEVEL ENAME
---------- ----------
1 KING
2 JONES
3 SCOTT
4 ADAMS
3 FORD
4 SMITH
2 BLAKE
3 ALLEN
3 WARD
3 MARTIN
3 TURNER
3 JAMES
2 CLARK
3 MILLER
14 개의 행이 선택되었습니다.
.....................................................................................................................................................................................................................
연습 3) 계층구조가 더 쉽게 파악될 수 있으려면 단계별로 들여쓰기식 출력을 할 필요가 있다.
LEVEL 만큼 들여쓰는 정도를 증가하면 하위계층으로 갈수록 오른쪽으로 들어간 상태로 출력되도록 해 보자.
LEVEL 수 만큼 이름의 왼쪽에 공백문자를 붙여주려면 LPAD라는 오라클 함수를 사용할 필요가 있다.
LPAD(ename, 20, ' ') 라고 사용하면, 사원의 이름(ename) 왼쪽에 이름을 합쳐 총 길이가 20이 되도록 공백문자를 붙이라는
의미가 된다. LEVEL만큼 공백을 추가하여 사원이름(ename)을 출력해 보자.
.....................................................................................................................................................................................................................
select LPAD(ename, LEVEL*5+20, ' ') from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr;
LPAD(ENAME,LEVEL*5+20,'')
-----------------------------------------
KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
14 개의 행이 선택되었습니다.
.....................................................................................................................................................................................................................
연습 4) 각 사원이름 앞에 LEVEL에 비례한 수만큼 공백을 붙여주는 것도 좋지만 선을 넣어서 표현하는 경우도 있다.
다음과 같은 표현이 되도록 문장을 다시 작성해 보자.
KING
|__JONES
|__SCOTT
|__BLAKE
|__ALLEN
.....................................................................................................................................................................................................................
기본적인 계층구조 질의문
select empno, LPAD(ename, (LEVEL+1)*3, ' ') name from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr;
.....................................................................................................................................................................................................................
레코드를 출력할 때 상급자의 이름도 함께 출력한다
select empno, LPAD(ename, (LEVEL+1)*3, ' ') name, PRIOR ename 상급자 from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr;
.....................................................................................................................................................................................................................
계층구조의 결과집합에 줄번호(ROWNUM)를 포함하도록 한다
select empno, LPAD(ename, (LEVEL+1)*3, ' ') name, prior ename 상급자, ROWNUM rn from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr;
.....................................................................................................................................................................................................................
계층구조상 동등한 수준(LEVEL)일 경우에는 정렬방법을 지정하여 출력할 수 있다
select empno, LPAD(ename, (LEVEL+1)*3, ' ') name, prior ename 상급자, ROWNUM rn from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr ORDER SIBLINGS BY empno desc;
.....................................................................................................................................................................................................................
페이징 기능을 구현하기 위해 결과집합 중에서 원하는 레코드 수 만큼만 가져온다
select empno, name, 상급자, rn from (select empno, LPAD(ename, (LEVEL+1)*3, ' ') name, prior ename 상급자, ROWNUM rn from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr) where rn between 1 and 5;
.....................................................................................................................................................................................................................
결과집합 중에서 두번째 페이지를 가져온다
select empno, name, 상급자, rn from (select empno, LPAD(ename, (LEVEL+1)*3, ' ') name, prior ename 상급자, ROWNUM rn from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr) where rn between 6 and 10;
.....................................................................................................................................................................................................................
동등한 레벨일 경우 특정 순서로 정렬한 후에 원하는 페이지의 레코드만 가져오는 경우
select empno, name, 상급자, rn from (select empno, LPAD(ename, (LEVEL+1)*3, ' ') name, prior ename 상급자, ROWNUM rn from emp START WITH ename='KING' CONNECT BY PRIOR empno=mgr ORDER SIBLINGS BY empno desc) where rn between 6 and 10;
.....................................................................................................................................................................................................................
SQL Plus에서 accept 명령으로 이용자로부터 페이지번호를 입력받아서 해당 페이지를 보여주는 예
set verify off;
accept pg prompt '페이지번호입력:'
select &pg||' 페이지를 보여주마!' " " from dual;
select rn 순번, empno, ename, dname, grade from
(select rownum rn, t1.* from
(select empno, lpad('|__', level*(4-1))||ename ename, dname, grade from
emp e INNER JOIN dept d ON e.deptno=d.deptno
LEFT OUTER JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal start with ename='KING' connect by prior empno=mgr)t1)
where rn between &pg*4-4+1 and &pg*4
/