1.存储过程(procedure)
MySQL 存储过程是从 MySQL 5.0 开始增加的新功能。存储过程的优点有一箩筐。不过最主要的还是执行效率和SQL 代码封装。特别是 SQL 代码封装功能,如果没有存储过程,在外部程序访问数据库时(例如 PHP),要组织很多 SQL 语句。特别是业务逻辑复杂的时候,一大堆的 SQL 和条件夹杂在 PHP 代码中,让人不寒而栗。现在有了 MySQL 存储过程,业务逻辑可以封装存储过程中,这样不仅容易维护,而且执行效率也高。
创建 MySQL 存储过程的简单语法为:
create procedure procedure_name
(
[in|out|inout] 参数 datatype
)
begin
MySQL 语句;
end;
一个存储过程的例子:向mapping中插入一条记录并返回记录的总和
mysql> drop procedure if exists mappingProc;
-> create procedure mappingProc(out cnt int)
-> begin
-> declare maxid int;
-> select max(cFieldID)+1 into maxid from mapping;
-> insert into mapping(cFieldID,cFieldName) values(maxid,'hello');
-> select count(cFieldID) into cnt from mapping;
-> end
-> //
2.游标
游标是从数据表中提取出来的数据,以临时表的形式存放在内存中,在游标中有一个数据指针,在初始状态下指向的是首记录,利用fetch语句可以移动该指针,从而对游标中的数据进行各种操作,然后将操作结果写回数据表中。
游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录,并赋给主变量,交由主语言进一步处理。主语言是面向记录的,一组主变量一次只能存放一条记录。仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求。嵌入式SQL引入了游标的概念,用来协调这两种不同的处理方式。在数据库开发过程中,当你检索的数据只是一条记录时,你所编写的事务语句代码往往使用SELECT INSERT 语句。但是我们常常会遇到这样情况,即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢?游标为我们提供了一种极为优秀的解决方案。
- 定义游标
DECLARE cursor_name CURSOR FOR SELECT_statement;
- 打开游标
OPEN cursor_name;
-
获取游标。获得多行数据,使用循环语句去执行FETCH
FETCH cursor_name INTO variable list;
-
关闭游标
CLOSE cursor_name ;
注意:mysql的游标是向前只读的,也就是说,你只能顺序地从开始往后读取结果集,不能从后往前,也不能直接跳到中间的记录.
一个例子:把从数据库查询出来的所有name链接起来。
/*初始化*/
drop procedure if exists useCursor //
/*建立 存储过程 create */
CREATE PROCEDURE useCursor()
BEGIN
/*局部变量的定义 declare*/
declare tmpName varchar(20) default '' ;
declare allName varchar(255) default '' ;
declare cur1 CURSOR FOR SELECT name FROM test.level ;
/* mysql 不知道为什么用异常加入判断 ? * 此请参考官方文档 20.2.11. 光标 光标 * 这把 游标 异常后 捕捉 * 并设置 循环使用 变量 tmpname 为 null 跳出循环。 */
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
/*开游标*/
OPEN cur1;
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
/* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */
WHILE ( tmpname is not null) DO
set tmpName = CONCAT(tmpName ,";") ;
set allName = CONCAT(allName ,tmpName) ;
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
END WHILE;
CLOSE cur1;
select allName ;
END;
//
call useCursor()
//
/*初始化*/
drop procedure if exists useCursor //
/*建立 存储过程 create */
CREATE PROCEDURE useCursor()
BEGIN
/*局部变量的定义 declare*/
declare tmpName varchar(20) default '' ;
declare allName varchar(255) default '' ;
declare cur1 CURSOR FOR SELECT name FROM test.level ;
/* mysql 不知道为什么用异常加入判断 ? * 此请参考官方文档 20.2.11. 光标 光标 * 这把 游标 异常后 捕捉 * 并设置 循环使用 变量 tmpname 为 null 跳出循环。 */
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;
/*开游标*/
OPEN cur1;
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
/* 循环体 这很明显 把游标查询出的 name 都加起并用 ; 号隔开 */
WHILE ( tmpname is not null) DO
set tmpName = CONCAT(tmpName ,";") ;
set allName = CONCAT(allName ,tmpName) ;
/*游标向下走一步*/
FETCH cur1 INTO tmpName;
END WHILE;
CLOSE cur1;
select allName ;
END;
//
call useCursor()
//
3.触发器
触发器是特定事件出现的时候,自动执行的代码块。类似于存储过程,触发器与存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的。
CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_stmt END;
trigger_name:触发器的名字,我常用的命名规则t_name_tableName_(b|a)(i|u|d),t:触发器标识,name:英文名,tableName:表名,b(BEFORE):标识是触发事件之前,a(AFTER):标识触发事件之后,i(insert):标识insert事件,u(update):标识update事件,d(delete):标识delete事件;
trigger_time:触发时间(BEFORE或AFTER)
trigger_event:事件名(insert或update或delete)
tbl_name:表名(必须是永久性表)
trigger_stmt:执行语句(可以是复合语名),使用别名OLD和NEW,能够引用与触发程序相关的表中的列。
CREATE TRIGGER t22_biBEFORE INSERT ON t22FOR EACH ROWBEGINSET @x = 'Trigger was activated!';SET NEW.s1 = 55;END;//
在表t22中,在插入一条数据之前,显示'Trigger was activated!',并且将插入的这条数据的s1值设为55.