[DBMS_Oracle] CASE..WHEN...THEN


CASE..WHEN...THEN(조회된 값에 대한 조건 걸기)


조회해 올 때의 조건은 WHERE 문으로 조건을 걸어 가져올 수 있습니다.

하지만 가져온 값에 어떤 조건을 걸어 보여 주거나 또는 값을 변환할 필요가 있을 경우는 CASE...WHEN...THEN 문을 씁니다.

CASE WHEN 문에는 두 가지 형태의 사용 방법이 있습니다.


SELECT * FROM table명 WHERE

column명<검색할 컬럼명> CASE WHEN 'A'='B'<비교조건> THEN '2013' <처리조건>

ELSE 'C'<default 조건>

END<반드시 끝에 EMD를 써야한다>



1. 특정 Column으로 여러가지 값을 비교

SELECT CASE TO_CHAR(SYSDATE, 'YYYY') WHEN '2013' THEN SYSDATE 

   WHEN '2014' THEN SYSDATE + 1

              ELSE NULL END SYS_DATE

FROM DUAL;

 

CASE 와 WHEN 사이에 비교하고자 하는 Column 을 넣고

WHEN 과 THEN 사이에 비교하고자 하는 값을 넣어서 비교하는 방법입니다.


2. 여러가지 조건을 비교

SELECT CASE WHEN TO_CHAR(SYSDATE, 'YYYY') = '2013' THEN SYSDATE

                     WHEN TO_CHAR(SYSDATE, 'MM') = '11' THEN SYSDATE + 1 

             ELSE NULL END SYS_DATE 

FROM DUAL;

CASE 와 WHEN 사이는 비우고(컬럼없음), WHEN 과 THEN 사이에 내가 필요한 조건문을 WHERE 절에 넣는 것처럼 넣으면 됩니다.

2번의 경우에는 하나의 조건 뿐만 아니라 여러 개의 조건을 한꺼번에 넣을 수 있는 장점이 있습니다.


SELECT CASE WHEN TO_CHAR(SYSDATE, 'YYYY') = '2013' 

   AND TO_CHAR(SYSDATE, 'MM') = '10' THEN SYSDATE

                     WHEN TO_CHAR(SYSDATE, 'YYYY') = '2014' 

                     OR TO_CHAR(SYSDATE, 'MM') = '11' THEN SYSDATE + 1

            ELSE NULL END SYS_DATE 

FROM DUAL;

이상 조회된 값에 조건 걸어 사용하기였습니다.


3. 중첩된 CASE문
- 여러개의 CASE문을 중첩해서 사용할 수 있다.
SELECT  T1.STORE_ID
        ,T1.STORE_ADDR
        ,CASE WHEN T1.STORE_SIZE >= 100 THEN
                CASE WHEN T2.REGION_GD IN ('S') THEN 'High grade'
                     WHEN T2.REGION_GD IN ('A','B') THEN 'Mid Grade'
                     ELSE 'Low Grade'
                END
              WHEN T1.STORE_SIZE >= 50 THEN
                CASE WHEN T2.REGION_GD IN ('S', 'A') THEN 'High Grade'
                     WHEN T2.REGION_GD IN ('B') THEN 'Mid Grade'
                     ELSE 'Low Grade'
                END
             ELSE
                CASE WHEN T2.REGION_GD IN ('S', 'A', 'B') THEN 'High Grade'
                     ELSE 'Low Grade'
                END
        END STORE_SIZE_GD
FROM    SQL_TEST.MA_STORE T1
        ,SQL_TEST.CD_REGION T2
WHERE   T1.REGION_CD = T2.REGION_CD
- STORE의 SIZE에 대한 등급을 STORE의 지역(REGION), STORE_SIZE별로 STORE SIZE등급을 구한다.
- 이러한 중첩된 CASE는 사용자가 원하는 다양한 결과를 얻어 내는데 큰 도움이 된다.


4. DECODE와 CASE

DECODE와 CASE 함수는 SQL 문장에서 조건에 해당하는 값을 추출하고자 할 때 주로 사용한다


(1) DECODE

- DECODE 함수는 조건에 따라 데이터를 다른 값이나 컬럼값으로 추출 할 수 있다.

- DECODE(VALUE, IF1, THEN1, IF2, THEN2...) 형태로 사용 할 수 있다.

- VALUE 값이 IF1일 경우에 THEN1 값을 반환하고, VALUE 값이 IF2일 경우에는 THEN2 값을 반환한다.

- DECODE 함수 안에 DECODE함수를 중첩으로 사용 할 수 있다.

아래는 DECODE 함수의 일반적인 예제이다.


- 부서번호가 10이면 ACCOUNTING, 20이면 RESEARCH, 30이면 SALES

- 나머지는 OPERATIONS를 출력하는 예제

SELECT deptno, DECODE(deptno, 10 , 'ACCOUNTING' ,

                              20 , 'RESEARCH' ,

                              30 , 'SALES', 'OPERATIONS') name

FROM dept;

DEPTNO   NAME

------ ----------

    10    ACCOUNTING

    20    RESEARCH

    30    SALES

    40    OPERATIONS


아래는 DECODE 함수에서 집계 함수를 사용한 예제이다

-- 10부서는 급여합계를, 20부서는 최대값을, 30부서는 최소값을 출력하는 예제

SELECT deptno, DECODE(deptno, 10 , SUM(sal),

                              20 , MAX(sal),

                              30 , MIN(sal)) sal

 FROM emp

 GROUP BY deptno; 

 DEPTNO        SAL

--------- --------

      30          950

      20          3000

      10          8750 


DECODE함수는 집계함수와 함께 통계 데이터를 추출할 때 많이 사용한다. 아래는 부서별로 급여합계를 조회하는 예이다

-- 부서별로 급여 합계를 출력한다. 

SELECT deptno, NVL(SUM(DECODE(deptno, 10, sal)),0) deptno10, 

               NVL(SUM(DECODE(deptno, 20, sal)),0) deptno20,

               NVL(SUM(DECODE(deptno, 30, sal)),0) deptno30,

               NVL(SUM(DECODE(deptno, 40, sal)),0) deptno40

FROM emp

GROUP BY deptno;  

DEPTNO   DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40

------- --------- --------- ---------- ----------

     30            0             0               9400             0

     20            0             10875        0                  0

     10         8750           0               0                  0 


아래 부서별 급여합계 예를 보면 일반적인 집계함수를 사용할 때는 급여 합계가 행으로 조회가 되지만, DECODE와 MAX함수를 사용하면 열로 값을 표시할 수 있다.

-- 부서별로 급여 합계를 행으로 출력한다. 

SELECT d.deptno, NVL(SUM(e.sal),0) sal

FROM emp e, dept d

WHERE e.deptno(+) = d.deptno

GROUP BY d.deptno; 

DEPTNO        SAL

-------- ----------

      10         8750

      20        10875

      30        9400

      40             0

 

-- 부서별로 급여 합계를  열로 출력한다. 

SELECT MAX(NVL(SUM(DECODE(deptno, 10, sal)),0)) deptno10, 

       MAX(NVL(SUM(DECODE(deptno, 20, sal)),0)) deptno20,

       MAX(NVL(SUM(DECODE(deptno, 30, sal)),0)) deptno30,

       MAX(NVL(SUM(DECODE(deptno, 40, sal)),0)) deptno40

 FROM emp

 GROUP BY deptno; 

DEPTNO10   DEPTNO20   DEPTNO30   DEPTNO40

--------- ---------- ---------- ----------

    8750         10875              9400            0


(2) CASE

- CASE 함수는 DECODE함수가 제공하지 못하는 비교연산의 단점을 해결할 수 있는 함수이다.

- DECODE함수에서 비교연산을 수행하기 위해서는 GREATEST, LEAST등의 함수를 사용해야 하지만, CASE함수에서는 조건 연산자를 모두 사용 할 수 있다.

- CASE함수는 IF.. THEN .. ELSE 구문과 비슷 하다. WHEN절 다음에 여러 조건이 올 수 있다.


-아래의  DECODE예제를 CASE함수로 변환한 예이다. 

SELECT deptno, 

       CASE deptno

         WHEN 10 THEN 'ACCOUNTING'

         WHEN 20 THEN 'RESEARCH'

         WHEN 30 THEN 'SALES'

         ELSE 'OPERATIONS'

       END as "Dept Name"

