LuceteMango:)

시퀀스란?

• - 유일(UNIQUE)한 값을 생성해주는 오라클 객체이다.
• - 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.
• - 보통 PRIMARY KEY 값을 생성하기 위해 사용 한다.
• - 메모리에 Cache되었을 때 시퀀스값의 액세스 효율이 증가 한다.
• - 시퀀스는 테이블과는 독립적으로 저장되고 생성된다.

시퀀스 생성

-- 시퀀스 생성 Syntax
CREATE SEQUENCE sequence_name
    [START WITH n]
    [INCREMENT BY n]
    [MAXVALUE n | NOMAXVALUE]
    [MINVALUE n | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE | NOCACHE]

• - START WITH : 시퀀스의 시작 값을 지정한다. n을 1로 지정하면 1부터 순차적으로 시퀀스번호가 증가 한다.
• - INCREMENT BY : 시퀀스의 증가 값을 지정한다. n을 2로 하면 2씩 증가한다. START WITH를 1로, INCREMENT BY를 2로 설정하면 1, 3, 5, 7,.. 이렇게 시퀀스 번호가 증가한다.
• - MAXVALUE : 시퀀스 최대값
• - MINVALUE : 시퀀스 최소값
• - CYCLE|NOCYCLE : 최대값 도달시 순환 여부
• - CACHE | NOCACHE : CACHE 여부, 원하는 숫자만큼 미리 만들어 Shared Pool의 Library Cache에 상주시킨다.

시퀀스 수정

START WITH 값을 제외하고 ALTER SEQUENCE 명령어로 수정 할 수 있다.

-- 시퀀스 수정 Syntax
ALTER SEQUENCE sequence_name
    [INCREMENT BY n]
    [MAXVALUE n | NOMAXVALUE]
    [MINVALUE n | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE | NOCACHE]

시퀀스의 사용예

시퀀스 생성

empno를 자동 증가하는 시퀀스를 만들어 보자

-- 8000부터 시작하는 시퀀스를 생성하자
CREATE SEQUENCE  seq_empno
 START WITH  8000
INCREMENT BY 1
 CACHE 20;


-- 시퀀스가 정상적으로 생성되었는지 조회해 보자
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.CURRVAL FROM DUAL;

시퀀스를 이용해서 데이터를 등록해 보자

-- INSERT 시 시퀀스 사용
INSERT INTO emp 
        (empno, 
         ename, job, mgr, 
         hiredate, sal, comm, deptno)
VALUES
        (seq_empno.NEXTVAL, 
         'TIGER', 'MANAGER', 7839, 
         SYSDATE, 3000, null, 20);


-- SELECT 절에서 시퀀스 사용
CREATE TABLE emp2
AS 
SELECT seq_empno.NEXTVAL as empno, ename, 
       job, mgr, hiredate, sal, comm, deptno
FROM emp; 


-- 시퀀스가 정상적으로 반영되었는지 조회해 보자
SELECT * FROM emp;

시퀀스 수정

시퀀스 수정 예이다.

-- seq_empno  시퀀스의 증과값과 Cache 사이즈를 변경하는 예이다.
ALTER SEQUENCE  seq_empno
INCREMENT BY 10
CACHE 40;


-- 시퀀스가 정상적으로 변경되었는지 조회해 보자
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.NEXTVAL FROM DUAL;
SELECT seq_empno.CURRVAL FROM DUAL;

시퀀스 삭제

DROP SEQUENCE 명령으로 시퀀스를 삭제 할 수 있다.

-- seq_empno 시퀀스를 삭제한다.
DROP SEQUENCE seq_empno;

1. Single-Row Subquery

- SELECT 문장에서 오직 하나의 행(값)만 반환하는 Query이다

- 단일 행 연산자(=,>, >=, <, <=, <>, !=) 만 사용된다.

-- empno가 7369의 job을 조회 한 후 
-- job이 'CLERK'인 사원의 이름과 직업을 반환 한다.
SELECT ename,job
  FROM emp
 WHERE job = (SELECT job
                FROM emp
               WHERE empno = 7369);

• [그림] Single-Row Subquery
• 

 
-- 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제
SELECT empno,ename,sal,deptno  
  FROM emp
 WHERE sal IN (SELECT MAX(sal)
                 FROM emp
                GROUP BY deptno);

