[Oracle/SQL] 10. VIEW | 인라인뷰 | DDL 활용한 베이스테이블 조작

목차

1. VIEW(뷰)
   1-1. VIEW 객체 사용 목적
   1-2. 데이터 딕셔너리
2. CREATE OR REPLACE VIEW
   2-1. VIEW에 별칭 부여(인라인뷰)
   2-2. 베이스테이블 변경사항 VIEW와 연동
   2-3. VIEW 서브쿼리 안에 연산 결과 포함 가능
3. VIEW를 통한 DML 구문 수행
   3-1. 베이스테이블 조작 가능
   3-2. 베이스테이블 조작 불가
4. VIEW 옵션
   4-1. OR REPLACE
   4-2. FORCE
   4-3. NOFORCE
   4-4. WITH CHECK OPTION
   4-5. WITH READ ONLY
5. VIEW 사용 예제

 

---

 

학습점검

✅ VIEW 객체의 사용 목적에 대해 이해할 수 있다.
✅ VIEW 객체에 대해 이해할 수 있다.
✅ CREATE VIEW 구문을 이용하여 VIEW 객체를 생성할 수 있다.
✅ 생성한 VIEW 객체에 DML 명령어를 이용하여 조작을 할 수 있다.
✅ VIEW 객체에 DML 명령어를 이용하여 조작을 할 수 없는 상황을 이해할 수 있다.
✅ VIEW 객체 생성 시 지정할 수 있는 OPTION에 대해 이해할 수 있다.

 

---

 

1. VIEW(뷰)

❗ VIEW 생성 권한 부여
GRANT CREATE VIEW TO 계정명;
VIEW 생성을 위해서는 해당 계정에 VIEW 생성 권한 부여가 선행되어야 한다.
이때 권한 부여는 SYSTEM 계정으로 실행하여야 한다.
기존에 부여하였던 CONNECT는 접속, RESOURCE는 VIEW 이외의 객체 다루는 권한에 한정돼 있다.

• SELECT 쿼리문을 저장한 객체(OBJECT)이다.
• 실질적인 데이터를 저장하고 있지 않은 논리적 테이블을 말한다. 데이터가 저장돼 있는 객체는 여전히 테이블(TABLE)이다.
• 테이블을 사용하는 것과 동일하게 쓰인다.

 

 

1-1. VIEW 객체 사용 목적

복잡한 SELECT문을 통해 얻을 결과를 OBJECT로 저장해 쓸 수 있다는 것이 장점이다. 앞서 다뤘던 인라인뷰(INLINE VIEW)에서 그 VIEW를 말한다.

• 복잡한 SELECT문을 다시 작성할 필요가 없게 된다.

테이블 안에 민감한 데이터가 존재하는 경우 접근 권한을 구분해야 할 것이다. 이때 1~5까지의 컬럼 중 민감한 5번 데이터 빼고 1~4에 한정된 데이터에 대해 VIEW를 만들어 제공하는 것이다. 따라서 데이터 숨김 처리 기능을 수행하는 것이다.

• 민감한 데이터를 숨길 수 있다.

 

1-2. 데이터 딕셔너리

✅ SELECT UV. *
　　FROM USER_VIEWS UV; -- 뷰에 대한 정보를 확인하는 데이터 딕셔너리

조회하면 VIEW 생성 시 지정한 서브쿼리가 TEXT로 저장돼 있다.

 

• 데이터 딕셔너리(Data Dictionary)란, 자원을 효율적으로 관리하기 위해 다양한 정보를 저장하는 시스템 테이블이다.
• 테이블 생성, 사용자 변경 등의 작업을 할 때 데이터베이스 서버에 의해 자동 갱신되는 테이블을 말한다.
• 사용자는 데이터 딕셔너리 내용을 직접 수정/삭제 할 수 없다. 우리가 직접 DML(INSERT-UPDATE-DELETE) 할 수 있는 대상이 아니다.

 

데이터 딕셔너리 또한 VIEW이다.

• 원본 테이블을 커스터마이징해 보여주는 원본 테이블의 가상 테이블 객체(VIEW)이다.
• 실질적인 데이터 저장 공간이 아니라 SELECT 쿼리문을 통해 데이터를 나타내고 있을 뿐이다.

 