FROM dept; 

DEPTNO  Dept Name

------- ----------

     10     ACCOUNTING

     20     RESEARCH

     30     SALES

     40     OPERATIONS 


- 아래는 WHEN절 다음에 연산자가 오는 예제이다.

-급여별로 인상율을 다르게 계산하였다. 

SELECT ename ,

       CASE

          WHEN sal < 1000  THEN sal+(sal*0.8)

          WHEN sal BETWEEN 1000 AND 2000 THEN sal+(sal*0.5)

          WHEN sal BETWEEN 2001 AND 3000 THEN sal+(sal*0.3)

          ELSE sal+(sal*0.1)

       END sal

FROM emp; 



'Server > DBMS' 카테고리의 다른 글

[DBMS_Oracle] 뷰 3  (0) 2014.12.15
[DBMS_Oracle] 뷰 2  (0) 2014.12.15
[DBMS_Oracle] 뷰 1  (0) 2014.12.15
[DBMS_MySQL] MySQL 설치 및 사용자 추가,권한  (0) 2014.12.13
[DBMS_Oracle] 인덱스  (0) 2014.12.10

[DBMS_Oracle] 뷰 3

뷰 3


1. 뷰를 사용하는 이유

1 복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화시킬 수 있다. 

2 보안에 유리하다.

사용자마다 특정 객체만 조회할 수 있도록 권한을 부여를 할 수 있기에 동일한 테이블을 접근하는 사용자마다에 따라 서로 다르게 보도록 여러 개의 뷰를 정의해 놓고 특정 사용자만이 해당 뷰에 접근할 수 있도록 합니다. 



예를 들어 사원 테이블에 개인 적인 정보인 급여와 커미션은 부서에 따라 접근을 제한해야 합니다. 

급여나 커미션 모두에 대해서 인사과에서는 조회할 수 없도록 하고 경리과에서는 이 모두가 조회될 수 있도록 하지만 영업부서에서는 경쟁심을 유발하기 위해서 다른 사원의 커미션을 조회할 수 있도록 해야 합니다. 


2. 뷰의 종류

뷰는 뷰를 정의하기 위해서 사용되는 기본 테이블의 수에 따라 단순 뷰(Simple View)와 복합 뷰(Complex View)로 나뉩니다.

(1) 실습 [단순 뷰에 대한 데이터 조작]

1) 단순 뷰에 대해서 DML 즉,INSERT/UPDATE/DELETE 문을 사용할 수 있음을 확인합시다. 

EMP_VIEW30 뷰에 데이터를 추가해 봅시다.

INSERT INTO EMP_VIEW30

VALUES(8000, 'ANGEL', 30);

SELECT * FROM EMP_VIEW30;


2) 단순 뷰를 대상으로 실행한 DML 명령문의 치리 결과는 뷰를 정의할 때 사용한 기본 테이블에 적용됩니다. 

SELECT * FROM EMP_COPY;


(2) 실습 [단순 뷰의 컬럼에 별칭 부여하기]

기본 테이블(EMP_COPY)의 컬럼 명을 상속받지 않고 한글화 하여 컬럼 명이 사원번호, 사원명, 급여, 부서번호로 구성되도록 합시다.

1) EMP_VIEW30 뷰에 데이터를 추가해 봅시다.

CREATE OR REPLACE

VIEW EMP_VIEW(사원번호, 사원명, 급여, 부서번호)

AS 

SELECT EMPNO, ENAME, SAL, DEPTNO 

FROM EMP_COPY;


2) EMP_VIEW 는 전체 사원에 대해서 특정 컬럼만 보여주도록 작성하였습니다. 다음과 같이 EMP_VIEW 를 SELECT 하면서 WHERE 절을 추가하여 30번 부서 소속 사원들의 정보만 볼 수 있습니다. 

SELECT * FROM EMP_VIEW

WHERE 부서번호=30;

EMP_VIEW 뷰는 칼럼에 별칭을 주게 되면 기본 테이블의 칼럼 명을 더 이상 상속받지 못하므로 30번 부서를 검색하기 위해서는 뷰를 생성할 때 준 칼럼 별칭(부서번호)을 사용해야 합니다. 


(3) 실습 [그룹 함수를 사용한 단순 뷰]

그룹 함수 SUM과 AVG를 사용해서 각 부서별 급여 총액과 평균을 구하는 뷰를 작성해 봅시다. 뷰를 작성하기 위해서 SELECT 절 다음에 SUM이란 그룹 함수를 사용하면 결과를 뷰의 특정 컬럼처럼 사용하는 것입니다. 

따라서 물리적인 칼럼이 존재하지 않는 가상 칼럼이기에 칼럼 명도 상속 받을 수 없습니다. 뷰를 생성할 때 가상 칼럼을 사용하려면 사용자가 반드시 이름을 따로 설정해야 한다는 것을 명심하기 바랍니다. 


1) 부서별 급여 총액과 평균을 구하기 위한 뷰를 생성해보도록 합시다. 

CREATE VIEW VIEW_SAL

AS

SELECT DEPTNO, SUM(SAL) AS "SalSum", AVG(SAL) AS "SalAvg"

FROM EMP_COPY

GROUP BY DEPTNO;


3. 단순 뷰

단순 뷰에 대해서 DML 명령어를 사용하여 조작이 가능하다고 하였습니다. 

하지만, 다음과 같은 몇 가지의 경우에는 조작이 불가능합니다.


1 뷰 정의에 포함되지 않은 컬럼 중에 기본 테이블의 칼럼이 NOT NULL 제약 조건이 지정되어 있는 경우 INSERT 문이 사용 불가능합니다. 왜냐하면 뷰에 대한 INSERT 문은 기본 테이블에 NULL 값을 입력하는 형태가 되기 때문입니다. 

2 SAL*12와 같이 산술 표현식으로 정의된 가상 칼럼이 뷰에 정의되면 INSERT나 UPDATE가 불가능합니다. 

3 DISTINCT을 포함한 경우에도 DML 명령을 사용할 수 없습니다.

4 그룹 함수나 GROUP BY 절을 포함한 경우에도 DML 명령을 사용할 수 없습니다.


4. 복합 뷰
- 뷰를 사용하는 이유 중의 하나가 복잡하고 자주 사용하는 질의를 보다 쉽고 간단하게 사용하기 위해서라고 했습니다. 이를 살펴보기 위해서 사원 테이블과 부서 테이블을 자주 조인한다고 합시다.
- 사원 테이블과 부서 테이블을 조인하기 위해서는 다음과 같이 복잡한 SELECT 문을 매번 작성해야 합니다. 
SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC 
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO DESC;
- 뷰를 사용하는 이유 중의 하나가 복잡하고 자주 사용하는 질의를 보다 쉽고 간단하게 사용하기 위해서라고 했습니다. 위에 작성한 조인문에 "CREATE VIEW EMP_VIEW_DEPT AS" 만 추가해서 뷰로 작성해 놓으면 “SELECT * FROM EMP_VIEW_DEPT; " 와 같이 간단하게 질의 결과를 얻을 수 있습니다. 

(1) 실습 [복합 뷰 만들기]
사원 테이블과 부서 테이블을 조인하기 위해서 복합 뷰를 생성해 봅시다. 
1) 다음은 사번, 이름, 급여, 부서번호, 부서명, 지역명을 출력하기 위한 복합 뷰입니다. 
CREATE VIEW EMP_VIEW_DEPT
AS
SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO, D.DNAME, D.LOC 
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO DESC;

2) 뷰를 생성한 후, 이를 활용하면 복잡한 질의를 쉽게 처리할 수 있습니다.
SELECT * FROM EMP_VIEW_DEPT;

5. 뷰 삭제
- 뷰는 실체가 없는 가상 테이블이기 때문에 뷰를 삭제한다는 것은 USER_VIEWS 데이터 딕셔너리에 저장되어 있는 뷰의 정의를 삭제하는 것을 의미합니다. 
- 따라서 뷰를 삭제해도 뷰를 정의한 기본 테이블의 구조나 데이터에는 전혀 영향을 주지 않습니다.

지금까지 생성한 뷰 중에서 VIEW_SAL을 삭제합시다.
DROP VIEW VIEW_SAL;

6. 인라인 뷰

- 사원 중에서 입사일이 빠른 사람 5명(TOP-5)만을 얻어 오는 질의문을 작성해 봅시다. 

