0817_Oracle : VIEW 뷰를 이용한 데이터 추가 및 수정 삭제

실습을 위한 테이블을 만들어 보자.

CREATE TABLE test1 (
    id VARCHAR2(30) PRIMARY KEY, 
    name VARCHAR2(30) NOT NULL,
    city VARCHAR2(30) NOT NULL,
    memo VARCHAR2(100)
);

CREATE TABLE test2(
    num NUMBER PRIMARY KEY,
    id VARCHAR2(30)NOT NULL,
    score NUMBER(3) NOT NULL,
    FOREIGN KEY ( id ) REFERENCES test1 ( id )
    ON DELETE CASCADE
);

INSERT INTO test1(id, name, city, memo) VALUES ('a', '김자바', '서울', NULL);
INSERT INTO test1(id, name, city, memo) VALUES ('b', '오라클', '경기', NULL);
INSERT INTO test1(id, name, city, memo) VALUES ('c', '스프링', '인천', 'C/C++');
INSERT INTO test1(id, name, city, memo) VALUES ('d', '이기자', '서울', NULL);

INSERT INTO test2 (num, id, score) VALUES (1,'a',80);
INSERT INTO test2 (num, id, score) VALUES (2,'b',70);
INSERT INTO test2 (num, id, score) VALUES (3,'c',90);
INSERT INTO test2 (num, id, score) VALUES (4,'d',85);
COMMIT;

-- 테이블 test1과 test2를 조인해서 뷰를 만들어보자.

CREATE OR REPLACE VIEW testView1
AS
SELECT t1.id, num, name, memo, score
FROM test1 t1
JOIN test2 t2 ON t1.id = t2.id;

-- test1 테이블을 이용하여 작성

CREATE OR REPLACE VIEW testView2
AS
SELECT id, name, city
FROM test1;

-- 뷰가 만들었는지 확인

SELECT * FROM tab;

테이블이 많아서 이것저것 많이나오길래 뷰테이블만 뽑았습니다.

지금 만들어진 뷰 중에 testView1은 두 개의 테이블을 JOIN해서 만든 뷰이다.

 

-- 뷰에 데이터 추가

이 경우에, 뷰를 통한 데이터 추가는 에러가 발생한다.

INSERT INTO testView1(id, num, name, city, memo, score)
    VALUES ('e', 5, '너자바', '부산', null, 80);

단순 뷰(하나의 테이블로 만들어진 뷰)가 아닌 경우 데이터 추가가 불가능하다.

반면, 하나의 테이블로 이루어진 뷰는 데이터 추가가 가능하다.

INSERT INTO testView2(id, name, city) VALUES('e', '너자바', '부산');

e, 너자바, 부산이 추가되었음을 확인할 수 있다.

단순뷰는 제약 조건을 위반하지 않으면 추가가 가능하다. 위 테이블의 경우 id, name, city가 모두 NOT NULL이므로 값이 다 입력되어야 하는 제약조건이 있다. INSERT할 때 제약조건을 만족했으므로 데이터의 추가가 잘 되었다.

INSERT INTO testView2(id, name) VALUES ('f', '안된다');

위 경우는 city가 NULL이 될 수 없기 때문에 에러가 발생한다.

 

-- 뷰를 이용한 데이터 수정

하나의 테이블로 만들어졌던 뷰 testView2는 데이터 수정이 가능하다.

UPDATE testView2 SET name='가나다' WHERE id = 'a';

하지만 테이블 2개를 JOIN했던 testView1은 수정이 불가능하다.

 

-- WITH CHECK OPTION 

 

-- testView2 뷰를 city가 서울인 자료만 갖는 뷰로 수정해보자.

CREATE OR REPLACE VIEW testView2
AS
SELECT id, name, city
FROM test1
WHERE city = '서울';

-- testView2 뷰를 이용하여 id='a'의 city를 '제주'로 변경해보자.

UPDATE testView2 SET city = '제주' WHERE id = 'a';

SELECT * FROM testView2;
SELECT * FROM test1;

ID가 a인 사람의 city가 제주로 변경되었음을 확인할 수 있다.

 

-- testView2 뷰에 WITH CHECK OPTION을 부여해서 서울이 아닌 값으로 수정하지 못하도록 설정

CREATE OR REPLACE VIEW testView2
AS
SELECT id, name, city
FROM test1
WHERE city = '서울'
WITH CHECK OPTION;

UPDATE testView2 SET city = '제주' WHERE id = 'd';

WITH CHECK OPTION이 '서울'인 것을 바꾸지 못하게 설정해놨으므로 city를 바꿀 수 없다. 

UPDATE testView2 SET name = '제주도안가' WHERE id = 'd';

하지만 WITH CHECK OPTION이 걸려있지 않은 name항목은 수정이 가능했다.

 

뷰 삭제

DROP VIEW testView1;
DROP VIEW testView2;