딕셔너리 VIEW 3가지

① DBA_*** : 데이터베이스 관리자만 접근 가능한 객체 등의 정보 조회
② ALL_*** : 자신의 계정 + 권한을 부여 받은 객체의 정보 조회
③ USER_*** : 자신의 계정이 소유한 객체 등에 관한 정보 조회

 

---

 

2. CREATE OR REPLACE VIEW

✅ CREATE [OR REPLACE] VIEW 뷰이름 AS 서브쿼리

OR REPLACE 키워드는 필요에 따라 생략할 수 있다. 단, 생성 시 REPLACE 적용하면 동일한 이름의 객체(OBJECT)에 덮어쓰기가 가능하다.

 

2-1. VIEW에 별칭 부여 인라인뷰와 결과 동일

CREATE OR REPLACE VIEW V_EMP
(
  사번
, 이름
, 부서
)
AS
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, E.DEPT_CODE
 FROM EMPLOYEE E;

• VIEW 뷰이름 뒤에 (소괄호) 안에서 별칭 부여 가능하다.

• 컬럼 헤드가 사번, 이름, 부서로 부여된 모습을 확인할 수 있다.

• 이는 인라인뷰(INLINE VIEW)로 사용하던 것과 결과가 동일하다.
• 인라인뷰(INLINE VIEW)는 일반적으로 FROM절에 사용된 서브쿼리 결과 화면에 별칭을 붙인 것을 말한다.
• 즉 FROM절에 서브쿼리를 직접 작성하거나, 예시에서처럼 따로 VIEW를 생성해 FROM절에서 VIEW를 활용할 수 있는 것이다.

❗ 테이블 또는 뷰가 존재하지 않습니다
사용하던 VIEW를 삭제한 뒤 다시 조회 시도하면 위와 같은 오류 문구가 출력된다.
FROM절에는 테이블 또는 뷰, 즉 테이블뿐 아니라 VIEW도 쓰일 수 있는 구조임을 알 수 있다.

 

---

 

2-2. 베이스테이블 변경사항 VIEW와 연동

• 베이스테이블의 정보가 수정되면, VIEW도 같이 변경된다.

• 베이스테이블인 EMPLOYEE에 UPDATE가 치러졌다. 이때 EMPLOYEE 테이블과 V_RESULT_EMP 뷰를 조회하면 각각 205번 사원에 대해 바뀐 이름을 반환함을 알 수 있다.

 

---

 

2-3. VIEW 서브쿼리 안에 연산 결과 포함 가능 DECODE, EXTRACT 등

• VIEW 서브쿼리 안에 연산의 결과도 포함할 수 있다.
• 이때는 반드시 별칭을 부여해 생성해야 한다: (소괄호) 또는 컬럼 레벨에서 부여 가능

CREATE OR REPLACE VIEW V_EMP_JOB
(
  사번
, 이름
, 직급
, 성별
, 직급
, 근무년수
)
AS
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, J.JOB_NAME
　　　, DECODE(SUBSTR(E.EMP_NO, 8, 1), 2, '여', '남')
　　　, EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM E.HIRE_DATE)
 FROM EMPLOYEE E
 JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE);

컬럼레벨에서 별칭 부여

• 별칭의 경우 해당 컬럼 레벨에서 쓸 수도 있지만, 소괄호로 묶어서 부여할 수 있다. 결과는 똑같다.

 

---

 

3. VIEW를 통한 DML 구문 수행

• VIEW를 통해서도 베이스테이블 변경이 가능하다.
• VIEW 통한 DML(INSERT-UPDATE-DELETE) 구문 수행이 가능한 경우와 가능하지 않은 경우로 나누어 볼 수 있다.

3-1. 베이스테이블 조작 가능

-- VIEW 생성
CREATE OR REPLACE VIEW V_JOB
AS
SELECT J.JOB_CODE
　　　, J.JOB_NAME
 FROM JOB J;

-- VIEW에서 INSERT
INSERT
  INTO V_JOB
(
  JOB_CODE
, JOB_NAME
)
VALUES
(
  'J8'
, '인턴'
);