- TOP-N을 구하기 위해서는 ROWNUM인라인 뷰가 사용됩니다. 인라인 뷰는 조금 후에 다루어 보도록 하고, 우선 ROWNUM 칼럼에 대해서 살펴보도록 합시다. 

- ROWNUM 칼럼은 DDL을 학습하면서 살펴보았지만 보다 자세히 살펴보도록 합시다. 

    

(1) 실습 [ROWNUM 컬럼 성격 파악하기]

1) 다음은 ROWNUM 칼럼 값을 출력하기 위한 쿼리문입니다. 

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM EMP;


2) 입사일이 빠른 사람 5명만(TOP-N)을 얻어오기 위해서는 일련의 출력 데이터를 일단 임의의 순서로 정렬한 후에 그 중 일부의 데이터만 출력할 수 있도록 해야 하므로 ORDER BY 절을 사용하여 입사일을 기준으로 오름차순 정렬해 봅시다. 

SELECT EMPNO, ENAME, HIREDATE

FROM EMP

ORDER BY HIREDATE;


3) 이번에는 입사일을 기준으로 오름차순 정렬을 하는 쿼리문에 ROWNUM 칼럼을 출력해 봅시다.

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM EMP

ORDER BY HIREDATE;


위 결과를 보면 입사일을 기준으로 오름차순 정렬을 하였기에 출력되는 행의 순서는 바뀌더라도 해당 행의 ROWNUM 칼럼 값은 바뀌지 않는다는 것을 알 수 있습니다. 

ROWNUM 칼럼은 오라클의 내부적으로 부여되는데 INSERT 문을 이용하여 입력하면 입력한 순서에 따라 1씩 증가되면서 값이 지정되어 바뀌지 않습니다. 

정렬된 순서대로 ROWNUM 칼럼 값이 매겨지도록 하려면 새로운 테이블이나 뷰로 새롭게 데이터를 저장해야만 합니다. 


(2) 실습 [뷰와 ROWNUM 칼럼으로 TON-N 구하기]

ROWNUM 칼럼의 성격은 파악했으므로 이제 뷰와 함께 사용하여 TOP-N을 구해봅시다. TOP-N은 일련의 출력 데이터를 일단 임의의 순서로 정렬한 후에 그 중 일부의 데이터만 출력할 수 있도록 하여 구합니다. 

1) 입사일을 기준으로 오름차순 정렬한 쿼리문으로 새로운 뷰를 생성해 봅시다

CREATE OR REPLACE VIEW VIEW_HIRE

AS

SELECT EMPNO, ENAME, HIREDATE

FROM EMP

ORDER BY HIREDATE;

 

2) 입사일을 기준으로 오름차순 정렬을 하는 뷰에 ROWNUM 칼럼을 함께 출력해 봅시다.

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM VIEW_HIRE;


3) 자, 이제 입사일이 빠른 사람 5명만을 얻어와 봅시다.

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM VIEW_HIRE

WHERE ROWNUM<=5;


(3) 인라인 뷰로 TOP-N 구하기

- 인라인 뷰는 SQL 문장에서 사용하는 서브 쿼리의 일종으로 보통 FROM 절에 위치해서 테이블처럼 사용하는 것입니다. 형식은 다음과 같습니다.

- 인라인 뷰란 메인 쿼리의 SELECT 문의 FROM 절 내부에 사용된 서브 쿼리문을 말합니다. 

- 우리가 지금까지 생성한 뷰는 CREATE 명령어로 뷰를 생성했지만, 인라인 뷰는 SQL 문 내부에 뷰를 정의하고 이를 테이블처럼 사용합니다. 


1) 실습 [인라인 뷰로 TOP-N 구하기]

인라인 뷰를 사용해서 입사일이 빠른 사람 5명만을 얻어오기로 합시다. 아래 문장을 보면 FROM 절 다음인 VIEW_HIRE 위치에 VIEW_HIRE를 정의할 때 사용한 서브 쿼리문을 기술한 것뿐입니다. 

SELECT ROWNUM, EMPNO, ENAME, HIREDATE

FROM ( SELECT EMPNO, ENAME, HIREDATE

FROM EMP

ORDER BY HIREDATE)

WHERE ROWNUM<=5;



'Server > DBMS' 카테고리의 다른 글

[DBMS_Oracle] CASE..WHEN...THEN  (0) 2014.12.16
[DBMS_Oracle] 뷰 2  (0) 2014.12.15
[DBMS_Oracle] 뷰 1  (0) 2014.12.15
[DBMS_MySQL] MySQL 설치 및 사용자 추가,권한  (0) 2014.12.13
[DBMS_Oracle] 인덱스  (0) 2014.12.10

[DBMS_Oracle] 뷰 2

뷰 2


1. 뷰의 내부구조와 USER_VIEWS 데이터 딕셔너리

- 뷰는 물리적으로 데이터를 저장하고 있지 않다고 하였습니다. 그런데도 다음과 같은 질의 문을 수행할 수 있는 이유가 무엇일까요?

SELECT * FROM EMP_VIEW30;


- EMP_VIEW30라는 뷰는 데이터를 물리적으로 저장하고 있지 않습니다. 

- CREATE VIEW 명령어로 뷰를 정의할 때 AS 절 다음에 기술한 쿼리 문장 자체를 저장하고 있습니다. 

- 뷰 정의할 때 기술한 쿼리문이 궁금하다면 데이터 딕셔너리 USER_VIEWS 테이블의 TEXT 컬럼 값을 살펴보면 됩니다. 

- USER_VIEWS에서 테이블 이름과 텍스트만 출력해 보겠습니다.

SELECT VIEW_NAME, TEXT

FROM USER_VIEWS;


- 기본 테이블은 디스크 공간을 할당 받아서 실질적으로 데이터를 저장하고 있지만, 뷰는 데이터 딕셔너리USER_VIEWS 에 사용자가 뷰를 정의할 때 기술한 서브 쿼리문(SELECT 문)만을 문자열 형태로 저장하고 있습니다. 


뷰의 동작 원리를 이해하기 위해서 뷰에 대한 질의가 어떻게 내부적으로 처리되는지 자세히 살펴보도록 합시다.

1) 사용자가 뷰에 대해서 질의를 하면 USER_VIEWS에서 뷰에 대한 정의를 조회합니다.

2) 기본 테이블에 대한 뷰의 접근 권한을 살핀다.

3) 뷰에 대한 질의를 기본 테이블에 대한 질의로 변환합니다.

4) 기본 테이블에 대한 질의를 통해 데이터를 검색합니다.

5) 검색된 결과를 출력합니다.


- 우리가 앞에서 생성한 뷰인 EMP_VIEW30를 SELECT문의 FROM절 다음에 기술하여 질의를 하면 오라클 서버는 USER_VIEWS에서 EMP_VIEW30를 찾아 이를 정의할 때 기술한 서브 쿼리문이 저장된 TEXT 값을 EMP_VIEW30 위치로 가져갑니다. 


- 질의는 기본 테이블인 EMP_COPY를 통해 일어납니다. 즉, 기본 테이블인 EMP_COPY 에 대해서 서브 쿼리문을 수행하게 됩니다. 이러한 동작 원리 덕분에 뷰가 실질적으로 데이터를 저장하고 있지 않더라도 데이터를 검색할 수 있는 것입니다. 


(1) 실습 [뷰와 기본 테이블 관계 파악하기]

기본 테이블을 가져다가 쿼리문을 수행한다는 것을 증명하기 위해서 간단한 예를 살펴보도록 하겠습니다. 

1) 다음은 EMP_VIEW30 뷰에 행을 하나 추가하는 문장입니다. 

INSERT INTO EMP_VIEW30

VALUES(1111, 'AAAA', 30);


2) INSERT 문으로 뷰에 새로운 행을 추가하였습니다. 뷰의 내용을 출력해 보면 추가된 행이 뷰에 존재하고 있음을 확인할 수 있습니다. 

SELECT * FROM EMP_VIEW30;


3) 뷰 뿐만 아니라 기본 테이블의 내용을 출력해 보면 INSERT 문에 의해서 뷰에 추가한 행이 테이블에도 존재함을 확인할 수 있습니다. 

SELECT * FROM EMP_COPY;


