DataBase+SQL

뷰(VIEW)

campanula 2023. 6. 22. 17:33

1)뷰의 개념

  • 물리적인 테이블을 근거한 논리적인 가상 테이블(실제로 데이터를 가지고 있는 테이블X)                                               =>데이터 저장 공간이 없다. 단지 쿼리문을 저장하고 있는 객체
  • 기본 테이블에서 파생된 객체로 기본 테이블에 대한 하나의 쿼리문
  • 실제 테이블에 저장된 데이터를 뷰를 통해서 볼 수 있도록 한다.

 

--ORA-01031: 권한이 불충분합니다
GRANT CREATE VIEW TO hr; --PDB_SYS로 접속해서 권한 부여, GRANT=DCL

--Grant을(를) 성공했습니다.
--CREATE를 주면 생성뿐만 아니라 조회하고, 삭제할 수 있는 권한도 부여
--각 각 주고 싶으면 SELECT 이용
--작업 끝나면 PDB_SYS 접속 해제(최고 권한자 접속 해제)
--PDB(데이터베이스)_SYS(사용자이름)

 

 

뷰의 사용 목적: 직접적인 테이블 접근을 제한(보안성), 복잡한 질의를 쉽게 만듦(편의성)

뷰의 특징: 테이블에 대한 제한을 가지고 테이블의 일정한 부분만 보일 수 있는 가상의 테이블, 실제 자료를 갖지는 않지만 테이블을 관리 가능, 하나의 테이블에 뷰의 개수는 제한이 없다.

 

2) 뷰 생성과 조회

테이블 생성과 같이 CREATE문 사용

기본 테이블: 뷰에 의해 제한적으로 겁근해서 사용하는 실질적으로 데이터를 저장하고 있는 물리적인 테이블.

 

3) 뷰 생성

테이블을 생성할 때와 유사하게 CREATE VIEW 명령어로 생성

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW VIEW_NAME
[(ALIAS,ALIAS,ALIAS,...)]
AS SUBQUERY
[WITH CHECK OPTION]
[WITH READ ONLY];		--해당 뷰를 통해서는 SELECT만 가능하며
				--INSERT/UPDATE/DELETE 불가능
                
 CREATE VIEW 뷰이름
 AS
 SELECT ~
 
 --뷰 확인
 SELECT 컬럼 FROM 뷰이름;

 

OR REPLACE VIEW: 새로운 뷰를 만들 수 있을 뿐만 아니라 기존에 뷰가 존재하더라도 삭제하지 않고 새로운 구조의 뷰로.

 

WITH CHECK OPTION: 해당 뷰를 통해서 볼 수 있는 범위 내에서만 UPDATE OR INSERT가 가능

 

1] 뷰에 관련된 데이터 딕셔너리

데이터 딕셔너리 USER_VIEWS에 사용자가 생성한 모든 뷰에 대한 정의가 저장

VIEW_NAME: 뷰의 이름, TEXT:칼럼과 뷰를 작성할 때 기술한 서브 쿼리문

SELECT VIEW_NAME, TEXT
FROM USER_VIEWS;

 

2] 뷰의 동작 원리

  1. 사용자가 뷰에 대해서 질의를 하면 USER_VIEWS에서 뷰에 대한 정의를 조회
  2. 기본 테이블에 대한 뷰의 접근 권한을 살핌.
  3. 뷰에 대한 질의를 기본 테이블에 대한 질의로 변환
  4. 기본 테이블에 대한 질의를 통해 데이터를 검색
  5. 검색된 결과 출력

4) 뷰의 종류(뷰를 정의하기 위해서 사용되는 기본 테이블의 수에 따라 단순 뷰와 복합 뷰로 나뉜다)

단순 뷰 복합 뷰
하나의 테이블로 생성 여러 개의 테이블로 생성
그룹 함수의 사용이 불가능 그룹 함수의 사용이 가능
DISTINCT 사용이 불가능 DISTINCT 사용이 가능
DML(INSERT/UPDATE/DELETE) 사용 가능 DML(INSERT/UPDATE/DELETE) 사용 불가능

 

1]단순 뷰에 대한 데이터 조작 :  INSERT, UPDATE, DELETE 사용 가능

--단순뷰를 기준으로 INSERT/UPDATE/DELETE 수행이 가능
--이때 뷰를 기준으로 입력하면 실제 테이블(기본 테이블)에 데이터가 저장
--뷰에 INSERT하면 기본 테이블에도 삽입이 된다.
--기본테이블에 INSERT가 가능한 조건이여야 뷰에 INSERT가능
INSERT INTO VIEW_EMP01
VALUES(250, 'ANGEL', 7000, 30);

 

2] 단순 뷰의 칼럼에 별칭 부여