EMPNO ENAME         SAL     DEPTNO
----- --------  --------- ---------
 7698 BLAKE        2850        30
 7788 SCOTT        3000        20
 7902 FORD         3000        20

 
-- SALESMAN 직업의 급여보다 많이 받는 사원의 사원명과 급여 정보를 출력하는 예제
SELECT ename, sal
  FROM emp
 WHERE deptno != 20
   AND sal > ANY (SELECT sal 
                    FROM emp 
                   WHERE job='SALESMAN');

ENAME             SAL
---------- ----------
ALLEN            1600
BLAKE            2850
CLARK            2450
...

 
-- 모든 SALESMAN직업의 급여보다 많이받는 사원의 사원명과 급여정보를 출력하는예제
SELECT ename, sal
  FROM emp
 WHERE deptno != 20
   AND sal > ALL (SELECT sal 
                    FROM emp 
                   WHERE job='SALESMAN');

ENAME             SAL
---------- --------
CLARK            2450
BLAKE            2850
KING             5000

 
-- 아래 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우
-- 추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를 
-- 체크해야 한다.
-- 두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고 
-- DISTINCT로 중복 제거를 한다.
SELECT DISTINCT d.deptno, d.dname
  FROM dept d, emp e
 WHERE d.deptno = e.deptno;



-- EXISTS를 사용하는 Subquery로 변경
-- 추출하고자 하는 대상만을 FROM절에 놓고 emp테이블은 체크만 하기위해 
-- EXISTS절에 위치시켰으며 이로 인해 수행속도가 대폭 감소하게 된다.
SELECT d.deptno, d.dname
  FROM dept d
 WHERE EXISTS 
      (SELECT 1
         FROM emp e
        WHERE e.deptno = d.deptno);

3. Multiple-Column Subquery란?
결과 값이 두 개 이상의 컬럼을 반환하는 Subquery이다
Pairwise(쌍비교) Subquery
Subquery가 한 번 실행되면서 두 개 이상의 컬럼을 검색해서 주 쿼리로 넘겨 준다.
 
SELECT empno, sal, deptno
  FROM emp
 WHERE (sal, deptno) IN (SELECT sal, deptno
                          FROM emp
                         WHERE deptno = 30
                           AND comm is NOT NULL);

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7521       1250         30
      7654       1250         30
      7844       1500         30
      7499       1600         30 
Nonpairwise(비쌍비교) Subquery
WHERE 절에서 두 개 이상의 서로 다른 Subquery가 사용되어서 결과 값을 주 쿼리로 넘겨 준다.
 
SELECT empno, sal, deptno
  FROM emp
 WHERE sal IN (SELECT sal
                 FROM emp
                WHERE deptno = 30
                  AND comm is NOT NULL);
  AND deptno IN (SELECT deptno
                   FROM emp
                  WHERE deptno = 30
                    AND comm is NOT NULL); 

     EMPNO        SAL     DEPTNO
---------- ---------- ----------
      7521       1250         30
      7654       1250         30
      7844       1500         30
      7499       1600         30 

Null Values in a Subquery
서브쿼리에서 NULL 값이 반환 되면 주 쿼리 에서는 어떠한 행도 반환되지 않는다.

4. Inline View (From절 Subquery)란?
FROM절에 오는 Subquery이다.
FROM절에서 원하는 데이터를 조회하여 가상의 집합을 만들어 조인을 수행하거나 가상의 집합을 다시 조회 할 때 사용한다.
Inlivew View 안에 또 다른 Inline View가 올 수 있다.
 
-- 부서번호 20의 평균 급여보다 크고, 
-- 부서번호 20에 속하지 않은 관리자를 조회하는 예제이다.
SELECT b.empno, b.ename, b.job, b.sal, b.deptno
  FROM (SELECT empno
          FROM emp  
         WHERE sal >(SELECT AVG(sal) 
                       FROM emp 
                      WHERE deptno = 20)) a, emp b
 WHERE a.empno = b.empno
   AND b.mgr is NOT NULL
   AND b.deptno != 20;

 EMPNO ENAME      JOB         SAL     DEPTNO
 ----- --------- --------- ------- ---------
  7698 BLAKE      MANAGER     2850        30
  7782 CLARK      MANAGER     2450        10

