存储过程
由过程化SQL语句书写的过程,经编译和优化后存储在数据库服务器中,使用时只要调用即可。
优点:
- 运行效率高
- 降低了客户机和服务器之间的通信量
- 存储过程在服务器注册,加快了过程的运行速度
- 加强了系统的安全性
创建存储过程
要创建存储过程,必须具有create routine 的权限。
语句:create procedure
语法格式: create procedure sp_name([proc_parameter[,...]]) [characteristic ...] routine_body
说明:
-
sp_name
为存储过程的名称,如果要调用某个特定数据 库的存储过程,则需要在前面加上该数据库的名称。 -
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,…];
注意:
- 变量名的个数必须与声明游标时使用的select语句结果集中的字段个数保持一致。第一次执行fetch语句时,fetch语句从 结果集中提取第一条记录,再次执行fetch语句时,fetch语句 从结果集中提取第二条记录,…以此类推。
- fetch语句每次从结果集中仅仅提取一条记录,因此fetch语 句需要循环语句的配合,才能实现整个结果集的遍历。fetch 离不开循环语句。一般使用Loop和while比较清楚,而且代码 简单。这里使用Loop为例,代码如下。
fetchLoop:Loop
fetch s_cursor into v_tno,v_tname;
end Loop;
- 当使用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. 关闭游标
close cursor_name;
- 关闭游标的目的在于释放游标打开时产生的结果集,以通知服务器释放游标所占用的资源,节省MySQL服务器的内存空 间。游标如果没有被明确地关闭,游标将在它被声明的begin-end语句块的末尾关闭。
- 使用声明过的游标不需要再次声明。如果不明确关闭游标, MySQL将会在到达end语句时自动关闭它。
- 在检索游标s_cursor后可用如下语句来关闭它。
close s_cursor;
触发器
触发器是一种特殊的存储过程,可以是表定义的一部分。触发器基于一个表创建,但可以针对多个表进行操作,所以触发器可以用来对表实施完整性约束。
- _当预定义的事件(如用户修改指定表或者视图中 的数据时)发生时,触发器被自动激活,从而防 止对数据进行不正确的修改。 _
认识触发器
触发器是一种特殊的存储过程,只要满足一定的条件,对数据进行insert、update和delete事件时, 数据库系统就会自动执行触发器中定义的程序语句,以进行维护数据完整性或其他一些特殊的任务。
如图所示,触发器可以分为insert、update 和delete等3类,每一类根据执行的先后顺序又可以分成before和arfter触发器。
![image.png]([object Object]&name=image.png&originHeight=307&originWidth=682&originalType=binary&ratio=1&rotation=0&showTitle=true&size=43134&status=done&style=none&taskId=u937c66ab-9003-435e-8a4e-1e79d3b77cb&title=触发器的分类&width=545.6 “触发器的分类”)
- 触发器的优点
触发器自动执行,在表的数据做了任何修改(比如手 工输入或者使用程序采集的操作)之后立即激活 。
触发器可以通过数据库中的相关表进行层叠更改。这比直接把代码写在前台的做法更安全合理。
触发器可以强制限制,这些限制比用check约束所定义 的更复杂。与check约束不同的是,触发器可以引用 其他表中的列。
定义触发器
创建触发器的语法格式如下:create trigger trigger_name trigger_time trigger_event on table_name for each row trigger_statement
说明:
- create trigger:创建触发器的关键词。触发器程序是与表有 关的数据库对象.
- table_name:触发程序的相关表。不能将触发程序与 temporary表或视图关联起来。
- trigger_time:是触发程序的动作时间。它可以是before或 after,以指明触发程序是在激活它的语句之前或之后触发。
- trigger_event:trigger_event可以是下述值之一。
insert:将新行插入表时激活触发程序。例如,通过insert、load data和replace语 句。
update:更改某一行时激活触发程序。例如,通过update语句。
delete:从表中删除某一行时激活触发程序。例如,通过delete和replace语句。
- for each row:这个声明用来指定受触发事件影响的每一行,都要激活触发器的动作。目前MySQL仅支持行级触发器,不支持语句级别的触发器(例如create table 等语句)。for each row表示更新(insert、update或者delete)操作影响的每一条记录都会执行一次触发程序。
- trigger_statement:当触发程序激活时执行的语句。如果执行多个语句,可使用begin … end复合语句结构。
- 使用触发器时,触发器执行的顺序是before触发器、表数据修改操作、after触发器。其中,before表示在触发事件发生之前执行触发程序,after表示在触发事件发 生之后执行触发器。因此严格意义上讲一个数据库表最多可以设置6种类型的触发器。
触发器的执行顺序
触发器的执行,是由触发事件激活的,并由数据库服务器自动执行
一个数据表可能定义了多个触发器,遵循如下的执行顺序:
- 执行该表上的 BEFORE 触发器
- 激活触发器的SQL语句
- 执行该表上的 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同时使用。