뷰(view)

이름을 가지고 있는 select 문장

• 뷰는 실제 데이터를 저장하지 X
• 뷰 생성 시 작성한 구문을 객체에 저장 후 불러옴

-- emp50이라는 이름으로 뷰 생성
CREATE VIEW emp50 
AS 
SELECT employee_id, last_name, salary, department_id 
FROM employees 
WHERE department_id = 50 ; 

-- 검색가능
SELECT * FROM emp50 ;

-- emp60이라는 이름으로 뷰 생성
CREATE VIEW emp60 
AS 
SELECT employee_id, last_name, salary, department_id 
FROM employees 
WHERE department_id = 60 ; 

-- 검색가능
SELECT * FROM emp60 ;

-- emp50과 emp60이 검색됨
SELECT * 
FROM user_views ;

-- 아래 두 문장은 같은 내용이 검색됨
SELECT * FROM emp50 ;

SELECT * 
FROM (SELECT employee_id, last_name, salary, department_id 
        FROM employees 
       WHERE department_id = 50) ; 

🔸 뷰의 이름이 등장하면, 그걸 해당 쿼리로 바꿔치기 함

sub-query = view의 영역

SELECT * 
FROM (SELECT employee_id, last_name, salary, department_id 
        FROM employees 
       WHERE department_id = 50) ; 

🔸 뷰는 실제 데이터를 저장하지 않기 때문에 update 해도 검색해보면 나오지 않음

-- 실제 데이터가 저장되지 않을 뿐, 갱신은 가능
UPDATE emp50 
SET salary = 9000
   ,department_id = 60 
WHERE employee_id = 120 ; 

-- employee_id = 120이 검색되지 않음
SELECT * 
FROM emp50 ; 

🔸 DML로 view를 조작 가능하지만, 조작의 결과는 테이블에 적용됨

-- salary = 9000, department_id = 60으로 조회됨
SELECT * 
FROM employees 
WHERE employee_id = 120 ; 

뷰의 이점

• 데이터 액세스 제한
• 복합 query를 단순화
• 데이터 독립성 제공
• 동일한 데이터의 다른 뷰 제공

뷰 생성 (단순 뷰)

CREATE |OR REPLACE| |FORCE|NOFORCE| VIEW view
	[ (alias[, alias]...) ]
  AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

• 이 subquery에 복합 SELECT구문 포함 가능
• FORCE: 뷰를 강제적으로 테이블보다 먼저 만들고 싶을 때 사용
  (뷰가 데이터가 아닌 텍스트만 저장하기 때문에)
  (거의 쓸 일 없긴 함)

뷰의 구조 조회

-- 둘다 가능
describe emp50
desc emp50

alias(별칭) 사용 가능

CREATE VIEW emp50
 AS SELECT employee_id, last_name, salary*12 AS ANNSAL, 
 			department_id 
	FROM 	employees 
	WHERE 	department_id = 50 ; 

📌 salary*12: *은 컬럼명으로 사용 불가하기 때문에 별칭 필수!

뷰 수정

✔️ CREATE OR REPLACE VIEW 절

CREATE OR REPLACE VIEW emp50
AS 
SELECT employee_id, last_name, salary*12 AS ANNSAL, department_id 
FROM employees 
WHERE department_id = 50 ; 

복합 뷰 생성

그룹함수를 포함하는 복합 뷰 생성

-- 예제

CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
AS 
SELECT d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
  FROM employees e JOIN departments d
 USING (department_id) 
GROUP BY d.department_name ;

📌 뷰에 대한 DML 작업 수행 규칙

단순 뷰에서는 대개 DML 작업 수행 가능

✔️ 뷰에 다음 항목이 포함되어 있으면, 행 제거(delete) 불가능

• 그룹 함수
• GROUP BY 절
• DISTINCT 키워드
• pseudocolumn ROWNUM 키워드
  • ROWNUM: 행 번호. 각각의 행마다 순차적으로 증가되는 번호

✔️ 뷰의 데이터 수정(update) 불가능

• 그룹 함수
• GROUP BY 절
• DISTINCT 키워드
• pseudocolumn ROWNUM 키워드
• 표현식

✔️ 데이터 추가(insert) 불가능

• 그룹 함수
• GROUP BY 절
• DISTINCT 키워드
• pseudocolumn ROWNUM 키워드
• 표현식으로 정의된 열
• 뷰에서 선택되지 않은 기본 테이블에서 기본값이 없는 NOT NULL 열
  • 뷰를 생성할 때 일부만 데이터를 추출해서 생성할 수 있으므로
    제약이 있는 경우 DML문이 실행되지 않을 수 있음

-- 오류발생
DELETE dept_sum_vu 
WHERE name = 'Administration' ; 

-- 가능
DELETE emp50 
WHERE employee_id = 125 ; 

-- 오류발생: 표현식 포함. update 불가능
UPDATE emp50 
SET annsal = annsal * 1.2 
WHERE employee_id = 120 ;

WITH CHECK OPTION 절

제약조건 정의 가능 (제약을 걸 수 있음)
뷰에 수행된 DML 작업이 뷰 영역에만 적용되도록 할 수 있음

-- 제약조건 정의됨
-- department_id가 60이 아닌 행을 INSERT하거나
-- 뷰에 있는 임의의 행에서 부서번호를 UPDATE하려는 시도는
-- WITH CHECK OPTION을 위반하므로 실패함
CREATE OR REPLACE VIEW emp60 
AS 
SELECT employee_id, last_name, salary, department_id 
FROM employees 
WHERE department_id = 60 
WITH CHECK OPTION CONSTRAINT emp60_ck ; 

-- 갱신됨
UPDATE emp60 
SET department_id = 70 
WHERE employee_id = 103 ; 

DML 작업 거부

WITH READ ONLY를 사용해서 읽기 전용 모드로 만들어줌

CREATE OR REPLACE VIEW emp60 
AS 
SELECT employee_id, last_name, salary, department_id 
FROM employees 
WHERE department_id = 60 
WITH READ ONLY ; 

뷰 제거

• 뷰는 데이터베이스의 기본 테이블을 기반으로 하기 때문에
  뷰를 제거해도 데이터 손실 X

DROP VIEW view;