指组合在一起的一堆SQL语句，用于简化数据处理流程。
可以将某个常用功能对应的一些语句封装成一个存储程序，之后只需要调用这个存储程序就可以完成这个常用功能，从而免去每次都要写好多语句的麻烦。
存储程序包含以下分类：
<?xml version="1.0" encoding="utf-8"?>
<map version="1.0.1">
<node ID="aiRrV41m" TEXT="存储程序">
<node ID="tzxlYvKW" TEXT="存储例程" POSITION="right">
<node ID="rO72tK0L" TEXT="存储函数" POSITION="right"></node>
<node ID="K0e1zLOZ" TEXT="存储过程" POSITION="right"></node>
</node>
<node ID="7U20o0J9" TEXT="触发器" POSITION="right"></node>
<node ID="ETPVzxlF" TEXT="事件" POSITION="right"></node>
</node>
</map> |
用户自定义变量
SET @a = 1; SELECT @a; SET @a = 2; SET @b = @a; SET @a = (SELECT m1 FROM t1 LIMIT 1); SELECT n1 FROM t1 LIMIT 1 INTO @b; SELECT m1, n1 FROM t1 LIMIT 1 INTO @a, @b; |
如果定义变量时没有加@符号，则MySQL会把这个变量当成系统变量来对待。 |
存储函数
创建存储函数
格式如下：
DELIMITER $ # 将MySQL客户端语句分隔符替换为$
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
函数体内容
END
DELIMITER ; # 恢复MySQL客户端语句分隔符为; |
比如：
DELIMITER $
CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END
DELIMITER ; |
调用上面的存储函数的语句如下：
SELECT avg_score('MySQL是怎样运行的'); |
如果创建存储函数的过程中报如下错误： ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) 则可以先执行下面的语句，然后再尝试： SET global log_bin_trust_function_creators=TRUE; |
查看和删除存储函数
查看已定义的存储函数：
SHOW FUNCTION STATUS [LIKE 需要匹配的函数名]; |
查看某个存储函数的具体定义：
SHOW CREATE FUNCTION 函数名\G |
删除存储函数：
DROP FUNCTION 函数名 |
函数体的定义
定义局部变量
DECLARE 变量名1, 变量名2, ...数据类型 [DEFAULT 默认值]; |
示例：
DELIMITER $
CREATE FUNCTION var_default_demo()
RETURN INT
BEGIN
DECLARE c INT DEFAULT 1;
RETURN c;
END $
DELIMITER ; |
使用自定义变量
DELIMITER $
CREATE FUNCTION user_defined_var_demo()
RETURN INT
BEGIN
SET @abc = 10;
RETURN @abc;
END $
DELIMITER ; |
注意自定义变量和局部变量的区别，局部变量只在当前作用域有效，而自定义变量可以在函数体外生效，相当于全局变量，如下：
|
存储函数的参数
定义存储函数时可以指定多个参数，每个参数都要指定对应的数据类型，像这样：
参数名 数据类型
示例：
CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS INT
BEGIN
RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END |
判断语句
IF 表达式 THEN
语句列表
[ELSEIF 表达式 THEN 语句列表]
... # 这里可以有多个ELSEIF语句
[ELSE 语句列表]
END IF; |
示例：
DELIMITER $ CREATE FUNCTION condition_demo(i INT) RETURNS VARCHAR(10) BEGIN DECLARE result VARCHAR(10); IF i = 1 THEN SET result = '结果是1'; ELSEIF i = 2 THEN SET result = '结果是2'; ELSEIF i = 3 THEN SET result = '结果是3'; ELSE SET result = '非法参数'; END IF; RETURN result; END $ DELIMITER ; |
循环语句
WHILE循环：
WHILE 表达式 DO 语句列表 END WHILE; |
REPEAT循环：
REPEAT 语句列表 UNTIL 表达式 END REPEAT; |
REPEAT循环是先执行语句列表，再判断表达式是否为真，如果为真则退出循环，否则继续执行语句。 REPEAT循环至少执行次，WHILE循环可能一次都不执行。 |
LOOP循环：
LOOP 语句列表 END LOOP; |
LOOP循环没有终止条件，它的终止要靠RETURN语句或是LEAVE语句，如下：
其实也可以在BEGIN ... END、REPEAT和WHILE这些语句上打上标记，以便于跳转到指定的语句。 |
存储过程
存储过程与存储函数形式一致，只不过没有返回值，仅表示一个处理过程。
创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表]) BEGIN 语句列表 END |
示例：
mysql> DELIMITER $
mysql> CREATE PROCEDURE t1_operation(
-> m1_value INT,
-> n1_value CHAR(1)
-> )
-> BEGIN
-> SELECT * FROM t1;
-> INSERT INTO t1(m1, n1) VALUES(m1_value, n1_value);
-> SELECT * FROM t1;
-> END $
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ; |
调用存储过程
CALL 存储过程([参数列表]); |
查看和删除存储过程
查看：
SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称] |
查看定义：
SHOW CREATE PROCEDURE 存储过程名称 |
删除：
DROP PROCEDURE 存储过程名称 |
存储过程中的语句
前面存储函数中的语句都可以使用。
存储过程的参数前缀
存储过程在定义参数时可以加上前缀，如下：
[IN | OUT | INOUT] 参数名 数据类型 |
这三种前缀的作用如下：
| 前缀 | 实际参数是否必须是变量 | 描述 |
|---|---|---|
| IN | 否 | 用于调用者向存储过程传递数据，如果IN参数在存储过程中修改，则调用者不可见（形参） |
| OUT | 是 | 用于把存储过程运行中产生的数据赋值给OUT参数，存储过程执行结束后，调用都可以访问OUT参数 |
| INOUT | 是 | 综合IN和OUT的特点，即可以用于调用者向存储过程传递数据，也可以用于存放存储过程中产生的数据，以供调用者使用 |
存储过程和存储函数的不同点
- 存储函数需要显示使用RETURNS语句标明返回的数据类型，而且在函数体中必须使用RETURN来指定返回值，存储过程则不需要。
- 存储函数不支持IN、OUT、INOUT参数前缀。
- 存储函数只能返回一个值，存储过程可以通过OUT参数来返回多个结果。
- 存储函数在执行过程中产生的结果集不会被显示到客户端；存储过程在执行过程中产生的结果集会被显示到客户端。
- 存储函数以函数的形式调用；存储过程只能通过CALL语句来显示调用。
游标
用于分步骤将结果集中的记录逐条赋值给某些变量。
初始时，游标指向结果集中的第一条记录，可以根据游标取出它对应的信息，随后再移动游标，让它指向下一条记录。
游标既可以用于存储函数，也可以用于存储过程。
创建游标
DECLARE 游标名称 CURSOR FOR 查询语句; |
示例：
CREATE PROCEDURE cursor_demo() BEGIN DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1; END |
如果存储过程中有声明局部变量，则创建游标的语句一定要放在局部变量声明语句的后面。 |
打开和关闭游标
OPEN 游标名称; CLOSE 游标名称; |
打开游标意味着执行查询语句，获取结果集合，并使游标指向第一条结果。关闭游标意味着释放与该游标相关的资源，游标使用结束之后要关闭掉。存储过程的END语句执行之后也会自动关闭游标。
示例：
CREATE PROCEDURE cursor_demo() BEGIN DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1; OPEN t1_record_cursor; CLOSE t1_record_cursor; END |
通过游标获取记录
FETCH 游标名 INTO 变量1, 变量2, ... 变量n |
示例：
CREATE PROCEDURE cursor_demo() BEGIN DECLARE m_value INT; DECLARE n_value CHAR(1); DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1; OPEN t1_record_cursor; FETCH t1_record_cursor INTO m_value, n_value; SELECT m_value, n_value; CLOSE t1_record_cursor; END |
以上存储过程在CALL调用后会显示出m_value和n_value的结果。当然这里只显示了第一条记录的结果，如果要让游标遍历完全部结果，则需要使用循环语句，如下：
CREATE PROCEDURE cursor_demo() BEGIN DECLARE m_value INT; DECLARE n_value CHAR(1); DECLARE record_count INT; DECLARE i INT DEFAULT 0; DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1; SELECT COUNT(*) FROM t1 INTO record_count; OPEN t1_record_cursor; WHILE i < record_count DO FETCH t1_record_cursor INTO m_value, n_value; SELECT m_value, n_value; SET i = i + 1; END WHILE; CLOSE t1_record_cursor; END |
遍历结束时的执行策略
前面的示例通过预先获得结果集的记录数来结束游标循环，其实FETCH自带结束标志，当FETCH语句获取不到记录时默认支停止存储函数或存储过程的执行，并且向客户端返回一个如下的错误：
ERROR 1329 (02000): No data - zero rows fetched, selected or processed |
于是我们可以在存储函数或存储过程中事先声明针对这种错误的处理方式，以执行相关的语句，如下：
DECLARE CONTINUE HANDLER FOR NOT FOUND 处理语句; |
下面改写一下上面的查询语句：
CREATE PROCEDURE cursor_demo() BEGIN DECLARE m_value INT; DECLARE n_value CHAR(1); DECLARE done INT DEFAULT 0; DECLARE t1_record_cursor CURSOR FOR SELECT m1, n1 FROM t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN t1_record_cursor; flag:LOOP FETCH t1_record_cursor INTO m_value, n_value; IF done = 1 THEN LEAVE flag; END IF; SELECT m_value, n_value; END LOOP flag; CLOSE t1_record_cursor; END |