231020 Oracle SQL VIEW TABLE

■ VIEW
    A. 하나이상의 테이블이 있는 데이터를 논리적으로 처리하는 객체
    B. 단지, SELECT 문을 가지고 있는 객체
    C. 간접 ACCESS 를 하기 위해서 

    D. VIEW 객체를 생성하려면  CREATE VIEW 시스템 권한이 있어야 한다.

# test table 생성

CREATE TABLE hr.emp_2023 
AS
SELECT employee_id, last_name, first_name, job_id, manager_id, department_id
FROM hr.employees;

# 권한 부여 → 문제) 스토리지 과부하, 유지관리

GRANT SELECT ON hr.emp_2023 TO insa;

# test table 삭제 

DROP TABLE hr.emp_2023 PURGE;

# test view 생성

CREATE VIEW hr.emp_2023 
AS
SELECT employee_id, last_name, first_name, job_id, manager_id, department_id
FROM hr.employees;

# test view 확인

SELECT * FROM hr.emp_2023;

# view 권한 부여 

GRANT SELECT ON hr.emp_2023 TO insa;

# view 확인 → 'TEXT' 컬럼 확인해서 권한 확인.

SELECT * FROM user_views WHERE view_name = 'EMP_2023';

# object 타입 확인

SELECT * FROM user_objects WHERE object_name IN ( 'EMPLYEES', 'EMP_2023' );

[문제] 부서이름별, 총액급여, 평균급여를 ACCESS 하는 dept_sum_sal view 를 생성한 후 'insa' 유저한테 dept_sum_sal view 에 대한 SELECT 권한을 부여해주세요.
1# SELECT 문 작성.

SELECT d.department_id, sumsal, avgsal
FROM ( SELECT department_id, sum(salary) sumsal, round(avg(salary)) avgsal
       FROM hr.employees
       group by department_id ) e, hr.departments d
WHERE e.department_id = d.department_id ;

2# test view 생성

CREATE VIEW hr.dept_sum_sal
AS
SELECT d.department_id, sumsal, avgsal
FROM ( SELECT department_id, sum(salary) sumsal, avg(salary) avgsal
       FROM hr.employees
       GROUP BY department_id ) e, hr.departments d
WHERE e.department_id = d.department_id ;

3# test view 확인

SELECT * FROM hr.dept_sum_sal;

4# view 권한 부여, 객체권한 확인 

GRANT SELECT ON hr.dept_sum_sal TO insa;
SELECT * FROM user_tab_privs WHERE TABLE_NAME = 'DEPT_SUM_SAL';

5# view 확인 

SELECT * FROM user_views WHERE view_name = 'DEPT_SUM_SAL';

6# object 타입 확인

SELECT * FROM user_objects WHERE object_name IN ( 'EMPLYEES', 'DEPT_SUM_SAL' );

# 모든 객체의권한 회수 

REVOKE ALL ON 테이블명 FROM '권한을 준 계정명';

REVOKE ALL ON hr.employees FROM insa;
REVOKE ALL ON hr.departments FROM insa;

■ DROP VIEW 
    A. DROP VIEW 테이블명; 

# test view 생성

CREATE VIEW hr.dept_sum_sal
AS
SELECT d.department_id, sumsal, avgsal
FROM ( SELECT department_id, sum(salary) sumsal, avg(salary) avgsal
       FROM hr.employees
       GROUP BY department_id ) e, hr.departments d
WHERE e.department_id = d.department_id ;

# test view 확인

SELECT * FROM hr.dept_sum_sal;

# view 수정
    A. OR REPLACE: 동일한 이름의 뷰가 있으면 삭제하고 재생성한다./

CREATE OR REPLACE VIEW hr.dept_sum_sal
AS
SELECT d.department_id 부서이름, sumsal 총액급여, avgsal 평균급여
FROM ( SELECT department_id, sum(salary) sumsal, avg(salary) avgsal
       FROM hr.employees
       GROUP BY department_id ) e, hr.departments d
WHERE e.department_id = d.department_id;

# 수정된 view 확인

SELECT * FROM hr.dept_sum_sal;

# view 수정

CREATE OR REPLACE VIEW hr.dept_sum_sal(부서이름, 총액급여, 평균급여)
AS
SELECT d.department_id, sumsal, avgsal
FROM ( SELECT department_id, sum(salary) sumsal, avg(salary) avgsal
       FROM hr.employees
       GROUP BY department_id ) e, hr.departments d
WHERE e.department_id = d.department_id;

# 수정된 view 확인

SELECT * FROM hr.dept_sum_sal;

# test table, view 생성

CREATE TABLE hr.emp_20 
AS 
SELECT employee_id id, last_name name, salary sal
FROM hr.employees
WHERE department_id = 20;

# test table 확인

SELECT * FROM hr.emp_20;

# test view 생성

CREATE VIEW hr.emp_20_view 
AS 
SELECT * FROM hr.emp_20;

# test table, view 확인