- INSERT 문에 뷰(EMP_VIEW30)를 사용하였지만, 뷰는 쿼리문에 대한 이름일 뿐이기 때문에 새로운 행은 기본 테이블(EMP_COPY)에 실질적으로 추가되는 것임을 알 수 있습니다. 뷰(EMP_VIEW30)의 내용을 확인하기 위해 SELECT문을 수행하면 변경된 기본 테이블(EMP_COPY)의 내용에서 일부를 서브 쿼리한 결과를 보여줍니다. 

- 뷰는 실질적인 데이터를 저장한 기본 테이블을 볼 수 있도록 한 투명한 창입니다, 기본 테이블의 모양이 바뀐 것이고 그 바뀐 내용을 뷰라는 창을 통해서 볼 뿐입니다. 뷰에 INSERT 뿐만 아니라 UPDATE, DELETE 모두 사용할 수 있는데, UPDATE, DELETE 쿼리문 역시 뷰의 텍스트에 저장되어 있는 기본 테이블이 변경하는 것입니다. 

- 이 정도면 뷰가 물리적인 테이블을 근거로 한 논리적인 가상 테이블이란 말의 의미를 이해할 수 있으리라고 생각됩니다. 


'Server > DBMS' 카테고리의 다른 글

[DBMS_Oracle] CASE..WHEN...THEN  (0) 2014.12.16
[DBMS_Oracle] 뷰 3  (0) 2014.12.15
[DBMS_Oracle] 뷰 1  (0) 2014.12.15
[DBMS_MySQL] MySQL 설치 및 사용자 추가,권한  (0) 2014.12.13
[DBMS_Oracle] 인덱스  (0) 2014.12.10

[DBMS_Oracle] 뷰 1

뷰 1


1. 뷰의 개념

뷰(View)는 한마디로 물리적인 테이블을 근거한 논리적인 가상 테이블이라고 정의할 수 있습니다. 

뷰를 가상 테이블이라고 하는 이유를 살펴보겠습니다. 

가상이란 단어는 실질적으로 데이터를 저장하고 있지 않기 때문에 붙인 것이고, 테이블이란 단어는 실질적으로 데이터를 저장하고 있지 않더라도 사용자는 마치 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있기 때문에 붙인 것입니다. 

뷰는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문입니다. 

뷰(View)란 ‘보다’란 의미를 갖고 있는 점을 감안해 보면 알 수 있듯이 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 합니다. 

사용자에게 주어진 뷰를 통해서 기본 테이블을 제한적으로 사용하게 됩니다. 


2. 뷰의 기본 테이블

뷰는 이미 존재하고 있는 테이블에 제한적으로 접근하도록 합니다. 

뷰를 생성하기 위해서는 실질적으로 데이터를 저장하고 있는 물리적인 테이블이 존재해야 하는데 이 테이블을 기본 테이블이라고 합니다. 

우선 시스템에서 제공하는 dept 테이블과 emp 테이블의 내용이 변경되는 것을 막기 위해서 테이블의 내용을 복사한 새로운 테이블을 생성한 후에 이를 기본 테이블로 사용합시다. 

뷰의 기본 테이블을 생성합시다.

1) DEPT_COPY를 DETP 테이블의 복사본으로 생성합시다.

CREATE TABLE DEPT_COPY

AS

SELECT * FROM DEPT;


2) EMP 테이블의 복사본으로 EMP_COPY를 생성합시다.

CREATE TABLE EMP_COPY

AS

SELECT * FROM EMP;


3. 뷰 정의하기

뷰를 생성하여 자주 사용되는 SELECT 문을 간단하게 접근하는 방법을 학습해 봅시다. 다음은 뷰를 생성하기 위한 기본 형식입니다. 

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name

[(alias, alias, alias, ...)]

AS subquery

[WITH CHECK OPTION]

[WITH READ ONLY];


테이블을 생성하기 위해서 CREATE TABLE 로 시작하지만, 뷰를 생성하기 위해서는 CREATE VIEW로 시작합니다. AS 다음은 마치 서브 쿼리문과 유사합니다. 

subquery에는 우리가 지금까지 사용하였던 SELECT 문을 기술하면 됩니다.


(1) 문장 해설

1) CREATE OR RELPACE VIEW

뷰를 만들 때 CREATE OR RELPACE VIEW 대신 그냥 CREATE VIEW만 사용해도 됩니다. 

그러나 그냥 CREATE VIEW를 통해 만들어진 뷰의 구조를 바꾸려면 뷰를 삭제하고 다시 만들어야 되는 반면, CREATE OR REPLACE VIEW는 새로운 뷰를 만들 수 있을 뿐만 아니라 기존에 뷰가 존재하더라도 삭제하지 않고 새로운 구조의 뷰로 변경(REPLACE)할 수 있습니다. 

그래서 대부분 뷰를 만들 때는 CREATE VIEW 대신 CREATE OR REPLACE VIEW를 사용하는 편입니다.


2) FORCE

FORCE를 사용하면, 기본 테이블의 존재 여부에 상관없이 뷰를 생성합니다. 


3) WITH CHECK OPTION

WITH CHECK OPTION을 사용하면, 해당 뷰를 통해서 볼 수 있는 범위 내에서만 UPDATE 또는 INSERT가 가능합니다.


4) WITH READ ONLY

WITH READ ONLY를 사용하면 해당 뷰를 통해서는 SELECT만 가능하며 INSERT/UPDATE/DELETE를 할 수 없게 됩니다. 

만약 이것을 생략한다면, 뷰를 사용하여 추가, 수정, 삭제(INSERT/UPDATE/DELETE)가 모두 가능합니다.


(2) 뷰 생성 예제

뷰를 만들기 전에 어떤 경우에 뷰를 사용하게 되는지 다음 예를 통해서 뷰가 필요한 이유를 설명해 보도록 하겠습니다. 

만일, 30번 부서에 소속된 사원들의 사번과 이름과 부서번호를 자주 검색한다고 한다면 다음과 같은 SELECT 문을 여러 번 입력해야 합니다. 

SELECT EMPNO, ENAME, DEPTNO

FROM EMP_COPY

WHERE DEPTNO=30;

위와 같은 결과를 출력하기위해서 매번 SELECT 문을 입력하기란 번거로운 일입니다. 

뷰는 이와 같이 번거로운 SELECT 문을 매번 입력하는 대신 보다 쉽게 원하는 결과를 얻고자 하는 바람에서 출발한 개념입니다


자주 사용되는 30번 부서에 소속된 사원들의 사번과 이름과 부서번호를 출력하기 위한 SELECT문을 하나의 뷰로 정의해 봅시다. 

CREATE VIEW EMP_VIEW30

AS 

SELECT EMPNO, ENAME, DEPTNO

FROM EMP_COPY

WHERE DEPTNO=30;


뷰는 테이블에 접근(SELECT)한 것과 동일한 방법으로 결과를 얻을 수 있습니다. 

SELECT * FROM EMP_VIEW30;


(3) 실습 [뷰를 생성할 권한이 불충분한 경우]

30번 부서에 소속된 사원들의 사번과 이름과 부서번호를 출력하기 위한 SELECT문을 하나의 뷰로 정의해 봅시다. 


1) 뷰를 생성하려는데 다음과 같이 권한이 불충분하다고 오류가 발생할 경우가 있습니다.


2) 이럴 경우에는 DBA인 SYSTEM 계정으로 로그인하여 뷰를 생성할 권한을 부여합니다. 

특정 사용자에 대해서 아무 문제없이 뷰가 생성된다면 괜찮지만, 그렇지 않을 경우 GRANT 명령어로 특정 사용자에게 권한을 부여해야 합니다. 

아래 문장은 SCOTT 사용자에게 테이블을 생성할 CREATE VIEW 권한을 부여하는 명령어입니다. 

이 명령어는 DBA 권한이 있는 사용자만이 부여할 수 있으므로 SYSTEM 계정으로 접속합니다. 

CONN system/manager

GRANT CREATE VIEW TO scott;


(4) 실습 [뷰 정의 하기]

뷰를 생성하려는데 다음과 같이 권한을 설정하였다면 30번 부서에 소속된 사원들의 사번과 이름과 부서번호를 출력하기 위한 SELECT문을 하나의 뷰로 다시 정의해 봅시다. 


1) 뷰를 정의합시다.

CREATE VIEW EMP_VIEW30

AS 

