meri oracle view_Oracle视图(View)的使用

Oracle视图(View)的创建使用

视图(View)其实就是一张或者多张表上的查询组合，这些表称为基表。

从视图中可以查询到自己想要的信息保存到视图中形成另外一张虚拟表表。

视图具有如下优点：

1. 限制用户只能通过视图检索数据，这样就可以对最终用户屏蔽建表时底层的基表。

2. 将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。

3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。

4. 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。

创建视图语法：

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name

AS SELECT  [WITH READ ONLY CONSTRAINT]

注释：

1. OR REPLACE：如果视图已经存在，则替换旧视图。

2. FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。

3. NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。

4. WITH READ ONLY：默认可以通过视图对基表执行增删改操作；但是有很多在基表上的限制：WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。

案例：

1、基于EMP表和DEPT表创建视图

SQL> conn / as sysdba

Connected.

SQL>

SQL> grant create view to scott;

Grant succeeded.

SQL> conn scott/tiger;

Connected.

SQL> CREATE OR REPLACE VIEW EMPDETAIL

2  AS

3  SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME

4  FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO

5  WITH READ ONLY

6  /

View created.

SQL> select * from EMPDETAIL;

EMPNO ENAME      JOB       HIREDATE      DEPTNO DNAME

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

7782 CLARK      MANAGER   09-JUN-81         10 ACCOUNTING

7839 KING       PRESIDENT 17-NOV-81         10 ACCOUNTING

7934 MILLER     CLERK     23-JAN-82         10 ACCOUNTING

7566 JONES      MANAGER   02-APR-81         20 RESEARCH

7902 FORD       ANALYST   03-DEC-81         20 RESEARCH

7876 ADAMS      CLERK     23-MAY-87         20 RESEARCH

7369 SMITH      CLERK     17-DEC-80         20 RESEARCH

7788 SCOTT      ANALYST   19-APR-87         20 RESEARCH

7521 WARD       SALESMAN  22-FEB-81         30 SALES

7844 TURNER     SALESMAN  08-SEP-81         30 SALES

7499 ALLEN      SALESMAN  20-FEB-81         30 SALES

EMPNO ENAME      JOB       HIREDATE      DEPTNO DNAME

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

7900 JAMES      CLERK     03-DEC-81         30 SALES

7698 BLAKE      MANAGER   01-MAY-81         30 SALES

7654 MARTIN     SALESMAN  28-SEP-81         30 SALES

14 rows selected.

2、这里我创建一个用户并赋予它查询所有表的权限。

CREATE USER TEST IDENTIFIED BY TEST

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP;

alter user TEST quota unlimited on USERS;

grant create session to TEST;

grant select any table to TEST;

3、已经给予它查询所有表的权限了，但是却不能查询scott用户创建的视图

SQL> conn test/TEST

Connected.

SQL>

SQL> SELECT * FROM SCOTT.EMPDETAIL;

SELECT * FROM SCOTT.EMPDETAIL

*

ERROR at line 1:

ORA-00942: table or view does not exist

4、赋予test查询scott用户emp以及DEPT表的权限

SQL> grant select on scott.emp to test;

Grant succeeded.

SQL> grant select on scott.dept to test;

Grant succeeded.

SQL> conn test/TEST

Connected.

SQL> SELECT * FROM SCOTT.EMPDETAIL;

SELECT * FROM SCOTT.EMPDETAIL

*

ERROR at line 1:

ORA-00942: table or view does not exist

5、任然不能查询scott创建的视图

那我就把scott下的所有表权限都给他

这两条SQL需要有DBA权限用户执行

select 'grant select on '|| tname ||' to test;' from tab;

select 'grant select on scott.' || tname || ' to rptuser;' from tab;

SQL> select 'grant select on '|| tname ||' to test;' from tab;

'GRANTSELECTON'||TNAME||'TOTEST;'

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

grant select on BIN$UA9YZyk1KxjgU2Y4qMA6VA==$0 to test;

grant select on BONUS to test;

grant select on DEPT to test;

grant select on EMP to test;

grant select on EMPDETAIL to test;

grant select on SALGRADE to test;

grant select on SYS_TEMP_FBT to test;

grant select on TEST to test;

grant select on TEST02 to test;

grant select on TEST101 to test;

10 rows selected.

6、执行上面的授权语句

SQL> grant select on BONUS to test;

Grant succeeded.

SQL> grant select on DEPT to test;

grant select on EMP to test;

grant select on EMPDETAIL to test;

grant select on SALGRADE to test;

grant select on SYS_TEMP_FBT to test;

grant select on TEST to test;

grant select on TEST101 to test;

Grant succeeded.

SQL> grant select on TEST02 to test;

Grant succeeded.

SQL>

Grant succeeded.

SQL>

Grant succeeded.

SQL>

Grant succeeded.

SQL>

Grant succeeded.

SQL>

Grant succeeded.

SQL>

Grant succeeded.

SQL> SELECT * FROM SCOTT.EMPDETAIL;

EMPNO ENAME      JOB       HIREDATE      DEPTNO DNAME

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

7782 CLARK      MANAGER   09-JUN-81         10 ACCOUNTING

7839 KING       PRESIDENT 17-NOV-81         10 ACCOUNTING

7934 MILLER     CLERK     23-JAN-82         10 ACCOUNTING

7566 JONES      MANAGER   02-APR-81         20 RESEARCH

7902 FORD       ANALYST   03-DEC-81         20 RESEARCH

7876 ADAMS      CLERK     23-MAY-87         20 RESEARCH

7369 SMITH      CLERK     17-DEC-80         20 RESEARCH

7788 SCOTT      ANALYST   19-APR-87         20 RESEARCH

7521 WARD       SALESMAN  22-FEB-81         30 SALES

7844 TURNER     SALESMAN  08-SEP-81         30 SALES

7499 ALLEN      SALESMAN  20-FEB-81         30 SALES

EMPNO ENAME      JOB       HIREDATE      DEPTNO DNAME

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

7900 JAMES      CLERK     03-DEC-81         30 SALES

7698 BLAKE      MANAGER   01-MAY-81         30 SALES

7654 MARTIN     SALESMAN  28-SEP-81         30 SALES

14 rows selected.

7、这次成功了，因为我把视图的查询权限给他了。

8、删除view视图

DROP VIEW view_name

SQL> show user

USER is "SCOTT"

SQL> DROP VIEW EMPDETAIL;

View dropped.

删除视图不会影响基表的数据。

结论：拥有查询所有表权限的用户，对表所关联创建的视图没有查询权限。如果需要查询相关视图，需要对用户单独授权。