5. Scalar Subquery란?
SELECT 절에서 사용하는 Subquery 이다.
Scalar Subquery의 특징
한 개의 로우만 반환 한다.
메인 쿼리에서 추출되는 데이터 건 수 만큼 수행되기 때문에 조인으로 수행 될 때보다 수행회수가 적을 수 있다.
일치하는 값이 없는 경우 NULL을 반환 한다.
코드성 테이블에서 코드값을 조회 할 때, 불필요한 조인을 하지 않기 위해 많이 사용한다.
Scalar Subquery 예제
아래는 일반적인 Scalar Subquery 예이다.
 
-- 직업이 ‘MANAGER’인 사원의 사원명, 부서명을 조회하는 예제
SELECT ename,
       (SELECT dname FROM dept d WHERE d.deptno = e.deptno) deptno
FROM emp e
WHERE job ='MANAGER';

ENAME      DEPTNO
---------- --------------
JONES      RESEARCH
BLAKE      SALES
CLARK      ACCOUNTING
NULL값을 반환하는 Outer Join형태의 Scalar Subquery 예
 -- Scalar Subquery는 일치하는 값이 없으면 NULL을 반환하므로 Outer Join과 같은 형태이다.
-- 아래는 부서별 최대 급여정보 조회 예이다.
-- 부서 40에 해당하는 직원이 없기 때문에 최대 급여가 조회 되지 않는다.
SELECT d.deptno, d.dname,
       (SELECT MAX(sal) 
          FROM emp
         WHERE deptno = d.deptno) sal
  FROM dept d;



-- 결과가 동일한 Outer Join 예이다.
SELECT d.deptno, d.dname, MAX(e.sal)
  FROM dept d, emp e
 WHERE d.deptno = e.deptno(+)
 GROUP BY d.deptno, d.dname
 ORDER bY d.deptno;

DEPTNO DNAME          SAL
------ -----------  -----
    10 ACCOUNTING    5000
    20 RESEARCH      3000
    30 SALES         2850
    40 OPERATIONS

GROUP BY절

• - GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
• - 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
• - 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다. (개발자 분들이 많이 실수 함)
• - 아래는 집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
• 
  

-- 부서별 사원수 조회
SELECT '2005년' year, deptno 부서번호, COUNT(*) 사원수
  FROM emp
 GROUP BY deptno
 ORDER BY COUNT(*) DESC;


YEAR     부서번호     사원수
------ ---------- ----------
2005년         30          6
2005년         20          5
2005년         10          3

아래 예제는 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 조회하는 예제이다.

 
SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균", 
       ROUND(SUM(sal)) "급여합계"
  FROM emp
 GROUP BY deptno;


  DEPTNO   COUNT(*)    급여평균    급여합계
-------- ---------- ---------- ----------
      30          6       1567       9400
      20          5       2175      10875
      10          3       2917       8750

아래 예제는 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회하는 예제이다.

 
SELECT job, COUNT(empno) "인원수", AVG(sal) "평균급여액",
       MAX(sal) "최고급여액", MIN(sal) "최저급여액", 
       SUM(sal) "급여합계"
  FROM emp
 GROUP BY job;


JOB           인원수   평균급여액   최고급여액   최저급여액    급여합계
----------- -------- ---------- ---------- ---------- ----------
CLERK              4     1037.5       1300        800       4150
SALESMAN           4       1400       1600       1250       5600
PRESIDENT          1       5000       5000       5000       5000
MANAGER            3 2758.33333       2975       2450       8275
ANALYST            2       3000       3000       3000       6000 

• - GROUP BY 절은 집계 함수 없이도 사용 될 수 있다.(DISTINCT와 용도가 비슷해 짐)
• - 아래 예제를 보면 GROUP BY는 말 그대로 그룹을 나누는 역할을 한다.

-- GROUP BY를 이용한 부서번호 조회 예
SELECT deptno
  FROM emp
 GROUP BY deptno;


DEPTNO
------
    30
    20
    10

DISTINCT와 GROUP BY절

