뷰는 simple view, comples view 두종류로 나뉜다.
뷰의 정의 하기
CREATE OR REPLACE VIEW -뷰를 만들때 CREATE VIEW 만 사용해도 되나 CREATE VIEW명령어로 만들어진 뷰는 구조를 바꾸려면 뷰를 삭제하고 다시 만들어야 하지만 , CREATE OR REPLACE VIEW 는 새로운 뷰를 만들수 있을뿐만 아니라 기존의 뷰를 새로운 구조의 뷰료 변경할수 있다. 보통 CREATE OR REPLACE VIEW 를 사용
FORCE | NOFORCE - RORCE를 사용하면 기본 테이블의 존재 여부에 상관없이 뷰를 생성
WITH CHECK OPTION - 해당 뷰를 통해서 볼수 있는 범위 내에서만 UPDATE 또는 INSERT가 가능합니다.
WITH READ ONLY - 해당뷰를 통해서 SELECT 만 가능하면 INSERT/UPDATE/DELETE를 할수 없게 됩니다. 만약 이것을 생량하여 뷰를 사용하면 추가,수정,삭제(INSERT/UPDATE/DELETE)가 모두 가능
SELECT문을 사용하여 VIEW를 생성해보는 예
EX) CREATE VIEW EMP_VIEW30
AS
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP_COPY WHERE DEPTNO=30; //30번 부서에 소속된 사람의 사번,이름,월급,부서번호의 값을 가진 VIEW를 생성
EX) SELECT * FROM EMP_VIEW30; //뷰의 내용을 볼수 있음.
#만약 뷰를 생성할 권한이 없을시 시스템 계정으로 접속하여 권한을 유저에게 부여해줍니다.
EX) CONN SYSTEMID/SYSTEMPASS;
EX) GRANT CREATE VIEW TO USER;//USER에 사용자 계정
생성한 VIEW는 물리적으로 저장하지 않고 명령어로 뷰를 정의할때 기술한 쿼리문장 자체를 저장합니다. 뷰를 정의 할때 기술한 쿼리문이 궁금하다면 USER_VIEWS테이블의 TEXT컬럼값 확인
EX) DESC USER_VIEWS;
EX) SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
#뷰의 동작원리
1. 사용자가 뷰에 대해서 질의를 하면 USER_VIEWS에서 뷰에 대한 정의를 조회합니다.
2. 기본 테이블에 대한 뷰의 접근 권할을 살핌
3. 뷰에 대한 질의의 기본 테이블에대한 질의로 변환
4. 기본 테이블에 대한 질의를 통해 데이터를 검색
5. 검색된 결과를 출력
ex) SELECT * FROM 뷰네임 // 뷰네임은 쿼리문이 저장된 TEXT문의 값을 가져와 내부적으로 대처함 ;;
뷰의 내용을 수정하면 실제 테이블의 값이 추가,수정,삭제 됨.
#뷰의 사용이유
복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화할 수 있고 보안에 유리
SIMPLE VIEW와 COMPLEX VIEW 의 차이
SIMPLE VIEW |
COMPLEX VIEW |
단일테이블 |
복수테이블 |
그룹함수 불가능 |
그룹함수 사용가능 |
DISTINCT사용이 불가능 |
DISTINCT 사용이 가능 |
DML 사용가능 |
DML 사용불가능 |
DML-SELECT,UPDATE,DELETE 문
SIMPLE VIEW 경우 DML이 사용이 가능 하지만 예외로 사용못할조건이 있음
1.NOT NULL 제약 조건이 들어가 있는 경우 INSERT문 사용이 불가능
2. 뷰에 산술표현식(SAL*12)으로 정의된 가상의 컬럼이 있다면 INSERT,UPDATE 가 사용불가능
3. DISTINCT 를 포함한 경우에도 DML사용불가
4. 그룹함수나 GROUP BY절을 포함할 경우에도 DML명령 사용불가.
단순뷰컬럼에 별칭부여하가.
EX) CREATE OR REPLACE
VIEW EMP_VIEW(번호,이름,주소,취미)
AS
SELECT NO,UNAME,ADDRESSS,HOBBY
FROM TEST_02;
EX)SELECT * FROM WHERE 번호=1 ; // 제정의된 컬럼명으로 검색됨. 단 테이블에 정의되었던 컬럼명으로는 사용안됨.. 사용시 에러뜸.
EX) SELECT * FROM WHERE NO=1;//에러..
COMPLEX VIEW - 2개이상의 기본테이블에 의해 정의된 뷰
사용용도 - 자주조인되는 테이블들이 있다면 매번 사용할때마다 조인 문을 매번 작성해서 사용해야됨..
EX) CREATE VIEW EMP_VIEW_DEPT
AS
SELECT E.EMPNO,E.ENAME,E.SAL,E.DEPTNO,D.DNAME,D.LOC
FROM EMP E. DEPT D
WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO DESC;
EX) SELECT * FROM EMP_VIEW_DEPT //쿼리문이 가독성좋아짐.
DROP VIEW VIEW_NAME - //뷰를 삭제 함. (뷰만 삭제하는거라 실제 데이터는 삭제되거나 영향을 주지 않음)
-----------뷰생성에 사용되는 옵션에 대해서 기술 ---------------------
OR REPLACE - CREATE VIEW로 뷰를 생성하였는데 컬럼을 추가 하거나 뺴고 싶다면 어떻게 해야 할까요 그때 필요한게 이옵션입니다. 만약 생성된 뷰가 없다면 새로 생성할것이고 생성된 뷰가 있다면 내용이 수정됩니다.
EX) CREATE OR REPLACE VIEW EMP_VIEW30 AS SELECT EMPNO, ENAME, SAL, COMM, DEPTNO FROM EMP_COPY WHERE DEPTNO=30;
FORCE - 테이블 정보 없이 뷰를 생성할때
EX) CREATE OR REPLACE FORCE VIEW NOTABLE_VIEW AS SELECT EMPNO,ENAME,DEPTNO FROM EMPLOYEES WHERE DEPTNO=30;
WITH CHECK OPTION - 조건 칼럼값의 값을 변경하지 못하게 할때
CREATE OR REPLACE VIEW VIEW_CHK30
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30 WITH CHECK OPTION;
WITH READ ONLY - 기본테이블 값 변경을 막음 WITH CHECK OPTION처럼 조건 칼럼값만 막는게 아님
EX) CREATE OR REPLACE VIEW VIEW_READ30
AS
SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30 WITH READ ONLY;
----- 뷰를 활용하여 TOP-N 구하기 -----
ROWNUM - ROWNUM 은 테이블 구조를 생성하면서 제공되는 컬럼, 각행에 대한 일련번호
EX) CREATE OR REPLACE VIEW VIEW_HIRE
AS
SELECT EMPNO, ENAME, HIREDATE
FROM EMP
ORDER BY HIREDATE;
EX) SELECT ROWNUM,EMPNO,ENAME, HIREDATE FROM VIEW_HIRE WHERE ROWNUM<=5;
하지만 일반적으로 뷰를 따로 생성하여 TOP-N 을 구하기보다는 인라인 뷰의(안쪽쿼리) 개념으로 사용
EX) SELECT ROWNUM, EMPNO, ENAME, HIREDATE FROM (SELECT EMPNO, ENAME, HIREDATE FROM EMP ORDER BY HIREDATE) WHERE ROWNUM <=5;
