Oracle-11-视图

视图定义：视图是一个已经被命名的查询语句

创建视图必须有创建视图的权限

--**
select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE VIEW
CREATE SEQUENCE
SELECT ANY SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE MATERIALIZED VIEW
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14 rows selected.

Elapsed: 00:00:00.00

创建视图

create view v_emp as select * from emp where deptno=20;

select * from v_emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

Elapsed: 00:00:00.01

修改视图

create or replace view v_emp as select * from emp where deptno=10;

删除视图

drop view v_emp;

创建复杂视图

create or replace view v_emp as select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

select * from v_emp;

ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

Elapsed: 00:00:00.02

如果要对视图进行insert操作，要求视图中不能有组函数、group by、distinct、rownum

如果要对视图进行update操作，要求视图中不能有组函数、group by、distinct、rownum、计算生成的伪列

如果要对视图进行insert操作，要求视图中不能有组函数、group by、distinct、rownum、计算生成的表达式，而且必须有非空约束的列。

重新准备实验需要的基表

create or replace view v_emp as select ename,sal,dname from e1 where dname='SALES';

select * from e1;

ENAME             SAL       COMM DNAME
---------- ---------- ---------- --------------
SMITH             800            RESEARCH
ALLEN            1600        300 SALES
WARD             1250        500 SALES
JONES            2975            RESEARCH
MARTIN           1250       1400 SALES
BLAKE            2850            SALES
CLARK            2450            ACCOUNTING
SCOTT            3000            RESEARCH
KING             5000            ACCOUNTING
TURNER           1500          0 SALES
ADAMS            1100            RESEARCH
JAMES             950            SALES
FORD             3000            RESEARCH
MILLER           1300            ACCOUNTING

15 rows selected.

Elapsed: 00:00:00.01

select * from v_EMP;

ENAME             SAL DNAME
---------- ---------- --------------
ALLEN            1600 SALES
WARD             1250 SALES
MARTIN           1250 SALES
BLAKE            2850 SALES
TURNER           1500 SALES
JAMES             950 SALES

6 rows selected.

Elapsed: 00:00:00.00

insert into v_emp values('tt',1230,'SALES');

select * from v_emp;

ENAME             SAL DNAME
---------- ---------- --------------
ALLEN            1600 SALES
WARD             1250 SALES
MARTIN           1250 SALES
BLAKE            2850 SALES
TURNER           1500 SALES
JAMES             950 SALES
tt               1230 SALES

7 rows selected.

Elapsed: 00:00:00.00

创建带约束的视图

create or replace view v_emp as select ename,sal,dname from e1 where dname='SALES' with check option;

select * from v_emp;

ENAME             SAL DNAME
---------- ---------- --------------
ALLEN            1600 SALES
WARD             1250 SALES
MARTIN           1250 SALES
BLAKE            2850 SALES
TURNER           1500 SALES
JAMES             950 SALES
tt               1230 SALES

7 rows selected.

Elapsed: 00:00:00.01

insert into v_emp values('aa',112,'sales');

insert into v_emp values('aa',112,'sales')
            *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation


Elapsed: 00:00:00.00

insert into v_emp values('aa',112,'SALES');

select * from v_emp;

ENAME             SAL DNAME
---------- ---------- --------------
ALLEN            1600 SALES
WARD             1250 SALES
MARTIN           1250 SALES
BLAKE            2850 SALES
TURNER           1500 SALES
JAMES             950 SALES
tt               1230 SALES
aa                112 SALES

8 rows selected.

只读视图

create or replace view v_emp as select ename,sal,dname from e1 where dname='SALES' with read only;

insert into v_emp values('aa',112,'SALES');

insert into v_emp values('aa',112,'SALES')
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a read-only view


Elapsed: 00:00:00.00

如果创建了视图，就可以从数据字典中查询到创建视图的语句

--**
select text from user_views where view_name='V_EMP';

TEXT
--------------------------------------------------------------------------------
select ename,sal,dname from e1 where dname='SALES' with read only

Elapsed: 00:00:00.02