SELECT V.* FROM V_JOB V; -- INSERT 완료, VIEW 변경 완료
SELECT J.* FROM JOB J; -- INSERT 완료, 베이스테이블 변경 완료

-- VIEW에서 UPDATE
UPDATE
　　　　V_JOB
     SET V.JOB_NAME = '알바'
WHERE V.JOB_CODE = 'J8';

SELECT V.* FROM V_JOB V; -- UPDATE 완료, VIEW 변경 완료
SELECT J.* FROM JOB J; -- UPDATE 완료, 베이스테이블 변경 완료

-- VIEW에서 DELETE
DELETE
  FROM V_JOB V
WHERE V.JOB_CODE = 'J8';

SELECT V.* FROM V_JOB V; -- DELETE 완료, VIEW 변경 완료
SELECT J.* FROM JOB J; -- UPDATE 완료, 베이스테이블 변경 완료

 

---

 

3-2. 베이스테이블 조작 불가

❗ DML 명령어 통한 베이스테이블 조작 불가한 경우
① VIEW 정의에 포함되지 않은 컬럼을 조작하는 경우
② VIEW에 포함되지 않은 컬럼 중, 베이스테이블 컬럼이 NOT NULL 제약조건으로 지정돼 있는 경우
③ 산술표현식으로 정의된 경우
④ JOIN을 이용해 여러 테이블을 연결한 경우
⑤ DISTINCT 포함한 경우
⑥ 그룹함수나 GROUP BY절을 포함한 경우

 

-- VIEW 생성
CREATE OR REPLACE VIEW V_JOB
AS
SELECT J.JOB_CODE -- JOB_CODE 컬럼만 정의
 FROM JOB J;

-- VIEW에서 INSERT X
INSERT
  INTO V_JOB
(
  JOB_CODE
, JOB_NAME -- 뷰에 정의되지 않은 컬럼
)
VALUES
(
  'J8'
, '인턴'
);
▶JOB_NAME 부적합한 식별자 오류

-- VIEW에서 UPDATE X
UPDATE
　　　　V_JOB V
     SET V.JOB_NAME = '인턴' -- 뷰에 정의되지 않은 컬럼
WHERE V.JOB_CODE = 'J7';
▶JOB_NAME 부적합한 식별자 오류

-- VIEW에서 INSERT O
INSERT
  INTO V_JOB
(
  JOB_CODE -- 뷰에 정의된 컬럼
)
VALUES
(
  'J8'
);
▶VIEW에 정의된 컬럼만 사용했기에 INSERT 완료

-- 베이스테이블 조회 O
SELECT J.*
 FROM JOB J;
▶VIEW에서 INSERT 후, 베이스테이블을 조회하면
JOB_CODE와 JOB_NAME에 각각 J8 | (null)로 반영돼 있다.

• ① VIEW 정의에 포함되지 않은 컬럼을 조작하는 경우

 

-- VIEW 생성
CREATE OR REPLACE VIEW V_JOB
AS
SELECT J.JOB_NAME
 FROM JOB J;

-- VIEW에서 INSERT X
INSERT
  INTO V_JOB
(
  JOB_CODE -- 뷰에 정의되지 않은 컬럼
, JOB_NAME
)
VALUES
(
  'J8'
, '인턴'
);
▶JOB_CODE 부적합한 식별자 오류

-- VIEW에서 INSERT X
INSERT
  INTO V_JOB
(
  JOB_NAME -- 뷰에 정의된 컬럼
)
VALUES
(
  '인턴'
);
▶베이스테이블의 JOB_CODE 컬럼은 NULLABLE하지 않기에 오류
NULL을 ("C##EMPLOYEE"."JOB"."JOB_CODE") 안에 삽입할 수 없습니다

-- VIEW에서 UPDATE O
UPDATE
　　　　V_JOB V
     SET V.JOB_NAME = '인턴'
WHERE V.JOB_NAME = '사원';
▶VIEW에 정의된 컬럼만 사용했기에 UPDATE 완료

• ② VIEW에 포함되지 않은 컬럼 중, 베이스테이블 컬럼이 NOT NULL 제약조건으로 지정돼 있는 경우

 

-- VIEW 생성
CREATE OR REPLACE VIEW EMP_SAL
AS
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, E.SALARY
　　　, (E.SALARY + (E.SALARY * NVL(E.BONUS, 0))) * 12 연봉
 FROM EMPLOYEE E;

