Chapter 14 View
最基本的表也就是实体是不会发生变化的,变化的应该是视图。
create view average
as
select d.dname "部门", avg(e.sal) "平均工资", avg(nvl(comm,0)) "平均佣金", count(*) "员工数"
from emp e, dept d
where e.deptno = d.deptno
group by d.dname
select *
from average;
create or replace view acct
("名字","工资","职位","雇佣日期")
as
select ename, sal, job, hiredate
from emp;
select * from acct;
select view_name, text_length, text
from user_views;
create view sales30
as
select *
from emp
where deptno = 30
with check option constraint sales30_ck;
--所有通过该视图进行的DML操作操作都不能违反了在创建视图是用where子句所限定的条件
SQL> desc sales30;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
select *
from sales30;
update sales30
set deptno = 10
where job = 'MANAGER';
update acct
set "工资" =9999;
select *
from acct;
select ename, sal, job
from emp
where deptno = 10;
rollback;
create or replace view acct
as
select d.dname "部门", avg(e.sal) "平均工资", avg(nvl(comm,0)) "平均佣金", count(*) "员工数"
from emp e, dept d
where e.deptno = d.deptno
and e.deptno = 10
group by d.dname
with read only;
update acct
set "工 资" = 9999;
delete from acct;
select view_name, text_length
from user_views
drop view acct;
select
from a , (select * from ...) m
select *
from
where rownum <=5;
最基本的表也就是实体是不会发生变化的,变化的应该是视图。
create view average
as
select d.dname "部门", avg(e.sal) "平均工资", avg(nvl(comm,0)) "平均佣金", count(*) "员工数"
from emp e, dept d
where e.deptno = d.deptno
group by d.dname
select *
from average;
create or replace view acct
("名字","工资","职位","雇佣日期")
as
select ename, sal, job, hiredate
from emp;
select * from acct;
select view_name, text_length, text
from user_views;
create view sales30
as
select *
from emp
where deptno = 30
with check option constraint sales30_ck;
--所有通过该视图进行的DML操作操作都不能违反了在创建视图是用where子句所限定的条件
SQL> desc sales30;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
select *
from sales30;
update sales30
set deptno = 10
where job = 'MANAGER';
update acct
set "工资" =9999;
select *
from acct;
select ename, sal, job
from emp
where deptno = 10;
rollback;
create or replace view acct
as
select d.dname "部门", avg(e.sal) "平均工资", avg(nvl(comm,0)) "平均佣金", count(*) "员工数"
from emp e, dept d
where e.deptno = d.deptno
and e.deptno = 10
group by d.dname
with read only;
update acct
set "工 资" = 9999;
delete from acct;
select view_name, text_length
from user_views
drop view acct;
select
from a , (select * from ...) m
select *
from
where rownum <=5;