Database_56_데이터베이스 객체_뷰_VIEW

-- 데이터베이스 객체
    -- 뷰
        -- 사용 목적
            -- 데이터 엑세스를 제한하기 위함입니다.
            -- 복잡한 질의를 쉽게 작성하기 위함입니다.
            -- 데이터 독립성을 제공하기 위함입니다.
            -- 동일한 데이터로부터 다양한 결과를 얻기 위함입니다.
                -- 한 개의 테이블에서 여러 개의 뷰를 생성할 수 있습니다.
        -- 뷰 사용 원리
            -- 뷰 이름으로 조회하는 경우 뷰를 생성할 때 작성한 SQL 문장을 기억하고 있다가 호출하여 실행하는 것입니다.
        --  뷰 생성
            /*
            CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW view
                [ alias ]
            AS
            supquery
            [ WITH CHECK OPTION [CONSTRAINT constraint] ]
            [ WITH READ ONLY [CONSTRAINT constraint] ]
                -- OR REPLACE :  뷰가 이미 있어도 다시 생성합니다.
                -- FORCE : 기본 테이블의 존재 여부와 관계 없이 뷰를 생성합니다.
                -- NOFORCE (기본값) (기본값) : 기본 테이블이 있는 경우만 뷰를 생성합니다.
                -- WITH CHECK OPTION : 뷰를 통해 엑세스 할 수 있는 행만 삽입, 갱신할 수 있도록 지정합니다.
                -- WITH READ ONLY : 뷰를 통해서는 DML 작업을 수행할 수 없도록 지정합니다.
                    -- WITH 절은 한 번에 두 개를 같이 사용할 수 없습니다.
                -- alias : 함수를 사용하거나 테이블에 표시되는 데이터가 아닌 경우 필수로 작성해야 합니다.
                    -- 테이블에 있는 데이터를 표현식/함수를 사용하여 가공하는 경우 별칭을 작성해야 합니다.
                    -- 변형된 데이터는 조회만 가능하고 변경이 불가능합니다.
            */
        -- 유형
            -- 단순 뷰_Simple View
                -- 한 테이블에서만 데이터를 얻습니다.
                -- 함수 또는 데이터 그룹을 포함하지 않습니다.
                -- 뷰를 통해 DML 작업을 수행할 수 있습니다.
            -- 복합 뷰_Complex View
                -- 여러 테이블에서 데이터를 얻습니다.
                -- 함수 또는 데이터 그룹을 포함합니다.
                -- 뷰를 통해 DML 작업을 수행할 수 없는 경우도 있습니다.
        -- 뷰 조회
            -- 뷰의 이름으로 조회를 하는 구문을 작성하는 경우 뷰를 생성할 때 작성한 SELECT 문으로 바꿔 조회합니다.
            /*
            SELECT *
            FROM view;
            */
        -- 뷰를 통한 DML이 가능합니다.
            -- 뷰에서 데이터를 수정하는 경우 개념 스키마의 데이터를 변경하는 것입니다.
            
-- 뷰를 만들 데이터를 조회합니다.
SELECT employee_id, first_name || ' ' || last_name AS emp_name, job_id, department_id
FROM employees
WHERE department_id=50;

    
-- 뷰를 생성합니다.
CREATE VIEW emp50_vu
AS
SELECT employee_id, first_name || ' ' || last_name AS emp_name, job_id, department_id
FROM employees
WHERE department_id=50;
    /*
        <오류>-----------------------------------------------------------------------------
        오류 보고 -
        ORA-01031: insufficient privileges
        01031. 00000 -  "insufficient privileges"
        *Cause:    An attempt was made to perform a database operation without
                   the necessary privileges.
        *Action:   Ask your database administrator or designated security
                   administrator to grant you the necessary privileges
        -------------------------------------------------------------------------------------
            -- 뷰를 생성할 권한이 없습니다.
    */

------------------------------------------------
----------관리자 데이터베이스----------
-- 뷰 생성 권한을 부여합니다.
GRANT create view TO hr;
    /*
    Grant을(를) 성공했습니다.
    */


