[Oracle] Level, Lpad 를 이용한 결재선 쿼리 만들기

/* level, lpad */ /* 한테이블 내에서 1개의 컬럼이 다른 컬럼을 참조하는 경우 사용 */ /* 결재선 조회하여 올때 사용하면 좋다! */ -- 문법 select [level], co1, col2, ... from table_name [where ] [start with ] [connect by prior ]; -- 전체 사원 테이플 select * from emp; /* 7369 SMITH CLERK 7902 12/17/1980 00:00:00 800 20 7499 ALLEN SALESMAN 7698 02/20/1981 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 02/22/1981 00:00:00 1250 500 30 7566 JONES MANAGER 7839 04/02/1981 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 09/28/1981 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 05/01/1981 00:00:00 2850 30 7782 CLARK MANAGER 7839 06/09/1981 00:00:00 2450 10 7788 SCOTT ANALYST 7566 04/19/1987 00:00:00 3000 20 7839 KING PRESIDENT 11/17/1981 00:00:00 5000 10 7844 TURNER SALESMAN 7698 09/08/1981 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 05/23/1987 00:00:00 1100 20 7900 JAMES CLERK 7698 12/03/1981 00:00:00 950 30 7902 FORD ANALYST 7566 12/03/1981 00:00:00 3000 20 7934 MILLER CLERK 7782 01/23/1982 00:00:00 1300 10 */ -- Level 과 start with, connect by prior 를 이용해 구조도를 출력 select ename, LEVEL, empno, mgr from emp start with ename='KING' connect by prior empno=mgr; /* KING 1 7839 JONES 2 7566 7839 SCOTT 3 7788 7566 ADAMS 4 7876 7788 FORD 3 7902 7566 SMITH 4 7369 7902 BLAKE 2 7698 7839 ALLEN 3 7499 7698 WARD 3 7521 7698 MARTIN 3 7654 7698 TURNER 3 7844 7698 JAMES 3 7900 7698 CLARK 2 7782 7839 MILLER 3 7934 7782 */ -- lpad 를 이용하여 보기좋게 수정 select lpad(' ', 3* LEVEL-3)||ename, LEVEL, empno, mgr from emp start with ename='KING' connect by prior empno=mgr; /* KING 1 7839 JONES 2 7566 7839 SCOTT 3 7788 7566 ADAMS 4 7876 7788 FORD 3 7902 7566 SMITH 4 7369 7902 BLAKE 2 7698 7839 ALLEN 3 7499 7698 WARD 3 7521 7698 MARTIN 3 7654 7698 TURNER 3 7844 7698 JAMES 3 7900 7698 CLARK 2 7782 7839 MILLER 3 7934 7782 */ -- level 의 실전 사용예 -- bottom-up 정렬을 통해 결재선을 생성하는데 씀 select ename, LEVEL, empno, mgr from emp start with ename='ADAMS' connect by prior mgr=empno; /* ADAMS 1 7876 7788 SCOTT 2 7788 7566 JONES 3 7566 7839 KING 4 7839 */