--사원번호, 사원명, 급여, 부서번호로 구성된 뷰를 작성하되 기본 테이블은 EMP01로 하고 칼럼명은 한글화
CREATE OR REPLACE VIEW VIEW_EMP02 --기존에 뷰가 존재하면 구조 변경
AS
SELECT EMPLOYEE_ID 사원번호, FIRST_NAME 사원명, SALARY 급여, DEPARTMENT_ID 부서번호 --별칭이 컬럼명이됨
FROM EMP01;

-- 별칭을 부여한 뷰는 실제 컬럼명으로 조회할 수 없다.
--ORA-00904: "DEPARTMENT_ID": 부적합한 식별자
--부서번호로 조회해야 한다.
SELECT * 
FROM VIEW_EMP02
WHERE DEPARTMENT_ID =10;

SELECT * 
FROM VIEW_EMP02
WHERE 부서번호 =10;

 

--부서별 급여의 합, 급여의 평균을 조회할 수 있는 VIEW_SALARY 뷰 생성
CREATE OR REPLACE VIEW VIEW_SALARY
AS
SELECT DEPARTMENT_ID, SUM(SALARY) AS "SALARYSUM", TRUNC(AVG(SALARY)) AS "SALARYAVG" --함수가 선언되어 있는 컬럼에 대해서는 별칭이 무조건 필요!
FROM EMP01
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;

3] 단 순 뷰에 DML 명령어로 조작 불가능한 경우

  • 뷰 정의에 포함되지 않은 칼럼 중에 기본 테이블의 칼럼이 NOT NULL 제약 조건이 지정되어 있는 경우 INSERT 사용 불가능
  • SALARY*12와 같이 산술 표현식으로 정의된 가상 칼럼이 뷰에 정의되면 INSERT나 UPDATE 불가능
  • DISTINCT을 포함한 경우에도 DML명령 사용 불가능
  • 그룹함수나 GROUP BY 절을 포함한 경우에도 DML(INSERT, UPDATE, DELETE,SELECT) 명령 사용X

4] 복합 뷰: 2개 이상의 기본 테이블에 의해 정의된 뷰

--사원번호, 사원이름,급여, 부서번호, 부서명 조회할 수 있는 VIEW_EMP_DEPT 생성
CREATE VIEW VIEW_EMP_DEPT
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, E.DEPARTMENT_ID, DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY DEPARTMENT_ID DESC;

SELECT * FROM VIEW_EMP_DEPT;

--ON대신 USING으로 변경
--ORA-25154: USING 절의 열 부분은 식별자를 가질 수 없음
CREATE OR REPLACE VIEW VIEW_EMP_DEPT
AS
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, DEPARTMENT_ID, D.DEPARTMENT_NAME
FROM EMPLOYEES E INNER JOIN DEPARTMENTS D
USING (DEPARTMENT_ID)
ORDER BY DEPARTMENT_ID DESC;

 

6) 뷰 수정을 위한 OR REPLACE 옵션

CREATE VIEW 대신 CREATE OR REPLACE VIEW를 사용하면, 존재하지 않은 뷰이면 새로운 뷰를 생성하고 기존에 존재하는 뷰이면 내용을 변경

 

7) 기본 테이블 없이 뷰를 생성하기 위한 FORCE 옵션: 기본 테이블이 존재하지 않더라도 뷰를 생성하려면 FORCE옵션 추가

--경고: 컴파일 오류와 함께 뷰가 생성되었습니다.
CREATE OR REPLACE FORCE VIEW VIEW_NOTABLE
AS
SELECT EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID
FROM EMP15		--존재하지 않는 테이블
WHERE EMPLOYEE_ID = 10;

 

8)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;
-------------------------------------------------
--SQL 오류: ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다
UPDATE VIEW_CHK
SET DEPARTMENT_ID =10   --급여가 5000니상인 사원을 10번 부서로 이동하는 쿼리문
WHERE SALARY>=5000;     --부서 번호에 옵션을 지정하였으므로 이 뷰를 통해서는 부서번호 변경 불가능

 

9)WITH READ ONLY: 데이터 자체를 읽기 전용으로 VIEW를 통해 변경 불가능

CREATE OR REPLACE VIEW VIEW_READ
AS
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY,DEPARTMENT_ID
FROM EMP01
WHERE DEPARTMENT_ID = 30 WITH READ ONLY;

--SQL 오류: ORA-42399: 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
UPDATE VIEW_READ
SET SALARY =1000;

-WITH CHECK OPTION:조건에 사용한 컬럼의 값을 수정하지 못하게 함

-WITH READ ONLY: 기본 테이블의 모두를 수정 못하게 함.

 

10)뷰 활용하기

ROWNUM칼럼 : 오라클에서 내부적으로 부여, INSERT문에 의해 입력한 순서에 따라 1 씩 증가되며 값이 지정,레코드가 들어올때 카운팅(내부 컬럼: 행의 번호)

