SQL - VIEW

VIEW

• 흔히 가상 테이블(Virtual Table)로 불린다.
• 하나 이상의 테이블을 조회하는 SELECT문을 저장한 객체를 뜻한다.
• SELECT 문을 저장하기 떄문에 물리적으로 데이터를 따로 저장하지는 않는다.

 

 

 

VIEW의 사용목적

• 편리성 : SELECT문의 복잡도 완화
  
  • 여러 SQL문에서 사용하는 SELECT문을 VIEW로 저장해서 다른 SQL문에서 활용할 수 있다
• 보안성 : 테이블의 rows를 노출하고 싶지 않을 때
  • 테이블에는 특정인물을 제외하고는 열람할 수 없어야 하는 내용이 있다. 이럴 떄 특정 열만을 허용하는 VIEW를 제공하는 것으로 보안성을 높일 수 있다.

 

 

VIEW - 데이터 조회 절차

VIEW를 사용한 SQL 구문해석

• DATA DICTIONARY "USER_VIEWS" 에서 VIEW 정의 검색
• SQL 구문을 실행한 계정이 관련된 베이스 테이블에 접근하여 SELECT 할 수 있는 권한이 있는지 확인
• VIEW 대신 베이스 테이블을 기반으로 하는 동등한 작업으로 변환
• 베이스 테이블을 대상으로 데이터조회

 

 

생성구문

CREATE [OR REPLACE] [FORCE | NONFORCE] VIEW VIEW_NAME[ALIAS]...

AS SUBQUERY

[WITH CHECK OPTION [CONSTRAINT CONSTRAINT_NAME]]

[WITH READ ONLY [CONSTRAINT CONSTRAINT_NAME]]

 

- FORCE : 베이스 테이블이 존재하지 않아도 VIEW를 생성 가능하게 한다.

- NONFORCE : 베이스 테이블이 존재해야만 VIEW를 생성 가능하게 한다.

- WITH CHECK OPTION : VIEW를 통해서 접근 가능한 데이터(조건 일치)에 한해서만 DML 작업 허용

- WITH READ ONLY : VIEW를 통해 DML 작업을 허용하지 않음

 

 

VIEW를 생성해보자

CREATE OR REPLACE VIEW V_EMP
AS SELECT ENAME, DEPTNO
FROM EMP
WHERE DEPTNO = 30;

ORA-01031: insufficient privileges

 

권한이 없다는 오류가 뜰 것이다. 

 

그러므로 권한을 부여해줄 필요가 있다.

 

https://docs.oracle.com/cd/E17781_01/admin.112/e18585/toc.htm#XEGSG111

권한부여설정

 

 

 

권한을 부여하여 VIEW를 생성하자

• 관리자 계정에서 MYDB/admin1234 계정을 생성하자

--관리자 계정 접속(system/admin1234)

SQL> create user MYDB identified by admin1234;
SQL> grant connect, resource, dba to MYDB;

GRANT 명령어를 통해 권한을 부여한다.

 

• SCOTT.SQL을 MYDB에서 사용하기 위해 CONNECT SCOTT/TIGER 부분을 다 날려주자

Rem Copyright (c) 1990 by Oracle Corporation
Rem NAME
REM    UTLSAMPL.SQL
Rem  FUNCTION
Rem  NOTES
Rem  MODIFIED
Rem	gdudey	   06/28/95 -  Modified for desktop seed database
Rem	glumpkin   10/21/92 -  Renamed from SQLBLD.SQL
Rem	blinden   07/27/92 -  Added primary and foreign keys to EMP and DEPT
Rem	rlim	   04/29/91 -	      change char to varchar2
Rem	mmoore	   04/08/91 -	      use unlimited tablespace priv
Rem	pritto	   04/04/91 -	      change SYSDATE to 13-JUL-87
Rem   Mendels	 12/07/90 - bug 30123;add to_date calls so language independent
Rem
rem
rem $Header: utlsampl.sql 7020100.1 94/09/23 22:14:24 cli Generic<base> $ sqlbld.sql
rem
SET TERMOUT OFF
SET ECHO OFF

