[8i] 뷰 관리 예

/********************************************************************************************
-- Title : [8i] 뷰 관리 예
-- Reference : OLN
-- Key word : view inline view 인라인 뷰
********************************************************************************************/

/****************************************************************************************
-- 뷰의 이름과 정의 기술
****************************************************************************************/
SQL> SELECT *        
  2  FROM USER_VIEWS;

/****************************************************************************************
-- 뷰 생성
****************************************************************************************/
SQL> CREATE VIEW empvu10
  2  AS
  3  SELECT empno, ename, job
  4  FROM emp
  5  WHERE deptno = 10; 

View created.

/****************************************************************************************
-- 생성된 뷰 확인
****************************************************************************************/
SQL> DESC empvu10
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 
 
/****************************************************************************************
-- 서브쿼리에서 열 별칭을 사용하여 뷰를 생성
****************************************************************************************/
SQL> CREATE VIEW salvu30
  2  AS
  3  SELECT empno EMPLOYEE_NUMBER, ename NAME, sal SALARY
  4  FROM emp
  5  WHERE deptno = 30;

View created.

/****************************************************************************************
-- 뷰의 수정
****************************************************************************************/
SQL> CREATE OR REPLACE VIEW empvu10
  2  (employee_number, employee_name, job_title)
  3  AS
  4  SELECT empno, ename, job
  5  FROM emp
  6  WHERE deptno = 10;

View created.

/****************************************************************************************
-- 복합 뷰의 생성
****************************************************************************************/
SQL> CREATE VIEW dept_sum_vu 
  2  (name, minsal, maxsal, avgsal)
  3  AS 
  4  SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)
  5  FROM emp e, dept d
  6  GROUP BY d.dname;

View created.

/****************************************************************************************
-- WITH CHECK OPTION 절을 사용한 뷰 생성
****************************************************************************************/
SQL> CREATE OR REPLACE VIEW empvu20
  2  AS
  3  SELECT *
  4  FROM emp
  5  WHERE deptno = 20
  6* WITH CHECK OPTION CONSTRAINT empvu20_ck;

View created.

/****************************************************************************************
-- 뷰의 제거
****************************************************************************************/
SQL> DROP VIEW empvu10;

View dropped.

/****************************************************************************************
-- 인라인 뷰
****************************************************************************************/
SQL> SELECT a.ename, a.sal, a.deptno, b.maxsal
  2  FROM emp a, (SELECT deptno, max(sal) maxsal
  3               FROM emp
  4               GROUP BY deptno) b   
  5  WHERE a.deptno = b.deptno
  6  AND a.sal < b.maxsal;

ENAME             SAL     DEPTNO     MAXSAL
---------- ---------- ---------- ----------
CLARK            2450         10       5000
MILLER           1300         10       5000
SMITH             800         20       3000
ADAMS            1100         20       3000
JONES            2975         20       3000
ALLEN            1600         30       2850
MARTIN           1250         30       2850
JAMES             950         30       2850
TURNER           1500         30       2850
WARD             1250         30       2850

10 rows selected.

/****************************************************************************************
-- TOP-N 분석 수행
****************************************************************************************/
SQL> SELECT ROWNUM as RANK, ename, sal
  2  FROM (SELECT ename, sal
  3        FROM emp
  4        ORDER BY sal DESC)
  5  WHERE ROWNUM <= 3;

      RANK ENAME             SAL
---------- ---------- ----------
         1 KING             5000
         2 SCOTT            3000
         3 FORD             3000