创建视图语法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

OR REPLACE	如果视图已经存在重新创建它
FORCE	创建视图，而不管基表是否存在
NOFORCE	只在基表存在的情况下创建视图(这是默认   值)
view	视图的名字
alias	为由视图查询选择的表达式指定名字(别名的个数必须与由视图 选择的表达式的个数匹配)
subquery	是一个完整的 SELECT 语句(对于在 SELECT 列表中的字段你 可以用别名)
WITH CHECK OPTION	指定只有可访问的行在视图中才能被插入或修改
constraint	为 CHECK OPTION 约束指定的名字
WITH READ ONLY	确保在该视图中没有 DML 操作被执行

注： 如果基表不存在，那么
Force 可以创建成功，但会提示“编译错误”
Noforce 直接提示表或视图不存在

1.1. 简单视图、复杂视图区别及测试

特性	简单视图	复杂视图
表的数目	一个	一个或多个
包含函数	无	有
包含数据分组	无	有
通过视图进行 DML 操作	是	不允许

创建环境：
SQL> create table student (sno number(5),sname varchar2(20),idcard varchar2(18),createtime date default sysdate not null,native_place number(2));

 

Table created.

 

SQL> insert into student values(1,'zhangsan','123456789123456780',default,1);

 

1 row created.

 

SQL> insert into student values(2,'lisi','123456789123456781',default,2);

 

1 row created.

 

SQL> insert into student values(3,'wangwu','123456789123456782',default,3);

 

1 row created.

 

SQL> insert into student values(4,'zhaoliu','123456789123456783',default,4);

 

1 row created.

 

SQL> select * from student;

 

       SNO SNAME                IDCARD             CREATETIM NATIVE_PLACE

---------- -------------------- ------------------ --------- ------------

         1 zhangsan             123456789123456780 20-SEP-16            1

         2 lisi                 123456789123456781 20-SEP-16            2

         3 wangwu               123456789123456782 20-SEP-16            3

         4 zhaoliu              123456789123456783 20-SEP-16            4

 

创建简单视图和复杂视图:

SQL> create view vw_student1 as select sno,sname,createtime　from student;

create view vw_student1 as select sno,sname,createtime

            *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL> conn / as sysdba

Connected.

SQL>

SQL> grant create view to scott;

 

Grant succeeded.

 

SQL> conn scott/tiger;

Connected.

SQL>

SQL> create view vw_student1 as select sno,sname,createtime　from student;

（简单视图）

View created.

 

SQL> create view vw_student2 as select sno,sname,createtime,sysdate as dd from student;

（简单视图）

View created.

 

SQL> create view vw_student3 as select sno,sname,to_char(createtime,'yyyy-mm-dd') as ctime from student;

(复杂视图)

View created.

 

SQL> create view vw_student4 as select native_place,count(*)  as num from student group by native_place;

(复杂视图)

View created.

 

－－操作视图

插入数据测试：

SQL> insert into vw_student1 values(5,'test',sysdate);

（简单视图可以进行DML操作）

1 row created.

 

SQL> insert into vw_student2(sno,sname,createtime) values(6,'test1',sysdate);

（简单视图可以进行DML操作）

1 row created.

 

SQL> insert into vw_student3(sno,sname,ctime) values(7,'test2',to_char(sysdate,'yyyy-mm-dd'));

insert into vw_student3(sno,sname,ctime)

                                  *

ERROR at line 1: (复杂视图不允许DML操作)

ORA-01733: virtual column not allowed here

 

 

SQL> insert into vw_student4 values(1,1);

insert into vw_student4 values(1,1)

*

ERROR at line 1: (复杂视图不允许DML操作)

ORA-01733: virtual column not allowed here

 

1.2. WITH CHECK OPTION
视图中的 check 约束

——创建环境：

SQL> create table student(sno number(5) constraint pk_student primary key using index,sname varchar2(20),idcard varchar2(18),createtime date default sysdate,native_place number(2));

 

Table created.

