나의 개발일지

[SQL] 뷰 본문

Oracle SQL

[SQL] 뷰

YoonJuHan 2023. 6. 5. 19:31

뷰의 개념

  1. 논리적으로 하나 이상의 테이블에 있는 데이터의 부분 집합
  2. 뷰의 기반이 되는 기본 테이블 또는 다른 뷰를 기반으로 하는 논리 테이블
  3. 데이터 딕셔너리에 뷰에 정의한 SELECT 문으로 저장됨
  4. 뷰를 통해 테이블의 데이터를 액세스 하는 사용자는 뷰에서 제공되는 데이터만 제한적으로 사용
    1. 자체적으로 데이터를 포함하지 않음
    2. 뷰를 통해 테이블의 데이터를 보거나 일부 변경 가능

 

뷰의 사용 목적

  1. 데이터 액세스를 제한
    1. 테이블의 열을 선택적으로 표시
  2. 단순한 쿼리를 만들 수 있는 편의성
    1. 뷰를 사용하여 복잡한 쿼리 대신 뷰를 쿼리
    2. 예를 들어, 조인 문 대신 조인 뷰를 사용하여 여러 테이블에 있는 정보를 쿼리
  3. 보안성 증대
    1. 사용자 그룹이 특정 기준에 따라 데이터를 액세스

 

뷰의 종류

  1. 단순 뷰
    1. 뷰를 통해 볼 수 있는 데이터가 기본 테이블 데이터의 부분집합일 경우는 대부분 단순 뷰
    2. 대부분의 단순 뷰는 뷰를 통한 DML을 허용
  2. 복합 뷰
    1. 뷰를 통해 보는 데이터가 기본 테이블 데이터에 함수나 그룹 연산, 조인 등을 수행한 결과인 경우

 

단순 뷰의 특징

  1. 단일 테이블의 데이터만 포함
  2. 뷰의 정의에 함수 또는 데이터 그룹을 미포함
  3. 뷰의 정의에 여러 테이블의 조인 미포함
  4. 대부분의 단순 뷰는 뷰를 통해 DML 작업을 수행할 수 있다.
  5. 주로 기본 테이블에 대한 액세스를 제한하는 용도로 사용

 

복합 뷰의 특징

  1. 여러 테이블의 데이터를 포함
  2. 뷰의 정의에 함수 또는 데이터 그룹을 포함
  3. 뷰의 정의에 여러 테이블의 조인이 포함
  4. 뷰를 통해 DML 작업을 수행할 수 없는 경우가 많다.

 

뷰의 생성

  1. CREATE VIEW 문에 서브 쿼리를 포함시켜 뷰를 생성
    1. 기존 테이블을 복사하는 구문과 유사
    2. VIEW를 통해 보여지는 데이터는 뷰 생성 시 작성한 서브쿼리의 내용에 의해 결정
  2. 서브 쿼리의 열에 별칭을 포함시켜 뷰에서 사용할 열 이름 제어
    1. 테이블에 정의된 열이 아닌 표현식에 별칭을 주지 않으면 오류 반환
  3. 옵션을 추가해서 VIEW를 통한 데이터액세스 방식을 제한
    1. WITH CHECK OPTION
    2. WITH READ ONLY
  4. 구문
    1. 뷰가 이미 있어도 다시 생성하는 OR REPLACE 옵션은 주로 뷰를 재작성할 때 사용
    2. NOFORCE는 기본 테이블이 있는 경우만 뷰를 생성하는 옵션
    3. FORCE 옵션은 기본 테이블의 존재 여부에 관계없이 뷰를 생성
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name
AS
subquery
[WITH CHECK OPTION [CONSTRAINT constraint_name]]
[WITH READ ONLY [CONSTRAINT constraint_name]];

 

뷰의 사용

  1. 테이블 사용과 유사
    1. DESCRIBE 명령 사용해 뷰의 구조 표시
    2. 테이블에서 데이터를 검색하는 것처럼 뷰에서도 데이터를 검색

 

  • y_emp 테이블에서 dept_id가 300인 사원들에 대한 정보를 저장하는 dept300_vu 라는 이름의 단순 뷰 생성
CREATE OR REPLACE VIEW dept300_vu
AS
SELECT emp_id, emp_name, position, mgr_id, dept_id
FROM y_emp
WHERE dept_id = 300;

