数据库对象视图、索引、游标、触发器简介

视图

1.视图概述

1) 视图是什么：

视图是基于 SQL 语句的结果集的可视化的表（虚拟表），一个视图并不真正包含真实的数据，只是从另一个视角查看或改变表里的数据。

2) 视图的作用：

视图可以起到隐藏数据的功能，可以选择性的显示表中的字段和行（隐藏数据）

视图可以将复杂的多表查询的结果构成一个简单的表结构给用户（简化开发）

2.视图的使用

1)视图创建

CREATE OR REPLACE  VIEW 视图名 [(员工号,员工姓名,工资)] --别名列表必须和查询字段个数匹配
AS  
SELECT id, name,salary FROM emp WHERE salary>=7000 
[WITH CHECK OPTION]     --视图的DML操作才用到,满足上面的where条件(>=7000)的数据才能插入
[WITH READ ONLY] ;      --表示该视图不可执行DML操作,默认是可以进行DML操作

2）查询视图

          查询：SELECT * FROM emp_hiredate_view  WHERE "id" BETWEEN 200 AND 207； --从试图中查询员工id 200-201的员工
          查询所有存在的视图：SELECT * FROM USER_VIEWS

3）视图的DML操作

对于简单视图（涉及到一个表，不包含函数，不包含分组）可以像普通表一样进行DML操作

对于复杂视图的DML操作是有条件的：

1） DML 操作不允许违反约束。

2）在包含组函数、GROUP BY 子句、ROWNUM伪列或DISTINCT关键字的视图上不允许 DML 操作。

 

索引

1.索引概述

1）索引是什么：

索引是数据库对象，可以理解为是一本书的目录，它记录了数据在数据库中存放的位置，就像一本书的目录，记录了某一页在这本书的哪个位置。相同地，索引是一个单独的、存储在磁盘上的数据库结构是实际存在的，它们包含着对数据库表里所有记录的引用指针。

2）索引优缺点：

通过指针的形式提高查询速度，索引提供了对表中行的直接和快速的访问，

但会带来系统开销(牺牲增删改的速度换取查询的速度（DML操作在物理表的同时也会维护索引表）)

2.索引使用场景

1）适合加索引：

1. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
2. 在经常需要搜索的列上，可以加快搜索的速度； 
3. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
4. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 

2）不适合加索引

1. 对于那些在查询中很少使用或者参考的列不应该创建索引
2. 对于那些只有很少数据值的列也不应该增加索引
3. 当修改性能远远大于检索性能时，不应该创建索引

3）索引的使用

语法：

CREATE [UNIQUE] INDEX index_name

ON table_name(column_name[,column_name…])

语法解析：

1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。

2. index_name：指定索引名。

3. tabl_name：指定要为哪个表创建索引。

4. column_name：指定要对哪个列创建索引。我们也可以对多列创建索引;这种索引称为组合索引。

CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME);  --为员工名建立唯一索引

CREATE INDEX IDX_SAL ON EMP(SAL);                --为工资建立普通索引

CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));   --将JOB列转换为小写后创建索引

 

3.复合索引

1）什么是复合索引？
    索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作 复合索引。
2）复合索引作用
    利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引不同于使用两个单独的索引。

  复合索引的结构与电话簿类似，它首先按姓氏对雇员进行排序，然后按名字对所有姓氏相同的雇员进行排序。如果您知道姓氏，电话簿将非常有用，如果您知道名字和姓氏，电话簿则更为有用，但如果您只知道名字而不知道姓氏，电话簿将没有用处。所以复合索引，字段的先后顺序是很重要的。
列的顺序：在创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

游标

1.游标概述

游标是一个数据库对象，是SQL一个内存工作区，主要用于暂时存放受SQL语句影响到的所有数据，处理SQL结果集

2.游标的属性

• %rowcount        游标中包含的行数
• %found             布尔值，游标是否还有数据
• %notfount         布尔，游标是否已经没有数据
• %isopen            游标是否打开

3.游标的使用

使用过程 ：声明游标 打开游标 提取数据 关闭游标

写法一：

declare 
  CURSOR cemp is SELECT first_name,salary FROM employees;--声明游标
  pename employees.first_name%type;
  psal employees.salary%type;
begin
  open cemp; --打开游标
  loop       --提取数据
    fetch cemp into pename,psal;
    --退出条件，没有提取到数据
    exit when cemp%notfound;
    dbms_output.put_line(pename||'的薪水是'||psal);
    end loop;
    close cemp;--关闭游标
end;

写法二使用增强for循环（可以省略打开游标等过程）

declare 
  CURSOR cemp is SELECT ename,sal FROM emp; --声明游标 cemp
  begin 
  for v_temp in cemp loop                   --提取数据
    if (v_temp.sal<2000) THEN --v_temp可以不用定义
      dbms_output.put_line(v_temp.ename||'原工资：'||v_temp.sal||', 涨后'||(v_temp.sal+500));--加括号
    elsif (v_temp.sal<5000) THEN  
      dbms_output.put_line(v_temp.ename||'原工资：'||v_temp.sal||', 涨后'||(v_temp.sal+300));
    else 
          dbms_output.put_line(v_temp.ename||'原工资：'||v_temp.sal||', 涨后'||(v_temp.sal+100));
    end if;
    end loop;
end;

触发器

1.触发器概述

触发器是一个数据库对象，是一个特殊的过程，当特定的事件发生时隐式的执行。比如一个表中发生插入删除更新时会隐形的执行一些事件，在指定的时间内对一个表进行修改执行一些事件等。

2.应用场景

DDL DML 或者用户登录数据库关闭等操作时

3.触发器组成部分

触发时间：       BEFORE ,AFTER

触发事件：       INSERT,UPDATE,DELETE等操作

触发器类型：   Statement，now

触发器体：       具体操作 具体PL/SQL

4.触发器使用：

例子：当员工表的员工编号或者部门编号修改 后 触发事件 向工作历史表插入一条记录

CREATE OR REPLACE TRIGGER update_job_history
AFTER 
UPDATE OF job_id,department_id
ON employees
FOR EACH ROW
BEGIN
INSERT INTO job_history(employee_id,start_date...)VALUES();
END;

 

如有问题，多多指正！