SELECT * FROM hr.emp_20;
SELECT * FROM hr.emp_20_view;

# test table, view 확인

desc hr.emp_20
desc hr.emp_20_view

# DATA 로드

INSERT INTO hr.emp_20_view ( id, name, sal ) 
VALUES ( 1, '홍길동', 1000 );

# test table, view 확인

SELECT * FROM hr.emp_20;
SELECT * FROM hr.emp_20_view;

COMMIT;

# UPDATE

UPDATE hr.emp_20_view 
SET name = '박찬호'
WHERE id = 1;

 

# test table, view 확인

SELECT * FROM hr.emp_20;
SELECT * FROM hr.emp_20_view;

COMMIT;

# DELETE 하기

DELETE FROM hr.emp_20_view WHERE id = 1;

COMMIT;

# test table, view 확인

SELECT * FROM hr.emp_20;
SELECT * FROM hr.emp_20_view;

# 권한 부여

GRANT SELECT, INSERT, UPDATE, DELETE ON hr.emp_20_view TO insa;

1# 단순 VIEW
    A. 단순 VIEW 를 통해서 DML 작업을 수행할 수 있다.
    B. VIEW 안에 SELECT 문에 사용한 테이블이 하나인 경우
    C. VIEW 안에 SELECT 문에 함수를 사용하지 않은 경우
    EX)
    CREATE VIEW hr.emp_20_view 
    AS 
    SELECT id, upper(name), round(sal) FROM hr.emp_20;
    
2# 복합 VIEW
    A. DML 작업을 수행할 수 없다.
    B. VIEW 안에 SELECT 문에 사용한 테이블이 여러 개인 경우
    C. JOIN 문장이 있는 경우
    D. VIEW 안에 SELECT 문에 함수를 사용한 경우 
    EX)
    CREATE OR REPLACE VIEW hr.dept_sum_sal(부서이름, 총액급여, 평균급여)
    AS
    SELECT d.department_id, sumsal, avgsal
    FROM ( SELECT department_id, sum(salary) sumsal, avg(salary) avgsal
           FROM hr.employees
           GROUP BY department_id ) e, hr.departments d
    WHERE e.department_id = d.department_id;
    E. 복합 VIEW 이지만 DML 작업을 하려면 PL/SQL 를 이용해서 트리거 프로그램을 작성하면 된다.

# test view 생성
    - CHECK 제약조건     

CREATE OR REPLACE VIEW hr.emp_20_view 
AS 
SELECT id, name, sal 
FROM hr.emp_20
WHERE sal BETWEEN 1000 AND 10000
WITH CHECK OPTION CONSTRAINT emp_20_view_ck;

# CHECK 제약조건 확인

# WHERE 절에 있는 조건식이 CHECK 제약조건의 조건식이 된다.

SELECT * FROM user_constraints WHERE table_name = 'EMP_20_VIEW';

# 오류발생 : ORA-01402: view WITH CHECK OPTION where-clause violation
- CHECK 제약조건 위반

INSERT INTO hr.emp_20_view ( id, name, sal ) VALUES ( 2, '나얼', 100 );

# DATA 로드

INSERT INTO hr.emp_20_view ( id, name, sal ) VALUES ( 2, '나얼', 2000 );

# test table, view 확인

SELECT * FROM hr.emp_20;
SELECT * FROM hr.emp_20_view;

# CHECK 제약조건 위반 : "0개 행 이(가) 업데이트되었습니다."

UPDATE hr.emp_20_view 
SET sal = 100
WHERE id = 1;

# 가능
    - CHECK 제약조건은 INSERT , UPDATE 에서 수행된다

DELETE FROM hr.emp_20_view WHERE id = 1;

# 뷰를 통해서 읽기만 가능하도록 하는 방법

CREATE OR REPLACE VIEW hr.emp_20_view 
AS 
SELECT id, name, sal 
FROM hr.emp_20
WITH READ ONLY;

# READ ONLY 제약조건 확인

SELECT * FROM user_constraints WHERE table_name = 'EMP_20_VIEW';

# 오류발생 : ORA-42399: cannot perform a DML operation on a read-only view
- 원인) WITH READ ONLY 로 설정되어 있어서 오류발생, DML 불가.

INSERT INTO hr.emp_20_view ( id, name, sal ) VALUES ( 2, '나얼', 100 );

<insa session>;
# 객체 권한

SELECT * FROM user_tab_privs;
SELECT * FROM hr.emp_2023;

# 문제) SELECT 확인

SELECT * FROM user_tab_privs WHERE TABLE_NAME = 'DEPT_SUM_SAL';
SELECT * FROM hr.dept_sum_sal;

# 'INSA'가 받은 객체 권한에 대한 객체들만 확인

SELECT * FROM all_objects WHERE owner = 'HR';

# 'INSA'가 받은 객체 권한에 대한 VIEW 객체들만 확인 

SELECT * FROM all_views WHERE owner = 'HR';