작성일자 : 2023-10-27
Ver 0.1.1
View Table
뷰(View)는 테이블과 흡사한 오브젝트이다. 뷰는 실제로 데이터를 저장하고 있지는 않지만, DML 작업이 가능한 가상의 테이블이라고 생각하면 된다.
- 뷰는 복잡한 쿼리를 단순화 시킬 수 있다.
- 뷰는 사용자에게 필요한 정보만 접근하도록 접근을 제한할 수 있다.
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰 이름
[(column_aliaes)]
AS
SELECT …
[WITH READ ONLY]
[WITH CHECK OPTION [CONSTRAINT 제약조건명]]- OR REPLACE : 해당 구문을 사용하면 뷰를 수정할 때 DROP 없이 수정이 가능하다.
- FORCE : 뷰를 생성할 때 쿼리문의 테이블, 컬럼, 함수 등이 존재하지 않아도 생성이 가능하다.
- NOFORCES : 뷰를 생성할 때 쿼리문의 테이블, 컬럼 함수 등이 존재하지 않으면 생성되지 않는다.
- column_aliaes : SELECT 컬럼의 별칭을 미리 정의할 수 있다.
- WITH READ ONLY : SELECT만 가능하다. (INSERT, UPDATE, DELETE 불가능)
- WITEH CHECK OPTION : WHERE 절의 조건에 해당하는 데이터만 저장, 변경이 가능하다
*대괄호([]) 항목은 필요하지 않을 경우 생략이 가능하다
단순 뷰 생성
CREATE OR REPLACE VIEW v_emp
AS
SELECT empno
,ename
,job
,hiredate
FROM emp
;단순 뷰는 단일 테이블에 필요한 컬럼을 나열한 것이다. 조인, 함수, GROUP BY, UNION 등을 사용하지 않는다.
단순 뷰는 SELECT, INSERT, UPDATE, DELETE를 자유롭게 사용이 가능하다.
복합 뷰생성
CREATE OR REPLACE VIEW v_emp
AS
SELECT a.empno
,a.ename
,a.job
,TO_CHAR(a.hiredate,’YYYY-MM-DD’) AS hiredate
FROM emp a
,dept b
WHERE a.deptno = b.deptno
;복합 뷰는 조인, 함수, GROUPO BY, UNION 등을 사용하여 뷰를 생성한다.
함수 등을 사용할 경우 컬럼 별칭을 꼭 부여해야 한다 (ex - AS hiredate)
복합뷰는 SELECT은 가능하지만 상황에 따라서 INSERT, UPDATE, DELETE가 가능하지 않을 수도 잇다.
- 단순 뷰와 복합 뷰는 동일한 뷰를 의미하며, 기능을 설명하기 위해 분류한 것이다.
뷰 컬럼 코멘트 추가
COMMENT ON COLUMN v_emp.empno IS ‘사원번호’;테이블 컬럼 코멘트와 동일하게 뷰의 컬럼 코멘트도 입력이 가능하다.
뷰 삭제
DROP VIEW v_emp;컬럼 별칭 선언
CREATE OR REPLACE VIEW v_emp
(
empno
,ename
,job
,hiredate
)
AS
SELECT a.empno
,a.ename
,a.job
,TO_CHAR(a.hiredate,’YYYY-MM-DD’) AS hiredate
FROM emp a
,dept b
WHERE a.deptno = b.deptno
;컬럼 별칭을 상단에 미리 정의하면 쿼리문의 컬럼 순서대로 별칭이 부여된다. 함수를 사용 후 별칭을 부여하지 않아도 되며, SELECT 컬럼의 별칭과 일치하지 않아도 되며 순서와 개수만 맞으면 된다.
FORCE 옵션 사용
CREATE OR REPLACE FORCE VIEW v_emp
AS
SELECT empno
,ename
,job
,hiredate
FROM emp
;FORCE 옵션을 사용하면 쿼리문에 테이블, 컬럼, 함수 등이 존재하지 않을 경우 오류가 발생하지만 뷰는 생성되며 INVALID 상태이기 때문에 뷰는 동작하지 않는다. 오류가 없으면 정상적으로 뷰가 생성된다.
테이블, 함수 등을 미리 생성하지 않고 뷰를 먼저 생성해 놓은 경우 유용하게 사용 가능한 옵션이다.
WITH READ ONLY 옵션 사용
CREATE OR REPLACE VIEW v_emp
AS
SELECT empno
,ename
,job
,hiredate
FROM emp
WITH READ ONLY
;해당 옵션을 사용하면 SELECT만 가능하고 INSERT, UPDATE, DELETE는 불가능하다.
WITH CHECK 옵션 사용
-— 제약조건명을 지정하지 않은 경우
CREATE OR REPLACE VIEW v_emp
AS
SELECT empno
,ename
,job
,hiredate
,deptno
FROM emp
WHERE deptno = ‘10’
WITH CHECK OPTION
;
—- 제약조건명을 지정한 경우
CREATE OR REPLACE VIEW v_emp
AS
SELECT empno
,ename
,job
,hiredate
,deptno
FROM emp
WHERE deptno = ‘10’
WITH CHECK OPTION CONSTRAINT emp_ck
;해당 옵션을 사용하면 INSERT, UPDATE, DELETE를 사용할 때 WHERE절읠 조건에 해당하는 데이터만 접근이 가능하다 (ex - deptno = ‘10’)
INSERT INTO v_emp(empno, ename, deptno) VALUES(9999, ‘TEST’, 20)
ORA-012402 : 뷰의 WITH CHECK OPTION 의 조건에 위배됩니다.
deptno = ‘20’은 WHERE절의 조건에 부합하지 않아서 INSERT시 오류가 발생한다.
WITH CHECK OPTION [CONSTRAINT 제약 조건명]
제약조건명을 지정하지 않아도 기능은 동일하게 작동하며, 제약 조건이 생성될 때 원하는 이름으로 생성되느냐 시스템이 자동으로 이름을 부여하느냐의 차이이다. 지정하지 않으면 SYS_XXXXXXXX로 생성된다.
DML 사용시 유의사항(INSERT, UPDATE, DELETE)
단순 뷰인 경우 INSERT, UPDATE, DELETE가 자유로우며 ( NOT NULL 컬럼 주의) 함수, UNION, GROUPO BY 등을 사용한 복합 뷰인 경우 INSERT, UPDATE, DELETE가 불가능하다. (조인만 사용한 복합 뷰인 경우 제한적으로 가능)
CREATE OR REPLACE VIEW v_emp
AS
SELECT a.empno
,a.ename
,a.job
,TO_CHAR(a.hiredate, 'YYYY-MM-DD') AS hiredate
FROM emp a
,deptno b
WHERE a.deptno = b.deptno
;
UPDATE v_emp
SET deptno = '10'
WHERE empno = '7369'
ORA-01779 : 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다.
deptno 컬럼은 dept 테이블의 컬럼을 사용하였으므로 empno로 접근이 불가능하다.
INSERT INTO v_emp(empno, ename, deptno) VALUES(9999, ‘TEST’, 20)
ORA-01776 : 조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.
INSERT INTO v_emp(empno, ename, deptno) VALUES (9999, ‘TEST’, 20)
deptno 컬럼을 제외하면 정상적으로 입력된다.
UPDATE v_emp
SET hiredate = SYSDATE
WHERE empno = ‘7369’
ORA-01733 : 가상 열은 사용할 수 없습니다.
함수를 사용한 컬럼(hiredate)은 INSERT, UPDATE를 할수 없다.
UPDATE v_emp
SET ename = 'TEST'
CREATE OR REPLACE VIEW v_emp
AS
SELECT empno
,ename
,job
FROM emp
WHERE deptno = '10'
UNION
ALL
SELECT empno
,ename
,job
FROM emp
WHERE deptno = '20'
;WHERE empno = ‘7369’
ORA-01732 : 뷰에 대한 데이터 조작이 부적합합니다.
UNION, GROUP BY 등을 사용한 쿼리는 INSERT, UPDATE, DELETE 를 사용할 수 없다.
참조 사이트 : https://gent.tistory.com/361
