oracle教程13 Oracle数据库对象---视图详解和举例

短命女 2022-05-27 04:37 227阅读 0赞

### Oracle的视图介绍与创建    ###

--创建简单视图
    --建立用于查询员工号、姓名、工资的视图。
    create view emp_view
    as
    select empno,ename,sal from emp;
    
    
    --查询视图
    select * from emp_view;
    
    --创建视图时指定视图的列的别名
    create view emp_view2(员工号,姓名,工资)
    as
    select empno,ename,sal from emp;
    
    --查询视图
    select * from emp_view2;
    
    --连接视图
    --建立用于获得部门号为10的部门号、部门名称及员工信息。
    create view dept_emp_view
    as
    select d.deptno,d.dname,e.empno,e.ename,e.job
    from dept d, emp e
    where d.deptno = e.deptno and d.deptno = 10;
    
    --查询视图
    select * from dept_emp_view;
    
    --只读视图
    --建立查看10号部门员工信息的视图。
    create view emp_view3
    as
    select * from emp where deptno = 10
    with read only;
    
    --查询视图
    select * from emp_view3;
    --测试
    update emp_view3 set sal = sal +50;

### Oracle视图的查询修改与删除    ###

--视图上的DML操作
    
    --创建视图
    create view empnew_view
    as
    select empno,ename,sal from empnew;
    
    --select
    select * from empnew_view;
    
    --insert
    insert into empnew_view(empno,ename,sal) values(8888,'LAYNA',6666);
    select * from empnew;
    
    --update
    update empnew_view set sal = sal + 100 where empno = 8888;
    
    --delete
    delete from empnew_view where empno = 8888;
    commit;
    
    --在视图上定义check约束
    create view empnew_view2
    as
    select * from empnew where deptno = 20
    with check option constraint ck_view;
    
    --查询视图
    select * from empnew_view2;
    
    --测试
    --insert或update
    update empnew_view2 set deptno = 30 where empno = 7566;
    
    --修改视图
    
    --修改前查询
    select * from empnew_view;
    
    --修改empnew_view视图
    create or replace view empnew_view
    as
    select * from emp where job = 'SALESMAN';
    
    --修改后查询
    select * from empnew_view;
    
    --删除视图
    drop view empnew_view;
    
    select * from emp;

### Oracle的复杂视图    ###

--创建复杂视图
    create view job_view(job, avgsal, sumsal, maxsal, minsal)
    as
    select job,avg(sal),sum(sal),max(sal),min(sal) from emp group by job;
    
    --查看复杂视图
    select * from job_view;
    
    --通过with read only子句为复杂视图屏蔽DML操作
    create view job_view
    as
    select job,avg(sal) avgsal,sum(sal) sumsal,max(sal) maxsal,min(sal) minsal from emp group by job
    with read only;