[10g] 뷰 관리 및 Top-N 분석 - ver.dbrang

/********************************************************************************************
-- Title : [10g] 뷰 관리 및 Top-N 분석 - ver.dbrang
-- Reference : dbrang.com
-- Key word : view top n topn
********************************************************************************************/

/*
-- 뷰의 이름과 정의 기술
*/
SELECT * FROM DBA_VIEWS WHERE owner = 'SCOTT';

/*
-- 뷰 생성
*/
CREATE VIEW scott.empvu10
AS
SELECT empno, ename, job
FROM emp
WHERE deptno = 10; 

/*
-- 생성된 뷰 확인
*/
DESC empvu10;

SELECT * FROM dba_tab_columns
WHERE owner = 'SCOTT' AND table_name = 'EMPVU10'
 
 
/*
-- 서브쿼리에서 열 별칭을 사용하여 뷰를 생성
*/
CREATE VIEW scott.salvu30
AS
SELECT empno EMPLOYEE_NUMBER, ename NAME, sal SALARY
FROM emp
WHERE deptno = 30;

/*
-- 뷰의 수정(ALTER VIEW가 있나?)
*/
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS
SELECT empno, ename, job
FROM emp
WHERE deptno = 10;

/*
-- OR REPLACE를 활용한 권한 유지
*/
-- 뷰 생성
CREATE VIEW scott.vw_dept
AS
SELECT * FROM scott.dept;

SELECT * FROM dba_users;

-- 권한 부여
GRANT SELECT ON scott.vw_dept TO ttt;

-- 권한 확인
SELECT * FROM dba_tab_privs
WHERE grantee = 'TTT';

-- 뷰 수정
CREATE OR REPLACE VIEW scott.vw_dept
AS
SELECT deptno, dname FROM scott.dept
WHERE deptno > 10;

-- 권한 확인
SELECT * FROM dba_tab_privs
WHERE grantee = 'TTT';

-- 뷰 삭제
DROP VIEW scott.vw_dept;

-- 권한 확인
SELECT * FROM dba_tab_privs
WHERE grantee = 'TTT';

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

/*
-- WITH CHECK OPTION 절을 사용한 뷰 생성
*/
CREATE OR REPLACE VIEW scott.empvu20
AS
SELECT *
FROM scott.emp
WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;

-- 제약 확인
SELECT *
FROM DBA_constraints
WHERE owner = 'SCOTT' AND table_name IN ('EMPVU20', 'EMP');

-- FK 오류 입력(900은 없는 부서코드)
INSERT INTO scott.empvu20
SELECT 9999, 'HAYDEN', 'BOSS', 7566, sysdate, 9999, NULL, 900 FROM dual;

-- FK 오류 입력(40은 WHERE 조건에 오류)
INSERT INTO scott.empvu20
SELECT 9999, 'HAYDEN', 'BOSS', 7566, sysdate, 9999, NULL, 40 FROM dual;

-- FK 정상 입력(900은 없는 부서코드)
INSERT INTO scott.empvu20
SELECT 9999, 'HAYDEN', 'BOSS', 7566, sysdate, 9999, NULL, 20 FROM dual;

/*
-- 뷰 재컴파일
*/
ALTER VIEW scott.empvu20 compile;

/*
-- 뷰의 제거
*/
DROP VIEW scott.empvu10;

/*
-- 인라인 뷰
*/
SELECT a.ename, a.sal, a.deptno, b.maxsal
FROM scott.emp a, (SELECT deptno, MAX(sal) maxsal
                   FROM scott.emp
                   GROUP BY deptno) b   
WHERE a.deptno = b.deptno
AND a.sal < b.maxsal;

/*
-- TOP-N 분석 수행
*/
SELECT ROWNUM AS RANK, ename, sal
FROM (SELECT ename, sal
      FROM scott.emp
      ORDER BY sal DESC)
WHERE ROWNUM <= 3;