[SQL] VIEW

2022.09.07 16:53

📁 VIEW

오라클 객체의 한 종류

SELECT 쿼리의 결과로 나온 Result Set을 화면에 저장한 논리적 가상 테이블

실질적으로 데이터를 저장하진 않지만 사용자는 테이블과 동일하게 사용 가능

1) 권한 부여

GRANT CREATE VIEW TO 유저명;

2) 객체 생성

CREATE VIEW 뷰명
AS
SELECT 컬럼명, 컬럼명 FROM 테이블명;

3) 동기화

VIEW의 동작 방법은 다음과 같다.

  1. 뷰가 저장되어 있는 USER_VIEWS 테이블에서 해당 뷰를 찾는다.
  2. TEXT 컬럼에 저장된 SELECT문을 실행하여 결과값을 내보낸다.

따라서 테이블을 변경하면 그 결과가 뷰에도 반영된다.

CREATE VIEW V_MEMBER
AS SELECT ID, PW, AUTHORITY FROM MEMBER;

INSERT INTO MEMBER
VALUES('mrge', 'password');

SELECT * FROM MEMBER
MINUS
SELECT * FROM V_MEMBER;	-- 인출된 모든 행 : 0

📁 VIEW의 DML

뷰에도 DML을 활용할 수 있지만, 조작 불가능한 경우가 존재한다.

(1) 뷰 정의에 포함되지 않는 컬럼

뷰에서 정의에 포함되지 않은 컬럼을 조작하는 경우, 에러가 발생한다.

CREATE VIEW V_JOB
AS SELECT JOB_NAME FROM JOB;

-- 값의 수가 너무 많습니다
INSERT INTO V_JOB VALUES('J8', '인턴');

-- 부적합한 식별자
UPDATE V_JOB
SET JOB_NAME='인턴' WHERE JOB_CODE='J8';

-- 부적합한 식별자
DELETE FROM V_JOB
WHERE JOB_CODE='J8';

(2) NOT NULL 제약조건

베이스 테이블에서 NOT NULL 제약조건이 지정된 컬럼이 뷰에 포함되지 않은 경우에 해당된다.

뷰에 데이터를 삽입할 경우 해당 컬럼에는 NULL값이 삽입되어야 하기 때문에 에러가 발생한다.

CREATE OR REPLACE VIEW V_JOB
AS SELECT JOB_NAME FROM JOB;

-- NULL을 ("KH"."JOB"."JOB_CODE") 안에 삽입할 수 없습니다
INSERT INTO V_JOB3 VALUES('인턴');

(3) 산술 표현식으로 정의된 경우

INSERT, UPDATE는 불가능하지만 DELETE는 가능하다.

CREATE VIEW V_SAL
AS
SELECT EMP_NAME, SALARY*(1+NVL(BONUS, 0))
FROM EMPLOYEE;

-- 가상 열은 사용할 수 없습니다
INSERT INTO V_SAL VALUES(‘김땡땡’, 3000000);

(4) 그룹함수나 GROUP BY절을 포함한 경우

그룹함수는 이미 계산된 값이기 때문에 갑자기 고정값이 들어올 수 없다.

CREATE OR REPLACE VIEW V_GROUPDEPT
AS
SELECT DEPT_CODE, AVG(SALARY) FROM EMPLOYEE
GROUP BY DEPT_CODE;

-- 가상 열은 사용할 수 없습니다
INSERT INTO V_GROUPDEPT
VALUES('D10', 4000000);

-- 뷰에 대한 데이터 조작이 부적합합니다
UPDATE V_GROUPDEPT
SET DEPT_CODE='D10'
WHERE DEPT_CODE='D3';

-- 뷰에 대한 데이터 조작이 부적합합니다
DELETE FROM V_GROUPDEPT
WHERE DEPT_CODE='D1';

(5) DISTINCT를 포함한 경우

CREATE OR REPLACE VIEW V_DT_EMP
AS
SELECT DISTINCT JOB_CODE FROM EMPLOYEE;

-- 테이블 또는 뷰가 존재하지 않습니다
INSERT INTO V_DT_EMP
VALUES('J8');

-- 테이블 또는 뷰가 존재하지 않습니다
UPDATE V_DT_EMP
SET JOB_CODE='J8'
WHERE JOB_CODE='J7';

-- 테이블 또는 뷰가 존재하지 않습니다
DELETE FROM V_DT_EMP
WHERE JOB_CODE='J1';

(6) JOIN으로 여러 테이블을 연결한 경우

INSERT, UPDATE는 불가능하지만 DELETE는 가능

CREATE OR REPLACE VIEW V_JOINEMP
AS
SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID);

-- 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.
INSERT INTO V_JOINEMP
VALUES('김땡땡', '인사관리부');

-- 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
UPDATE V_JOINEMP
SET DEPT_TITLE='인사관리부'
WHERE EMP_NAME='김땡땡';

DELETE V_JOINEMP
WHERE DEPT_TITLE='인사관리부';

📁 옵션

1) OR REPLACE

생성한 뷰가 이미 존재할 경우 그 뷰에 덮어씀

CREATE OR REPLACE VIEW 뷰명

2) FORCE, NOFORCE

기본 테이블이 존재하지 않는 상태에서 뷰를 생성할 수 있는지 지정하는 옵션

기본값으로는 테이블이 존재해야 뷰를 생성할 수 있는 NOFORCE가 지정되어 있음

CREATE FORCE VIEW 뷰명
CREATE NO FORCE VIEW 뷰명

3) WITH CHECK OPTION

WHERE절의 조건을 벗어나는 데이터를 삽입하거나 조건에 맞지 않는 값으로 수정하지 못하도록 막는 옵션

CREATE VIEW 뷰명
AS
SELECT * FROM 테이블명
WHERE 조건
WITH CHECK OPTION;

4) WITH READ ONLY

뷰에 대해 조회만 가능하고 삽입, 수정, 삭제는 불가능하게 함

CREATE VIEW 뷰명
AS SELECT * FROM 테이블명
WITH READ ONLY;
저작자표시

티스토리툴바