220422 데이터베이스 뷰, 시퀀스

1. VIEW(뷰)

1-1. 뷰란?

• 하나의 가상 테이블이라고 생각
• 작업을 하다 보면 자주 조회하는 데이터들이 존재한다. 한 테이블에 데이터가 있으면 조회하기 편하겠지만 그렇지 않은 경우가 있다. 여러 테이블을 JOIN하여 가져오고 쿼리가 복잡한 경우도 있다. 이런 경우 쿼리로 뷰를 만들어 놓고 사용하면 편리하다.
• 뷰는 보안에도 유리하다. 테이블에 데이터를 노출시키고 싶지 않을 때, 뷰를 사용하여 보여줄 테이터만 뷰로 제공할 수 있다.

 

1-2. 뷰의 특징

• 뷰는 테이블과 유사하며, 테이블처럼 사용한다.
• 테이블과는 달리 데이터를 저장하기 위한 물리적인 공간이 필요하지 않은 가상 테이블이다.
• 데이터를 물리적으로 갖지는 않지만, 논리적인 집합을 갖는다.
• 테이블과 마찬가지로 SELECT, INSERT, UPDATE, DELETE 명령이 가능하다.

 

1-3. 예제

-- job_id가 ST_CLERK인 사람의 정보로 VIEW 생성
CREATE OR REPLACE VIEW v_emp2(emp_id, first_name, job_id, hiredate, dept_id)
AS 	SELECT	EMPLOYEE_ID, FIRST_NAME, JOB_ID , HIRE_DATE , DEPARTMENT_ID 
	FROM	EMPLOYEES
	WHERE 	JOB_ID = 'ST_CLERK'
;

SELECT * FROM V_EMP2 ve

-- Julia(125)
UPDATE V_EMP2 SET FIRST_NAME = 'Kim' WHERE EMP_ID = 125;
SELECT * FROM v_emp2 WHERE emp_id = 125;
-- James(127)
UPDATE v_emp2 SET first_name = 'Kim' WHERE emp_id = 127;
SELECT * FROM v_emp2 WHERE emp_id = 127;

-- 뷰로 변경한 컬럼이 원본 컬럼에도 영향을 미친 걸 확인할 수 있다.
SELECT * FROM EMPLOYEES e WHERE JOB_ID = 'ST_CLERK' ;


-- 함수로 작성한 부분은 수정 불가
-- job_id가 SH_CLERK인 사람의 정보로 VIEW 생성. NVL() 함수 사용
CREATE OR REPLACE VIEW v_emp(emp_id, first_name, job_id, hiredate, dept_id)
AS 	SELECT	NVL(EMPLOYEE_ID, NULL), nvl(FIRST_NAME, null), JOB_ID , HIRE_DATE , DEPARTMENT_ID 
	FROM	employees
	WHERE 	JOB_ID = 'SH_CLERK'
;
-- 수정이 불가능하기 때문에 UPDATE 오류
UPDATE V_EMP SET FIRST_NAME = 'Julia' WHERE EMP_ID = 127;


-- EMPLOYEES 테이블 UPDATE(Julia, James 원상복구)
UPDATE EMPLOYEES SET FIRST_NAME = 'Julia' WHERE EMP_ID = 125;
UPDATE EMPLOYEES SET FIRST_NAME = 'James' WHERE EMP_ID = 127;
SELECT * FROM EMPLOYEES e WHERE EMPLOYEE_ID IN (125, 127);


-- 뷰 이름 : V_EMP_SALARY
-- EMPLOYEES 테이블에서 EMPLOYEE_ID, LAST_NAME, SALARY(월급), 연봉
CREATE VIEW V_EMP_SALARY (EMP_ID, LAST_NAME, SALARY, ANNUAL_SAL)
AS 	SELECT	EMPLOYEE_ID, LAST_NAME, SALARY, SALARY*12
	FROM	EMPLOYEES
;

SELECT * FROM V_EMP_SALARY;

-- read_only
CREATE VIEW V_EMP_SALARY_readonly (EMP_ID, LAST_NAME, SALARY, ANNUAL_SAL)
AS 	SELECT	EMPLOYEE_ID, LAST_NAME, SALARY, SALARY*12
	FROM	EMPLOYEES
WITH READ ONLY
;

SELECT * FROM V_EMP_SALARY;

-- UPDATE 오류
-- UPDATE V_EMP_SALARY_READONLY SET LAST_NAME = 'Kim' WHERE EMP_ID = 100;

 

1-4. 실습