rem CONGDON    Invoked in RDBMS at build time.	 29-DEC-1988
rem OATES:     Created: 16-Feb-83

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER

이 부분을 삭제한 SCOTT.SQL. 파일을 따로 저장해둔다(myscott.sql)

 

• myscott.sql 파일을 MYDB 계정에서 IMPORT한 후 VIEW를 생성한다.

SQL> @C:\testwork\myscott.sql

SQL> CREATE OR REPLACE VIEW V_EMP
  2  AS SELECT ENAME, DEPTNO
  3  FROM EMP
  4  WHERE DEPTNO = 30;
  
SQL> select * from v_emp;

ENAME                DEPTNO
-------------------- ------
ALLEN                    30
WARD                     30
MARTIN                   30
BLAKE                    30
TURNER                   30
JAMES                    30

VIEW가 정상적으로 생성된 것을 확인할 수 있다.

 

 

 

VIEW의 정보를 확인하자.

SELECT COLUMN_NAME, DATA_TYPE, NULLABLE
FROM USER_TAB_COLS
WHERE TABLE_NAME = 'V_EMP';

ENAME	VARCHAR2	Y
DEPTNO	NUMBER	Y

• USER_TAB_COLS : 소유자가 가지고 있는 테이블,뷰,클러스터의 열을 보여준다.

 

 

 

JOIN한 결과로 테이블의 뷰를 생성하고, 확인해보자. 

CREATE OR REPLACE VIEW V_EMP_DEPT
AS SELECT ENAME,DNAME,JOB
FROM EMP
LEFT JOIN DEPT USING(DEPTNO);
-------------------------------------------------
SELECT * FROM V_EMP_DEPT;

ENAME  DDNAME     	JOB
MARTIN	SALES		SALESMAN
WARD	SALES		SALESMAN
ALLEN	SALES		SALESMAN
TURNER	SALES		SALESMAN
KING	ACCOUNTING	PRESIDENT
BLAKE	SALES		MANAGER
CLARK	ACCOUNTING	MANAGER
JONES	RESEARCH	MANAGER
JAMES	SALES		CLERK
MILLER	ACCOUNTING	CLERK
SMITH	RESEARCH	CLERK
FORD	RESEARCH	ANALYST

 

 

 

ALIAS를 이용하여 테이블 컬럼에 해당하는 VIEW 객체를 생성하자.

CREATE OR REPLACE VIEW V_EMP_DEPT02(M_NAME, M_DNAME, M_JOB)
AS SELECT ENAME , DNAME, JOB
FROM EMP LEFT JOIN DEPT USING(DEPTNO);

SELECT * FROM V_EMP_DEPT02;

M_NAME  M_DNAME     	M_JOB
MARTIN	SALES		SALESMAN
WARD	SALES		SALESMAN
ALLEN	SALES		SALESMAN
TURNER	SALES		SALESMAN
KING	ACCOUNTING	PRESIDENT
BLAKE	SALES		MANAGER
CLARK	ACCOUNTING	MANAGER
JONES	RESEARCH	MANAGER
JAMES	SALES		CLERK
MILLER	ACCOUNTING	CLERK
SMITH	RESEARCH	CLERK
FORD	RESEARCH	ANALYST

 

 

 

베이스 테이블에 값을 추가하고 VIEW 테이블로 확인해보자 

INSERT INTO EMP(EMPNO,ENAME,DEPTNO) VALUES(111,'111',30);

SELECT * FROM V_EMP;

ALLEN	30
WARD	30
MARTIN	30
BLAKE	30
TURNER	30
JAMES	30
111	30

 

 

 

VIEW 테이블로 추가하고 확인해보자

INSERT INTO V_EMP(EMPNO,ENAME,DEPTNO) VALUES(222,'222',20);

--EROR 발생

• 원본 테이블의 제약조건 떄문에 VIEW에서는 INSERT가 불가능하다.

 

 

 

UPDATE 확인하기

UPDATE V_EMP 
SET DEPTNO = NULL;
SELECT * FROM EMP;

