오라클 Database 04

유용한 SQL 함수 정리

*평소 헷갈리거나 몰랐던 명령어들만 정리한다.

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17T00:00:00Z	800	(null)	20
7499	ALLEN	SALESMAN	7698	1981-02-20T00:00:00Z	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22T00:00:00Z	1250	500	30
7566	JONES	MANAGER	7839	1981-04-02T00:00:00Z	2975	(null)	20
7654	MARTIN	SALESMAN	7698	1981-09-28T00:00:00Z	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01T00:00:00Z	2850	(null)	30
7782	CLARK	MANAGER	7839	1981-06-09T00:00:00Z	2450	(null)	10
7788	SCOTT	ANALYST	7566	1982-12-09T00:00:00Z	3000	(null)	20
7839	KING	PRESIDENT	(null)	1981-11-17T00:00:00Z	5000	(null)	10
7844	TURNER	SALESMAN	7698	1981-09-08T00:00:00Z	1500	(null)	30
7876	ADAMS	CLERK	7788	1983-01-12T00:00:00Z	1100	(null)	20
7900	JAMES	CLERK	7698	1981-12-03T00:00:00Z	950	(null)	30
7902	FORD	ANALYST	7566	1981-12-03T00:00:00Z	3000	(null)	20
7934	MILLER	CLERK	7782	1982-01-23T00:00:00Z	1300	(null)	10

자동으로 소계/합계 구해주는 함수

ROLLUP

• 데이터들의 소계를 구해주는 함수

SELECT DEPTNO, JOB, COUNT(*), SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO, JOB);
/* 부서의 직업별로 데이터 소계 */

DEPTNO	JOB	COUNT(*)	SUM(SAL)
10	CLERK	1	1300
10	MANAGER	1	2450
10	PRESIDENT	1	5000
10	(null)	3	8750
20	CLERK	2	1900
20	ANALYST	2	6000
20	MANAGER	1	2975
20	(null)	5	10875
30	CLERK	1	950
30	MANAGER	1	2850
30	SALESMAN	4	5600
30	(null)	6	9400
(null)	(null)	14	29025

CUBE

*데이터 소계 + 전체 총계

SELECT DEPTNO, JOB, COUNT(*), SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO, JOB);
/* 부서 별로 소계를 내고 다음 직업별로 소계를 내고 총 합계

DEPTNO	JOB	COUNT(*)	SUM(SAL)
(null)	(null)	14	29025
(null)	CLERK	4	4150
(null)	ANALYST	2	6000
(null)	MANAGER	3	8275
(null)	SALESMAN	4	5600
(null)	PRESIDENT	1	5000
10	(null)	3	8750
10	CLERK	1	1300
10	MANAGER	1	2450
10	PRESIDENT	1	5000
20	(null)	5	10875
20	CLERK	2	1900
20	ANALYST	2	6000
20	MANAGER	1	2975
30	(null)	6	9400
30	CLERK	1	950
30	MANAGER	1	2850
30	SALESMAN	4	5600

계층형 쿼리

*데이터 조회할 때 상위와 하위 관계를 표시할 때 사용

키워드	설명
LEVEL	connect by 를 사용하면 생성. 해당 데이터 단계를 표현
START WITH	어디서 시작할 것인지
CONNECT BY PRIOR	각 행의 연결 관계를 설정
PRIOR	어디서 시작할 것인지
WHERE	connect by 로 뽑아낸 데이터에서 where 조건이 마지막으로 실행

CONNECT BY

SELECT 
    LEVEL, 
    LPAD(DEPTNO, LEVEL＊4, '*') AS EMPNO1, ENAME, 
    MGR 
FROM EMP 
START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR;

LEVEL	EMPNO1	ENAME	MGR
1	**10	KING	(null)
2	**20	JONES	7839
3	****20	SCOTT	7566
4	******20	ADAMS	7788
3	****20	FORD	7566
4	******20	SMITH	7902
2	**30	BLAKE	7839
3	****30	ALLEN	7698
3	****30	WARD	7698
3	****30	MARTIN	7698
3	****30	TURNER	7698
3	****30	JAMES	7698
2	**10	CLARK	7839
3	****10	MILLER	7782

SELECT 
    LEVEL, 
    LPAD(ENAME, LEVEL＊4, '*') AS ENAME1, 
    SUBSTR(SYS_CONNECT_BY_PATH(ENAME,'-'), 2) ENAME2, 
    MGR 
FROM EMP 
START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR;

LEVEL	ENAME1	ENAME2	MGR
1	KING	KING	(null)
2	***JONES	KING-JONES	7839
3	***SCOTT	KING-JONES-SCOTT	7566
4	*******ADAMS	KING-JONES-SCOTT-ADAMS	7788
3	****FORD	KING-JONES-FORD	7566
4	*******SMITH	KING-JONES-FORD-SMITH	7902
2	***BLAKE	KING-BLAKE	7839
3	***ALLEN	KING-BLAKE-ALLEN	7698
3	****WARD	KING-BLAKE-WARD	7698
3	**MARTIN	KING-BLAKE-MARTIN	7698
3	**TURNER	KING-BLAKE-TURNER	7698
3	***JAMES	KING-BLAKE-JAMES	7698
2	***CLARK	KING-CLARK	7839
3	**MILLER	KING-CLARK-MILLER	7782

SELECT LEVEL, LPAD(DEPTNO, LEVEL＊4, '*') AS EMPNO1, ENAME, MGR 
FROM EMP 
START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR;

LEVEL	ENAME1	ENAME2	MGR
1	KING	KING	(null)
2	***JONES	KING-JONES	7839
3	***SCOTT	KING-JONES-SCOTT	7566
4	*******ADAMS	KING-JONES-SCOTT-ADAMS	7788
3	****FORD	KING-JONES-FORD	7566
2	***BLAKE	KING-BLAKE	7839
3	****WARD	KING-BLAKE-WARD	7698
3	**MARTIN	KING-BLAKE-MARTIN	7698
3	**TURNER	KING-BLAKE-TURNER	7698
3	***JAMES	KING-BLAKE-JAMES	7698
2	***CLARK	KING-CLARK	7839
3	**MILLER	KING-CLARK-MILLER	7782