视图
视图:建立在表|结果集|视图上的虚拟表，有以下作用
- 简化:select 查询语句
- 重用:封装select语句 命名
- 隐藏:内部细节
- 区分:相同数据不同查询
不是所有的用户都有创建视图的权限
- 前提： create view -->组 connect resource dba
- 授权: -->sqlplus /nolog
a)、sys登录 conn sys/123456@orcl as sysdba
b)、授权: grant dba to scott;
回收: revoke dba from scott;
c)、重新登录
create or replace view 视图名 as select语句
[with read only]
要求:所有列必须存在名称。
1.普通视图(无法实现数据持久化)
--创建视图
create view vi_t as select * from t where age>40;
--通过视图向表插入数据
insert into vi_t values(23,'chen',23);--可以插入数据到基本表中
--通过试图可以更新数据
update vi_t set age=10 where id=1; --也可以更改
--删除视图后重新创建
drop view vi_t ;
create view vi_t as select * from t where age>40 with check option; --多了with check option
--with check option 在更改数据是多了一个条件，如果不满足视图的条件，则不执行
SQL> insert into vi_t values(24,'CC',34);
insert into vi_t values(24,'CC',34)
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
SQL> insert into vi_t values(24,'CC',54); --将34改为54 OK!
1 row inserted
SQL> update vi_t set age=12 where id=24;
update vi_t set age=12 where id=24
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
SQL> update vi_t set age=82 where id=24; --将12改为82 OK
1 row updated
查询视图信息
SQL> select * from user_views;
2. 物化视图（可以实现数据持久化）
--先进行授权
grant create materialized view to user_test;
--创建测试表
create table stu(sno number(5) primary key,sname varchar(10) not null,sage number(3) ,deptno varchar2(10));
--插入数据
insert into stu values(1,'zhang',12,'CS');
insert into stu values(2,'wang',23,'En');
insert into stu values(3,'wu',20,'CS');
--创建物化视图
create materialized view mvi_stu as select * from stu;--手工更新
SQL> select * from user_tables where table_name='MVI_STU';--真正存在表
SQL> insert into stu values(4,'hu',10,'MA');
1 row inserted
SQL> select * from stu;
SNO SNAME SAGE DEPTNO
------ ---------- ---- ----------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
SQL> select * from mvi_stu;
SNO SNAME SAGE DEPTNO
------ ---------- ---- ----------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
SQL> execute dbms_mview.refresh('mvi_stu');--手工更新
SQL> select * from mvi_stu;
SNO SNAME SAGE DEPTNO
------ ---------- ---- ----------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
--强制刷新，无需手动刷新
create materialized view mvi_stu_ref_on_commit refresh force on commit as select * from stu;--一旦提交时自己更新视图
SQL> select * from mvi_stu_ref_on_commit;
SNO SNAME SAGE DEPTNO
------ ---------- ---- ----------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
SQL> insert into stu values(5,'xu',67,'MA');
1 row inserted
--查询
SQL> select * from stu;
SNO SNAME SAGE DEPTNO
------ ---------- ---- ----------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
5 xu 67 MA
SQL> select * from mvi_stu_ref_on_commit;
SNO SNAME SAGE DEPTNO
------ ---------- ---- ----------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
SQL> commit;
Commit complete
SQL> select * from mvi_stu_ref_on_commit;
SNO SNAME SAGE DEPTNO
------ ---------- ---- ----------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
5 xu 67 MA
--创建一个聚集物化视图
create materialized view mvi_stu_dept_avg refresh force on commit as select count(sage) cnt,deptno,avg(sage) as v_age from stu group by deptno;
SQL> insert into stu values(6,'hh',32,'CS');
1 row inserted
SQL> select * from stu;
SNO SNAME SAGE DEPTNO
------ ---------- ---- ----------
1 zhang 12 CS
2 wang 23 En
3 wu 20 CS
4 hu 10 MA
5 xu 67 MA
6 hh 32 CS
SQL> select * from mvi_stu_dept_avg;
CNT DEPTNO V_AGE
---------- ---------- ----------
2 MA 38.5
2 CS 16
1 En 23
SQL> select count(sage) cnt,deptno,avg(sage) as v_age from stu group by deptno;
CNT DEPTNO V_AGE
---------- ---------- ----------
2 MA 38.5
3 CS 21.3333333
1 En 23
SQL> select * from mvi_stu_dept_avg;
CNT DEPTNO V_AGE
---------- ---------- ----------
2 MA 38.5
2 CS 16
1 En 23
SQL> commit;
Commit complete
SQL> select * from mvi_stu_dept_avg;
CNT DEPTNO V_AGE
---------- ---------- ----------
2 MA 38.5
3 CS 21.3333333
1 En 23
--查询物化视图
SQL> select * from user_mviews;
--删除物化视图
SQL> drop materialized view view_name;
浙公网安备 33010602011771号