2015-10-24
目录
1.创建视图 CREATE VIEW
2.修改视图 ALTER VIEW
3.删除视图 DROP VIEW
4.创建物化视图 CREATE MATERIALIZED VIEW
6.修改物化视图 ALTER MATERIALIZED VIEW
5.删除物化视图 DROP MATERIALIZED VIEW
1.创建视图 CREATE VIEW
【语法】CREATE VIEW 官方文档
create_view::=
CREATE [OR REPLACE] [[NO] FORCE] [ EDITIONING | EDITIONABLE [ EDITIONING ] | NONEDITIONABLE ] VIEW [schema.] view [ ( { alias [ VISIBLE | INVISIBLE ] [ inline_constraint... ] | out_of_line_constraint } [, { alias [ VISIBLE | INVISIBLE ] [ inline_constraint...] | out_of_line_constraint } ] ) | object_view_clause | XMLType_view_clause ] [ BEQUEATH { CURRENT_USER | DEFINER } ] AS subquery [ subquery_restriction_clause ] ;
2.修改视图 ALTER VIEW
【语法】 ALTER VIEW 官方文档
alter_view::=
ALTER VIEW [ schema. ] view { ADD out_of_line_constraint | MODIFY CONSTRAINT constraint { RELY | NORELY } | DROP { CONSTRAINT constraint | PRIMARY KEY | UNIQUE (column [, column ]...) } | COMPILE | { READ ONLY | READ WRITE } | { EDITIONABLE | NONEDITIONABLE } } ;
3.删除视图 DROP VIEW
【语法】DROP VIEW 官方文档
drop_view::=
DROP VIEW [ schema. ] view [ CASCADE CONSTRAINTS ] ;
4.创建物化视图 CREATE MATERIALIZED VIEW
【语法】 CREATE MATERIALIZED VIEW 官方文档
create_materialized_view::=
CREATE MATERIALIZED VIEW [ schema. ] materialized_view [ OF [ schema. ] object_type ] [ ( { scoped_table_ref_constraint | column_alias [ENCRYPT [encryption_spec]] } [, { scoped_table_ref_constraint | column_alias [ENCRYPT [encryption_spec]] } ]... ) ] { ON PREBUILT TABLE [ { WITH | WITHOUT } REDUCED PRECISION ] | physical_properties materialized_view_props } [ USING INDEX [ physical_attributes_clause | TABLESPACE tablespace ]... | USING NO INDEX ] [ create_mv_refresh ] [ FOR UPDATE ] [ evaluation_edition_clause ] [ query_rewrite_clause ] AS subquery ;
5.修改物化视图 ALTER MATERIALIZED VIEW
【语法】 ALTER MATERIALIZED VIEW 官方文档
alter_materialized_view::=
ALTER MATERIALIZED VIEW [ schema. ] materialized_view [ physical_attributes_clause | modify_mv_column_clause | table_compression | inmemory_alter_table_clause | LOB_storage_clause [, LOB_storage_clause ]... | modify_LOB_storage_clause [, modify_LOB_storage_clause ]... | alter_table_partitioning | parallel_clause | logging_clause | allocate_extent_clause | deallocate_unused_clause | shrink_clause | { CACHE | NOCACHE } ] [ alter_iot_clauses ] [ USING INDEX physical_attributes_clause ] [ MODIFY scoped_table_ref_constraint | alter_mv_refresh ] [ evaluation_edition_clause ] [ alter_query_rewrite_clause | COMPILE | CONSIDER FRESH ] ;
6.删除物化视图 DROP MATERIALIZED VIEW
【语法】 DROP MATERIALIZED VIEW 官方文档
drop_materialized_view::=
DROP MATERIALIZED VIEW [ schema. ] materialized_view [ PRESERVE TABLE ] ;
【例子】
视图是通过select语句定义的基于物理表的虚表，它不存储数据，在数据字典中只记录视图的定义。 简单视图是从一个表读取数据，不包括函数和分组数据，可以进行DML操作。 复杂视图是从多个表提取数据，包括函数和分组数据，不一定能进行DML操作。 物化视图是具有物理存储的特殊视图。 #授予scott用户创建视图的权限 SQL> conn system/oracle as sysdba; SQL> grant create view to scott; #创建属于account部门的员工视图 SQL> create view accounting_view as select e.ename "employee_name",e.job "job",e.hiredate "hiredate",e.sal "salary",d.dname "dep_name" from dept d,epm e where e.deptno = d.deptno and d.deptno < 20; #查询数据字典中视图定义 SQL> select view_name from user_views; #查询视图accounting_view定义 SQL> select text from user_views where view_name = 'ACCOUNTING_VIEW'; #查询accounting部门所有员工信息 SQL> select * from accounting_view; #创建部门sales的员工视图 SQL> create or replace view sales_view ("employee_name","job","hiredate","salary","dep_name") as select e.ename,e.job,e.hiredate,e.sal,d.dname from dept d,emp e where e.deptno = d.deptno and d.deptno = 30 ; #查询是否成功创建sales_view视图 SQL> select view_name,text from user_views where view_name like 'SAL%'; #使用视图 SQL> select * from sales_view; #创建部门research的员工视图 SQL> create or replace view research_view ("employee_name","job","hiredate","salary","dep_name") as select e.ename,e.job,e.hiredate,e.sal,d.dname from dept d,emp e where e.deptno = d.deptno and d.deptno = 20 with read only; #测试update，否 SQL> update research_view set "salary" = 1000 where "job" = 'CLERK'; #测试delete，否 SQL> delete from research_view where "job" = 'CLERK'; #创建基于单表的视图 SQL> create view emp_view as select * from emp where job in ('SALESMAN','MANAGER'); #测试insert，否 SQL> insert into emp_view(empno,ename,job,mgr,hiredate,sal,comm,deptno) values (7565,'TOM','Marketing',79920,SYSDATE,2000,22.40); #测试insert，是 SQL> insert into emp_view(empno,ename,job,mgr,hiredate,sal.comm,deptno) values (7565,'TOM','MANAGER',72039,SYSDATE,2000,22,20); #确认插入结果 SQL> select * from emp_view enmae = 'TOM'; #查询表emp是否插入了一行数据 SQL> select * from emp where ename = 'TOM'; #查看视图research_view是否存在 SQL> select view_name from user_views; #查看视图结构 SQL> desc research_view; #修改视图 research_view SQL> create or replace view research_view ("员工号","员工姓名","岗位","雇佣时间","薪水","部门") as select e.empno,e.ename,e.job,e.hiredate,e.sal,d.dname from dept d, emp e where e.deptno = d.deptno and d.deptno = 20; #确认是否创建视图 SQL> desc research_view; #查询视图research_view SQL> select * from research_view where rownum<3; #查看重写查询是否开启 SQL> show parameter query_rewrite_enabled; #授予scott用户创建物化视图的权限 SQL>conn system/oracle as sysdba; SQL> grant create materialized view,query rewrite,create any table,select any table to scott; #针对基表创建物化视图日志 SQL> create materialized view log on dept; SQL> create materialized view log on emp; #创建物化视图 SQL> create materialized view mtrlview_test build immediate refresh fast on commit enable query rewrite as select d.dname,d.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal from dept d,emp e where d.deptno = e.deptno; #参数说明 build immedate 立即创建物化视图 refresh fast on commit 快速刷新类型，基表数据提交后立即更新物化视图 enable query rewrite 开启重写查询功能 as子句 定义物化视图内容 #删除物化视图 SQL> drop materialized view mtrlview_test;
参考资料
[1] 林树泽.Oracle 11g R2 DBA操作指南[M].北京：清华大学出版社，2013
[2] oracle视图总结(转)
[3] oracle中的视图详解
[4] Oracle 视图 的应用
[5] Oracle视图基础详解与实例
[6] ORACLE 创建与使用视图
[7] Oracle常用视图
[8] oracle视图索引操作
[9] 关于Oracle的视图
[10] Oracle中创建视图
[11] oracle 视图创建和操作,创建简单,复杂的视图,创建基表不存在的视图,视图增删改,查看视图的结构
[12] 管理Oracle视图和序列
[13] Oracle 视图可以DML操作的条件
[14] Oracle连接视图DML操作的限制
[15] 对视图进行 DML 操作
[16] Oracle之物化视图
[17] Oracle物化视图创建全过程(转)
[18] ORACLE物化视图
[19] ORACLE物化视图入门
[20] ORACLE物化视图
[21] ORACLE物化视图
[22] oracle物化视图