------------------------------------------------
----------인사관리 데이터베이스----------
-- 뷰를 생성합니다.
CREATE VIEW emp50_vu
AS
SELECT employee_id, first_name || ' ' || last_name AS emp_name, job_id, department_id
FROM employees
WHERE department_id=50;
    /*
    View EMP50_VU이(가) 생성되었습니다.
    */

DESC emp50_vu;
    /*
    이름                  널?              유형           
    -------------     --------          ------------ 
    EMPLOYEE_ID   NOT NULL      NUMBER(6)    
    EMP_NAME                          VARCHAR2(46) 
    JOB_ID            NOT NULL      VARCHAR2(10) 
    DEPARTMENT_ID                   NUMBER(4)    
    */

-- 뷰를 조회합니다.
SELECT * 
FROM emp50_vu;

    -- 테이블 복사와 뷰의 차이점 알아보기
-- 테이블에서 60번 부서의 정보를 조회합니다.
SELECT employee_id, last_name, email, job_id, department_id
FROM employees
WHERE department_id=60;

-- 60번 부서 직원 정보의 뷰를 생성합니다.
CREATE VIEW emp60_vu
AS
SELECT employee_id, last_name, email, job_id, department_id
FROM employees
WHERE department_id=60;
    /*
    View EMP60_VU이(가) 생성되었습니다.
    */

-- 뷰를 조회합니다.
SELECT *
FROM emp60_vu;

-- 테이블을 복사합니다.
CREATE TABLE dept60
AS
SELECT employee_id, last_name, email, job_id, department_id
FROM employees
WHERE department_id=60;
    /*
    Table DEPT60이(가) 생성되었습니다.
    */

-- 테이블을 조회합니다.
SELECT * FROM dept60;

-- 테이블과 뷰가 조회하는 경우 동일합니다.

-- 107번 직원의 부서를 변경합니다.
UPDATE employees
SET department_id=50
WHERE employee_id=107;
    /*
    1 행 이(가) 업데이트되었습니다.
    */

-- 테이블을 조회합니다.
SELECT * FROM dept60;
        -- 테이블 데이터를 복사하여 테이블을 생성한 것이므로 변경되지 않습니다.

    
-- 뷰를 조회합니다.
SELECT *
FROM emp60_vu;
        -- 데이터가 업데이트 되어 정보가 출력되지 않습니다.

    -- 뷰의 데이터를 조회합니다.
SELECT view_name, TEXT
FROM user_views;
        -- TEXT : VIEW를 생성할 때 작성한 SQL문을 저장합니다.

    -- 뷰의 SELECT 문장을 변경하여 뷰에 포함되는 데이터의 범위를 수정할 수 있습니다.
CREATE OR REPLACE VIEW emp50_vu
AS
SELECT employee_id, first_name || ' ' || last_name AS emp_name, job_id, manager_id, department_id
FROM employees  
WHERE department_id=50;
    /*
    View EMP50_VU이(가) 생성되었습니다.
    */

-- 뷰를 조회합니다.                                                                                                                      
SELECT *
FROM emp50_vu;

-- 부서 50번의 뷰를 조회합니다.
SELECT *
FROM emp50_vu;

-- 144번 직원의 부서를 80으로 변경합니다.
UPDATE emp50_vu
SET department_id=80
WHERE employee_id=144;
    /*
    1 행 이(가) 업데이트되었습니다.
    */

-- 부서 50번의 뷰를 조회합니다.
SELECT *
FROM emp50_vu;

-- 144번 직원의 부서가 변경되었는지 확인합니다.
SELECT employee_id, department_id
FROM employees
WHERE employee_id=144;
    /*
    EM_ID   DP_ID
    ----------------
    144         80
    */

-- 복합 뷰의 데이터를 조회합니다.
SELECT d.department_name, COUNT(*) AS emp_num, SUM(salary) AS sumsal, TRUNC(AVG(salary)) AS avgsal
FROM employees E JOIN departments d
ON (e.department_id = d.department_id)
GROUP BY d.department_name;

