Oracle（十一）视图

Oracle（十一）视图

1、创建视图--create view ...as ...

create view empvu30
as select e.empno, e.ename, e.deptno, d.dname from emp e, dept d
where e.deptno = d.deptno and e.deptno = 30;

2、修改视图--create or replace view ...

（1）插入数据（实际插入的是视图）

create or replace view empvu30
as select e.empno, e.ename, e.deptno from emp e;
insert into empvu30 values(8001, 'lxr', 10);

（2）插入视图（视图中不含基表中不为空的列）中原基表不为空的列，插入失败

create or replace view empview
as select ename from emp;

insert into empview values（'lxr');

（3）删除视图--drop view

不会删除基表中的数据

drop view empview;

3、临时视图

select sal, rownum from(
select sal from emp order by sal desc)
where rownum <= 3;

--工资由高到底，取出第3~8的记录

select m.* from (
  select sal, rownum rn from(
  select sal from emp order by sal desc)
  where rownum <= 8)m
  where rn >= 3;
}