7369	SMITH	CLERK		7902	80/12/17	800		20
7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	
7521	WARD	SALESMAN	7698	81/02/22	1250	500	
7566	JONES	MANAGER		7839	81/04/02	2975		20
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	
7698	BLAKE	MANAGER		7839	81/05/01	2850		
7782	CLARK	MANAGER		7839	81/06/09	2450		10
7839	KING	PRESIDENT		81/11/17	5000		10
7844	TURNER	SALESMAN	7698	81/09/08	1500	0	
7900	JAMES	CLERK		7698	81/12/03	950		
7902	FORD	ANALYST		7566	81/12/03	3000		20
7934	MILLER	CLERK		7782	82/01/23	1300		10

SELECT * FROM V_EMP;

• UPDATE 구문은 설정이 가능하다. ROLLBACK 실행
• V_EMP는 DEPTNO = 30이 NULL값으로 바뀌었기떄문에 ENAME역시 NULL값이 되어 값이 사라진다

 

 

 

WITH READ ONLY

CREATE OR REPLACE VIEW V_EMP
AS SELECT *
FROM EMP
WITH READ ONLY;

UPDATE V_EMP 
SET DEPTNO = NULL;

SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view
42399.0000 - "cannot perform a DML operation on a read-only view"

• 읽기전용이기 떄문에 실행 불가능

 

 

 

WITH CEHCK OPTION

1. BASE 테이블을 생성하자

CREATE TABLE EMP_TEST01
AS SELECT ENAME, SAL FROM EMP
WHERE 1=0;

ALTER TABLE EMP_TEST01
ADD MARRIAGE CHAR(2);

DESC EMP_TEST01;

INSERT INTO EMP_TEST01 VALUES(1,3000,'Y');
INSERT INTO EMP_TEST01 VALUES(2,3000,'Y');
INSERT INTO EMP_TEST01 VALUES(3,3000,'N');
INSERT INTO EMP_TEST01 VALUES(4,3000,'Y');
INSERT INTO EMP_TEST01 VALUES(5,3000,'N');
INSERT INTO EMP_TEST01 VALUES(6,3000,'Y');

SELECT * FROM EMP_TEST01;

2	3000	Y 
3	3000	N 
4	3000	Y 
5	3000	N 
6	3000	Y 
1	3000	Y 

 

2. WITH CHECK OPTION 확인

CREATE OR REPLACE VIEW V_EMP01
AS SELECT * FROM EMP_TEST01
WHERE MARRIAGE = 'N'
WITH CHECK OPTION; -- WHERE문의 조건에 맞는 값을 체크해서 DML로수행

SELECT * FROM V_EMP01;

INSERT INTO EMP_TEST01 VALUES(7,5000,'Y');

INSERT INTO V_EMP01 VALUES(8,5000,'N');

• VIEW 테이블 역시 WHERE 조건절이 부합한다면 INSERT가 가능하다

 

 

 

INLINE VIEW 사용

1. VIEW를 이용해서 부서별 평균 월급보다 더 많은 월급을 받는 사원과 급여를 출력하자.

--INLINE VIEW - 부서별 평균 월급보다 더 많은 월급을 받는 사원을 출력
CREATE OR REPLACE VIEW V_DEPT_SALAVG("Did","Davg")
AS SELECT NVL(DEPTNO,10),
ROUND(AVG(SAL),-1)
FROM EMP
GROUP BY DEPTNO;

SELECT * FROM V_DEPT;

SELECT ENAME,SAL
FROM EMP
JOIN V_DEPT_SALAVG ON(NVL(DEPTNO,10)="Did")
WHERE SAL > "Davg"
ORDER BY 2 DESC;

 

2. INLINE VIEW로 전환

SELECT ENAME,SAL
FROM (SELECT NVL(DEPTNO,10) AS "Did",
      ROUND(AVG(SAL),-1) As "Davg"
	  FROM EMP
	  GROUP BY DEPTNO) V_DEPT_SALAVG
JOIN EMP ON (NVL(DEPTNO,10)) = V_DEPT_SALAVG."Did"
WHERE SAL > V_DEPT_SALAVG."Davg"
ORDER BY 2 DESC;