[Oracle] SQL문법 10. VIEW

VIEW (VIEW ORACLE OBJECT)

논리적인 가상의 테이블(RESULTSET),
SELECT문을 이용해서 실제 테이블으로부터 데이터를 가져와 가상의 테이블을 만든 것으로 실질적인 데이터를 저장하고 있진 않으며 대상 테이블에 대한 SQL(서브쿼리)만 저장되어있다
따라서 여러 테이블에 대한 검색결과를 뷰 하나로 검색할 수 있으며 복잡하고 긴 쿼리문을 뷰로 정의해 접근을 단순화할 수 있다
테이블을 사용하는 것과 동일하게 DDL구문을 이용하여 생성, 수정, 삭제 등 이용이 가능하다

INLINE-VIEW : FROM 절에 서브쿼리를 사용한 개념(다중열 다중행 서브쿼리)
STORED-VIEW : 영구적으로 저장하고 사용하는 VIEW(서브쿼리를 가상테이블로 저장시켜둔 것)

VIEW는 크게 위의 두가지 종류로 나눌 수 있으며 위의 INLINE-VIEW는 이전에 서브쿼리 관련 포스트 상 다뤘던 내용으로, 현재 포스트에서는 STORED-VIEW를 다룰 예정이다

VIEW 생성 권한

[표현법]
GRANT CREATE VIEW TO 계정명;

VIEW를 생성하기 위해서는 위 권한을 부여해야한다
=> VEIW 생성 권한은 RESOURCE 안에 포함되지 않는다
권한 부여 없이 VIEW를 생성할 시 “권한이 불충분합니다”, “insufficient privileges” 에러가 발생한다

VIEW 생성

[표현법]
CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW 뷰명칭
AS (서브쿼리)
[ WITH CHECK OPTION [CONSTRAINT 제약조건] ]
[ WITH READ ONLY ];

-- 단순 뷰 예시
CREATE VIEW TEST_VIEW AS SELECT * FROM A_TABLE;

-- 복합 뷰 예시
CREATE OR REPLACE VIEW TEST_VIEW
AS SELECT *
  FROM A_TABLE
  JOIN B_TABLE ON A_COLUMN = B_COLUMN;

표현법
- CREATE : 뷰 생성
- CREATE OR REPLACE : 뷰 생성시 동일한 이름의 뷰가 있으면 현재 생성하는 뷰로 변경된다
- FORCE : 기본 테이블 유무에 관계없이 뷰를 생성한다
  => 뷰는 일반적으로 기본 테이블이 존재한다는 가정하에 쿼리문을 작성한다
  => 하지만 위 옵션을 이용해서 존재하지 않는 테이블을 대상으로 뷰를 생성할수도 있다(생성만 가능하며 뷰는 INVALID상태로 동작하지 않는다)
  => 기본값은 NOFORCE이며 생략가능하다
- WITH CHECK OPTION : 뷰에 대한 DML문을 실행시 WHERE절의 조건에 맞는 데이터만 입력되거나 변경될 수 있다
  => VIEW에 대한 갱신이나 삽입연산을 실행할때 VIEW의 정의조건을 위반하면 실행이 거부된다는 것을 의미한다
  => 튜플이 뷰에서 사라지지않도록 보호, 데이터 무결성 유지, INSERT/UPDATE로 뷰에 정의된 조건에 위배된 튜플을 생성할 수 없도록 한다
  => ex) 뷰 생성시 WHERE A_COLUMN = 30 과 같은 조건을 제시했을 때 이후 INSERT/UPDATE문으로(UPDATE TEST_VIEW SET A_COLUMN = 20 등) 뷰에 정의된 조건에 위배된 튜플을 생성할 수 없음
  => 다시말해 해당 뷰에 표시될 수 없는 값들은 넣거나 수정할 수 없다
- WITH CHECK OPTION CONSTRAINT 제약조건명 : 제약조건명을 지정하지 않아도 기능은 동일하게 작동한다
  => 제약조건명을 시스템이 SYS_XXXX와 같이 자동 생성하느냐 원하는 이름으로 생성하느냐의 차이
