[oracle] 오라클 뷰(VIEW) - 이해하기 쉽게

오라클에서 뷰(VIEW)는 저장된 쿼리이다. 실제 데이터를 저장하고 있진 않지만, 테이블처럼 행과 열을 가지고 있다. 하나 이상의 테이블에서 데이터를 가져와서 조회하거나 사용할 수 있다. 데이터 사전에서 뷰를 정의하는 쿼리를 저장하고, 그 외에는 별도의 저장 공간이 필요하지 않다.

 

뷰의 특징

• 복잡한 쿼리 단순화
• 데이터 복잡성 숨김
• 테이블 수준 데이터 보안
• 참조된 오브젝트 종속성

 

1. CREATE VIEW

직원 테이블 생성 및 데이터 삽입

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(100),
    emp_number VARCHAR2(20),
    emp_salary NUMBER
);

INSERT INTO employees (emp_id, emp_name, emp_number, emp_salary)
VALUES (1, 'John', '010-1234-5678', 5000);

INSERT INTO employees (emp_id, emp_name, emp_number, emp_salary)
VALUES (2, 'Smith', '010-5555-7777', 6000);

SELECT emp_id, emp_name, emp_number, emp_salary FROM employees;

    EMP_ID EMP_NAME                       EMP_NUMBER           EMP_SALARY
---------- ------------------------------ -------------------- ----------
         1 John                           010-1234-5678              5000
         2 Smith                          010-5555-7777              6000

 

뷰 생성 (연봉 5500이상 직원 조회하는)

CREATE VIEW high_salary_employees AS
SELECT emp_id, emp_name, emp_number, emp_salary
FROM employees
WHERE emp_salary > 5500;

-- 일반 쿼리로 테이블 조회
SELECT emp_id, emp_name, emp_number, emp_salary
FROM employees
WHERE emp_salary > 5500;

    EMP_ID EMP_NAME                       EMP_NUMBER           EMP_SALARY
---------- ------------------------------ -------------------- ----------
         2 Smith                          010-5555-7777              6000

-- 뷰로 테이블 조회
SELECT * FROM high_salary_employees;

    EMP_ID EMP_NAME                       EMP_NUMBER           EMP_SALARY
---------- ------------------------------ -------------------- ----------
         2 Smith                          010-5555-7777              6000

 

뷰 정의 조회

SELECT TEXT
FROM ALL_VIEWS
WHERE VIEW_NAME = 'HIGH_SALARY_EMPLOYEES';

TEXT
--------------------------------------------------------------------------------
SELECT emp_id, emp_name, emp_number, emp_salary
FROM employees
WHERE emp_salary > 5500

 

2. DROP TABLE

뷰는 참조된 오브젝트에 종속성을 가지며, 데이터베이스에서 자동으로 관리한다. 예를 들어, 만약 뷰가 참조하고 있는 테이블을 삭제하거나 다시 생성하면 데이터베이스는 뷰의 정의가 유효한지 아닌지를 판단한다.

 

-- 테이블 삭제
DROP TABLE employees;

-- 뷰로 테이블 조회
SELECT * FROM high_salary_employees;

ERROR at line 1:
ORA-04063: view "TESTUSER.HIGH_SALARY_EMPLOYEES" has errors

-- 테이블 다시 생성 / 데이터 삽입 / 뷰 조회
    EMP_ID EMP_NAME                       EMP_NUMBER           EMP_SALARY
---------- ------------------------------ -------------------- ----------
         2 Smith                          010-5555-7777              6000

 

3. 데이터 보안/복잡성 숨김

테이블의 특정 행이나 열에 대한 접근을 제한하여 테이블 수준의 보안성을 제공한다. 사용자는 조인이나 복잡한 계산을 수행하는 방법을 알 필요 없이 뷰를 통해 쿼리 할 수 있다.

 

-- 뷰 생성 (전화번호, 연봉 컬럼 제외한)
CREATE VIEW public_info_employees AS
SELECT emp_id, emp_name
FROM employees;

-- 뷰 조회
SELECT * FROM public_info_employees;

	EMP_ID EMP_NAME
---------- ------------------------------
         1 John
         2 Smith

-- 테이블 생성 (조인하려는 직원주소 테이블)
CREATE TABLE employee_addresses (
    emp_id NUMBER,
    address VARCHAR2(255)
);

INSERT INTO employee_addresses (emp_id, address)
VALUES (1, '123 Main St, Cityville, CA 12345');

INSERT INTO employee_addresses (emp_id, address)
VALUES (2, '456 Elm St, Townsville, TX 67890');

-- 뷰 생성 (주소 포함)
CREATE VIEW join_addr_employees AS
SELECT e.emp_id, e.emp_name, e.emp_number, e.emp_salary, a.address
FROM employees e
JOIN employee_addresses a ON e.emp_id = a.emp_id;

SELECT * FROM join_addr_employees;

    EMP_ID EMP_NAME                       EMP_NUMBER           EMP_SALARY ADDRESS
---------- ------------------------------ -------------------- ---------- --------------------------------------------------
         1 John                           010-1234-5678              5000 123 Main St, Cityville, CA 12345
         2 Smith                          010-5555-7777              6000 456 Elm St, Townsville, TX 67890

 

4. INSERT / UPDATE / DELETE

뷰는 테이블과 마찬가지로 최대 1000개의 열을 포함할 수 있다. 일부 제한 사항이 적용되지만 뷰에 대해 DML 작업이 가능하다.

 

-- 뷰에 데이터 삽입
INSERT INTO public_info_employees (emp_id, emp_name, emp_salary)
VALUES (3, 'PAUL', 70000);

ERROR at line 1:
ORA-00904: "EMP_SALARY": invalid identifier

// VIEW에 정의되지 않은 컬럼에 대해서는 INSERT가 불가능하다.

SELECT * FROM public_info_employees;

    EMP_ID EMP_NAME
---------- ------------------------------
         1 John
         2 Smith

-- 뷰에 데이터 삽입 (EMP_SALARY 컬럼값 제외)
INSERT INTO public_info_employees (emp_id, emp_name)
VALUES (3, 'PAUL');

1 row created.

 

5. 제약 조건

CHECK OPTION은 뷰에 제약 조건을 추가하여, 뷰를 통해 선택할 수 없는 행에 대해 INSERT 및 UPDATE 문이 실행되지 않는다.

 

-- 뷰 생성(연봉 4000이상 제약조건)
CREATE VIEW const_employees AS
SELECT emp_id, emp_name, emp_number, emp_salary
FROM employees
WHERE emp_salary > 4000
WITH CHECK OPTION CONSTRAINT employees_const_name;

INSERT INTO const_employees (emp_id, emp_name, emp_number, emp_salary)
VALUES (4, 'Jimy', '010-3333-9999', 4000);

ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

-- 뷰 생성(제약조건 없음)
CREATE VIEW no_const_employees AS
SELECT emp_id, emp_name, emp_number, emp_salary
FROM employees
WHERE emp_salary > 4000;

INSERT INTO no_const_employees (emp_id, emp_name, emp_number, emp_salary)
VALUES (4, 'Jimy', '010-3333-9999', 4000);

1 row created.