SELECT EMPNO, ENAME, DEPTNO

FROM EMP_COPY

WHERE DEPTNO=30;


2) 뷰를 생성할 때 컬럼 이름을 명시하지 않으면 뷰(EMP_VIEW30)를 정의하는 기본 테이블(EMP_COPY)의 컬럼 명을 상속받아 사용합니다. 다음은 생성된 뷰의 구조를 살펴봅시다. 

DESC EMP_VIEW30


3) 뷰의 내용을 출력해 봅시다. 테이블의 내용을 출력하는 것과 동일한 방식으로 수행하면 됩니다.  SELECT * FROM 다음에 테이블 명 대신에 뷰 이름을 기술하면 됩니다. 

SELECT * FROM EMP_VIEW30;


'Server > DBMS' 카테고리의 다른 글

[DBMS_Oracle] 뷰 3  (0) 2014.12.15
[DBMS_Oracle] 뷰 2  (0) 2014.12.15
[DBMS_MySQL] MySQL 설치 및 사용자 추가,권한  (0) 2014.12.13
[DBMS_Oracle] 인덱스  (0) 2014.12.10
[DBMS_Oracle] 시퀀스  (0) 2014.12.08

[DBMS_MySQL] MySQL 설치 및 사용자 추가,권한

MySQL 설치 및 사용자 추가,권한


1. MySQL 설치


다음과 같이 명령하면 MySQL이 설치됩니다.

yum install mysql-server


MySQL 서버를 실행시키는 명령어는 다음과 같습니다.

service mysqld start


root 사용자 비밀번호 생성

mysql -u root -p mysql

비밀번호를 입력하라고 나오는데 아직 비밀번호가 없으므로 엔터를 칩니다.


root의 비밀번호를 생성하는 명령은 다음과 같습니다. (new-password에 원하는 비밀번호를 넣으세요.)

mysql > update user set password=password('new-password') where user='root';


이제 권한 테이블을 업데이트합니다.

mysql > flush privileges;


다음과 같이 명령하여 MySQL에서 나옵니다.

mysql > quit;        



2. 사용자 추가 (권한추가)

mysql > create user 사용자ID; // 사용자 추가

mysql > create user userid@localhost identified by '비밀번호'; // 사용자(user)를 추가하면서 패스워드까지 설정


기존에 사용하던 계정에 외부 접근 권한을 부여하려면, Host를 '%' 로 하여 똑같은 계정을 추가한다

mysql > create user userid@'%' identified by '비밀번호';  // '%' 의 의미는 외부에서의 접근을 허용

mysql > FLUSH privileges; // 변경된 내용을 메모리에 반영(권한 적용)


cf.) 다른 방법으로는

mysql > USE mysql;  // mysql database 선택

mysql > INSERT INTO user (Host, User, Password) VALUES ('localhost', '계정아이디', password('비밀번호'));

mysql > INSERT INTO user (Host, User, Password) VALUES ('%', '계정아이디', password('비밀번호'));

mysql > FLUSH privileges; 


cf.) 사용자 삭제

mysql > drop user userid@'%'; // 사용자 삭제

mysql > drop user userid@localhost;

mysql > select * from user;    // 등록된 모든 사용자 ID 조회

mysql > delete from user where user = '사용자ID';    // 사용자 삭제


3. 사용자 권한 부여

계정이 생성되었다면, 그 계정이 접근할 수 있는 데이터베이스를 생성하고 권한을 부여해야 한다

MySQL은 사용자 이름, 비밀번호, 접속 호스트로 여러분을 인증한다. MySQL은 로그인을 시도하는 위치가 어디인가 하는 것도 인증의 일부로 간주한다.

MySQL 에서 사용자 계정을 추가하고 권한을 추가하거나 제거하는 데 GRANT 와 REVOKE 명령을 사용하기를 권장한다. 사용자에게 허가된 것을 확인하려면 SHOW GRANTS 를 사용한다.

mysql > GRANT ALL PRIVILEGES ON DB명.테이블 TO 계정아이디@호스트 IDENTIFIED BY '비밀번호'; 

// 계정이 이미 존재 하는데 'identified by '비밀번호' 부분을 추가하면 비밀번호가 변경된다


(1) localhost 접속 권한 추가 

mysql > GRANT ALL privileges ON database명.* TO 사용자ID@localhost IDENTIFIED BY '비밀번호';

mysql > GRANT ALL privileges ON database명.* TO 사용자ID@127.0.0.1 IDENTIFIED BY '비밀번호';


(2) localhost 의 경우 host명으로 접속하게 되는 경우

mysql > GRANT ALL privileges ON database명.* TO 사용자ID@'audi' IDENTIFIED BY '비밀번호';


(3) 모든 원격지 에서의 접속 권한 추가 

mysql > GRANT ALL privileges ON database명.* TO 사용자ID@'%' IDENTIFIED BY '비밀번호';


(4) 특정 테이블에서만 접속 권한 추가 

mysql > GRANT ALL privileges ON test.* TO 사용자ID@localhost identified by '비밀번호';


(5) 모든 테이블에 모든 원격지에서 접속 권한 추가

mysql > GRANT ALL privileges ON *.* TO 사용자ID@'%' identified by '비밀번호' ;  


(6) 변경사항 적용

mysql > flush privileges;


cf.)

mysql> grant select, insert, update on test.* to user@localhost identified by '비밀번호';

// user 에게 test 데이터베이스 모든 테이블에 select, insert, update 권한 부여--> ALL은 모든 권한 부여


cf.) 사용자에게 데이터베이스 사용권한 제거

revoke all on DB명.테이블명 from 사용자ID;   // 모든 권한을 삭제




'Server > DBMS' 카테고리의 다른 글

[DBMS_Oracle] 뷰 2  (0) 2014.12.15
[DBMS_Oracle] 뷰 1  (0) 2014.12.15
[DBMS_Oracle] 인덱스  (0) 2014.12.10
[DBMS_Oracle] 시퀀스  (0) 2014.12.08
[DBMS_Oracle] dual  (0) 2014.12.08

[DBMS_Oracle] 인덱스


인덱스


1. 인덱스를 왜 사용하는 것일까요? 

이에 대한 답은 “빠른 검색을 위해서 인덱스를 사용합니다.” 입니다. 

여러분이 테이블 생성 방법을 책에서 찾으려고 할 때 어떻게 합니까? 책 첫 페이지부터 한 장씩 넘겨가면서 테이블 생성 방법이 기술되어 있는지 일일이 살펴보는 사람은 드물 것입니다. 

일반적으로 책 맨 뒤에 있는 색인(인덱스, 찾아보기)에서 해당 단어(테이블)를 찾아 그 페이지로 이동합니다. 

이렇게 원하는 단어를 쉽게 찾는 방법으로 색인, 인덱스가 사용되는 것처럼 오라클의 인덱스 역시 원하는 데이터를 빨리 찾기 위해서 사용됩니다. 


2. 인덱스의 장점

검색 속도가 빨라진다. 

시스템에 걸리는 부하를 줄여서 시스템 전체 성능을 향상시킨다.


3. 인덱스의 단점

인덱스를 위한 추가적인 공간이 필요하다.

인덱스를 생성하는데 시간이 걸린다.

데이터의 변경 작업(INSERT/UPDATE/DELETE)이 자주 일어날 경우에는 오히려 성능이 저하된다.


4. 인덱스 생성 확인 방법

USER_IND_COLUMNS 데이터 딕셔너리 뷰로 인덱스의 생성 유무를 확인해 봅시다. 

SELECT INDEX_NAME, TABLE_NAME , COLUMN_NAME

FROM USER_IND_COLUMNS

WHERE TABLE_NAME IN('EMP', 'DEPT');



5. 조회 속도 비교하기
- 인덱스가 조회 속도를 빠르게 해 준다는 것을 증명하기 위해서 기본 키나 유일 키로 지정하지 않는 컬럼인 사원 이름으로 검색해 봅시다. 
- 아마도 시간이 어느 정도 소요될 것입니다. 
- 검색을 위해서 WHERE 절에 사용되는 컬럼인 사원 이름 컬럼을 인덱스로 생성한 후에 다시 한번 사원 이름으로 검색해보면 검색시간이 현저하게 줄어드는 것을 확인할 수 있습니다. 