• - DISTINCT와 GROUP BY 개념에 대해서 좀 더 이해를 해보자.
• - DISTINCT는 주로 UNIQUE(중복을 제거)한 컬럼이나 레코드를 조회하는 경우 사용한다.
• - GROUP BY는 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용한다.
• - 하지만 두 작업은 조금만 생각해보면 동일한 형태의 작업이라는 것을 쉽게 알 수 있으며, 일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리될 수 있는 쿼리들이 있다.
• - 두 기능 모두 Oracle9i까지는 sort를 이용하여 데이터를 만들었지만, Oracle10g 부터는 모두 Hash를 이용하여 처리한다.
• - 그래서 DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이 좋을지 고민되는 경우들이 가끔 있다.

아래의 예제는 동일한 결과를 반환한다.

-- DISTINCT를 사용한 중복 데이터 제거
SELECT DISTINCT deptno FROM emp;


-- GROUP BY를 사용한 중복 데이터 제거
SELECT deptno FROM emp GROUP BY deptno;


DEPTNO
------
    30
    20
    10

하지만 곰곰히 생각해 보면 GROUP BY와 DISTINCT는 각자 고유의 기능이 있다

집계함수를 사용하여 특정 그룹으로 구분 할 때는GROUP BY 절을 사용하며, 특정 그룹 구분없이 중복된 데이터를 제거할 경우에는 DISTINCT 절을 사용 하도록 하자

-- 아래와 같은 기능은 DISTINCT를 사용하는 것이 훨씬 효율적이다.
SELECT COUNT(DISTINCT d.deptno) "중복제거 수", 
       COUNT(d.deptno) "전체 수"
  FROM emp e, dept d
 WHERE e.deptno = d.deptno;


-- 집계 함수가 필요한 경우는 GROUP BY를 사용해야 한다.
SELECT deptno, MIN(sal)
  FROM emp 
 GROUP BY deptno;

HAVING 절

• - WHERE 절에서는 집계함수를 사용 할 수 없다.
• - HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
• - HAVING절은 GROUP BY절과 함께 사용이 된다.

아래 예제는 사원수가 다섯 명이 넘는 부서와 사원수를 조회하는 예제이다.

SELECT b.dname, COUNT(a.empno) "사원수" 
  FROM emp a, dept b
 WHERE a.deptno = b.deptno
 GROUP BY dname
HAVING COUNT(a.empno) > 5;


DNAME          사원수
------------ -------
SALES              6

아래 예제는 전체 월급이 5000을 초과하는 JOB에 대해서 JOB과 월급여 합계를 조회하는 예이다. 단 판매원(SALES)은 제외하고 월 급여 합계로 내림차순 정렬하였다.

SELECT job, SUM(sal) "급여합계"
  FROM emp  
 WHERE job != 'SALES'      -- 판매원은 제외
 GROUP BY job              -- 업무별로 Group By 
HAVING SUM(sal) > 5000     -- 전체 월급이 5000을 초과하는
 ORDER BY SUM(sal) DESC;   -- 월급여 합계로 내림차순 정렬


JOB                  급여합계
------------------ ----------
MANAGER                  8275
ANALYST                  6000
SALESMAN                 5600

GROUPING SETS

• - GROUPING SETS 함수는 GROUP BY의 확장된 형태로 하나의 GROUP BY절에 여러개의 그룹 조건을 기술할 수 있다.
• - GROUPING SETS 함수의 결과는 각 그룹 조건에 대해 별도로 GROUP BY한 결과를 UNION ALL한 결과와 동일하다.
• - GROUPING SETS 함수를 사용하면 UNION ALL등을 사용하여 복잡하게 SQL문장을 작성했던 것을 간단하게 한 문장으로 해결 할 수 있다.

아래는 GROUPING SETS함수와 GROUP BY, UNION ALL을 이용하여 동일한 결과를 출력하는 예제이다.

--  GROUPING SETS 예제
SELECT deptno, job, SUM(sal)
 FROM emp
 GROUP BY GROUPING SETS(deptno, job);


-- GROUP BY와 UNION ALL을 이용한 예제
SELECT NULL deptno,  job, SUM(sal)
  FROM emp
 GROUP BY job
 UNION ALL
SELECT deptno, NULL job, SUM(sal)
  FROM emp
 GROUP BY deptno;


-- 조회결과
   DEPTNO JOB                  SUM(SAL)
--------- ------------------ ----------
          CLERK                    4150
          SALESMAN                 5600
          PRESIDENT                5000
          MANAGER                  8275
          ANALYST                  6000
       30                          9400
       20                         10875
       10                          8750