오라클 9탄 (sequence,view)

시퀀스 (Sequence)

유일한 값을 생성해 주는 오라클 객체

시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 

생성할 수 있다.

보통 PRIMARY KEY 값을 생성하기 위해 사용하게 됩니다.  

기본형식) 생성

CREATE SEQUENCE 테이블명

[START WITH n] -> 시퀀스의 시작 값을 지정할 때 사용

 n을 1로 지정하면 1부터 순차적으로 번호가

 증가하게 된다.

  INCREMENT BY n  -> 증가값, N을 2로 지정하게 되면 2씩 증가

[MAXVALUE] -> 최대값 지정할 때 사용

  [MINVALUE] -> 최소값 지정할 때 사용

  [CYCLE|NOCYCLE] -> 최대값 도달시 순환여부 결정할 때 사용

  [CACHE|NOCACHE] ->  CACHE 여부결정 

시퀀스 수정 형식)

ALTER CREATE SEQUENCE 시퀀스이름

[START WITH n] -> 시퀀스의 시작 값을 지정할 때 사용

 n을 1로 지정하면 1부터 순차적으로 번호가

 증가하게 된다.

  INCREMENT BY n  -> 증가값, N을 2로 지정하게 되면 2씩 증가

[MAXVALUE] -> 최대값 지정할 때 사용

  [MINVALUE] -> 최소값 지정할 때 사용

  [CYCLE|NOCYCLE] -> 최대값 도달시 순환여부 결정할 때 사용

  [CACHE|NOCACHE] ->  CACHE 여부결정

  

사용 예)

CREATE SEQUENCE seq_empno

;SELECT * FROM EMP;

CREATE SEQUENCE seq_empno

START WITH 8000

  INCREMENT BY 1

  CACHE 20;

-- 8000에서 시작하여 1씩 추가됨

SELECT SEQ_EMPNO.NEXTVAL FROM DUAL;

--사원 번호를 계속 추가시켜줌

--NEXTVAL 중간에 오류가 날수 있어 연속된 값이 보장될 수 없다 EX)8000,(NULL),8002

--시퀀스를 안쓰는 경우도 있다.

SELECT * FROM EMP;

INSERT INTO EMP

(

            EMPNO --사원번호

              , ENAME --사원이름 컬럼

              , JOB --업무

              , MGR --상급자 사원번호

              , HIREDATE--입사일 

              , SAL --급여

              , COMM --보너스

              , DEPTNO --부

            )

VALUES  (

    SEQ_EMPNO.NEXTVAL

    , 'KIM'

              , 'MANAGER'

              , 7000

              , SYSDATE

              , 3000

              , NULL

              , 20

    );

SELECT

    EMPNO

     , ENAME

     , JOB

     , MGR

     , HIREDATE

     , SAL

     , COMM

     , DEPTNO

  FROM EMP

 WHERE ENAME = 'KIM';

SELECT SEQUENCE_NAME

, MIN_VALUE

     , MAX_VALUE

     , INCREMENT_BY

     , LAST_NUMBER

  FROM USER_SEQUENCES;

---

VIEW

사용목적)

1) 데이터 보안

2) 복잡한 쿼리(질의문, SELECT문 -> WHERE 절의 검색문이나, 조인문)를 반복사용할 경우 편리

형식)

CREATE VIEW 뷰이름

AS

  SELECT 컬럼1, 컬럼2,.... 

  FROM 테이블이름

  WHERE 조건 -> 조인

;

CREATE VIEW V_EMP1

AS

SELECT 

 FIRST_NAME 

      , LAST_NAME

      , EMAIL

      , HIRE_DATE

      , SALARY

  FROM EMPLOYEES

 WHERE DEPARTMENT_ID IN(20,30);

 

SELECT * FROM V_EMP1;

-- VIEW 테이블 생성

사원테이블에서 DEPARTMENT_ID 가 20인 사원과 급여가 10000이상인

사원정보를 대상으로 V_EMP2 뷰를 생성하시오;;

CREATE VIEW V_EMP2

AS

SELECT 

 FIRST_NAME 

      , LAST_NAME

      , EMAIL

      , HIRE_DATE

      , SALARY

  FROM EMPLOYEES

 WHERE DEPARTMENT_ID = 20

  AND SALARY >= 10000;

   

SELECT * FROM V_EMP2;

--OR REPLACE <- 수정 꼭!! 확실히 확인하고 수정 @#$^%!

CREATE OR REPLACE VIEW V_EMP2

AS

SELECT 

 FIRST_NAME 

      , LAST_NAME

      , EMAIL

      , HIRE_DATE

      , SALARY

      , EMPLOYEE_ID

      , DEPARTMENT_ID

  FROM EMPLOYEES

 WHERE DEPARTMENT_ID = 20

  AND SALARY >= 10000;

   

   

문제) 사원테이블에서 입사일이 2005년 상반기인 사원정보를 출력하시오

VIEW 를 생성하시오;;

CREATE VIEW V_EMP3

AS

