Oracle视图及索引

向右看齐 2022-03-30 05:39 311阅读 0赞

一.概念

视图以经过定制的方式显示包含在一个或多个表(或其它视图)中的数据.视图获取查询的输出结果,并将其作为表来处理.因此可以将视图视为”已存储的查询”或”虚拟表”.创建视图所依据的表称为”基表”..  
视图的优点:  
1.提供了另外一种级别的表安全性  
2.隐藏的数据的复杂性  
3.简化的用户的SQL命令  
4.隔离基表结构的改变  
5.通过重命名列，从另一个角度提供数据

二.语法

Create \[or replace\] \[force | noforce\] view 视图名  
\[(alias \[,alias\]……)\]  
As select语句  
\[with check option \[constraint constraint\]\]  
\[with read only\];  
注:  
or replace 如果视图已存在,引选项将重新创建该视图.  
Force 如果使用了此关键字,则无论基表是否存在,都将创建视图  
Noforce 这是默认值,如果使用此关键字,则仅当基表存在时才创建视图.  
Alias 指定由视图的查询所选择的表达式或列的别名.别名的数目必须与视图所选择的表达式的数目相匹配  
with check option此选项指定只能插入或更新视图可以访问的行.术语constraint表示为check option约束指定的名称.如果视图中的select语句加入了where条件来显示特定的行.如果指定with check option.使用update更改where条件下的列,视图将会报错.如果不指定with check option视图不会报错,但再用此视图将不会在显示出查询结果,因为基表中已没有where条件下的特定行.  
with read only 此选项确保不能在此视图上执行任何修改操作.如果没有此选项,在视图上进行的修改操作只能对视图中所带的键保留表进行修改操作.  
在select语句中使用order by,结果集会按指定的顺序排列行.在查询视图中可以不再使用order by子句  
在视图中可以使用单行函数(由数字,字符,日期组成),分组函数和表达式.

在视图上使用insert delete update语句的限制:  
1. 在视图中使用DML语句只能修改一个底层的基表.  
2. 如果对记录的修改违反了基表的约束条件,则无法更新视图.  
3. 如果创建的视图包含连接运算符,DISTINCT运算符,集合运算符,聚合函数和group by子句,则不无对视图更新.  
4. 如果创建的视图含伪列或表达式,则将无法更新视图.

删除视图语法:  
 Drop view 视图名

三.实例

--使用sys管理权限给soctt用户授权创建试图的权限
    grant create view to scott;
    --使用sys管理权限撤销scott用户创建视图的权限
    revoke create view from scott;
    --创建单表视图 v_emp_2
    select * from t_emp;
    create view v_emp_2 as select empno,ename,job,sal from t_emp;
    
    --查询视图
    select*from v_emp_2;
    --更新视图
    update t_emp set ename='张三' where empno=7369;
    commit;
    --增加视图数据
    insert into v_emp_2 values(1111,'李四','工人',44);
    commit;
    --按条件创建视图
    create view v_emp_4 as select empno,ename,job,sal，deptno from t_emp where deptno=10;
    --查询视图
    select*from v_emp_4;
    
    --创建只读视图with read only(无法执行DML操作)
    create or replace view v_emp_3 as select * from t_emp where deptno=20 with read only;
    select*from v_emp_3;
    update v_emp_3 set ename='张三' where empno=7369;
    
    ----创建检查约束视图with check option 
    create or replace view v_emp_3 as select*from t_emp where deptno=10 with check option;
     --插入一条记录部门编号为10的记录（可以执行，符合约束条件）
        select * from tsm_emp;
        insert into v_emp_3 values(2222,'为你','java',
        7499,sysdate,10000,100,10);
        commit;
        --插入一条记录部门编号为20的记录(不能执行，违反约束条件)
        insert into v_emp_3 values(3333,'索拉卡','java',
        7499,sysdate,10000,100,20);
        commit;
        --修改视图 部门修改（不能执行，违反约束条件）
        update v_emp_3 set deptno='123' where deptno='20'
        
        select * from v_emp_3;
    --创建联表视图
     create or replace view v_emp_3 as
      select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname 
      from emp e,dept d where e.deptno=d.deptno
       
      select * from v_emp_3;