oracle 第19章视图

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 ] ;

View Code

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 }
  } ;

View Code

3.删除视图 DROP VIEW

【语法】DROP VIEW 官方文档

drop_view::=

DROP VIEW [ schema. ] view [ CASCADE CONSTRAINTS ] ;

View Code

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 ;

View Code

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
  ] ;

View Code

6.删除物化视图 DROP MATERIALIZED VIEW

【语法】 DROP MATERIALIZED VIEW 官方文档

drop_materialized_view::=

DROP MATERIALIZED VIEW [ schema. ] materialized_view
   [ PRESERVE TABLE ] ;

View Code

【例子】

视图是通过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;