源码-Oracle数据库管理-第七章-视图-Part 1

视图是一个或多个表的逻辑呈现方式，主要用于：

1. 简化用户对于数据的理解（将经常使用的查询定义为视图）

2. 提供更好的安全性级别（将Oracle的授权命令作用于视图，权限被限制在行、列子集上）

3. 增加基表的呈现角度（如，重命名视图的列）

4. 隔离应用程序对及基表定义的更改

--第7章-视图
--创建一个标准的视图
--代码7.1 创建v_emp_dept视图
CREATE OR REPLACE VIEW v_emp_dept
    AS
       SELECT emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, dept.dname,
              dept.loc
         FROM emp, dept
        WHERE emp.deptno = dept.deptno;

select * from v_emp_dept;

--创建内嵌视图
SELECT d.deptno,d.dname,emp_cnt.tot FROM dept d,
                   (SELECT deptno,COUNT(*) tot FROM emp GROUP BY deptno) emp_cnt
                   WHERE d.deptno=emp_cnt.deptno;


--代码7.2 创建对象类型

CREATE OR REPLACE TYPE typ_emp AS OBJECT (
   empno     NUMBER,
   empname   VARCHAR2 (20),
   job       VARCHAR2 (20)
);


--创建对象视图
CREATE OR REPLACE VIEW ov_emp
   OF typ_emp                                        --视图基于对象类型typ_emp
   WITH OBJECT OID (empno)
AS                                                  --为对象视图指定对象标识符
   SELECT empno a_id, ename, job
     FROM emp;
     
select * from ov_emp;


--代码7.4 对象类型的使用示例
DECLARE
   o_emp   typ_emp;                  --定义对象类型
BEGIN
   SELECT VALUE (t)                  --从对象视图中获取对象实例
     INTO o_emp
     FROM ov_emp t
    WHERE empno = 7369;
   --更改对象类型的属性
   o_emp.empname := '张三丰';
   o_emp.job := '神职';
   o_emp.empno := 7999;
   --向对象视图中插入一个新的对象实例
   INSERT INTO ov_emp
        VALUES (o_emp);
END;  


--代码 7.5 创建一个简单视图
 CREATE OR REPLACE VIEW v_deptemp
    AS
       SELECT empno 工号, ename 姓别, job 职位, mgr 经理, hiredate 雇佣日期, 
       sal 工资, comm 提成，deptno 部门
         FROM emp
        WHERE deptno = 20;

select * from v_deptemp where 工号=8005;

--代码 7.6 创建简单视图并指定视图别名
CREATE OR REPLACE VIEW v_deptemp_alias (工号,
                                            姓名,
                                            职位,
                                            经理,
                                            雇佣日期,
                                            薪水,
                                            提成
                                           )
    AS
      SELECT empno, ename, job, mgr, hiredate, sal, comm
        FROM emp
       WHERE deptno = 20;

select * from v_deptemp_alias;

--向简单视图中插入数据
INSERT INTO v_deptemp VALUES(8001,'李思','经理',7369,SYSDATE,8000,200,20);

--插入一个不在视图范围内的记录
INSERT INTO v_deptemp VALUES(8005,'张天师','经理',7369,SYSDATE,8000,200,30);

select * from emp where empno=8005;

--代码 7.8 使用WITH CHECK OPTION创建视图
CREATE OR REPLACE VIEW v_deptemp_check
    AS
       SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
         FROM emp
        WHERE deptno = 20
              WITH CHECK OPTION CONSTRAINT v_empdept_chk1;

Select search_condition from user_constraints where constraint_name=upper('v_empdept_chk1');
select * from user_constraints where table_name=upper('v_deptemp_check1');
alter table v_deptemp_check drop constraint V_EMPDEPT_CHK1;
alter table v_deptemp_check modify constraint v_empdept_chk1


--代码 7.9 创建一个复杂视图
CREATE OR REPLACE VIEW v_emp_sum (deptno, emp_count, max_sal, min_sal,
                                  sum_sal)
AS
   SELECT   dept.dname, COUNT (emp.empno), MAX (emp.sal), MIN (emp.sal),
            SUM (emp.sal)
       FROM emp, dept
      WHERE emp.deptno = dept.deptno
   GROUP BY dept.dname     


select * from v_emp_sum;   
   
--代码7.10 创建可更新（可进行DML操作）的复杂视图
   
CREATE OR REPLACE VIEW v_empdept_update
AS
   SELECT emp.empno, emp.ename, emp.job, dept.dname, dept.loc
     FROM dept, emp
    WHERE dept.deptno = emp.deptno;
    
    
--查询可更新的视图列
SELECT table_name, column_name, updatable,insertable,deletable 
  FROM user_updatable_columns
 WHERE table_name = 'V_EMPDEPT_UPDATE';       



--查询视图的状态
SELECT last_ddl_time, object_name, status
  FROM user_objects
 WHERE object_name = 'V_EMP_DEPT'; 
 
 alter table emp modify ename varchar2(20);
 select * from v_emp_dept;