本文详细介绍了Oracle视图的概念、优点、创建语法，以及如何描述、查询和修改视图。强调视图是逻辑上的数据集合，用于简化多表查询并提供一定的数据安全性。同时，指出了视图在DML操作上的限制，并提供了示例来展示如何操作视图。
什么是视图
视图：从表中抽出的逻辑上相关的数据集合。
视图是一个虚表
视图建立在已有表的基础上，视图赖以建立的这些表称为基表。
向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句。
视图向用户提供基表数据的另一种形式。
视图的优点
隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系, 否则容易写错; 如果基于这样的查询语句创建一个视图, 用户就可以直接对这个视图进行"简单查询"而获得结果. 这样就隐藏了数据的复杂性并简化了查询语句。
提供某些安全性保证. 视图提供了一种可以控制的方式, 即可以让不同的用户看见不同的列, 而不允许访问那些敏感的列, 这样就可以保证敏感数据不被用户看见；
视图不能提高性能，视图很多时候是为了屏蔽逻辑上的复杂。
创建视图
使用下面语法格式创建视图
-
FORCE: 子查询不一定存在
NOFORCE: 子查询存在(默认)
WITH READ ONLY:只能做查询操作
子查询可以是复杂的SELECT语句
创建视图举例
查询部门编号为10和30的部门及雇员信息
SQL> CREATE VIEW vw_dept_emp AS SELECT 2 a.deptno, 3 a.dname, 4 a.loc, 5 b.empno, 6 b.ename, 7 b.sal 8 FROM 9 dept a, 10 emp b 11 WHERE 12 a.deptno = b.deptno 13 AND a.deptno IN ( 10, 30 );View created.SQL>描述视图结构
SQL> desc vw_dept_emp Name Null? Type ----------------------------------------- -------- ---------------------------- DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) SAL NUMBER(7,2)SQL>查询视图
SQL> select * from vw_dept_emp; DEPTNO DNAME LOC EMPNO ENAME SAL---------- -------------- ------------- ---------- ---------- ---------- 30 SALES CHICAGO 7499 ALLEN 1600 30 SALES CHICAGO 7521 WARD 1250 30 SALES CHICAGO 7654 MARTIN 1250 30 SALES CHICAGO 7698 BLAKE 2850 10 ACCOUNTING NEW YORK 7782 CLARK 2450 10 ACCOUNTING NEW YORK 7839 KING 5000 30 SALES CHICAGO 7844 TURNER 1500 30 SALES CHICAGO 7900 JAMES 950 10 ACCOUNTING NEW YORK 7934 MILLER 13009 rows selected.SQL>简单和复杂视图
注意：不建议通过视图对表进行修改
修改视图
使用CREATE OR REPLACE VIEW 子句 修改视图
SQL> edWrote file afiedt.buf 1 CREATE 2 OR REPLACE VIEW vw_dept_emp ( id, name, loc, sal ) AS SELECT 3 a.deptno || ' ' || b.empno, 4 a.dname || ' ' || b.ename, 5 a.loc, 6 b.sal 7 FROM 8 dept a, 9 emp b 10 WHERE 11 a.deptno = b.deptno 12* AND a.deptno IN ( 10, 30 )SQL> /View created.SQL> desc vw_dept_emp Name Null? Type ----------------------------------------- -------- ---------------------------- ID VARCHAR2(81) NAME VARCHAR2(25) LOC VARCHAR2(13) SAL NUMBER(7,2)SQL> select * from vw_dept_emp;ID NAME LOC SAL---------- ------------------------- ------------- --------30 7499 SALES ALLEN CHICAGO 160030 7521 SALES WARD CHICAGO 125030 7654 SALES MARTIN CHICAGO 125030 7698 SALES BLAKE CHICAGO 285010 7782 ACCOUNTING CLARK NEW YORK 245010 7839 ACCOUNTING KING NEW YORK 500030 7844 SALES TURNER CHICAGO 150030 7900 SALES JAMES CHICAGO 95010 7934 ACCOUNTING MILLER NEW YORK 13009 rows selected.SQL>创建复杂的视图
接下来我们用oracle自带的hr实例演示。
tips：使用管理者system身份登录，登陆成功后输入 alter user hr identified by hr(默认密码为hr) account unlock;就可以用了；
SQL> alter user hr identified by hr account unlock;User altered.SQL> conn hr/hrConnected.SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------COUNTRIES TABLEDEPARTMENTS TABLEEMPLOYEES TABLEEMP_DETAILS_VIEW VIEWJOBS TABLEJOB_HISTORY TABLELOCATIONS TABLEREGIONS TABLE8 rows selected.SQL>复杂视图举例：查询各个部门的最低工资，最高工资，平均工资
SQL> edWrote file afiedt.buf 1 CREATE VIEW dept_sum_vu ( name, minsal, maxsal, avgsal ) AS SELECT 2 d.DEPARTMENT_NAME, 3 MIN( e.SALARY ), 4 MAX( e.SALARY ), 5 AVG( e.SALARY ) 6 FROM 7 EMPLOYEES e, 8 DEPARTMENTS d 9 WHERE 10 e.DEPARTMENT_ID = d.DEPARTMENT_ID 11 GROUP BY 12* d.DEPARTMENT_NAMESQL> /View created.SQL> desc dept_sum_vu Name Null? Type ----------------------------------------- -------- ---------------------------- NAME NOT NULL VARCHAR2(30) MINSAL NUMBER MAXSAL NUMBER AVGSAL NUMBERSQL> select * from dept_sum_vu;NAME MINSAL MAXSAL AVGSAL------------------------------ ---------- ---------- ----------Administration 4400 4400 4400Accounting 8300 12008 10154Purchasing 2500 11000 4150Human Resources 6500 6500 6500IT 4200 9000 5760Public Relations 10000 10000 10000Executive 17000 24000 19333.3333Shipping 2100 8200 3475.55556Sales 6100 14000 8955.88235Finance 6900 12008 8601.33333Marketing 6000 13000 950011 rows selected.SQL>视图中使用DML的规定
可以在简单的视图中执行DML操作
当视图中定义包含以下元素之一时不能使用delete：
组函数
GROUP BY子句
DISTINCT关键字
ROWNUM伪列
当视图中定义包含以下元素之一时不能使用update：
组函数
GROUP BY子句
DISTINCT关键字
ROWNUM伪列
列的定义为表达式
当视图中定义包含以下元素之一时不能使用insert：
组函数
GROUP BY子句
DISTINCT关键字
ROWNUM伪列
列的定义为表达式
表中的非空的列在视图定义中未包含
SQL> delete from dept_sum_vu where MINSAL=4400;delete from dept_sum_vu where MINSAL=4400 *ERROR at line 1:ORA-01732: data manipulation operation not legal on this viewSQL> update dept_sum_vu set MINSAL=3300 where MINSAL=4400;update dept_sum_vu set MINSAL=3300 where MINSAL=4400 *ERROR at line 1:ORA-01732: data manipulation operation not legal on this viewSQL>屏蔽DML操作
可以使用WITH READ ONLY 选项屏蔽对视图的DML操作
任何DML操作都会返回一个Oracle server错误
SQL> edWrote file afiedt.buf 1 CREATE VIEW empvu11 ( employee_number, employee_name, job_title ) AS SELECT 2 e.EMPLOYEE_ID, 3 e.LAST_NAME, 4 e.JOB_ID 5 FROM 6 EMPLOYEES e 7 WHERE 8 e.DEPARTMENT_ID = 10 9* with read onlySQL> /View created.SQL> select * from empvu11;EMPLOYEE_NUMBER EMPLOYEE_NAME JOB_TITLE--------------- ------------------------- ---------- 200 Whalen AD_ASSTSQL> delete * from empvu11;delete * from empvu11 *ERROR at line 1:ORA-00903: invalid table nameSQL> edWrote file afiedt.buf 1* delete from empvu11SQL> /delete from empvu11 *ERROR at line 1:ORA-42399: cannot perform a DML operation on a read-only viewSQL>删除视图
删除视图只是删除视图的定义，并不会删除基表的数据
SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------COUNTRIES TABLEDEPARTMENTS TABLEDEPT_SUM_VU VIEWEMPLOYEES TABLEEMPVU10 VIEWEMPVU11 VIEWEMP_DETAILS_VIEW VIEWJOBS TABLEJOB_HISTORY TABLELOCATIONS TABLEREGIONS TABLE11 rows selected.SQL> drop view DEPT_SUM_VU;View dropped.SQL> drop view EMPVU10;View dropped.SQL> drop view EMPVU11;View dropped.SQL> select * from tab;TNAME TABTYPE CLUSTERID------------------------------ ------- ----------COUNTRIES TABLEDEPARTMENTS TABLEEMPLOYEES TABLEEMP_DETAILS_VIEW VIEWJOBS TABLEJOB_HISTORY TABLELOCATIONS TABLEREGIONS TABLE8 rows selected.SQL>
扫一扫
5174
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