DESC dept300_vu;

SELECT * FROM dept300_vu;

 

  • 200번 부서의 모든 사원에 대해 별칭이 id, name, ann_sal인 뷰를 생성
CREATE OR REPLACE VIEW sal_vu200
AS
SELECT emp_id id, emp_name name, salary*12 ann_sal
FROM y_emp
WHERE dept_id = 200;

SELECT * FROM sal_vu200;

-- 다음과 같이 작성해도 같은 결과
CREATE OR REPLACE VIEW sal_vu200 (id, name, ann_sal)
AS
SELECT emp_id , emp_name, salary*12 
FROM y_emp
WHERE dept_id = 200;

 

  • 뷰를 정의하여 데이터 액세스를 제한하는 것과 테이블의 사본을 정의하여 데이터 액세스하는 것의 차이점을 알아보기 위해 sal_vu200 뷰와 동일한 서브쿼리로 sal200이라는 이름의 테이블을 생성
CREATE TABLE sal200
AS
SELECT emp_id id, emp_name, salary*12 ann_sal
FROM y_emp
WHERE dept_id = 200;

DESC sal200;
SELECT * FROM sal200;

 

  • 기본 테이블인 y_emp 테이블에서 200번 부서 사원들의 급여에 대한 UPDATE를 수행
UPDATE y_emp
SET salary = salary + 200
WHERE dept_id = 200;
COMMIT;

 

  • sal200 테이블과 sal_vu200 뷰를 검색하여 ann_sal 열의 값이 UPDATE의 결과를 반영하는지 확인
    • 복사한 테이블인 sal200은 기존 테이블 y_emp를 바꿔도 영향을 받지 않는다.
    • sal_vu200은 뷰를 작성한 쿼리 문을 실행하기 때문에 기존 테이블 y_emp를 바꾸면 바뀐 값이 나오게 된다.
SELECT * FROM sal200;
SELECT * FROM sal_vu200;

sal200 테이블
sal_vu200 뷰

 

  • dept300_vu 뷰를 이용해 새 행을 INSERT 하고 기본 테이블을 확인
    • 뷰를 통해 행을 INSERT 하면 기본 테이블에도 반영이 되어 DML이 가능하다는 것을 보여준다.
INSERT INTO dept300_vu
VALUES(2020,  '이병헌', '과장', 1001, 500);
COMMIT;

SELECT emp_id, emp_name, position, salary, mgr_id, dept_id 
FROM y_emp
WHERE emp_id = 2020;

 

  • 뷰를 수정하기 위해 CREATE OR REPLACE 명령문을 사용하여 sal_vu200 뷰에 dept_id 열을 deptid 라는 이름의 별칭으로 추가하여 변경 후 확인
CREATE OR REPLACE VIEW sal_vu200
AS 
SELECT emp_id id, emp_name, salary*12 ann_sal, dept_id deptid
FROM y_emp
WHERE dept_id = 200;

SELECT * FROM sal_vu200;

 

복합 뷰의 생성 및 기타 뷰 관련 옵션

  1. 뷰 정의 서브 쿼리에 다음 중 하나 이상이 포함된 뷰
    1. 표현식
    2. 둘 이상의 테이블의 조인
    3. 일반함수나 표현식
    4. 그룹 함수
    5. 서브 쿼리 등의 복합 SELECT 구문을 포함
  2. 뷰를 통한 DML이 불가능하거나 제한적으로 허용

 

  • 부서 이름 별 총 급여 및 평균 급여를 표시하는 복합 뷰, 뷰의 열이 함수나 표현식으로부터 파생된 경우에는 반드시 별칭이 필요
CREATE VIEW dname_tot_vu (name, avgsal, sumsal)
AS
SELECT d.dept_name, AVG(e.salary), SUM(e.salary)
FROM y_emp e JOIN y_dept d
ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

SELECT * FROM dname_tot_vu;

 

  • INSERT 또는 UPDATE 되는 데이터에 대해 데이터의 유효성을 검사하기 위해 dept300_vu에 WITH CHECK OPTION을 추가하여 뷰를 재작성
