| 1. View의 정의 • VIEW의 동작 원리 • VIEW를 사용하는 이유 • VIEW의 특징 2. VIEW생성,조회 3. View 옵션 • VIEW 삭제 • VIEW목록 조회 • OR RELPACE VIEW • FORCE • WITH CHECK OPTION • WITH READ ONLY 4. View 종류 1.단순 VIEW 1. INSERT/UPDATE/DELETE 사용 2. 별칭 부여 3. 그룹 함수를 사용 2.복합 VIEW 1. JOIN을이용해 두개 이상의 테이블을 씀 5. View 활용 |
1. Concept of View
- VIEW는 한마디로 물리적인 테이블을 근거한 논리적인 가상 테이블이라고 정의할 수 있다.
- VIEW는 기본 테이블에서 파생된 객체로서 기본 테이블에 대한 하나의 쿼리문이다
- 실제 테이블에 저장된 데이터를 VIEW를 통해 볼 수 있다
- VIEW는 물리적인 구조인 테이블과 달리 데이터 저장 공간이 없다. VIEW는 단지 쿼리문을 저장하고 잇는 객체라고 표현 할 수 있다
▶ VIEW의 동작 원리
① 사용자가 뷰에 대해서 질의를 하면 USER_VIEWS에서 VIEW에 대한 정의를 조회한다
② 기본 테이블에 대한 VIEW의 접근 권한을 살펴본다
③ VIEW에 대한 질의를 기본테이블에 대한 질의로 변환한다
④ 검색된 결과를 출력한다
▶ VIEW를 사용하는 이유
- 보안성: 직접적인 테이블 접근을 제한하기 위해서 사용된다.
- 편의성: 복잡한 질의 쉽게 만들기 위해 사용된다
▶ VIEW의 특징
- VIEW는 테이블에 대한 제한을 가지고 테이블의 일정한 부분만 보일 수 있는 가상의 테이블이다
- VIEW는 실제 자료를 갖지는 않지만, VIEW를 통해 테이블을 관리 할 수 있다.
- 하나의 테이블에 VIEW의 갯수는 제한이 없다.
- VIEW통해서 작업하는건 보통 조회할때 사용한다 (연산식에 제한이 많아서)
2. VIEW 생성과 조회
▶ 일단 VIEW는 권한부여를 해야 사용할 수 있다 SYS로 접근하여 권한을 부여한다.
GRANT CREATE VIEW TO hr;
▶ 기본 테이블 준비 (HR로 접속
: VIEW에 의해 제한적으로 접근해서 사용하는 물리적인 테이블(실질적으로 데이터를 저장하고 있음)
--예제2) VIEW 컬럼을 4개로 한정해보자 CREATE TABLE EMP01 AS SELECT EMPLOYEE_ID , FIRST_NAME , SALARY, DEPARTMENT_ID FROM EMPLOYEES; --예제2) EMPLOYEES만큼 컬럼이 있음 CREATE TABLE EMP02 AS SELECT EMPLOYEE_ID , FIRST_NAME , SALARY, DEPARTMENT_ID FROM EMPLOYEES;
▶ VIEW 생성과 조회
: 가상 테이블 View_EMP01를 통해 칼럼을 2개로 선정하고 부서번호가30인 사원만 나오도록 조건도 걸어보자
CREATE OR REPLACE VIEW View_EMP01 AS SELECT EMPLOYEE_ID, DEPARTMENT_ID FROM EMP01 WHERE DEPARTMENT_ID=30;
SELECT*FROM View_EMP01;
3. VIEW 옵션
• VIEW 삭제
: VIEW는 실체가 없는 가상 테이블이기 때문에 뷰를 삭제한다는 것은 USER_VIEWS 데이터 딕셔너리에 저장되어 있는 VIEW의 정의를 삭제하는 것을 의미한다.
DROP VIEW VIEW_EMP01;
• VIEW : 생성한 뷰 목록들 조회
: 사용자가 생성한 모든 뷰에 대한 정의가 저장되어 있다
SELECT VIEW_NAME, TEXT FROM USER_VIEWS;
• OR RELPACE VIEW : 뷰 생성,수정
: 존재하지 않은 VIEW이면 새로운 VIEW를 생성하고 기존에 존재하는 VIEW이면 그 내용을 변경한다.
CREATE OR REPLACE VIEW VIEW_EMP01 AS /*SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID > */ SELECT FIRST_NAME, SALARY FROM EMP01 /*WHERE DEPARTMENT_ID=30; > */ WHERE SALARY=2500;
• FORCE : 기본테이블 없이 뷰생성
: FORCE 을 VIEW앞에 추가하면 기본 테이블의 존재 여부에 상관없이 뷰를 생성한다
CREATE OR REPLACE FORCE VIEW VIEW_NOTABLE (사원번호,사원명,급여,부서번호) AS SELECT EMPLOYEE_ID , FIRST_NAME , SALARY, DEPARTMENT_ID FROM EMP01;
SELECT*FROM VIEW_NOTABLE;
• WITH CHECK OPTION : 지정한 컬럼값을 변경하지 못하게 함
: WITH CHECK OPTION 옵션은 뷰 생성시 조건으로 지정한 칼럼 값을 변경하지 못하도록 한다.
CREATE OR REPLACE VIEW VIEW_CHK AS SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID FROM EMP01 WHERE DEPARTMENT_ID=20 WITH CHECK OPTION;
UPDATE VIEW_CHK SET DEPARTMENT_ID =10 --급여가 5000이상인 사원을 10번 부서로 이동하는 쿼리문 WHERE SALARY>=5000; --부서번호에 옵션을지정하였으므로 이 뷰를 통해서는 부서번호를 변경할수 없다
• WITH READ ONLY : 뷰를 이용해서 칼럼값 바꾸지 못하게 함
: 뷰를 통해서는 기본 테이블의 컬럼의 내용을 절대 변경할 수 없도록 한다.
CREATE OR REPLACE VIEW VIEW_READ AS SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID FROM EMP01 WHERE DEPARTMENT_ID=30 WITH READ ONLY;
UPDATE VIEW_READ SET DEPARTMENT_ID=1000; -- 오류발생
4. VIEW의 종류
| 단순 VIEW | 복합 VIEW |
| 하나의 TABLE로 생성 | 여러 개의 TABLE로 생성 |
| 그룹 함수의 사용이 불가능 | 그룹 함수의 사용이 가능 |
| DISTINCT 사용이 불가능 | DISTINCT 사용이 가능 |
| DML(INSERT/UPDATE/DELETE) 사용 가능 |
DML(INSERT/UPDATE/DELETE) 사용 불가능
|
※ 단순 뷰에 DML 명령어로 조작 불가능한 경우
- VIEW정의에 포함되지 않은 칼럼 중에 기본 테이블의 칼럼이 NOT NULL 제약 조건이 지정되어 있는 경우 INSERT문이 사용 불가능하다
- SALARY*12 와 같이 산술 표현식으로 정의된 가상 칼럼이 뷰에 정의되면 INSERT나 UPDATE가 불가능하다
- DISTINCT을 포함한 경우에도 DML 명령을 사용할 수 없다.
- 그룹 함수나 GROUP BY절을 포함한 경우에도 DML명령을 사용할 수 없다
1) VIEW에 대한 데이터 조작
: 단순 VIEW에서는 INSERT/UPDATE/DELETE문 사용 가능
CREATE OR REPLACE VIEW View_EMP01 AS SELECT EMPLOYEE_ID , FIRST_NAME , SALARY, DEPARTMENT_ID FROM EMP01 WHERE DEPARTMENT_ID=30;
SELECT * FROM VIEW_EMP01;
INSERT INTO VIEW_EMP01 VALUES(250,'ANGER',7000,30); SELECT*FROM VIEW_EMP01;
단순 VIEW를 대상으로 실행한 DML 명령문의 결과는 뷰를 정의할 때 사용한 기본 테이블을 적용한다
2) VIEW의 컬럼에 별칭 부여하기
CREATE OR REPLACE VIEW VIEW_EMP02 AS SELECT EMPLOYEE_ID 사원번호, FIRST_NAME 사원명, SALARY 급여, DEPARTMENT_ID 부서번호 FROM EMPLOYEES; CREATE OR REPLACE VIEW VIEW_EMP02 (사원번호,사원명,급여,부서번호) AS SELECT EMPLOYEE_ID , FIRST_NAME , SALARY, DEPARTMENT_ID FROM EMP01;
SELECT * FROM VIEW_EMP02 WHERE 부서번호 =10; --> EMPLOYEE_ID는 못쓴다
칼럼의 별칭을 사용해서 뷰를 생성하면 VIEW_EMP02의 칼럼 이름만 별칭으로 데이터 구조에 반영되서
EMP01 테이블의 원래 칼럼 이름에는 전혀 영향을 주지 못한다.
3) 그룹 함수를 사용한 단순 VIEW
: 함수를 사용하여 얻은 컬럼은 반드시 이름을 설정해 주어야 한다.
CREATE OR REPLACE VIEW VIEW_SALARY AS SELECT DEPARTMENT_ID , SUM(SALARY) AS "SalarySum",TRUNC(AVG(SALARY)) "SalaryAvg" FROM EMP01 GROUP BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID;
SELECT * FROM VIEW_SALARY;
4) 복합 VIEW (JOIN)
: 두 개 이상의 기본 테이블(JOIN)에 의해 정의된 뷰
CREATE VIEW VIEW_EMP_DEPT AS SELECT E.EMPLOYEE_ID,E. FIRST_NAME, E.SALARY, E.DEPARTMENT_ID, D.DEPARTMENT_NAME FROM EMPLOYEES E INNER JOIN DEPARTMENTS D --> USE INNER JOIN ON E.DEPARTMENT_ID=D.DEPARTMENT_ID ORDER BY DEPARTMENT_ID DESC;
SELECT * FROM VIEW_EMP_DEPT;
5. VIEW 활용
ROWNUM 칼럼 이용
: 오라클에서 지원하는 가상컬럼(Pseudo Column)으로 쿼리의 결과에 가상으로 행번호를 1부터 부여한다.
주로 페이징처리, TOP-N QUERY에 사용한다
ROWNUM 주의사항
ROWNUM은 첫번째 값이 할당 된 이후부터 증가한다.
그러므로 다음과 같은 쿼리는 아무것도 반환하지 않는다
SELECT * FROM EMP WHERE ROWNUM > 1; --(X) --ROWNUM이 1보다 큰값을 추출하고싶다면 SELECT * FROM (SELECT E.*, ROWNUM R FROM EMP E) WHERE R > 1; --(O) /* 이렇게서브쿼리에서 별칭을 붙여서 가상컬럼인 ROWNUM을 진짜 컬럼으로 만들어줬다. 테이블에서 조건을 판별하기위해 행을 가져올때마다 ROWNUM을 부여하지 않고 서브쿼리가 만들어질때 고정된 값을 가지게된다. 그리고 R이라는 별칭을 부여해서 바깥에서 조건을 걸 수 있게 된다. */
SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, HIRE_DATE FROM EMPLOYEES;
