오라클 기초 #9(sequence,view)

[ 시퀀스 ]

- 연속적인 숫자값을 자동으로 증감시키는 일련번호를 발생시키는 객체

 

- 형식

CREATE SEQUENCE 시퀀스명
[INCREMENT BY N]
[START WITH N]
[MAXVALUE N | NOMAXVALUE N]
[MINVALUE N | NOMINVALUE N]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE]

 

예)

CREATE SEQUENCE TEST_SEQ; -- 시퀀스 생성하기

 

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;-- 일련번호 얻어오기

NEXTVAL
----------
1

 

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

 

NEXTVAL
----------
2

 

SQL> SELECT TEST_SEQ.CURRVAL FROM DUAL; -- 현재발생된 일련번호 얻어오기

 

SQL> DROP SEQUENCE TEST_SEQ; -- 시퀀스 삭제하기

 

예)

SQL> CREATE SEQUENCE TEST_SEQ1
    2  INCREMENT BY 10
    3  START WITH 100
    4  MAXVALUE 100
    5  CYCLE
    6  NOCACHE;
Sequence created.

 

SQL> SELECT TEST_SEQ1.NEXTVAL FROM DUAL;

 

NEXTVAL
----------
100

 

SQL> SELECT TEST_SEQ1.NEXTVAL FROM DUAL;

 

NEXTVAL
----------
1

 

SQL> SELECT TEST_SEQ1.NEXTVAL FROM DUAL;

 

NEXTVAL
----------
11

 

-- 시퀀스 조회하기

SQL> SELECT * FROM SEQ;

 

예)

SQL> CREATE SEQUENCE TEST_SEQ1
    2  INCREMENT BY 10
    3  START WITH 100
    4  MAXVALUE 100
    5  CYCLE
    6  NOCACHE;

SQL> CREATE SEQUENCE BOARD_SEQ;

Sequence created.

 

SQL> INSERT INTO BOARD
    2  VALUES(BOARD_SEQ.NEXTVAL,'TEST','SONG');

1 row created.

 

SQL> INSERT INTO BOARD
    2  VALUES(BOARD_SEQ.NEXTVAL,'TODAY','KIM');

 

SQL> SELECT * FROM BOARD;

 

NUM TITLE  WRITER
--- ------ -------
1   TEST   SONG
2   TODAY  KIM

 

 

[ 뷰(VIEW) ]

1) 뷰는 테이블에 대한 가상 테이블로써 SELECT로 조회된 데이터들의 가상 테이블이다.

 

2) 사용이유

(1) 보안설정
(2) 복잡하고 자주 사용되는 질의를 뷰를 통해서 쉽게 사용

 

3) 형식

CREATE OR REPLACE VIEW 뷰이름
AS
서브쿼리;
..
[WITH CHECK OPTION]
[WITH READ ONLY]

 

 

## 뷰 생성 권한 부여하기

SQL> CONN / AS SYSDBA

SQL> GRANT CREATE VIEW TO SCOTT;

SQL> CONN scott/tiger

 

 

-- 뷰생성하기

SQL> CREATE VIEW MYVIEW
      2  AS
      3     SELECT EMPNO "사원번호",ENAME "사원이름",SAL "급여",JOB "직업"
      4     FROM EMP

 

SQL> SELECT * FROM MYVIEW;

 

사원번호 사원이름 급여 직업
-------- -------- ---- ------
7369     SMITH    800  CLERK
7521     WARD     1250 SALESMAN
7566     JONES    2975 MANAGER
7654     MARTIN   1250 SALESMAN
..

 

SQL> SELECT 사원이름,급여 FROM MYVIEW;

 

사원이름 급여
-------- -----
SMITH    800
WARD     1250
JONES    2975
MARTIN   1250
BLAKE    2850
......

SQL> INSERT INTO MYVIEW
      2  VALUES(9000,'김길동',3000,'청소부');

1 row created.

SQL> SELECT * FROM EMP; -- 실제테이블에 추가됨

-- 뷰제거하기
SQL> DROP VIEW MYVIEW;

 

 

예)

SQL> CREATE OR REPLACE VIEW MYVIEW
      2  AS
      3     SELECT EMPNO,ENAME,SAL,JOB,DEPTNO
      4     FROM EMP
      5     WHERE SAL>=2000
      6     WITH CHECK OPTION;

 

 

SQL> INSERT INTO MYVIEW
      2  VALUES(9002,'송규남',1000,'강사',30);

INSERT INTO MYVIEW
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
--> WITH CHECK OPTION 이 설정되어 있어서 오류!
(뷰의 질의에 대한 조건이 맞지 않으므로 오류!)

 

 

4) 뷰의 종류

(1) 단일뷰 : 하나의 테이블로 생성되는 뷰(기본적으로 DML작업가능. WITH READ ONLY 옵션을 주면 DML작업은 할수없다.)

(2) 복합뷰 : 두개 이상의 테이블로 생성되는 뷰(조인).DML 작업을 할 수 없다.

 

 

Q1) EMP테이블과 DEPT테이블을 이용하여 이름,직업,급여,부서명,위치를 조회하는 뷰를 생성하고 사용해 보세요.

SQL> CREATE OR REPLACE VIEW EMPVIEW
  2  AS
  3     SELECT E.ENAME,E.JOB,E.SAL,D.DNAME,D.LOC
  4     FROM EMP E,DEPT D
  5     WHERE E.DEPTNO=D.DEPTNO;

View created.

SQL> SELECT * FROM EMPVIEW;              

-- 뷰 정보 조회하기
SQL> SELECT VIEW_NAME FROM USER_VIEWS;

VIEW_NAME
----------
EMPVIEW
MYVIEW