ㅁ < VIEW 뷰 >
- 쿼리문(SELECT문)을 저장해둘 수 있는 데이터베이스 객체.
- 내가 자주 쓰는 쿼리문을 저장해두는 것.
- 임시 테이블처럼 활용할 수 있음.
실제 데이터가 존재하는 물리적 테이블은 아니고, 논리적인 테이블이다.
- VIEW를 통해 DML문 수행시 많은 제약이 있으므로 조회용으로만 쓰는 것을 권장함.
ㅁ 이전까지는 다 작성해야 했다.
ㅇ '한국'에서 근무하는 사원의 사번, 이름, 부서명, 급여, 근무국가명 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION L ON LOCATION_ID = LOCAL_CODE
JOIN NATIONAL N ON L.NATIONAL_CODE = L.NATIONAL_CODE
WHERE NATIONAL_NAME = '한국';
ㅇ '러시아'에서 근무하는 사원의 사번, 이름, 부서명, 급여, 근무국가명 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION L ON LOCATION_ID = LOCAL_CODE
JOIN NATIONAL N ON L.NATIONAL_CODE = L.NATIONAL_CODE
WHERE NATIONAL_NAME = '러시아';
ㅇ '일본'에서 근무하는 사원의 사번, 이름, 부서명, 급여, 근무국가명 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION L ON LOCATION_ID = LOCAL_CODE
JOIN NATIONAL N ON L.NATIONAL_CODE = L.NATIONAL_CODE
WHERE NATIONAL_NAME = '일본';
ㅁ VIEW 생성
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰명 AS 서브쿼리
[WITH CHECK OPTION]
[WITH READ ONLY];
- 테이블이나 시퀀스는 ALTER로 수정하지만, 뷰는 ALTER로 수정하지 않음.
그냥 CREATE OR REPLACE로 덮어씌운다.
- OR REPLACE : 뷰명이 없으면 만들고, 있으면 교체한다.
- FORCE : 서브쿼리에 기술된 테이블이 (아직) 없는 테이블이어도 뷰가 생성되도록 하는 옵션.
NO FORCE : 서브쿼리에 기술된 테이블이 존재해야만 뷰를 생성시키는 옵션. (기본값)
- WITH CHECK OPTION : VIEW를 통해서 DML시 서브쿼리에 기술된 조건에 부합한 값으로만 DML이 가능하도록 하는 옵션. 없으면 아무값으로나 DML다 가능함.
- WITH READ ONLY : 뷰를 조회용으로만 설정하는 옵션.
ㅁ 뷰 사용
ㅇ BR계정) 뷰 생성 시도1
CREATE OR REPLACE VIEW VW_EMPLOYEE
AS
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
JOIN LOCATION L ON LOCATION_ID = LOCAL_CODE
JOIN NATIONAL N ON L.NATIONAL_CODE = L.NATIONAL_CODE;
- 오류. "insufficient privileges". 시퀀스나 뷰를 생성하려면 생성권한이 필요함.
ㅇ 관리자계정) BR계정에게 CREATE VIEW 권한 부여
GRANT CREATE VIEW TO BR;
ㅇ BR계정) 뷰 생성 시도2 & 뷰 조회
- 뷰 VW_EMPLOYEE 생성 완료.
- 뷰 조회하면 저 서브쿼리의 결과가 조회됨.
- 실제 데이터를 담고있는 물리적인 테이블은 아님. 데이터가 없음.
저 서브쿼리 결과를 보여주는 논리적인 테이블일 뿐.
- EMPLOYEE, JOB처럼 실제 데이터가 담겨있는 테이블을 베이스 테이블이라고도 함.
ㅇ 간편하게 조회하기
ㅁ 참고) USER_VIEWS
- 현재 계정이 소유하고 있는 뷰에 대한 정보를 표현하는 테이블
- Data Dictionary
- 뷰는 쿼리문을 텍스트로 가지고 있는 객체. (서브쿼리의 결과값을 가지고 있는게 아님)
ㅁ 뷰 사용2
ㅇ 사번, 이름, 직급명, 성별(남|여), 근무년수를 조회할 수 있는 SELECT문을 뷰(VW_EMPJOB)로 정의.
CREATE OR REPLACE VIEW VW_EMPJOB
AS
SELECT EMP_ID, EMP_NAME, JOB_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여'),
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE);
- 근무년수 : EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
- 오류. "must name this expression with a column alias"
- 뷰 정의시 서브쿼리에 산술식, 함수식이 있을 경우 반드시 별칭을 부여해야 한다.
CREATE OR REPLACE VIEW VW_EMPJOB
AS
SELECT EMP_ID, EMP_NAME, JOB_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여') "성별",
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) "근무년수"
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE);
- 성공적으로 생성된다.
ㅇ 별칭을 이렇게도 줄 수 있다.
CREATE OR REPLACE VIEW VW_EMPJOB(사번, 사원명, 직급명, 성별, 근무년수)
AS
SELECT EMP_ID, EMP_NAME, JOB_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여'),
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
FROM EMPLOYEE
JOIN JOB USING (JOB_CODE);
- OR REPLACE 옵션 덕에 오류 안나고 갱신됨.
- 단, 모든 별칭을 다 써야 한다. 모든 컬럼에 다 alias 부여.
ㅁ 뷰를 통해서 DML(INSERT, UPDATE, DELETE) 사용하기
- 뷰에 DML을 사용하면 뷰가 아닌 실제 베이스 테이블에 반영이 된다.
ㅇ 뷰 생성
- 실제 데이터는 베이스 테이블인 JOB 테이블에 있다. 뷰는 논리적인 테이블이라 데이터가 없다.
ㅇ 뷰에 DELETE
- 뷰에 DELETE를 사용했는데 베이스 테이블인 JOB에서도 삭제가 된다.
ㅁ 뷰에 DML로 조작이 불가능한 경우가 더 많다. (그래서 조회용으로만 쓰는게 좋다)
- 뷰에 정의되어 있지 않은 컬럼을 조작하려하는 경우
- 뷰에 정의되어 있지 않은 컬럼 중에 베이스테이블 상에 NOT NULL 제약조건이 지정되어 있는 경우
- 산술연산식 또는 함수식으로 정의되어 있는 경우
- 그룹함수나 GROUP BY절이 포함된 경우
- DISTINCT 구문이 포함된 경우
- JOIN을 이용해서 여러 테이블을 연결시켜놓은 경우
'클라우드 활용 자바개발자 양성과정 > 02. 데이터베이스 활용' 카테고리의 다른 글
13. OBJECT(TRIGGER) (0) | 2024.07.25 |
---|---|
12. PL/SQL (0) | 2024.07.25 |
10. OBJECT(SEQUENCE) (6) | 2024.07.24 |
09. TCL(COMMIT, ROLLBACK + SAVEPOINT) (2) | 2024.07.24 |
08_DDL(ALTER, DROP) + DCL(GRANT, REVOKE) (0) | 2024.07.23 |