(1) 실습 [사원 테이블 복사하기]
다음은 인덱스로 인해 검색시간이 현저하게 줄어드는 것을 증명하기 위한 실습을 위해서 사원 테이블을 복사해서 새로운 테이블을 생성해 봅시다. 
1) 다음은 실습을 위해서 사원(emp) 테이블을 복사해서 새로운 테이블을 생성합니다. 
CREATE TABLE EMP01
AS
SELECT * FROM EMP;

2) EMP와 EMP01 테이블에 인덱스가 설정되어 있는지 확인합니다. 
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME 
FROM USER_IND_COLUMNS
WHERE TABLE_NAME IN('EMP', 'EMP01');
결과 화면의 USER_IND_COLUMNS 를 살펴보면 EMP 테이블은 EMPNO 칼럼에 인덱스가 존재하지만 EMP를 서브 쿼리로 복사한 EMP01 테이블에 대해서는 어떠한 인덱스도 존재하지 않음을 확인할 수 있습니다. 서브 쿼리문으로 복사한 테이블은 구조와 내용만 복사될 뿐 제약 조건은 복사되지 않기 때문입니다.

(2) 실습 [인덱스가 아닌 컬럼으로 검색하기]
EMP01 테이블은 인덱스 설정이 되어 있지 않기에 검색하는데 시간이 걸립니다. 이를 증명하기 위해서 EMP01 테이블에 수많은 데이터가 저장되어 있어야 합니다. 서브 쿼리문으로 INSERT 문을 여러 번 반복해서 EMP01 테이블의 데이터를 늘린 후에 사원이름으로 특정 사원을 찾아보도록 합시다. 속도의 차이가 현저하게 난다는 것을 느낄 수 있습니다. 

1) 서브 쿼리문으로 INSERT 문을 여러 번 반복합시다. 
INSERT INTO EMP01 SELECT * FROM EMP01;
:
:
INSERT INTO EMP01 SELECT * FROM EMP01;

2) 이제 검색용으로 사용할 행을 새롭게 하나 추가합니다.
INSERT INTO EMP01
(EMPNO, ENAME)
VALUES(1111, 'SYJ');


3) 시간을 체크하기 위해서 다음과 같은 명령을 입력합니다. 
SET TIMING ON

4) 사원 이름이 ‘SYJ'인 행을 검색해 봅시다. 
SELECT DISTINCT EMPNO, ENAME 
FROM EMP01 
WHERE ENAME='SYJ';

컴퓨터의 성능에 따라 검색하는데 소요되는 시간이 다르겠지만, 어느 정도의 시간은 소요됨을 확인할 수 있습니다.

6. 인덱스 생성하기
- 제약 조건에 의해 자동으로 생성되는 인덱스 외에 CREATE INDEX 명령어로 직접 인덱스를 생성할 수도 있습니다. 
- 다음은 인덱스를 생성하기 위한 기본 형식입니다.
CREATE INDEX index_name
ON table_name (column_name);
CREATE INDEX 다음에 인덱스 객체 이름을 지정합니다. 어떤 테이블의 어떤 컬럼에 인덱스를 설정할 것인지를 결정하기위해서 ON 절 다음에 테이블 이름과 컬럼 이름을 기술합니다. 

(1) 실습 [인덱스 설정하기]
인덱스가 지정하지 않은 컬럼인 ENAME 으로 조회하여 어느 정도의 시간은 소요됨을 확인하였으므로 이번에는 ENAME 컴럼으로 인덱스를 지정하여 조회 시간이 단축됨을 확인해봅시다. 

1) 이번에는 테이블 EMP01의 컬럼 중에서 이름(ENAME)에 대해서 인덱스를 생성해봅시다. 
CREATE INDEX IDX_EMP01_ENAME
ON EMP01(ENAME);

2) 사원 이름이 ‘SYJ'인 행을 검색해 봅시다. 
SELECT DISTINCT EMPNO, ENAME 
FROM EMP01 
WHERE ENAME='SYJ';

인덱스를 생성한 후에 사원 이름이 ‘SYJ'인 행을 다시 검색하면 수행속도가 매우 감소함을 알 수 있습니다.

7. 인덱스 제거하기
- 인덱스가 검색 속도를 현저하게 줄이는 것을 확인하기 위해서 위와 같은 예제를 실습해 보았습니다. 
- 이번에는 인덱스를 삭제해봅시다. 
- 이를 위해서 오라클은 DROP INDEX 명령어를 제공합니다
DROP INDEX index_name;

(1) 실습
EMP01 테이블의 직급 컬럼을 인덱스로 설정하되 인덱스 이름을 IDX_EMP01_JOB로 줍시다. 
SELECT INDEX_NAME, TABLE_NAME , COLUMN_NAME
FROM USER_IND_COLUMNS
WHERE TABLE_NAME IN('EMP01');


8. 인덱스를 사용해야 하는 경우 판단하기

- 인덱스가 검색을 위한 처리 속도만 향상시킨다고 했습니다. 

- 하지만, 무조건 인덱스를 사용한다고 검색 속도가 향상되는 것은 아닙니다. 

- 계획성 없이 너무 많은 인덱스를 지정하면 오히려 성능을 저하시킬 수도 있습니다. 

- 언제 인덱스를 사용하는 것이 좋을까요?

- 다음과 같은 조건에서 사원 테이블의 부서 번호에 인덱스를 거는 것이 좋을까요? 

테이블에 전체 행의 수는 10000 건이다.

위의 쿼리문을 전체 쿼리문 들 중에서 95% 사용된다.

쿼리문의 결과로 구해지는 행은 10건 정도이다. 


- 조건을 위 표를 비추어보고 판단해 보면 DEPTNO 컬럼을 인덱스로 사용하기에 알맞다는 결론이 납니다. 

- 위 결론에 따라 사원 테이블의 부서 번호(DEPTNO)를 인덱스로 지정합시다. 

CREATE INDEX IDX_EMP01_DEPTNO

ON EMP01(DEPTNO);




'Server > DBMS' 카테고리의 다른 글

[DBMS_Oracle] 뷰 1  (0) 2014.12.15
[DBMS_MySQL] MySQL 설치 및 사용자 추가,권한  (0) 2014.12.13
[DBMS_Oracle] 시퀀스  (0) 2014.12.08
[DBMS_Oracle] dual  (0) 2014.12.08
[DBMS] HAVING 조건  (0) 2014.12.02

[DBMS_Oracle] 시퀀스


시퀀스


1. 시퀀스 개념 이해와 시퀀스 생성

- 오라클에서는 행을 구분하기 위해서 기본 키를 두고 있습니다. 기본 키는 중복된 값을 가질 수 있으므로 항상 유일한 값을 가져야 합니다. 

- 기본 키가 유일한 값을 갖도록 사용가가 직접 값을 생성해내려면 부담이 클 것입니다. 

- 시퀀스는 블 내의 유일한 숫자를 자동으로 생성하는 자동 번호 발생기이므로 시퀀스를 기본 키로 사용하게 되면 사용자의 부담을 줄일 수 있습니다.


2. 뷰의 기본 테이블

다음은 시퀀스를 생성하기 위한 기본 형식입니다. 

CREATE SEQUENCE sequence_name 

                [START WITH n]                             ① 

                [INCREMENT BY n]                         ② 

                [{MAXVALUE n | NOMAXVALUE}]     ③

                [{MINVALUE n | NOMINVALUE}]       ④

                [{CYCLE | NOCYCLE}]                    

                [{CACHE n | NOCACHE}]                ⑥ 

① START WITH 

시퀀스 번호의 시작값을 지정할 때 사용됩니다. 만일 1부터 시작되는 시퀀스를 생성하려면 START WITH 1이라고 기술하면 됩니다. 


② INCREMENT BY 

연속적인 시퀀스 번호의 증가치를 지정할 때 사용됩니다. 만일 1씩 증가하는 시퀀스를 생성하려면 INCREMENT BY 1이라고 기술하면 됩니다. 


③ MAXVALUE n | NOMAXVALUE 

MAXVALUE 은 시퀀스가 가질 수 있는 최대값을 지정합니다. 만일 NOMAXVALUE를 지정하게 되면 ASCENDING 순서일 경우에는 1027승이고 DESCENDING 순서일 경우에는 -1로 설정됩니다.


④ MINVALUE n | NOMINVALUE

