#11 DBMS _VIEW, SEQUENCE

truelight92·2021년 11월 12일
0

DBMS

목록 보기
11/11

oracleDB XE11g R2, DBeaver 21.2.3 wihdow10

  1. VIEW(뷰)
  • view느 table과 유사하며, 테이블 처럼 사용한다
  • 테이블과는 달리 데이터를 저장하기 위한 물리적인 공간이 필요하지 않은 가상 테이블이다
  • 데이터를 물리적으로 갖진 않지만 논리적인 집합을 갖는다
  • 테이블 마찬가지로 select, insert, update, delete 명령이 가능하다
  1. view의 활용

2-1. 보안관리를 위한 뷰

  • 보안 등급에 맞추어 컬럼 및 범위를 정하여 조회 하도록 함
  • 연산결과만 제공하고 알고리즘을 숨기귀 위해 사용
  • select list 함수로 가공하여 update, insert를 못하도록함
  • 테이블 명이나 컬럼 명을 숨시도록 함

2-2. 사용편의성을 위한 뷰

  • 검색조건을 단순화하여 사용할 수 있도록 함
  • 조인은 단순화
  • 사용자를 위한 컴럼명이나 테이블명 제공
  1. SEQUENCE(시퀀스)
  • 시퀀스랑 자동으로 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체
  • row가 추가될 때마다 자동으로 순번을 추가해준다

3-1. 시퀀스 생성구문

CERATE SEQUENCE 시퀀스이름
INCREMENT BY N			-- 증가값을 설정, 기본값 1 설정
START WITH N			-- 시작값 설정, 기본값 1 설정
MAXVALUE N | NOMAXVALUE 	-- 최대값 설정 | 무한대 값
MINVALUE N | NOMINVALUE		-- 최소값 설정 | 무한대 값
CYCLE | NOCYCLE			-- 시퀀스 순환을 사용할지 설정
cache n				-- 시퀀스를 빠르게 제공하기 위해 메모리에 캐쉬하는 갯수를 정한다 기본값은 20
-- CREATE VIEW
CREATE VIEW V_EMP (
	EMP_ID , FIRST_NAME , JOB_ID , HIRE_DATE , DEPT_ID
) AS
SELECT	EMPLOYEE_ID , FIRST_NAME , JOB_ID , HIRE_DATE , DEPARTMNET_ID
FROM	EMPLOYEES e
WHERE	JOB_ID = 'ST_CLERTK'
;

-- Julia
SELECT * FROM V_EMP ;
SELECT * FROM V_EMP EMP_ID = 125 ;
SELECT * FROM EMPLOYEES e WHERE EMPLOYEE_ID = 125 ;

UPDATE V_EMP SET FIRST_NAME = 'Julia' WHERE EMP_ID = 125 ;

-- DROP VIEW
DROP VIEW V_EMP ;

-- CREATE VIEW
-- 함수로 작성된 부분은 수정 불가
CREATE VIEW V_EMP2 (
	EMP_ID , FIRST_NAME , LAST_NAME , JOB_ID , HIRE_DATE , DEPT_ID
) AS
SELECT	EMPLOYEE_ID , NVL(FIRST_NAME, '') , LAST_NAME , JOB_ID , HIRE_DATE , DEPARTMNET_ID
FROM	EMPLOYEES e
WHERE	JOB_ID = 'ST_CLERTK'
;

SELECT * FROM V_EMP2 ;
SELECT * FROM V_EMP2 EMP_ID = 125 ;
SELECT * FROM EMPLOYEES e WHERE EMPLOYEE_ID = 125 ;

-- 함수로 작성된 부분은 수정 불가
UPDATE V_EMP2 SET FIRST_NAME = 'test' WHERE EMP_ID = 125;
-- 함수로 작성안된 부분은 수정 가능
UPDATE V_EMP2 SET LAST_NAME = 'test' WHERE EMP_ID = 125;
-- 원복
UPDATE V_EMP2 SET LAST_NAME = 'Nayer' WHERE EMP_ID = 125;