SELECT ROWNUM, EMPLOYEE_ID,FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE DESC;
--입사일을 기준으로 내림차순으로 정렬을 하였는데도 해당 행의 ROWNUM은 바뀌지 않는다.
--데이터가 입력된 시점에서 결정되면 다시는 값이 바뀌지 않기 때문이다
--새로운 테이블에 입사일을 기준으로 내림차순으로 정렬한 쿼리문의 결과를 저장하면 
--최근에 입사한 순으로 ROWNUM 칼럼 값이 1부터 부여
CREATE OR REPLACE VIEW VIEW_HIRE
AS
SELECT EMPLOYEE_ID,FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE DESC;

SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM VIEW_HIRE;

--기본 ROWNUM을 첫번째부터 구할 때 사용 , N번째 부터 N번째까지 구하라는 것일 때 사용X
SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM VIEW_HIRE
WHERE ROWNUM<=5;

 

 

SELECT 컬럼
FROM (SELECT~):인라인 뷰 SELECT의 SELECT

SELECT 컬럼, (SELECT 컬럼 FROM 테이블
			 WHERE 외부테이블.컬럼 = 내부테이블.컬럼)
FROM 테이블: 스칼라

 

1]인라인 뷰로 TOP-N 구하기

--인라인 뷰로 TOP-N
SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM (SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE --함수X
        FROM EMPLOYEES
        ORDER BY HIRE_DATE DESC)
WHERE ROWNUM <=5;

--서브 쿼리에 이름을 붙여주고 인라인 뷰로 사용 시 서브쿼리의 이름으로 FROM 절에 기술 가능
--같은 서브쿼리가 여러번 사용될 경우 중복 작성을 피할 수 있고 실행속도도 빨라진다는 장점이 있음
WITH TOPN_HIRE
AS
(SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY HIRE_DATE DESC)

SELECT ROWNUM, EMPLOYEE_ID,FIRST_NAME, HIRE_DATE
FROM TOPN_HIRE;

--EMPLOYEES 테이블과 DEPARTMENTS 테이블을 조회하여 부서 번호와 부서별 최대 급여 및 부서명 출력
SELECT E.DEPARTMENT_ID, D.DEPARTMENT_NAME, E.SALARY
FROM ( SELECT DEPARTMENT_ID, MAX(SALARY) SALARY
        FROM EMPLOYEES 
        GROUP BY DEPARTMENT_ID
        ) E INNER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
--EMPLOYEES 테이블에서 급여가 자신이 속한 부서의 평균 급여보다 많이 받는
--사원의 부서번호, 이름, 급여를 출력하는 SELECT
SELECT E.DEPARTMENT_ID, E.FIRST_NAME, E.SALARY
FROM EMPLOYEES E INNER JOIN (SELECT DEPARTMENT_ID, AVG(SALARY) D_SALARY
                            FROM EMPLOYEES
                            GROUP BY DEPARTMENT_ID) D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
WHERE E.SALARY > D.D_SALARY
ORDER BY E.DEPARTMENT_ID;

2] 순위 관련 함수들

  • RANK()  : 중복 순위 개수만큼 다음 순위 값을 증가 시킴
  • 형식 : RANK() OVER(ORDER BY 컬럼명 (ASC|DESC)) (AS 별칭)
  • DENSE_RANK() : 중복 순위가 존재해도 순차적으로 다음 순위 값을 표시함 
  • ROW_NUMBER() : 중복값에 관계없이 SEQUENCE(순차적인 순위 값) 값을 반환
--RANK() OVER
SELECT SALARY_RANK, FIRST_NAME, SALARY
FROM (SELECT FIRST_NAME, SALARY,
            RANK() OVER(ORDER BY SALARY DESC) AS SALARY_RANK
        FROM EMPLOYEES
        ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;

--사원테이블에서 80번 부서에 소속된 사원 중에서 입사년도가 가장 최근에 입사한 순부터 
--사원번호, 사원명, 입사일자, 순위를 부여하여 출력
SELECT EMPLOYEE_ID AS 사원번호, FIRST_NAME AS 사원명, HIRE_DATE AS 입사일자,
        RANK() OVER(ORDER BY HIRE_DATE DESC) AS 순위
FROM EMPLOYEES
WHERE DEPARTMENT_ID =80;

--DENSE_RANK() OVER
SELECT 순위, FIRST_NAME, SALARY
FROM (SELECT FIRST_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 순위
FROM EMPLOYEES
ORDER BY SALARY DESC)
WHERE ROWNUM <=5;

--사원 테이블에서 80번 부서에 소속된 사원 중에서 급여를 가장 많이 받는 순으로
--사원번호, 사원명,급여, 순위 출력
--급여가 가장 높은 순
SELECT EMPLOYEE_ID AS 사원번호, FIRST_NAME AS 사원명, SALARY AS 급여,
        DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 순위 --DENSE_는 중복되었을 때 다음 순서를 그대로 준다.
FROM EMPLOYEES
WHERE DEPARTMENT_ID =80;