Oracle视图(View)的创建使用
视图(View)其实就是一张或者多张表上的查询组合，这些表称为基表。
从视图中可以查询到自己想要的信息保存到视图中形成另外一张虚拟表表。
视图(View)其实就是一张或者多张表上的查询组合，这些表称为基表。
从视图中可以查询到自己想要的信息保存到视图中形成另外一张虚拟表表。
视图具有如下优点：
1. 限制用户只能通过视图检索数据，这样就可以对最终用户屏蔽建表时底层的基表。
2. 将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。
4. 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。
1. 限制用户只能通过视图检索数据，这样就可以对最终用户屏蔽建表时底层的基表。
2. 将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。
3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。
4. 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。
创建视图语法：
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS SELECT [WITH READ ONLY CONSTRAINT]
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. 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;
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 /
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
---------- ---------- --------- --------- ---------- --------------
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
---------- ---------- --------- --------- ---------- --------------
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;
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
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;
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
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;
-------------------------------------------------------
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;
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.
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.
Grant succeeded.
SQL>
Grant succeeded.
Grant succeeded.
SQL>
Grant succeeded.
Grant succeeded.
SQL>
Grant succeeded.
Grant succeeded.
SQL>
Grant succeeded.
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
---------- ---------- --------- --------- ---------- --------------
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
---------- ---------- --------- --------- ---------- --------------
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
DROP VIEW view_name
SQL> show user
USER is "SCOTT"
SQL> DROP VIEW EMPDETAIL;
USER is "SCOTT"
SQL> DROP VIEW EMPDETAIL;
View dropped.
删除视图不会影响基表的数据。
删除视图不会影响基表的数据。
结论：拥有查询所有表权限的用户，对表所关联创建的视图没有查询权限。如果需要查询相关视图，需要对用户单独授权。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31467250/viewspace-2141108/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