MINVALUE 은 시퀀스가 가질수 있는 최소값을 지정합니다. 만일 NOMINVALUE을 지정하게 되면 ASCENDING 순서일 경우에는 1이고 DESCENDING 순서일 경우에는 1026승으로 설정됩니다.


⑤ CYCLE | NOCYCLE

CYCLE 은 지정된 시퀀스 값이 최대값까지 증가가 완료되게 되면 다시 START WITH 옵션에 지정한 시작 값에서 다시 시퀀스를 시작하도록 합니다. NOCYCLE은 증가가 완료되게 되면 에러를 유발시킵니다. 


⑥ CACHE n | NOCACHE 

CACHE 은 메모리상의 시퀀스 값을 관리하도록 하는 것인데 기본 값은 20입니다. NOCACHE는 원칙적으로 메모리 상에서 시퀀스를 관리하지 않습니다.


다음은 부서 번호를 자동으로 부여해주는 시퀀스 객체를 생성하는 문장입니다. 

CREATE SEQUENCE DEPT_DEPTNO_SEQ

INCREMENT BY 10

START WITH 10;

시작 값이 10이고 10씩 증가하는 시퀀스 EMP_SEQ을 생성합니다. 


3. 시퀀스 관련 데이터 딕셔너리

- 사용자가 작성한 객체들의 정보를 저장하고 있는 데이터 딕셔너리의 이름을 보면 다음과 같은 규칙성이 있습니다. 

- 테이블(TABLE) 객체에 대한 정보를 저장하는 데이터 딕셔너리는 USER_TABLES이고, 뷰(VIEW)객체에 대한 정보를 저장하는 데이터 딕셔너리는 USER_VIEWS이므로 이번 장에서 새로 배운 시퀀스(SEQUENCE)에 대한 자료사전의 이름은 USER_SEQUENCES 이라고 사료될 것입니다.

- 생성된 시퀀스 객체에 대한 정보를 저장하는 데이터 딕셔너리로는 USER_SEQUENCES가 있습니다. 


(1)  시퀀스 관련 데이터 딕셔너리

시퀀스 객체의 정보를 살펴봅시다. 

1) 우선 데이터 딕셔너리 USER_SEQUENCES의 테이블 구조를 먼저 살펴봅시다. 

DESC USER_SEQUENCES;

SEQUENCE_NAME은 시퀀스 객체의 이름을 저장하고 

MIN_VALUE는 최소값, MAX_VALUE는 최대값. 

INCREMENT_BY는 증가치에 대한 정보를 가지고 있으며, 

CYCLE_FLAG는 CYCLE옵션을 사용하는지, 하지 않는지에 대한 정보를 가지며

LAST_NUMBER는 마지막 숫자 값을 가지고 있습니다. 


2) 데이터 딕셔너리 USER_SEQUENCES로 현재 사용 중인 시퀀스 객체의 정보를 살펴봅시다. 

SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE,

INCREMENT_BY, CYCLE_FLAG

FROM USER_SEQUENCES;


지금까지 생성한 시퀀스 객체는 DEPT_DEPTNO_SEQ 한 개이기에 데이터 딕셔너리에도 DEPT_DEPTNO_SEQ 시퀀스 객체 하나에 대한 정보만 나타납니다. 

INCREMENT_BY가 증가치를 지정하는 컬럼으로 10씩 증가한다고 증가치가 설정되어 있고 MIN_VALUE와 MAX_VALUE, CYCLE 옵션 값은 따로 지정하지 않았으므로 기본 값인 1과 1027, N(사이클을 사용하지 않겠다)으로 지정되어 있습니다. 


4. CURRVAL, NEXTVAL 

- 시퀀스의 현재 값을 알아내기 위해서 CURRVAL를 사용하고, 다음 값을 알아내기 위해서는 NEXTVAL를 사용합니다. 

CURRVAL : 현재 값을 반환한다.

NEXTVAL : 현재 시퀀스값의 다음 값을 반환한다


- CURRVAL에 새로운 값이 할당되기 위해서는 NEXTVAL로 새로운 값을 생성해야 합니다. 

- 즉, NEXTVAL로 새로운 값을 생성한 다음에 이 값을 CURRVAL에 대체하게 됩니다. 

NEXTVAL, CURRVAL을 사용할 수 있는 경우와 사용할 수 없는 경우를 살펴봅시다. 


- NEXTVAL, CURRVAL을 사용할 수 있는 경우

서브 쿼리가 아닌 SELECT 문

INSERT 문의 SELECT 절

INSERT 문의 VALUE절

UPDATE문의 SET 절


- NEXTVAL, CURRVAL을 사용할 수 없는 경우

VIEW의 SELECT 절

DISTINCT 키워드가 있는 SELECT 문

GROUP BY, HAVING, ORDER BY 절이 있는 SELECT 문

SELECT, DELETE, UPDATE의 서브 쿼리

CREATE TABLE, ALTER TABLE 명령의 DEFAULT 값 


- 실습 [CURRVAL, NEXTVAL 사용하기]

CURRVAL, NEXTVAL의 실제 사용 예를 살펴봅시다.

 

1) NEXTVAL로 새로운 값을 생성해야 합니다.

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;


2) 시퀀스의 현재 값을 알아내기 위해서 CURRVAL를 사용합니다. 

SELECT DEPT_DEPTNO_SEQ.CURRVAL FROM DUAL;


5. 시퀀스 실무에 적용하기 

- 시퀀스는 99.9%가 INSERT 연산과 같이 사용되어 컬럼 값을 자동으로 발생시키는 용도로 사용됩니다. 

- 사원 테이블을 생성하면서 사원 번호를 기본 키로 설정하였습니다. 

- 기본 키는 반드시 유일한 값을 가져야 합니다. 사용자가 새로운 사원을 추가할 때마다 유일한 사원번호를 INSERT 해야 하는 번거로움이 있습니다. 

- 사원 번호를 생성하는 시퀀스 객체를 사용하여 사원 번호가 자동 생성되도록 한다면 이러한 번거로움을 덜어줄 수 있습니다. 


- 실습 [시퀀스를 테이블의 기본 키에 접목하기]

사원 번호를 생성하는 시퀀스 객체를 생성하여 이를 기본 키인 사원 번호에 사용하여 사용자가 새로운 사원을 추가할 때마다 유일한 사원번호를 INSERT 해야 하는 번거로움을 줄입시다. 


1) 시작 값이 1이고 1씩 증가하고, 최댓값이 100000이 되는 시퀀스 EMP_SEQ 생성합니다.

CREATE SEQUENCE EMP_SEQ

START WITH 1 

INCREMENT BY 1 

MAXVALUE 100000 ; 


2)  이번에는 생성된 시퀀스를 사용하기 위해서 사원 번호를 기본 키로 설정하여 EMP01란 이름으로 새롭게 생성합시다.

DROP TABLE EMP01;

CREATE TABLE EMP01(

EMPNO NUMBER(4) PRIMARY KEY,

ENAME VARCHAR(10),

HIREDATE DATE

)


3) 사원 번호를 저장하는 EMPNO 컬럼은 기본 키로 설정하였으므로 중복된 값을 가질 수 없습니다. 

다음은 생성한 EMP_SEQ 시퀀스로부터 사원번호를 자동으로 할당받아 데이터를 추가하는 문장입니다. 

INSERT INTO EMP01

VALUES(EMP_SEQ.NEXTVAL, 'JULIA' , SYSDATE); 


- 실습 [뷰 정의하기]

- 뷰를 생성하려는데 다음과 같이 권한을 설정하였다면 30번 부서에 소속된 사원들의 사번과 이름과 부서번호를 출력하기 위한 SELECT문을 하나의 뷰로 다시 정의해 봅시다. 

1) 뷰를 정의합시다.

CREATE VIEW EMP_VIEW30

AS 

SELECT EMPNO, ENAME, DEPTNO

FROM EMP_COPY

WHERE DEPTNO=30;


2)  뷰를 생성할 때 컬럼 이름을 명시하지 않으면 뷰(EMP_VIEW30)를 정의하는 기본 테이블(EMP_COPY)의 컬럼 명을 상속받아 사용합니다. 다음은 생성된 뷰의 구조를 살펴봅시다. 

DESC EMP_VIEW30


3) 데이터를 추가하면서 EMP_SEQ 시퀀스로부터 사원번호를 자동으로 할당받았는지 EMP01 테이블의 내용을 확인합시다.