-- VIEW에서 INSERT X
INSERT
  INTO EMP_SAL
(
  EMP_ID
, EMP_NAME
, SALARY
, 연봉 -- virtual column not allowed here
)
VALUES
(
  '800'
, '정구름'
, 3000000
, 50000000
);
▶가상 열은 사용할 수 없습니다

-- VIEW에서 UPDATE X
UPDATE
　　　　EMP_SAL ES
     SET ES.연봉 = 80000000
WHERE ES.EMP_ID = '200';
▶가상 열은 사용할 수 없습니다

-- VIEW에서 DELETE O
DELETE
 FROM EMP_SAL ES
WHERE ES.연봉 = 124800000;
▶행을 특정할 수 있도록 존재하는 값 입력했고, DELETE 완료

• ③ 산술표현식으로 정의된 경우

 

-- VIEW 생성
CREATE OR REPLACE VIEW V_EMP
AS
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, D.DEPT_TITLE
 FROM EMPLOYEE E
 LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID);

-- VIEW에서 INSERT X
INSERT
  INTO V_EMP
(
  E.EMP_ID
, E.EMP_NAME
, D.DEPT_TITLE
)
VALUES
(
  888
, '조다습'
, '인사관리부'
);
▶조인 뷰에 의하여 하나 이상의 기본 테이블을 수정할 수 없습니다.

-- VIEW에서 UPDATE X
UPDATE
　　　　V_EMP
     SET V.DEPT_TITLE = '인사관리부'
▶키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다.

-- VIEW에서 DELETE O
DELETE
 FROM V_EMP
WHERE V.DEPT_TITLE = '회계관리부';
▶3개 행 이(가) 삭제되었습니다.

• ④ JOIN을 이용해 여러 테이블을 연결한 경우
• 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다: DEPT_CODE로 'D1'을 가진 직원은 여러 명이다. 즉 카디널리티 1 : N 관계라 할 수 있다. 따라서 DEPARTMENT 테이블에서 관련 데이터를 수정하는 작업 자체가 불가하다.

 

-- VIEW 생성
CREATE OR REPLACE VIEW V_EMP
AS
SELECT DISTINCT E.JOB_CODE -- 중복 제거하고 조회
 FROM EMPLOYEE E;

-- VIEW에서 INSERT X
INSERT
  INTO V_EMP data manipulation operation not legal on this view
(
  JOB_CODE
)
VALUES
(
  'J9'
);
▶뷰에 대한 데이터 조작이 부적합합니다

-- VIEW에서 UPDATE X
UPDATE
　　　　V_EMP V
    SET V.JOB_CODE = 'J9'
 FROM V.JOB_CODE = 'J7';
▶뷰에 대한 데이터 조작이 부적합합니다

-- VIEW에서 DELETE X
DELETE
 FROM V_EMP V
WHERE V.JOB_CODE = 'J7';
▶뷰에 대한 데이터 조작이 부적합합니다

• ⑤ DISTINCT 포함한 경우
• DISTINCT 데이터로 생성된 VIEW는 그 특성상 모든 DML(INSERT-UPDATE-DELETE) 작업이 불가하다.

 

-- VIEW 생성
CREATE OR REPLACE VIEW V_DEPT
AS
SELECT E.DEPT_CODE
　　　, SUM(E.SALARY) 합계
　　　, AVG(E.SALARY) 평균
 FROM EMPLOYEE E
GROUP BY E.DEPT_CODE;

-- VIEW에서 INSERT X
INSERT
  INTO V_DEPT
(
  DEPT_CODE
, 합계 그룹함수를 통한 연산
, 평균 그룹함수를 통한 연산
)
VALUES
(
  'D0'
, 60000000
, 4000000
);
▶가상 열은 사용할 수 없습니다

-- VIEW에서 UPDATE X
UPDATE
　　　　V_DEPT V
     SET V.DEPT_CODE = 'D1';
▶뷰에 대한 데이터 조작이 부적합합니다

-- VIEW에서 DELETE X
DELETE
 FROM V_DEPT V
WHERE V.DEPT_CODE = 'D1';
▶뷰에 대한 데이터 조작이 부적합합니다

