오라클에서 뷰(VIEW)는 테이블과 흡사한 오브젝트이다. 뷰는 실제로 데이터를 저장하고 있지는 않지만 DML 작업이 가능한 가상의 테이블이라고 생각하면 된다.
|
 |
- OR REPLACE : 해당 구문을 사용하면 뷰를 수정할 때 DROP 없이 수정이 가능하다.
- FORCE : 뷰를 생성할 때 쿼리문의 테이블, 컬럼, 함수 등이 존재하지 않아도 생성이 가능하다.
- NORORCE : 뷰를 생성할 때 쿼리문의 테이블, 컬럼 함수 등이 존재하지 않으면 생성되지 않는다.
- column_aliases : SELECT 컬럼의 별칭을 미리 정의할 수 있다.
- WITH READ ONLY : SELECT 만 가능하다. (INSERT, UPDATE, DELETE 불가능)
- WITH 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
;
복합 뷰는 조인, 함수, GROUP BY, UNION 등을 사용하여 뷰를 생성한다.
함수 등을 사용할 경우 컬럼 별칭은 꼭 부여해야 한다. (ex, AS hiredate)
복합 뷰는 SELECT는 가능하지만 INSERT, UPDATE, DELETE는 상황에 따라서 가능하지 않을 수도 있다.
※ 단순 뷰와 복합 뷰는 동일한 뷰(VIEW)를 의미하며, 기능을 설명하기 위하여 분류를 한 것이다.
뷰 컬럼 코멘트 추가
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')
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 OPTION 옵션 사용
--제약조건명을 지정하지 않는 경우
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-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
deptno = '20'은 WHERE절의 조건에 부합하지 않아서 INSERT시 오류가 발생한다.
WITH CHECK OPTION [CONSTRAINT 제약조건명]
제약조건명을 지정하지 않아도 기능은 동일하게 작동하며, 제약조건이 생성될때 원하는 이름으로 생성되느냐 시스템이 자동으로 이름을 부여하느냐의 차이이다. 지정하지 않으면 SYS_XXXXXXX로 생성된다.
DML 사용 시 유의사항 (INSERT, UPDATE, DELETE)
|
단순 뷰인 경우 INSERT, UPDATE, DELETE가 자유로우며 (NOT NULL 컬럼 주의) |
CREATE OR REPLACE VIEW v_emp
AS
SELECT a.empno
, a.ename
, a.job
, TO_CHAR(a.hiredate, 'YYYY-MM-DD') AS hiredate
, b.deptno
FROM emp a
, dept 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) VALUES(9999, 'TEST')
deptno 컬럼을 제외하면 정상적으로 입력 된다.
UPDATE v_emp
SET hiredate = SYSDATE
WHERE empno = '7369'
ORA-01733: 가상 열은 사용할 수 없습니다
함수를 사용한 컬럼(hiredaet)은 INSERT, UPDATE를 할 수 없다.
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'
;
UPDATE v_emp
SET ename = 'TEST'
WHERE empno = '7369'
ORA-01732: 뷰에 대한 데이터 조작이 부적합합니다
UNION, GROUP BY 등을 사용한 쿼리는 INSERT, UPDATE, DELETE를 사용할 수 없다.