SELECT * FROM EMP01;



6. 시퀀스 제거하기
DROP SEQUENCE문으로 시퀀스를 제거해 봅시다.
DROP SEQUENCE DEPT_DEPTNO_SEQ;


7. 시퀀스 수정 

- 시퀀스를 제거한 후 확인까지 해보았습니다. 이번에는 시퀀스를 변경해 봅시다. 

- 시퀀스를 변경하려면 ALTER SEQUENCE 문을 사용해야 합니다.

ALTER SEQUENCE sequence_name 

[INCREMENT BY n] 

[{MAXVALUE n | NOMAXVALUE}] 

[{MINVALUE n | NOMINVALUE}] 

[{CYCLE | NOCYCLE}] 

[{CACHE n | NOCACHE}] 


- ALTER SEQUENCE는 START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE와 구조가 동일합니다. 

- START WITH 옵션은 ALTER SEQUENCE를 써서 변경할 수 없습니다. 

- 다른 번호에서 다시 시작하려면 이전 시퀀스를 삭제하고 다시 생성해야 합니다.  


- 실습 [시퀀스 최대값을 변경하기]

이미 생성해서 사용하던 시퀀스의 최대값을 변경해 봅시다. 

1)  시퀀스는 최대값을 지정하지 않으면 기본적으로 1027으로 지정됩니다. 사용자가 임의로 최대값을 지정할 수 있는데 MAXVALUE에 값을 지정하면 됩니다. 10부터 10씩 증가하면서 최대 30까지의 값을 갖는 시퀀스를 생성합시다. 

DROP SEQUENCE DEPT_DEPTNO_SEQ;

CREATE SEQUENCE DEPT_DEPTNO_SEQ

START WITH 10

INCREMENT BY 10 

MAXVALUE 30; 


2) 부서 번호를 계속 생성하다 보면 최대값을 넘게 됩니다. 최대값을 넘을 때까지 시퀀스를 생성해 봅시다. 

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;

이때 CYCLE 옵션을 지정하지 않으면 기본값으로 NOCYCLE를 갖게 되므로 오류가 발생하게 됩니다.


3) ALTER SEQUENCE문을 사용하여 사용 중이던 DEPT_DEPTNO_SEQ 시퀀스의 최대값을 수정해 봅시다.

ALTER SEQUENCE DEPT_DEPTNO_SEQ

MAXVALUE 1000;


4) USER_SEQUENCES 를 조회하면 시퀀스가 수정되었는지 확인할 수 있습니다. 

SELECT SEQUENCE_NAME, MAX_VALUE, INCREMENT_BY, CYCLE_FLAG

FROM USER_SEQUENCES; 




'Server > DBMS' 카테고리의 다른 글

[DBMS_MySQL] MySQL 설치 및 사용자 추가,권한  (0) 2014.12.13
[DBMS_Oracle] 인덱스  (0) 2014.12.10
[DBMS_Oracle] dual  (0) 2014.12.08
[DBMS] HAVING 조건  (0) 2014.12.02
[DBMS] Group By  (0) 2014.12.02

[DBMS_Oracle] dual


dual


일반적으로 DUAL TABLE의 Owner는 SYS이지만 모든 사용자가 사용할 수 있다. 그리고 SELECT 문장에서 조회 시 오직 하나의 Row, 하나의 Column에 'X'만 출력된다.

DUAL TABLE은 Internal Views에 조인을 위한 목적으로 오라클의 Chuck Weiss에 의해 만들어졌다.

"DUAL 테이블을 오라클 데이터 딕셔너리에 Object로 만들었고, 뷰 내부에서 사용하기 위한 용도로 만들었다. DUAL이라는 이름에서 단지 하나로부터 한 쌍의 열을 만들어 내는 과정을 쉽게 볼 수 있다. 최초 2개의 열을 가지고 있었기 때문에 이름이 DUAL 테이블로 명명된 것이며 지금은 단지 하나의 데이터만 저장되게 되었다."

위 원작자의 설명은 DUAL 테이블의 제작 의도와 중요성에 대해 시사하는 바가 크다고 할 수 있다. 


1. DUAL TABLE 

- Oracle RDBMS가 제공하는 Dummy Table입니다.(임시TABLE)

- 하나의 Row와 하나의 Column으로 이루어져 있습니다. 

- 수식을 계산하거나 Sysdate를 Display하려고 할때 사용합니다.

-  DUAL 이라는 테이블은SYS 사용자가 소유하는 오라클의 표준 테이블로서 오직 한 행(row)에 한 컬럼만 담고 있는 dummy 테이블로서 일시적인 산술연산이나 날짜 연산을 위하여 주로 쓰인다.



2. DUAL TABLE 예제

select 1+2,2+3 from dual;

select 'leejunsik' from dual;

select 12/2,'leejun' from dual;

select sysdate from dual;

 

3. DUAL TABLE 을 쓰는 이유는?

MySQL 에서는 간단한 계산이나 날짜를 출력시 select now(); 형식을 사용합니다.

MySQL 은 이렇게 table명없이 사용해도 되지만 oracle에서는 단순계산이나 날짜를 출력하거나 실제 table의 내용이 아닌  임시의 값을 출력할때  from dual을 씁니다.

dual table은 drop시키거나 insert명령을 줄수없습니다.





'Server > DBMS' 카테고리의 다른 글

[DBMS_Oracle] 인덱스  (0) 2014.12.10
[DBMS_Oracle] 시퀀스  (0) 2014.12.08
[DBMS] HAVING 조건  (0) 2014.12.02
[DBMS] Group By  (0) 2014.12.02
[DBMS] NULL도 데이터이다  (0) 2014.12.02

[DBMS] HAVING 조건

HAVING 조건


- SELECT 절에 조건을 사용하여 결과를 제한할 때는 WHERE 절을 사용하지만 그룹의 결과를 제한할 때는 HAVING 절을 사용합니다. 

- 예를 들어 설명하자면 부서별로 그룹지은 후(GROUP BY), 그룹 지어진 부서별 평균 급여가 2000 이상인(HAVING) 부서번호와 부서별 평균 급여를 출력하는 경우입니다.

SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >= 2000;




부서의 최대값과 최소값을 구하되 최대 급여가 2900이상인 부서만 출력합니다. 

SELECT DEPTNO, MAX(SAL), MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MAX(SAL) >= 2900;







'Server > DBMS' 카테고리의 다른 글

[DBMS_Oracle] 시퀀스  (0) 2014.12.08
[DBMS_Oracle] dual  (0) 2014.12.08
[DBMS] Group By  (0) 2014.12.02
[DBMS] NULL도 데이터이다  (0) 2014.12.02
[DBMS] 서브쿼리  (0) 2014.11.30

[DBMS] Group By


Group By


- 그룹함수를 쓰되 어떤 컬럼 값을 기준으로 그룹함수를 적용할 경우 GROUP BY 절 뒤에 해당 컬럼을 기술하면 됩니다. 

SELECT 칼럼명, 그룹함수

FROM 테이블명 

WHERE 조건 (연산자) 

GROUP BY 칼럼명;


- 합계, 평균, 최대값.이나, 최소값. 등을 어떤 칼럼을 기준으로 그 칼럼의 값 별로 보고자 할 때 GROUP BY 절 뒤에 해당 칼럼을 기술하면 됩니다. 

- GROUP BY 절을 사용할 때 주의할 점은 GROUP BY 절 다음에는 칼럼의 별칭을 사용할 수 없고, 반드시 칼럼명을 기술해야 한다는 점입니다. 


- 사원 테이블을 부서 번호로 그룹지어 봅시다.

SELECT DEPTNO FROM EMP GROUP BY DEPTNO;



다음은 소속 부서별 평균 급여 구하는 예제입니다. 

SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;


 


- 다음은 소속 부서별 최대 급여와 최소 급여를 구하는 예제입니다.

SELECT DEPTNO, MAX(SAL), MIN(SAL) FROM EMP GROUP BY DEPTNO;





'Server > DBMS' 카테고리의 다른 글

[DBMS_Oracle] dual  (0) 2014.12.08
[DBMS] HAVING 조건  (0) 2014.12.02
[DBMS] NULL도 데이터이다  (0) 2014.12.02
[DBMS] 서브쿼리  (0) 2014.11.30
[DBMS] 집합연산자  (0) 2014.11.30