View

    View : 가상 테이블
    실체가 없는 테이블. 다른 테이블을 접근하기 위한 테이블
    
    Table <-------------- View --------------- User
    
    장점 : 한개의 테이블로 여러개의 Table을 검색 할 수 있다.
    속도가 빠르다.
    제한 설정이 가능하다.    ---> read only (읽기전용)

 

 

--기본 View

-- VIEW 생성

CREATE VIEW UB_TEST_01(JOB_ID, JOB_TITLE, MIN_SALARY)
AS
SELECT job_id, job_title, min_salary
FROM jobs;

 

CREATE VIEW UB_VIEW(
    employee_id, first_name, salary )
AS 
SELECT employee_id, first_name, salary
FROM tb_emp;

 

 

-- VIEW에 데이터삽입

INSERT INTO ub_test_01(JOB_ID, JOB_TITLE, MIN_SALARY)
VALUES('DEVELOPER','개발자',10000);           
-- VIEW라는 창구를 통해 JOBS테이블에 삽입됨, VIEW에 삽입된것처럼 보임

 

INSERT INTO UB_VIEW
VALUES(100, '홍길동', 10000);

INSERT INTO UB_VIEW
VALUES(101, '일지매', 9000);

 

 

 

 

 

-- VIEW를 통해 검색

SELECT
    *
FROM ub_test_01; 

 

 

 

-- 되돌리기

ROLLBACK;

 

-- 앞으로 되돌리기

COMMIT;

 

 

--테이블생성

CREATE TABLE TB_EMP
AS
SELECT employee_id, first_name, salary
FROM employees;

 

 

 

---READ ONLY 

 

-- READ ONLY VIEW 생성

CREATE OR REPLACE VIEW DEPTVIEW(
    "부서번호",
    "부서명",
    "지역명" )
AS
SELECT department_id, department_name, location_id
FROM departments
WITH READ ONLY;             --맨아래 붙여줌

CREATE OR REPLACE VIEW DEPT_EMP_VIEW(empno, name, deptno, deptname, loc)
AS
SELECT e.employee_id, e.first_name, d.department_id, d.department_name, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
WITH READ ONLY;

 

 

--READ ONLY VIWE라서 업데이트시 에러가 남

INSERT INTO deptview("부서번호","부서명","지역명")
VALUES(280, '기획부', 1700);

 

--SELECT은 가능하다(읽기)

SELECT
    *
FROM deptview;

 

 

--원하는 값만 검색하기

SELECT name, deptname
FROM dept_emp_view
WHERE name = 'Den';