-- 뷰를 생성합니다.
CREATE OR REPLACE VIEW emp_dept_join_sum_vu
AS
SELECT d.department_name, COUNT(*) AS emp_num, SUM(salary) AS sumsal, TRUNC(AVG(salary)) AS avgsal
FROM employees E JOIN departments d
ON (e.department_id = d.department_id)
GROUP BY d.department_name;
    /*
    View EMP_DEPT_JOIN_SUM_VU이(가) 생성되었습니다.
    */

-- 생성한 뷰를 조회합니다.
SELECT * FROM emp_dept_join_sum_vu;

SELECT * FROM emp50_vu;
        -- EMP_NAME은 성과 이름을 합쳐 출력한 데이터이므로 변경이 불가능합니다.

-- 업데이트한 정보를 확인하기 위해서는 SELECT 문으로 확인해야 합니다.
    -- 뷰를 만들 때 사용한 조건을 변경하는 경우 SELECT 문으로 조회 시 원하는 값으로 수정되었는지 확인할 수 없습니다.

-- 잘못된 부서로 데이터를 변경합니다.
UPDATE emp50_vu
SET department_id=80
WHERE employee_id=143;
    /*
    1 행 이(가) 업데이트되었습니다.
    */

SELECT * FROM emp50_vu;
    -- 제대로 변경되었는지 알 수 없습니다.

SELECT view_name, text
FROM user_views;

-- 되돌리기
ROLLBACK;
    /*
    롤백 완료.
    */

-- WITH CHECK OPTION
CREATE OR REPLACE VIEW emp50_vu
AS
SELECT employee_id, first_name || ' ' || last_name AS emp_name, job_id, manager_id, department_id
FROM employees
WHERE department_id=50
WITH CHECK OPTION;
    /*
    View EMP50_VU이(가) 생성되었습니다.
    */

UPDATE emp50_vu
SET department_id = 50
WHERE employee_id = 103;
    /*
    0개 행 이(가) 업데이트되었습니다.
        -- 부서번호를 수정하는 작업은 오류를 반환합니다.
    */

-- WITH READ ONLY
CREATE OR REPLACE VIEW emp50_vu
AS
SELECT employee_id, first_name || ' ' || last_name AS emp_name, job_id, manager_id, department_id
FROM employees
WHERE department_id=50
WITH READ ONLY;
    /*
    View EMP50_VU이(가) 생성되었습니다.
    */

UPDATE emp50_vu
SET department_id = 50
WHERE employee_id = 103;
    /*
        <오류>----------------------------------------------------------------------------
        오류 보고 -
        SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view
        42399.0000 - "cannot perform a DML operation on a read-only view"
        ------------------------------------------------------------------------------------
            -- 옵션이 추가된 뷰에서 DML 작업을 수행하려고 하는 경우 오류가 발생합니다.
    */

-- 기존에 있는 테이블을 삭제합니다.
DROP TABLE emp PURGE;

-- 테이블을 생성합니다.
CREATE TABLE emp
AS 
SELECT * FROM employees;
    /*
    Table EMP이(가) 생성되었습니다.
    */

-- 생성할 뷰의 데이터를 조회합니다.
SELECT employee_id empno, first_name || ' ' || last_name empname, job_id, department_id deptno
FROM emp
WHERE department_id IN (50, 60);

  

-- 뷰를 생성합니다.
CREATE OR REPLACE VIEW emp5060_vu
AS
SELECT employee_id empno, first_name || ' ' || last_name empname, job_id, department_id deptno
FROM emp
WHERE department_id IN (50, 60);
    /*
    View EMP5060_VU이(가) 생성되었습니다.
    */

-- 인덱스를 생성합니다.
CREATE INDEX emp_empno_ix
ON emp(employee_id);
    /*
    Index EMP_EMPNO_IX이(가) 생성되었습니다.
    */

