[Oracle] 오라클 VIEW

VIEW

• 보여만 주는 상태
• 테이블을 새롭게 생성하는 것이 아니라 기존의 테이블에서 필요한 데이터를 모아서 관리하는 것
• 필수 요건 : 테이블
• 메모리에 저장되는 과정이 아니고 가상으로 저장된다.

VIEW의 장점

• 가상으로 저장되기 때문에 보안이 좋다. (해킹불가)
• SQL문장을 단순화시킬 수 있다는 장점이 있다.

VIEW의 특징

• ALTER를 사용할 수 없다.
• 삭제를 할 때는 반드시 DROP VIEW view명칭 을 사용해야한다.
• 저장이나 수정이 가능한데 View에서 수정이나 삭제가 되는 것이 아니라 참조테이블에서 변경된다.
• READ ONLY이기 때문에 DML 사용이 불가하다.
• SELECT만 사용해서 테이블만 볼 수 있게 만든다.
• 같은 이름의 View는 사용할 수 없다.

VIEW의 형식

CREATE [OR REPLACE] VIEW view명칭
AS SELECT ~

• AS 밑의 문장이 가상데이터에 저장되는 것이 View임

VIEW 종류

1. 단순뷰

• 한 개의 테이블을 참조하는 것

2. 복합뷰

• 여러개의 테이블을 참조하는 것
• JOIN 이나 SubQuery가 들어가면 복합뷰임

3. 인라인뷰 (TOP-N)

• FROM SELECT~ 로 시작하는 뷰들

VIEW 실습예제

단순뷰 만들기

CREATE VIEW dept_view
AS
SELECT * FROM dept;

단순뷰에 INSERT하기

INSERT INTO dept_view VALUES(50,'영업부','서울');

• View에 데이터 추가하면 참조하고 있는 테이블에 데이터가 추가되는 것이기 때문에 주의해야함

READ ONLY 단순뷰

• READ ONLY옵션을 추가하면 데이터 수정/추가 불가하다.

  CREATE VIEW dept_view
  AS
  SELECT * FROM dept WITH READ ONLY;
  

INSERT INTO dept_view VALUES(50,'영업부','서울');
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view

복합뷰 만들기

CREATE VIEW emp_dept_view
AS 
SELECT empno,ename,job,hiredate,sal,dname,loc
FROM emp,dept
WHERE emp.deptno=dept.deptno;

같은 이름의 View를 수정 OR 생성할 때

• ALTER는 사용 불가함
• OR REPLACE를 사용하면 DROP TABLE 과정이 불필요해짐
• 주로 칼럼 추가할 때 주로 사용됨

CREATE OR REPLACE VIEW emp_dept_view
AS 
SELECT empno,ename,job,hiredate,comm,sal,dname,loc
FROM emp,dept
WHERE emp.deptno=dept.deptno;

원본 테이블에 없는 컬럼 추가해서 View 생성하기

• 학생 Table 생성

  CREATE TABLE student(
  hakbun NUMBER,
  name VARCHAR2(34) CONSTRAINT st_name_nn NOT NULL,
  kor NUMBER(3),
  eng NUMBER(3),
  math NUMBER(3),
  CONSTRAINT st_hakbun_pk PRIMARY KEY(hakbun)
  );
  

• 데이터 넣기

INSERT INTO student VALUES(1,'홍길동',80,90,75);
INSERT INTO student VALUES(2,'심청쓰',60,40,88);
INSERT INTO student VALUES(3,'이도령',75,95,85);
INSERT INTO student VALUES(4,'을지문덕',45,55,65);
INSERT INTO student VALUES(5,'이순신',100,80,95);

• 원본 테이블에 없는 컬럼 추가해서 View 생성하기

  CREATE OR REPLACE VIEW student_view(hakbun,name,kor,eng,math,total,avg,rank)
  AS
  SELECT hakbun,name,kor,eng,math,kor+eng+math,ROUND((kor+eng+math)/3.0,2),
  RANK() OVER(ORDER BY (kor+eng+math) DESC) 
  FROM student;
  

rownum

• rownum : 자동지정번호 (오라클에서 지정하는 컬럼명)
• 중간에 수정이나 추가되면 자동으로 1번부터 다시 매겨짐

rownum 사용하기

SELECT empno,ename,rownum
FROM emp;

rownum으로 5개씩 자르기

SELECT empno,ename,rownum
FROM emp
WHERE rownum<=5;

급여가 높은 사람 상위 5명만 출력하기 (인라인뷰 )

• 비교연산자 활용하기

  SELECT ename,sal,rownum
  FROM (SELECT empno,ename,sal FROM emp ORDER BY sal DESC)
  WHERE rownum<=5;
  

• BETWEEN 연산자 활용하기

  SELECT ename,sal,rownum
  FROM (SELECT empno,ename,sal FROM emp ORDER BY sal DESC)
  WHERE rownum BETWEEN 1 AND 5;
  

급여 높은 순으로 중위권 5명만 출력하기 (인라인뷰 )

• 오류

  -- 오류 : **중간을 자를 순 없음**
  SELECT ename,sal,rownum
  FROM (SELECT empno,ename,sal FROM emp ORDER BY sal DESC)
  WHERE rownum BETWEEN 6 AND 8;
  

• 정상

  SELECT empno,ename,sal,num
  FROM (SELECT empno,ename,sal,rownum as num  /* as num : Ailas num */
  FROM (SELECT empno,ename,sal 
  FROM emp ORDER BY sal DESC))
  WHERE num BETWEEN 6 AND 10;