-- 1. 뷰 생성
-- 50번 부서, job_id가 ST_CLERK, 연봉이 40000 이상인 사람들의 사번, LAST_NAME, DEPARTMENT_ID, HIRE_DATE
CREATE OR REPLACE VIEW V_EMP_TEST (EMP_ID, LAST_NAME, DEPT_ID, HIRE_DATE)
AS 	SELECT 	EMPLOYEE_ID , LAST_NAME , DEPARTMENT_ID , HIRE_DATE 
	FROM	EMPLOYEES e 
	WHERE 	DEPARTMENT_ID = 50
	AND		JOB_ID = 'ST_CLERK'
	AND 	(SALARY)*12 >= 40000
;

SELECT * FROM V_EMP_TEST;

-- 2. 뷰 생성
-- 50번 부서 사람들의 사번, FIRST_NAME LAST_NAME(이어서 출력), DEPARTMENT_ID, HIRE_DATE
CREATE VIEW V_EMP_TEST2 (EMP_ID, NAME, DEPT_ID, HIRE_DATE)
AS 	SELECT	EMPLOYEE_ID , FIRST_NAME || ' ' || LAST_NAME , DEPARTMENT_ID , HIRE_DATE 
	FROM 	EMPLOYEES e 
	WHERE 	DEPARTMENT_ID = 50
;

SELECT * FROM V_EMP_TEST2 ;

-- 3. 뷰 생성
-- 사번, 이름, 부서번호, 부서명, 입사일
CREATE VIEW V_EMP_TEST3 (사번, 이름, 부서번호, 부서명, 입사일)
AS 	SELECT	e.EMPLOYEE_ID , e.FIRST_NAME || ' ' || e.LAST_NAME
		, e.DEPARTMENT_ID , d.DEPARTMENT_NAME , e.HIRE_DATE 
	FROM 	EMPLOYEES e
	LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 	
;

SELECT * FROM V_EMP_TEST3 ;

 

 

 

2. SEQUENCE(시퀀스)

• 연속적인 번호를 만들어주는 기능
• 자동 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체이다.
• 보통 PK 값에 중복값을 방지하기 위해 사용한다.
• 예를 들어 게시판에 글이 하나 추가될 때마다 글번호(PK)가 생겨야 한다고 할 때, 만약 100번까지 글 번호가 생성되었다면 그 다음 글이 추가 되었을 경우, 글 번호가 101인 row를 하나 생성해주어야 하고, 이 때 101이라는 숫자를 얻기 위해 기존 글 번호 중 가장 큰 값에 +1을 하는 로직을 어딘가에는 넣어야 한다.
  시퀀스를 사용하면 이러한 로직 필요 없이 데이터 베이스에 row가 추가될 때마다 자동으로 +1을 시켜주어 매우 편리하다. 

 

2-1. 시퀀스 생성

CREATE SEQUENCE 시퀀스이름
	-- 증가값을 설정, 기본값은 1
    -- ex. N이 2이면 2씩 증가
    INCREMENT BY N
    -- 시작값을 설정, 기본값은 1
    START WITH N
    -- 시퀀스 최대값 설정 | 무한대
    MAXVALUE N | NOMAXVALUE
    -- 시퀀스 최소값 설정 | 무한대
    MINVALUE N | NOMINVALUE
    -- CYCLE로 지정하면 MAXVALUE에 도달했을 때, 다시 MINVALUE부터 시작하게 된다.
    CYCLE | NOCYCLE
    CACHE N | NOCACHE

** CACHE
속도를 증가시키기 위해서 시퀀스 번호를 한 번에 여러 개씩 메모리에 올려놓고 작업을 한다. 이것을 사용하면 매번 시퀀스 번호를 생성하는 것보다 빠르기 때문에 사용한다. 

▶ CACHE에 대한 부분은 이해가 아직 덜 되었다.

 

2-2. 예제

-- sequence
-- 제품번호 생성하는 시퀀스 만들기
CREATE SEQUENCE seq_serial_no
INCREMENT BY 1
START WITH 100
MAXVALUE 110
MINVALUE 99
CYCLE 
cache 2
;
-- 100에서 시작되서 110까지 도달하면 99부터 다시 시작한다.

CREATE TABLE good(
	good_no		number(30),
	good_name	varchar2(10)
);

SELECT * FROM good;
-- nextval : 다음 값
INSERT INTO good VALUES (seq_serial_no.nextval, '제품1');

-- 시퀀스 삭제
DROP SEQUENCE seq_serial_no;

-- currval : 시퀀스 현재값
SELECT 	seq_serial_no.currval
FROM	dual
;