SELECT 

        LAST_NAME

      , EMAIL

      , HIRE_DATE

      , SALARY

      , EMPLOYEE_ID

      , DEPARTMENT_ID

  FROM EMPLOYEES 

 WHERE TO_CHAR(HIRE_DATE, 'YYYYMM') BETWEEN '200501' AND '200506'

  WITH READ ONLY;

--실질적으로 뷰를 만들때는 READ ONLY를 띄운다. 

-- 읽기 전용으로만 가능하게 만든다.

-- SELECT 구문을 먼저 작성, 확인후 CREATE VIEW

-- 기존 VIEW 스크립트 활용 [대신 꼭 변경해야될 필요가 있을때 스크립트를 뜬다(복사)]

  SELECT * FROM V_EMP3;

  

  

  

  UPDATE 테이블이름(뷰이름)

  SET 컬럼1 = 값 1

    .

        .

   WHERE 조건

   

  -- FIRST_NAME이 Adam 인 사원의 LAST_NAME을 'AAA'로 변경하시오 

  사용예);

  UPDATE EMPLOYEES

  SET LAST_NAME = 'Fripp'

   WHERE FIRST_NAME = 'Adam';

   

   

   SELECT  FIRST_NAME

    ,LAST_NAME

          ,DEPARTMENT_ID

FROM EMPLOYEES

WHERE UPPER(FIRST_NAME) = 'ADAM';

   

    

  UPDATE V_EMP3

  SET LAST_NAME = 'AAA'

   WHERE FIRST_NAME = 'Adam';

   

 

SELECT * FROM V_EMP4;

문제3) 사원테이블에서 부서번호가 10,20인 사원의 정보를 저장하는 VIEW를 생성하시오;

CREATE OR REPLACE VIEW V_EMP4

AS

SELECT 

EMPLOYEE_ID

, FIRST_NAME 

      , LAST_NAME

      , EMAIL

      , HIRE_DATE

      , SALARY

      , DEPARTMENT_ID

      , JOB_ID

  FROM EMPLOYEES

 WHERE DEPARTMENT_ID IN(10,20)

  WITH CHECK OPTION;

 --WITH CHECK OPTION을 걸어야만 조건에 맞는 값만 가져올 수 있다.

 

 

 --뷰는 테이블이 아니다. 즉, 인서트가 되지 않는다

 --뷰의 인서트가 아니라 원래 테이블(EMPLOYEE)에 들어가 있다.

 ;;

INSERT INTO V_EMP4

(

                    EMPLOYEE_ID

                  , FIRST_NAME

                  , LAST_NAME

                  , JOB_ID

                  , EMAIL

                  , HIRE_DATE

                  , SALARY

                  , DEPARTMENT_ID

                  )

VALUES

      (

       8001

                    , 'AAA'

                    , 'BBB'

                    , 'AD_VP'

                    , 'CC4@AAG.C4M'

                    , SYSDATE

                    , 10000

                    , 20

      );

--뷰에 새로운 사람을 추가하는 INSERT 구문 

SELECT * FROM V_EMP4;

문제) 사원테이블에서 사원번호, 사원이름, 급여, 업무명, 부서명을 출력하시오

단, 업무명이 Accountant이고, 부서이름이 IT인 사원만 출력하시오

;

CREATE VIEW V_SALES

AS;

SELECT 

E.EMPLOYEE_ID

, E.FIRST_NAME || ' ' || LAST_NAME AS NAME

      , E.SALARY

      , E.JOB_ID

      , D.DEPARTMENT_NAME

      , J.JOB_TITLE

      

  FROM EMPLOYEES E

  JOIN DEPARTMENTS D

    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

  JOIN JOBS J

  ON J.JOB_ID = E.JOB_ID

 

 WHERE UPPER(DEPARTMENT_NAME) = 'IT'

AND UPPER(JOB_TITLE) = 'ACCOUNTANT'

   WITH CHECK OPTION;

 

SELECT * FROM V_SALES WHERE SALARY >= 10000;

SELECT  E.EMPLOYEE_ID

, E.FIRST_NAME || ' ' || LAST_NAME AS NAME

      , E.SALARY

      , E.JOB_ID

      , D.DEPARTMENT_NAME

      , J.JOB_TITLE

      

  FROM EMPLOYEES E

  JOIN DEPARTMENTS D

    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

  JOIN JOBS J

  ON J.JOB_ID = E.JOB_ID

 

 WHERE UPPER(DEPARTMENT_NAME) = 'SALES'

AND UPPER(JOB_TITLE) = 'SALES REPRESENTATIVE'

; 

SELECT COUNT(*)

  FROM EMPLOYEES E

 INNER JOIN JOBS J

  ON E.JOB_ID = J.JOB_ID

  INNER JOIN DEPARTMENTS D

  ON E.DEPARTMENT_ID = D.DEPARTMENT_ID

  WHERE upper(J.JOB_TITLE) = 'ACCOUNTANT'

  AND D.DEPARTMENT_NAME = 'IT' 

;