-- CREATE OR REPLACE VIEW VIEW
-- 뷰테이블이 없으면 만들고 있으면 대체
-- VIEW 대신 TABLE도 가능
CREATE OR REPLACE VIEW V_EMP_SALARY (
	EMP_ID , LAST_NAME , SALARY , TOTAl_SAL
) AS
SELECT	EMPLOYEE_ID , LAST_NAME , SALARY , 
	(SALARY+SALARY*NVL(COMMISSION_PCT, 0))*12
FROM	EMPLOYEES e
;

SELECT * FROM V_EMP_SALRARY ;

-- READ_ONLY
CREATE VIEW V_EMP_READONLY (
	EMP_ID , LAST_NAME , TOTAL_SAL
) AS
SELECT	EMPLOYEE_ID , LAST_NAME , 
	(SALARY+SALARY*NVL(COMMISSION_PCT, 0))*12
FROM	EMPLOYEES e
WITH REAd ONLY
;

SELECT * FROM V_EMP_READONLY ver ;
-- 리드온리 옵션으로 수정 불가
UPDATE V_EMP_REAKONLY SET LAST_NAME = 'kjh' WHERE EMP_ID = 100;

-- Steven
-- EMP_DETAILS_VIEW 뷰도 리드온리 옵션으로 수정이 불가능하다
SELECT * FROM EMP_DETAILS_VIEW edv WHERE EMPLOYEE_ID = 100 ;
UPDATE EMP_DETAILS_VIEW SET FIRST_NAME = 'kjh' WHERE EMPLOYEE_ID = 100 ;

-- 뷰테이블 조인
CREATE VIEW V_EMP_JOIN (
	EMP_ID , FIRST_NAME , JOD_ID , HIRE_DATE , DEPT_ID , DEPT_NAME
) AS
SELCT	e.EMPLOYEE_ID , e.FIRST_NAME , e.JOB_ID , e.HIRE_DATE , e. DEPARTMENT_ID , d.DEPARTMENT_NAME
FROM	EMPLOYEES e
	INNER JOIN DEPARTMENT d
    	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
WHERE	JOB_ID = 'ST_CLERK'
;

SELECT * FROM V_EMP_JOIN vej ;

-- SEQUENCE
-- CREATE SEQUENCE
CREATE SEQUENCE SEQ_SERIAL_NO
INCREMENT BY 1
START WITH 100
MAXVALUE 110
MINVALUE 99
CYCLE
cache 2
;

CREATE TABLE GOOD (
	GOOD_NO		NUMBER(3)
    	GOOD_NAME	VARCHAR2(10)
);

-- nextval 다음값
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품1');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품2');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품3');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품4');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품5');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품6');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품7');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품8');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품9');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품10');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품11');
-- 맥스벨류값을 넘으면 민벨류값으로 나온다
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품12');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.nextval, '제품13');

SELECT * FROM GOOD g ;

--currval 현재값
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.currval, '제품19');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.currval, '제품19');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.currval, '제품19');
INSERT INTO GOOD
VALUES (SEQ_SERIAL_NO.currval, '제품19');

SELECT * FROM GOOD g ;

-- 현재 시퀀스 값 확인
-- 듀얼 테이블을 이용한다
SELECT SEQ_SERIAL_NO.currval FROM dual g ;

-- 시퀀스 값이 있는 row를 삭제하면
SELECT * FROM GOOD g WHERE GOOD_NO = 102 ;
DELETE FROM GOOD g WHERE GOOD_NO = 102 ;
SELECT * FROM GOOD g WHERE GOOD_NO ;

-- SEQUENCE 삭제
DROP SEQUENCE SEQ_SERIAL_NO ;

-- SEQUENCE 초기화
ALTER SEQUENCE SEQ_SERIAL_NO INCREMENT BY 1 ;

ALTER SEQUENCE SEQ_SERIAL_NO INCREMENT BY -10 ;

DROP SEQUENCE SEQ_SERIAL_NO ;

0개의 댓글