数据库通关路之存储-游标-视图-索引 数据库技术（五）

子程序

子程序包括存储过程、自定义函数、游标、触发器。可以被编译和存储在数据库中，它具有模块化、重用性、可维护性、可扩展性、安全性等特点。其目的是完成特定的功能，能被程序和客户端工具直接调用。子程序也属于数据库对象，可以被授权能否执行。

创建存储过程

create procedure 存储过程名([IN|OUT|INOUT] 参数名 数据类型)
begin
……
end

对存储过程进行参数定义时，多个参数用 ， 分割，共有三种参数类型：IN，OUT，INOUT：

• IN:参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不会影响调用环境的数据值；
• OUT:该值可在存储过程内部被改变，同时引起调用环境中数据值的改变；
• INOUT:调用时指定，兼具 IN 和 OUT 类型参数的特点。

以 begin 和 end 对过程体的开始和结束进行标识。

需要强调一点，MySQL 中存储过程默认以 ； 结束符，如果不改变结束符，编译器会把存储过程当成 SQL 语句进行处理，因此编译过程会报错。所以要事先用 DELIMITER // 声明当前的分隔符，其目的是让编译器把两个 // 之间的内容当作一个存储过程，使用 DELIMITER ; 则恢复结束符为 ; 。

删除存储过程

删除存储过程使用 drop procedure 语句

drop procedure 存储过程名;

删除存储过程 demo2

drop procedure demo2//

自定义函数

创建自定义函数使用 create function 语句

create function 函数名([变量名1 变量类型1，……，变量名n 变量类型n]) returns 数据类型
begin
sql语句;
   return 值;
end;

删除自定义函数

删除自定义函数使用drop function语句

drop function 函数名;

需要注意，删除自定义函数时，函数名后面不能加括号，如下所示：

drop function dateDemo;

游标

游标（CURSOR）是一个存储在 MySQL 服务器上面的数据库查询机制，类似于数组的下标。使用游标后，可以逐步提取查询结果。

使用游标需要注意以下几点：

1. 声明游标之后，必须先打开游标才能使用；
2. 在游标结束之后，要关闭游标

声明游标

declare 游标名 cursor for select_statement

打开游标

open 游标名

从游标中取值，使用 fetch 进行取值

fetch 游标名 into var1,var2,……

关闭游标

close 游标名

触发器

触发器（TRIGGER）是一种特殊的存储过程，它在插入、修改或删除表中的数据时触发执行，拥有更精细、更复杂的数据控制能力。MySQL 从 5.0 版本开始支持触发器

创建触发器

create trigger trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_statement

• trigger_name：触发器名称，自己定义；
• trigger_time：触发时机，只有两个值，before（某事件之前），after（某事件之后）；
• trigger_event：触发事件，取值 INSERT（插入）、UPDATE（更新）、DELETE（删除）；
• table_name：需要建立触发器的表名；
• trigger_statement：触发器程序体，一条 SQL 语句或存储过程等；

视图

视图是从一个或多个表中糅合出来的虚拟表。一个视图并不包含真实的数据，它提供了另一个视角去查看或改变表中的数据。

打个比喻：把视图想象成一扇窗户，通过窗户往里看，我们只能看到一部分，而这部分就是数据库系统允许你看到的数据。而不允许你看到的内容会被遮挡住，让你不可见。

为什么要用视图

使用视图可以提高我们对数据的操作效率，同时增加安全性：

• 提高效率：将经常使用复杂查询定义为视图，由于对视图的权限、语法解析都会被存储，就避免了重复解析；
• 增加数据安全性：通过视图，用户只能查询和更改指定的数据；
• 提高表的逻辑独立性：看到的视图可能来源于一张表或多张表的局部，屏蔽了原有表结构变化带来的影响。

总之，使用视图的主要作用就是保障数据的安全性，同时提高查询效率。

查询视图

select * from city_view;

删除视图数据

对视图执行 DML 操作有诸多限制。

• 用户必须有插入数据的权限；

• 由于视图有可能只引用了表中的部分字段，在通过视图进行插入数据时，只能给出视图中出现的字段值。而对于基表中有却没出现在视图中的字段，必须满足以下条件之一：

  1. 该字段允许空值；
  2. 该字段有默认值；
  3. 该字段是主键，且可自动填充数据；
  4. 该字段的数据类型为 timestamp 或 uniqueidentifier（这两种数据类型都属于自动生成的二进制数据）。

• 视图中不能包含多个字段值的计算组合，或者包含统计函数的结果；

• 视图中不能包含 DISTINCT 或 GROUP BY 子句；

• 如果视图的基表是多张表，不能通过视图往基表插入数据和修改数据。

索引

索引在 MySQL 中又叫作 键 ，英文名 key ，是存储引擎用于快速找到记录的一种数据结构。索引对于性能的提升非常关键，尤其是当表中的数据量越来越庞大的时候。我们前面介绍约束使用到的 primary key ，其实就是一种索引，叫做主键索引。

举个简单的例子：我们把数据库比作汉语字典，那么索引就是这本字典的音序表，通过音序表可以快速查找到需要的汉字。索引的目的就是为了提高查询效率。

在 MySQL 中常用的索引可以分为三类，分别是：普通索引、唯一索引、联合索引。

普通索引

普通索引使用关键字 INDEX 定义，根据建立索引的时机不同，书写方式有细微差别。分为以下 3 种情况：

• 创建表的时候创建索引；
• 创建表后创建索引；
• 修改表的时候添加索引。

唯一索引

唯一索引不仅加速查找，还具有约束性。

• 主键索引 primary key
• 唯一键索引 unique

联合索引

联合索引即为索引同时设置多个字段。

• primary key(id,name)，联合主键索引
• index(id,name)，联合普通索引