DESC user_objects;
    /*
    이름                      널?            유형            
    ------------------------------------------ 
    OBJECT_NAME          VARCHAR2(128) 
    SUBOBJECT_NAME   VARCHAR2(30)  
    OBJECT_ID                 NUMBER        
    DATA_OBJECT_ID      NUMBER        
    OBJECT_TYPE          VARCHAR2(19)  
    CREATED                  DATE          
    LAST_DDL_TIME      DATE          
    TIMESTAMP               VARCHAR2(19)  
    STATUS                     VARCHAR2(7)   
    TEMPORARY            VARCHAR2(1)    
    GENERATED             VARCHAR2(1)   
    SECONDARY            VARCHAR2(1)   
    NAMESPACE            NUMBER        
   EDITION_NAME        VARCHAR2(30)
    */
   

SELECT object_name, object_type, created, status
FROM user_objects
WHERE object_name LIKE 'EMP%';

-- 뷰를 삭제합니다.
DROP VIEW emp5060_vu;
    /*
    View EMP5060_VU이(가) 삭제되었습니다.
        -- PURGE 옵션이 없습니다.
    */

SELECT * 
FROM emp5060_vu;
    /*
        <오류>-------------------------------------------
        ORA-00942: table or view does not exist
        00942. 00000 -  "table or view does not exist"
        *Cause:    
        *Action:
        504행, 6열에서 오류 발생
        ---------------------------------------------------
            -- 뷰가 존재하지 않습니다.
                -- 테이블은 그대로 있습니다.
    */

-- 다시 뷰를 생성합니다.
CREATE OR REPLACE VIEW emp5060_vu
AS
SELECT employee_id empno, first_name || ' ' || last_name empname, job_id, department_id deptno
FROM emp
WHERE department_id IN (50, 60);
    /*
    View EMP5060_VU이(가) 생성되었습니다.
    */

-- 테이블을 삭제합니다.
DROP TABLE emp;
    /*
    Table EMP이(가) 삭제되었습니다.
    */

SELECT object_name, object_type, created, status
FROM user_objects
WHERE object_name LIKE 'EMP%';
        -- 테이블이 삭제되면 관련 뷰도 삭제되지는 않습니다.

  

SELECT * 
FROM emp5060_vu;
    /*
        <오류>-------------------------------------------------------------------------
        ORA-04063: view "HR.EMP5060_VU" has errors
        04063. 00000 -  "%s has errors"
        *Cause:    Attempt to execute a stored procedure or use a view that has
                   errors.  For stored procedures, the problem could be syntax errors
                   or references to other, non-existent procedures.  For views,
                   the problem could be a reference in the view's defining query to
                   a non-existent table.
                   Can also be a table which has references to non-existent or
                   inaccessible types.
        *Action:   Fix the errors and/or create referenced objects as necessary.
        ---------------------------------------------------------------------------------
    */

    -- 테이블의 이름이나 컬럼 이름을 변경하는 경우 모든 뷰의 상태가 오류가 발생할 수 있습니다.
    -- 뷰는 테이블과 함께 삭제되지 않습니다.

-- 테이블을 복구합니다.
FLASHBACK TABLE emp TO BEFORE DROP;
    /*
    Flashback을(를) 성공했습니다.
    */
    
-- 뷰를 생성합니다.
CREATE OR REPLACE VIEW emp8090_vu
AS
SELECT employee_id empno, first_name || ' ' || last_name empname, job_id, department_id deptno
FROM emp
WHERE department_id IN (80, 90);
    /*
    View EMP8090_VU이(가) 생성되었습니다.
    */    
    
DROP TABLE emp PURGE;
    /*
    Table EMP이(가) 삭제되었습니다.
    */
    
SELECT object_name, object_type, created, status
FROM user_objects
WHERE object_name LIKE 'EMP%';
        -- 테이블이 삭제되면 관련 뷰도 삭제되지는 않습니다.

    
-- 제거할 SQL 문을 완성하여 출력할 수 있는 문장입니다.
SELECT 'DROP VIEW ' || object_name || ';'
FROM user_objects
WHERE object_type='VIEW'
AND status='INVALID';

    
-- 한 번에 복사하여 실행할 수 있습니다.
DROP VIEW EMP8090_VU;
    /*
    View EMP8090_VU이(가) 삭제되었습니다.
    */
    
DROP VIEW EMP5060_VU;
    /*
    View EMP5060_VU이(가) 삭제되었습니다.
    */