oracle视图查询数据类型,Oracle专题9之Oracle的数据库对象-视图

创建连接视图示例：

建立用于获取部门号为10的部门号、部门名称以及员工信息。

SQL> CREATE VIEW dept_emp_view AS SELECT d.deptno, d.dname, e.empno, e.ename, e.job FROM dept d, emp e WHERE d.deptno = e.deptno AND d.deptno = 10;

视图已创建。

SQL> select * from dept_emp_view;

DEPTNO DNAME EMPNO ENAME JOB

10 ACCOUNTING 7782 CLARK MANAGER

10 ACCOUNTING 7839 KING PRESIDENT

10 ACCOUNTING 7934 MILLER CLERK

只读视图示例：建立查看10号部门员工信息的视图。

SQL> CREATE VIEW emp_view3 AS select * from emp where deptno = 10 with read only;

视图已创建。

SQL> update emp_view3 set sal = sal + 50;

update emp_view3 set sal = sal + 50

第 1 行出现错误:

ORA-42399: 无法对只读视图执行 DML 操作

2、Oracle视图的查询修改与删除

a、视图上的DML操作

查询视图-select，如：select * from emp_view;

添加数据-insert，如：insert into empnew_view(empno, ename, sal) values(8888, 'LAYNA', 6666);

修改数据-update，如：update emp_view set sal = sal + 100 where empno = 8888;

删除数据-delete，如：delete from emp_view where empno = 8888;

注意：针对视图的更新操作(insert、update、delete)实际上改变的是基表中的数据。

b、在创建视图时定义check约束

WITH CHECK OPTION选项用于在视图上定义CHECK约束

示例：CHREATE VIEW emp_view4 AS SELECT * FROM emp_view WHERE deptno = 20 WITH CHECK OPTION chk_view;

SQL> create view dept_emp_view2 as select * from dept_emp_view where deptno = 10 with check option constraint ck_view;

视图已创建。

SQL> select * from dept_emp_view2;

DEPTNO DNAME EMPNO ENAME JOB

10 ACCOUNTING 7782 CLARK MANAGER

10 ACCOUNTING 7839 KING PRESIDENT

10 ACCOUNTING 7934 MILLER CLERK

SQL> update dept_emp_view set deptno = 20 where empno = 7782;

update dept_emp_view set deptno = 20 where empno = 7782

第 1 行出现错误:

ORA-01779: 无法修改与非键值保存表对应的列

check约束的作用是在添加或者修改数据时数据必须满足with check option前的where子句的条件。如上例：修改和添加数据时deptno 的值必须为10。

c、修改视图

使用CREATE OR REPLACE VIEW 子句修改视图。

示例：CREATE OR REPLACE VIEW emp_view AS SELECT * FROM emp WHERE job = 'SALESMAN';

SQL> CREATE OR REPLACE VIEW emp_view AS SELECT * FROM emp WHERE job = 'SALESMAN';

视图已创建。

d、删除视图

当视图不再需要时，用户可以执行DROP view语句删除视图。(不会删除基表)

SQL> DROP VIEW emp_view;

视图已删除。

SQL> select * from emp_view;

select * from emp_view

第 1 行出现错误:

ORA-00942: 表或视图不存在

3、Oracle的复杂视图

a、什么是复杂视图？

复杂视图是指包含函数、表达式或者分组数据的视图，它主要用于执行查询操作。

注意：当定义复杂视图时，必须要为函数或者表达式定义列的别名。

b、创建复杂视图

示例：创建用于获得每个岗位平均工资、工资总和、最高工资和最低工资的视图。

SQL> create VIEW job_view(job, avgsal, sumsal, maxsal, minsal) AS SELECT job, avg(sal), sum(sal), max(sal), min(sal) FROM emp GROUP BY job;

视图已创建。

SQL> select * from job_view;

JOB AVGSAL SUMSAL MAXSAL MINSAL

CLERK 1037.5 4150 1300 800

SALESMAN 1400 5600 1600 1250

PRESIDENT 5000 5000 5000 5000

MANAGER 2758.33333 8275 2975 2450

ANALYST 3000 9000 3000 3000

c、复杂视图上执行DML操作的原则

DELETE操作原则，视图中包含以下元素之一不能执行delete操作：

group by子句、分组函数、distinct关键字、rownum伪列。

UPDATE操作原则，视图中包含以下元素之一不能执行update操作：

group by子句、分组函数、dinstinct关键字、rownum伪列、使用表达式定义的列。

INSERT操作原则，视图中包含以下元素之一不能执行insert操作：

group by子句、分组函数、dinstinct关键字、rownum伪列、使用表达式定义的列、视图上没有包含基表的NOT NULL列。