MySQL基础4——触发器，存储过程，游标

存储过程

定义

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL

语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带

有参数）来调用执行它。

存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不

同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中

的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

特点

• 能完成较复杂的判断和运算 有限的编程

• 可编程行强，灵活

• SQL编程的代码可重复使用

• 执行的速度相对快一些

• 减少网络之间的数据传输，节省开销

语法

CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数 据类型…]]) [特性 ...] 过程体

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。

MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型 IN , OUT , INOUT 。

IN ：参数的值必须在调用存储过程时指定，0在存储过程中修改该参数的值不能被返回，可以设

置默认值

OUT ：该值可在存储过程内部被改变，并可返回

INOUT ：调用时指定，并且可被改变和返回

过程体的开始与结束使用 BEGIN 与 END 进行标识。

案例

DELIMITER // 
CREATE PROCEDURE proc_test1() 
BEGIN 
	SELECT current_time(); 
	SELECT current_date(); 
END 
//
DELIMITER ; 
call proc_test1();

IN

DELIMITER // 
CREATE PROCEDURE proc_in_param (IN p_in INT) 
BEGIN
	SELECTp_in ; 
	SET p_in = 2 ; 
	SELECT p_in ; 
	END ;// 
DELIMITER ; 

-- 调用 
SET @p_in = 1;
CALL proc_in_param (@p_in); 
-- p_in虽然在存储过程中被修改，但并不影响@p_id的值 
SELECT @p_in;=1

OUT

DELIMITER // 
	CREATE PROCEDURE proc_out_param(OUT p_out int) 
		BEGIN 
			SELECT p_out; 
			SET p_out=2; 
			SELECT p_out; 
		END; 
//
DELIMITER ; 
-- 调用 
SET @p_out=1; 
CALL proc_out_param(@p_out); 
SELECT @p_out; -- 2

INTOUT

DELIMITER //
CREATE PROCEDURE proc_inout_param(INOUT p_inout int) 
BEGIN 
	SELECT p_inout; 
	SET p_inout=2; 
	SELECT p_inout;
    END; 
 //
 DELIMITER ; #调用 
 SET @p_inout=1; 
 CALL proc_inout_param(@p_inout) ;
 SELECT @p_inout; -- 2

游标

游标是针对行操作的，对从数据库中 select 查询得到的结果集的每一行可以进行分开的独立的相

同或者不相同的操作。

对于取出多行数据集，需要针对每行操作；可以使用游标；游标常用于存储过程、函数、触发器、

事件；

游标相当于迭代器

定义

DECLARE cursor_name CURSOR FOR select_statement;

打开游标

OPEN cursor_name;

取游标数据

FETCH cursor_name INTO var_name[,var_name,......]

关闭游标

CLOSE curso_name;

释放

DEALLOCATE cursor_name;

设置游标结束标志

DECLARE done INT DEFAULT 0; 
DECLARE CONTINUE HANDLER FOR NOT FOUND 
SET done = 1; -- done 为标记为

案例

CREATE PROCEDURE proc_while ( 
    IN age_in INT,
    OUT total_out INT 
)
BEGIN 
-- 创建 用于接收游标值的变量 
DECLARE p_id,p_age,p_total INT ; 
DECLARE p_sex TINYINT ;
-- 注意:接收游标值为中文时,需要给变量 指定字符集utf8 
DECLARE p_name VARCHAR (32) CHARACTER SET utf8 ; -- 游标结束的标志 
DECLARE done INT DEFAULT 0 ; -- 声明游标 
DECLARE cur_teacher CURSOR FOR SELECT 
	teacher_id, 
	teacher_name, 
	teacher_sex, 
	teacher_age 
FROM
	teacher 
WHERE
	teacher_age > age_in ; -- 指定游标循环结束时的返回值 
DECLARE CONTINUE HANDLER FOR NOT found 
SET done = 1 ; -- 打开游标
OPEN cur_teacher ; -- 初始化 变量 
SET p_total = 0 ; -- while 循环 
WHILE done != 1 DO 
	FETCH cur_teacher INTO p_id, 
	p_name, 
	p_sex, 
	p_age ; 
IF done != 1 THEN 
SET p_total = p_total + 1 ; 
END 
IF ; 
END WHILE ; -- 关闭游标 
CLOSE cur_teacher ; -- 将累计的结果复制给输出参数 
SET total_out = p_total ; 
END// 
delimiter ; 
-- 调用 
SET @p_age =20; 
CALL proc_while(@p_age, @total); 
SELECT @total;