• ⑥ 그룹함수나 GROUP BY절을 포함한 경우
• 가상 열은 사용할 수 없습니다: 그룹함수를 통해 연산된 결과인데다 그루핑되어 있어 INSERT 불가하다.
• 그루핑 해놓은 결과를 통해 UPDATE/DELETE는 불가하다. 반대로 베이스테이블인 EMPLOYEE에서 원본을 가지고 변경은 가능하다.

 

---

 

4. VIEW 옵션

4-1. OR REPLACE

• 동일한 뷰 이름이 존재하는 경우 덮어쓰기 하고, 존재하지 않으면 새로 생성하는 옵션이다.
• 즉 CREATE OR REPLACE VIEW 뷰명 AS 서브쿼리; 구문에서 OR REPLACE는 생략이 가능하다.
• 다만, CREATE VIEW로 작성하면 '기존의 객체가 이름을 사용하고 있습니다'라는 오류가 발생한다. OR REPLACE를 명시해 생성해야 계속 덮어쓸 수 있다.

 

---

 

4-2. FORCE

CREATE OR REPLACE FORCE VIEW V_EMP
AS
SELECT TCODE
　　　, TNAME
　　　, TCONTENTS
FROM TT;
▶경고: 컴파일 오류와 함께 뷰가 생성되었습니다.

• 서브쿼리로 사용된 테이블이 존재하지 않아도 VIEW를 생성한다.
• 이때 테이블을 생성하긴 하지만, 예시에서처럼 경고 메시지를 보인다.

• 생성한 VIEW를 SELECT 하게 되면, 가져올 수 있는 데이터가 없기에 오류 메시지를 나타낸다.
• 베이스테이블이 추후에 만들어질 예정이나, 그에 앞서 동작 여부를 우선 테스트하고자 할 때 쓰일 수 있다.

 

---

 

4-3. NOFORCE

CREATE OR REPLACE /*NOFORCE*/ VIEW V_EMP -- 주석해도 결과 동일
AS
SELECT TCODE
　　　, TNAME
　　　, TCONTENTS
 FROM TT;
▶테이블 또는 뷰가 존재하지 않습니다

• 서브쿼리에 쓰이는 테이블이 반드시 존재해야만 VIEW를 생성한다.
• NOFORCE 키워드를 주석한 경우에도 동일한 결과를 얻을 수 있는 기본값이다.

 

---

 

4-4. WITH CHECK OPTION

CREATE OR REPLACE VIEW V_EMP
AS
SELECT E.*
 FROM EMPLOYEE E
WHERE MANAGER_ID = '200'
 WITH CHECK OPTION;

-- VIEW에서 UPDATE X
UPDATE 
　　　　V_EMP V
     SET V.MANAGER_ID = '900'
WHERE V.MANAGER_ID = '200';
▶뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

• WHERE절 조건으로 명시된 컬럼값을 수정하지 못하게 한다.
• 예시의 경우 MANAGER_ID는 무조건 '200'이어야 한다.

 

---

 

4-5. WITH READ ONLY

CREATE OR REPLACE VIEW V_DEPT
AS
SELECT D.*
 FROM DEPARTMENT D
 WITH READ ONLY;

-- VIEW에서 DELETE X
DELETE
 FROM V_DEPT; -- 모든 행에 대해 DELETE 시도
▶읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.

• DML(INSERT-UPDATE-DELETE) 수행을 모두 불가능하게 만든다. 오직 읽는 작업만 허용한다.

 

---

 

5. VIEW 사용 예제

▶VIEW를 CREATE하기 위해서는 반드시 권한 부여(GRANT) 작업이 선행되어야 한다.

-- 사번, 이름, 직급명, 부서명, 근무지역을 조회하고,
-- 그 결과를 V_RESULT_EMP 라는 뷰를 생성해서 저장
CREATE OR REPLACE VIEW V_RESULT_EMP
AS
SELECT E.EMP_ID
　　　, E.EMP_NAME
　　　, J.JOB_NAME
　　　, D.DEPT_TITLE
　　　, L.LOCAL_NAME
 FROM EMPLOYEE E
 LEFT JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
 LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
 LEFT JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE);