oracle视图

视图是一个虚拟表，其内容由查询定义，同真表一样，视图包含一系列带有名称的列和行数据。

使用scott用户创建视图权限不足的问题解决办法：

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as scott

SQL> create view myview as select * from emp where sal<1000;

create view myview as select * from emp where sal<1000

ORA-01031: 权限不足

使用system用户登录给scott用户授予创建视图的权限

SQL> conn system/change_on_install;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as system

SQL> grant create view to scott;

Grant succeeded
在切换回scott用户登录

SQL> conn scott/tiger;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as scott

创建视图

SQL> create view myview as select * from emp where sal<1000;

View created
查询创建的视图

SQL> select * from myview;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980/12/17     800.00               20
 7900 JAMES      CLERK      7698 1981/12/3      950.00               30

一个视图例子

SQL> --创建可以查询雇员工号，姓名和部门的视图
SQL> create or replace view myview as
  2  select e.empno,e.ename,d.dname
  3  from emp e,dept d
  4  where e.deptno=d.deptno;

View created

SQL> select * from myview;

EMPNO ENAME      DNAME
----- ---------- --------------
 7782 CLARK      ACCOUNTING
 7839 KING       ACCOUNTING
 7934 MILLER     ACCOUNTING
 7566 JONES      RESEARCH
 7902 FORD       RESEARCH
 7876 ADAMS      RESEARCH
 7369 SMITH      RESEARCH
 7788 SCOTT      RESEARCH
 7521 WARD       SALES
 7844 TURNER     SALES
 7499 ALLEN      SALES
 7900 JAMES      SALES
 7698 BLAKE      SALES
 7654 MARTIN     SALES

14 rows selected