VIEW

VIEW

-- 18_View
-- 뷰(View) : 하나 이상의 테이블이나 다른 뷰를 이용해서 생성하는 가상의 테이블
-- 활용) 1) 보안을 유지하기 위해 사용함 : 사원테이블 - 급여컬럼(비밀로 유지 : 대외비)
--      2) SQL 문을 미리 만들어 놓고 재활용 : 코딩 생산성 향상

VIEW 생성

-- 예제1) 부서테이블과(DEPARTMENT) 사원테이블을(EMPLOYEE) 
--        조인한 결과를 뷰로 만들어서 출력하세요
--  뷰이름 : VW_EMP_COMPLEX
-- 열명은 중복이 안되게 지정

-- 조인
SELECT EMP.*, DEP.DNAME, DEP.LOC
FROM EMPLOYEE EMP, DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO;

-- 뷰 생성
CREATE OR REPLACE VIEW VM_EMP_COMPLEX
AS
SELECT EMP.*, DEP.DNAME, DEP.LOC
FROM EMPLOYEE EMP, DEPARTMENT DEP
WHERE EMP.DNO = DEP.DNO;

-- 뷰 사용
SELECT * FROM VM_EMP_COMPLEX;

-- 뷰 정보 확인 : 데이터사전(시스템테이블:관리용(미리생성되어 있음) : USER_VIEWS
SELECT * FROM USER_VIEWS;

-- 뷰에 INSERT 가능한가? => 가능
-- 예제 2) VW_EMP_JOB 에 INSERT 하기
-- 뷰 컬럼 :  사번, 사원이름, 부서번호, 담당업무
INSERT INTO VW_EMP_JOB
VALUES(8000, '윤정화', 30, 'SALESMAN');
COMMIT;
SELECT * FROM VW_EMP_JOB;

-- 예제3) 뷰에 그룹함수 써서 만들기
-- 그룹함수 : SUM, AVG, COUNT, MAX, MIN 등
-- 부서별(DNO) 급여합계(SUM(SALARY)), 급여평균(AVG(SALARY))을 
-- 출력하는 SQL문을 뷰로 만드세요
-- 출력 : DNO, SUM(SALARY), AVG(SALARY)
-- 뷰이름 : VW_EMP_SALARY
-- 대상테이블 : EMPLOYEE
-- 힌트 : 컬럼에 별칭을 부여하세요.
--      급여합계 : SAL_SUM
--      급여평균 : SAL_AVG
-- 뷰 생성시 그룹함수는 별명이 필요함
CREATE OR REPLACE VIEW VW_EMP_SALARY
AS
SELECT DNO, SUM(SALARY) AS SAL_SUM, ROUND(AVG(SALARY), 1) AS SAL_AVG
FROM EMPLOYEE
GROUP BY DNO;

SELECT * FROM VW_EMP_SALARY;


-- 그룹함수로 만든 뷰에 INSERT? 불가!
-- 뷰 컬럼 : DNO, SAL_SUM, SAL_AVG
INSERT INTO VW_EMP_SALARY
VALUES(30, 1000, 3000);     -- 에러발생

-- 뷰 삭제(DROP ~)
DROP VIEW VW_EMP_SALARY;

참고사항

-- (참고) 뷰 INSERT 기본 가능(그룹함수가 없을 때)
-- 뷰에 INSERT 기능 제한 : 옵션 추가 => WITH READ ONLY
-- 예제 4) WITH READ ONLY 옵션을 붙여서 뷰 만들기
-- SQL : 직위가 MANAGER 인 사원을 출력하는 뷰 만들기
CREATE OR REPLACE VIEW VW_EMP_JOB_READONLY
AS
SELECT ENO, ENAME, DNO, JOB
FROM EMPLOYEE
WHERE JOB LIKE '%MANAGER%' WITH READ ONLY;

SELECT * FROM VW_EMP_JOB_READONLY;
-- INSERT 테스트
INSERT INTO VW_EMP_JOB_READONLY
VALUES(9000, '김주현', 30, 'MANAGER');

// 오류발생
SQL 오류: ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
42399.0000 - "cannot perform a DML operation on a read-only view"

연습문제

-- 19_View_Exam
-- 뷰 연습문제

--— 1) 20번 부서에(DNO) 소속된 사원의 사원번호와(ENO) 
--     이름과(ENAME) 부서번호를(DNO) 출력하는 
--—    VIEW를 정의하시오.(VW_EMP_DNO)
--    대상테이블 : EMPLOYEE
-- 뷰 : SQL 문을 특정 이름으로 정해놓은 가상 테이블
CREATE OR REPLACE VIEW VW_EMP_DNO
AS
SELECT ENO, ENAME, DNO
FROM EMPLOYEE
WHERE DNO = 20;

SELECT * FROM VW_EMP_DNO;

--— 2) 이미 생성되어 있는 상기 뷰에(1번뷰) 대해 급여(SALARY) 
--     역시 출력하도록 수정해서 재생성하세요
-- (VW_EMP_DNO)
--    대상테이블 : EMPLOYEE

CREATE OR REPLACE VIEW VW_EMP_DNO
AS
SELECT ENO, ENAME, DNO, SALARY
FROM EMPLOYEE
WHERE DNO = 20;

SELECT * FROM VW_EMP_DNO;

--— 3) 생성된 뷰를 제거하세요(VW_EMP_DNO)

DROP VIEW VW_EMP_DNO;