CREATE OR REPLACE VIEW dept300_vu
AS
SELECT emp_id, emp_name, position, mgr_id, dept_id
FROM y_emp
WHERE dept_id = 300
WITH CHECK OPTION;

 

  • dept300_vu 뷰를 통해 부서 번호 변경을 시도하면 WITH CKECK OPTION 제약 조건에 위배되어 오류
    • where 절에 dept_id = 300으로 뷰를 만들었기 때문에 dept_id를 바꿀 수 없다.
UPDATE dept300_vu
SET dept_id = 500
WHERE emp_id = 1044;

 

  • DML 작업이 수행되지 않도록 dept300_vu 뷰를 읽기 전용 뷰로 다시 정의
CREATE OR REPLACE VIEW dept300_vu
AS
SELECT emp_id, emp_name, position, mgr_id, dept_id
FROM y_emp
WHERE dept_id = 300
WITH READ ONLY;

 

  • 읽기 전용 뷰에 DML을 시도하면 오류
UPDATE dept300_vu
SET mgr_id = 1035
WHERE emp_id = 1044;

 

뷰를 통한 DML 작업의 규칙

  1. 뷰 정의에 다음의 항목이 포함되는 경우 행을 DELETE 할 수 없다.
    1. 그룹 함수
    2. GROUP BY 절
    3. DISTINCT 키워드
    4. 의사 열 ROWNUM 키워드
  2. 뷰 정의에 다음의 항목이 포함되는 경우 데이터를 UPDATE 할 수 없다.
    1. 그룹 함수
    2. GROUP BY 절
    3. DISTINCT 키워드
    4. 의사 열 ROWNUM 키워드
    5. 표현식에 의해 정의된 열
  3. 뷰 정의에 다음의 항목이 포함되는 경우 뷰를 통해 데이터를 INSERT할 수 없다.
    1. 그룹 함수
    2. GROUP BY 절
    3. DISTINCT 키워드
    4. 의사 열 ROWNUM 키워드
    5. 표현식에 의해 정의된 열
    6. 기본 테이블에서 뷰에 의해 선택되지 않은 열에 NOT NULL 제약 조건이 있는 경우

 

뷰의 동작 원리

  1. 사용자가 뷰를 쿼리하면 오라클 서버는 데이터 딕셔너리로부터 해당 뷰의 이름과 정의를 검색
  2. 사용자가 작성한 뷰를 쿼리하는 명령문은 뷰의 정의에 포함된 서브쿼리의 내용으로 변환되어 실행
  3. 뷰를 사용하면 데이터 액세스를 제한하거나 복잡한 쿼리문을 뷰를 쿼리하는 단순한 쿼리로 대체하는 효과
  4. 대용량 테이블의 쿼리에 있어서 뷰를 통한 쿼리는 성능 향상과 무관

 

뷰 관련 정보

  1. 뷰의 이름과 정의는 데이터 딕셔너리 USER_VIEWS에 저장
  2. USER_VIEWS를 쿼리로 뷰를 작성했던 서브쿼리의 내용을 참조
    1. 뷰 재작성 시 USER_VIEWS 데이터 딕셔너리 활용

 

뷰의 제거

  1. DROP VIEW 문을 사용하면 뷰를 제거
    1. 데이터베이스에서 뷰의 정의만 제거
    2. 뷰의 기반이 되는 테이블은 아무 영향 없음
    3. 삭제된 뷰를 기반으로 하는 뷰나 다른 응용 프로그램은 사용불가
  2. 구문
DROP VIEW view_name;

 

  • user_views를 쿼리하여 dept300_vu와 sal_vu200 뷰에 대한 정보를 확인
SELECT view_name, text
FROM user_views;

 

  • sal_vu200 뷰를 데이터베이스로부터 제거 후 user_views를 다시 쿼리
DROP VIEW sal_vu200;

SELECT view_name, text
FROM user_views;

저작자표시 (새창열림)

'Oracle SQL' 카테고리의 다른 글

[SQL] 데이터베이스 보안  (0) 2023.06.09
[SQL] 시퀀스  (1) 2023.06.07
[SQL] 인덱스  (0) 2023.06.02
[SQL] 제약 조건  (0) 2023.06.02
[SQL] 테이블 삭제  (0) 2023.06.02
'Oracle SQL' Related Articles more
Comments