视图以经过定制的方式显示来自一个或多个表的数据。
视图可视为“虚拟表”或“存储的查询”。
创建视图所依据的表,称为“基表”。
视图中是不存放数据的,它只是一个定义,查询视图实际上是从基表中查询数据。
视图的优点:
A、提供了另外一种级别的表安全性
B、隐藏的数据的复杂性
C、简化的用户SQL命令
D、隔离基表结构的改变
E、通过重命名列,从另一个角度提供数据
一、创建视图
语法:
create [or replace] [force] view 视图名[(列1,列2,......)] as 查询 [with check option] [with read only];
force:即使基表不存在也可以创建视图。不加“force”若基表不存在则不创建视图并报错。
with check option:当对视图进行更新使视图中的列数量变少时,禁止该更新操作。
witrh read only:视图不可更新。
//先用system用户给scott用户赋予创建视图的权限
SQL> conn system/system@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@ORCL
SQL> grant create view to scott;
Grant succeeded
//切换到scott用户,当前用户下没有student2表,尝试创建student2的视图
SQL> conn scott/scott;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> select * from student2;
select * from student2
ORA-00942: table or view does not exist
//不加“force”直接创建视图,不能创建视图并报错
SQL> create view view1 as select * from student2;
create view view1 as select * from student2
ORA-00942: table or view does not exist
//可以看到没有创建视图
SQL> select * from user_views;
VIEW_NAME TEXT_LENGTH TEXT TYPE_TEXT_LENGTH TYPE_TEXT OID_TEXT_LENGTH OID_TEXT VIEW_TYPE_OWNER VIEW_TYPE SUPERVIEW_NAME EDITIONING_VIEW READ_ONLY
------------------------------ ----------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ --------------- ---------
//添加“force”后可以创建视图,但创建的视图带有编译错误
SQL> create force view view1 as select * from student2;
Warning: View created with compilation errors
SQL> select * from user_views;
VIEW_NAME TEXT_LENGTH TEXT TYPE_TEXT_LENGTH TYPE_TEXT OID_TEXT_LENGTH OID_TEXT VIEW_TYPE_OWNER VIEW_TYPE SUPERVIEW_NAME EDITIONING_VIEW READ_ONLY
------------------------------ ----------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ --------------- ---------
VIEW1 23 select * from student2