- WITH READ ONLY : SELECT만 가능한 뷰를 생성한다
서브 쿼리의 결과물들이 VIEW로 저장된다
컬럼에 대해 산술 연산 및 함수의 실행결과를 포함할 수 있으며 이럴 경우 꼭 컬럼 별칭을 부여해야한다
서브 쿼리의 복잡도에 따라 단순 뷰, 복합 뷰로 분류 할 수 있다
- 단순 뷰
  => 단일 테이블에 필요한 컬럼을 나열한 것으로 GROUP BY, UNION 등을 사용하지 않은 것
  => SELECT, INSERT, UPDATE, DELETE를 자유롭게 사용 가능하다
- 복합 뷰
  => 조인, 함수, GROUP BY, UNION 등을 사용하여 뷰를 생성한 것
  => SELECT는 가능하지만 상황에 따라 INSERT, UPDATE, DELETE는 가능하지 않을 수도 있다
테이블로서 저장되지는 않기때문에 사용자계정에서 테이블 폴더가 아닌, 뷰 폴더에서 해당 VIEW가 저장되어있는 것을 확인 가능하다
VIEW에는 실제 데이터들이 아닌, VIEW를 저장할 때 넣었던 서브쿼리문이 TEXT형태로 저장되어있다
=> 단순하게 서브쿼리를 텍스트로 저장시키고, VIEW를 부를 때마다 해당 텍스트들을 실행시키는 식으로 동작한다
VIEW 확인법
- 데이터 딕셔너리 : SELECT * FROM USER_VIEWS;
- 구조 확인 : DESC 뷰명칭;

VIEW 컬럼 별칭 선언

CREATE VIEW TEST_VIEW
( 컬럼명1,
  컬럼명2,
  컬럼명3... )
AS SELECT A_COLUMN, B_COLUMN, SUBSTR(C_COLUMN, 1, 4) ...
  FROM A_TABLE;

위와같이 뷰 생성시 컬럼들의 별칭을 미리 정의해둘 수도 있다
서브쿼리문의 컬럼 순서대로 별칭이 자동 부여되게 된다
SELECT 컬럼의 별칭과 일치하지 않아도 되며 순서와 개수만 맞으면 된다
컬럼에 대해 산술 연산 및 함수의 실행결과를 포함시켰을 경우 반드시 별칭부여가 필요하다
=> 별칭 미부여시 “must name this expression with a column alias” 에러 발생

VIEW 데이터 조회

[표현법]
SELECT * FROM 생성한 뷰;

-- 예시
SELECT * FROM TEST_VIEW;

SELECT *
FROM TEST_VIEW
WHERE V_COLUMN > 10;

일반 테이블과 동일하게 SELECT문으로 해당 뷰를 조회할 수 있다

VIEW 데이터 변경

-- 데이터 변경 예시
INSERT INTO TEST_VIEW VALUES('테스트', 10);

UPDATE TEST_VIEW
SET V_COLUMN = 20
WHERE V_COLUMN =10;

VIEW는 DML문으로 데이터 조작이 가능하지만 해당 VIEW가 참조중인 실제 테이블에 담겨있는 값이 변경된다
DML문으로 조작이 불가능한 경우
- 뷰에서 정의하고 있지 않은 컬럼을 조작하는 경우
- 뷰에 포함되지 않은 컬럼 중 베이스(원본 테이블)가 되는 컬럼에 NOT NULL 제약조건이 설정되어있을 때(단, DELETE는 가능하다)
- 산술 연산으로 구성된 컬럼 (= 가상 컬럼)
  => 실제 테이블에 담겨있는 컬럼이 아니기 때문
- 그룹함수, GROUP BY 절이 포함된 VIEW
  => 그룹단위가 아닌, 하나의 행 단위로만 정보 변경을 할 수 있기 때문이다
- DISTINCT를 포함하고 있는 경우
- JOIN을 통해 여러 테이블을 연결하고 있는 경우엔 되는 경우와 안되는 경우들이 존재한다

VIEW 삭제

DROP VIEW 뷰명칭;

VIEW를 삭제하더라도 실제 뷰가 참조중인 테이블에 있는 데이터는 삭제되지 않는다
=> VIEW는 다른 테이블들을 참조만 하고 있을 뿐이기 때문
=> 실제 VIEW 상에 저장되는 것은 서브쿼리문일 뿐이다

Gom's