【MySQL复习】存储过程 游标 触发器

存储过程

由过程化SQL语句书写的过程，经编译和优化后存储在数据库服务器中，使用时只要调用即可。

优点：

1. 运行效率高
2. 降低了客户机和服务器之间的通信量
3. 存储过程在服务器注册，加快了过程的运行速度
4. 加强了系统的安全性

创建存储过程

要创建存储过程，必须具有create routine 的权限。
语句：create procedure
语法格式：
create procedure sp_name([proc_parameter[,...]]) [characteristic ...] routine_body
说明：

1. sp_name为存储过程的名称，如果要调用某个特定数据 库的存储过程，则需要在前面加上该数据库的名称。
2. parameter该存储过程使用的参数

待补充……

---

游标

使用游标处理结果集的步骤

1. 声明游标：

declare cursor_name cursor for select_statement;
例如：在student数据库中为了s表创建一个普通的游标，定义游标s_cursor的语句如下：

declar s_cursor cursor
for select S_id,S_name from s;

使用declare语句声明游标后，此时与该游标对应的select语句并没有执行，MySQL服务器内存中并不存在于select语句对应的结果集。

2. 打开游标

• 使用游标之前必须首先打开游标，语法如下：

open cursor_name;
例如 打开之前创建的游标：
open s_cursor;

• 使用open语句打开游标后，与该游标对应的select语句将被执行，MySQL服务器内存中将存放与select语句对应的结果集。
  

3. 从游标中提取数据

• 从游标中提取数据需要使用到 **fetch **语句，fetch语句的功能是获取游标当前指针的记录，并传给指定变量列表。
• 如果要提取多行数据，则需要使用循环语句去执行fetch语 句，MySQL的游标只能顺序地从开始往后读取结果集，不能 从后往前，也不能直接跳到中间的记录。
• fetch 语句的语法结构如下

fetch cursor_name into var1[,var2,…]；

注意：

1. 变量名的个数必须与声明游标时使用的select语句结果集中的字段个数保持一致。第一次执行fetch语句时，fetch语句从 结果集中提取第一条记录，再次执行fetch语句时，fetch语句 从结果集中提取第二条记录，…以此类推。
2. fetch语句每次从结果集中仅仅提取一条记录，因此fetch语 句需要循环语句的配合，才能实现整个结果集的遍历。fetch 离不开循环语句。一般使用Loop和while比较清楚，而且代码 简单。这里使用Loop为例，代码如下。

fetchLoop:Loop
fetch s_cursor into v_tno,v_tname;
end Loop;

3. 当使用fetch语句从游标中提取最后一条记录后，再次执行 fetch语句时，将产生‚ERROR 1329 (02000): No data to fetch‛ 错误信息，数据库开发人员可以针对mysql错误代码1329，自 定义错误处理程序以便结束‚结果集‛的遍历。 mysql是通过一个Error handler的声明来进行判断的。该语句 语法格式如下：

declare continue handler for not found set var=1; if var!=1…

4. 游标错误处理程序应该放在声明游标语句之后。游标通常 结合错误处理程序一起使用，用于结束结果集的访问。
   

4. 关闭游标

• close cursor_name;
• 关闭游标的目的在于释放游标打开时产生的结果集，以通知服务器释放游标所占用的资源，节省MySQL服务器的内存空 间。游标如果没有被明确地关闭，游标将在它被声明的begin-end语句块的末尾关闭。
• 使用声明过的游标不需要再次声明。如果不明确关闭游标， MySQL将会在到达end语句时自动关闭它。
• 在检索游标s_cursor后可用如下语句来关闭它。

close s_cursor;

---

触发器

触发器是一种特殊的存储过程，可以是表定义的一部分。触发器基于一个表创建，但可以针对多个表进行操作，所以触发器可以用来对表实施完整性约束。

• _当预定义的事件（如用户修改指定表或者视图中 的数据时）发生时，触发器被自动激活，从而防 止对数据进行不正确的修改。 _
  

认识触发器

触发器是一种特殊的存储过程，只要满足一定的条件，对数据进行insert、update和delete事件时， 数据库系统就会自动执行触发器中定义的程序语句，以进行维护数据完整性或其他一些特殊的任务。
如图所示，触发器可以分为insert、update 和delete等3类，每一类根据执行的先后顺序又可以分成before和arfter触发器。

• 触发器的优点

触发器自动执行，在表的数据做了任何修改（比如手 工输入或者使用程序采集的操作）之后立即激活 。
触发器可以通过数据库中的相关表进行层叠更改。这比直接把代码写在前台的做法更安全合理。
触发器可以强制限制，这些限制比用check约束所定义 的更复杂。与check约束不同的是，触发器可以引用 其他表中的列。

定义触发器

创建触发器的语法格式如下：
create trigger trigger_name trigger_time trigger_event on table_name for each row trigger_statement
说明：

1. create trigger：创建触发器的关键词。触发器程序是与表有 关的数据库对象.
2. table_name：触发程序的相关表。不能将触发程序与 temporary表或视图关联起来。
3. trigger_time：是触发程序的动作时间。它可以是before或 after，以指明触发程序是在激活它的语句之前或之后触发。
4. trigger_event：trigger_event可以是下述值之一。

insert：将新行插入表时激活触发程序。例如，通过insert、load data和replace语 句。
update：更改某一行时激活触发程序。例如，通过update语句。
delete：从表中删除某一行时激活触发程序。例如，通过delete和replace语句。

5. for each row：这个声明用来指定受触发事件影响的每一行，都要激活触发器的动作。目前MySQL仅支持行级触发器，不支持语句级别的触发器（例如create table 等语句）。for each row表示更新（insert、update或者delete）操作影响的每一条记录都会执行一次触发程序。
6. trigger_statement：当触发程序激活时执行的语句。如果执行多个语句，可使用begin … end复合语句结构。
7. 使用触发器时，触发器执行的顺序是before触发器、表数据修改操作、after触发器。其中，before表示在触发事件发生之前执行触发程序，after表示在触发事件发 生之后执行触发器。因此严格意义上讲一个数据库表最多可以设置6种类型的触发器。
   

触发器的执行顺序

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行
一个数据表可能定义了多个触发器，遵循如下的执行顺序：

1. 执行该表上的 BEFORE 触发器
2. 激活触发器的SQL语句
3. 执行该表上的 AFTER 触发器

（创建顺序或字母顺序）

old、new关键字

触发事件发生时，MySQL针对要修改数据的表，创建两个与本表结构完全一样的临时表old和new，old用于存放在数据修改过程中既有数据，new表用于存放在数据修改过程中将要更新的数据。
** 当向表插入新记录时**，在触发程序中可以利用new关键字访问新记录 ，当需要访问新记录的某个字段值时，可以使用 new.字段名的方式访问。
当从表中删除旧记录时，在触发程序中可以利用old关键字访问旧记 录，当需要访问旧记录的某个字段值时，可以使用‚old.字段名‛的 方式访问。
old记录是只读的，只能引用，不能更改。在before触发程 序中，可使用‚set new.col_name = value‛语句更改new记 录的值。
对于insert语句，只有new是合法的；对于delete语句，只有 old才合法；而update语句可以与new或old同时使用。