SQL> insert into student values(1,'zhangsan','123456789123456780',default,1);

 

1 row created.

 

SQL> insert into student values(2,'lisi','123456789123456781',default,2);

1 row created.

SQL> insert into student values(3,'wangwu','123456789123456782',default,3);

 

1 row created.

 

SQL> insert into student values(4,'zhaoliu','123456789123456783',default,2);

 

1 row created.

 

SQL> insert into student values(5,'zhaosi','123456789123456783',default,2);

 

1 row created.

 

SQL> insert into student values(6,'maba','123456789123456783',default,2);

 

1 row created.

 

SQL> commit;

 

Commit complete.

SQL> select * from student;

 

       SNO SNAME                IDCARD             CREATETIM NATIVE_PLACE

---------- -------------------- ------------------ --------- ------------

         1 zhangsan             123456789123456780 20-SEP-16            1

         2 lisi                 123456789123456781 20-SEP-16            2

         3 wangwu               123456789123456782 20-SEP-16            3

         4 zhaoliu              123456789123456783 20-SEP-16            2

         5 zhaosi               123456789123456783 20-SEP-16            2

         6 maba                 123456789123456783 20-SEP-16            2

 

6 rows selected.

 

——创建没有约束的简单视图：

SQL> create or replace view vw_student7 as select sno,sname,idcard,native_place from student where native_place=2;

 

View created.

 

——创建有约束的简单视图 ：

SQL> create or replace view vw_student8 as select sno,sname,idcard,native_place from student where native_place = 2 with check option;

 

View created.

——插入数据测试：

SQL> insert into vw_student8 values(7,'zhu88','123456789123456783',3);

insert into vw_student8 values(7,'zhu88','123456789123456783',3)

            *

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

 

SQL> insert into vw_student7 values(7,'zhu88','123456789123456783',3);

 

1 row created.

 

SQL> insert into vw_student8 values(8,'niu99','123456789123456783',2);

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

小结：
1) 当往有约束的简单视图中插入不符合约束的数据时，数据库则会抛出错误，当插入符合约束条件的数据，则插入正常。
2) 当往没有约束的视图中插入符合标准的数据时，则可以正常插入

 

1.3. WITH READ ONLY
——创建只读视图

SQL> create or replace view vw_student9 as select sno,sname,

  2  idcard,native_place from student where native_place=2

  3  with read only;

 

View created.

 

SQL> insert into vw_student9 values ( 9,'r_only','123456789123456783',2);

insert into vw_student9 values ( 9,'r_only','123456789123456783',2)

*

ERROR at line 1:

ORA-42399: cannot perform a DML operation on a read-only view

 

1.4. 子查询不允许视图中的 order by
1) 正常视图中可以排序

SQL> create or replace view vw_student7 as select sno,sname,

  2  idcard,native_place from student where native_place=2

  3  order by sno;

 

View created.

2) 视图中含有子查询，子查询中不允许排序

SQL> create or replace view vw_student6 as select sno,idcard,native_place from student where native_place in (select native_place from student where native_place<=2 order by native_place);

 

(select native_place from student where native_place<=2 order by native_place)

                                                        *

ERROR at line 2:

ORA-00907: missing right parenthesis

 

3) 视图中带有约束，允许排序

SQL> create or replace view vw_student8 as select sno,sname, 

  2  idcard,native_place from student where native_place=2

  3  order by sno

  4  with check option;

 

View created.

 

4) 视图为只读状态，允许排序

SQL> create or replace view vw_student9 as select sno,sname,

  2  idcard,native_place from student where native_place=2

  3  order by sno

  4  with read only;

 

View created.

SQL> select * from vw_student9;

 

       SNO SNAME                IDCARD             NATIVE_PLACE

---------- -------------------- ------------------ ------------

         2 lisi                 123456789123456781            2

         4 zhaoliu              123456789123456783            2

         5 zhaosi               123456789123456783            2

         6 maba                 123456789123456783            2

         8 niu99                123456789123456783            2