MySQL学习笔记（11）：存储过程和函数

本文更新于2019-06-23，使用MySQL 5.7，操作系统为Deepin 15.4。

目录

• SQL语句
  • 创建存储过程或函数
  • 修改存储过程或函数
  • 删除存储过程或函数
  • 调用存储过程或函数
• 变量、条件、处理、游标
  • 变量
  • 条件
  • 处理
  • 游标
• 流程控制
  • IF
  • CASE
  • LEAVE
  • ITERATE
  • LOOP
  • WHILE
  • REPEAT
• 示例

SQL语句

创建存储过程或函数

创建存储过程：

CREATE PROCEDURE name ({[IN|OUT|INOUT] param type}[, ...])
[characteristic]
body

创建函数：

CREATE FUNCTION name ({param type}[, ...]) RETURNS type
[characteristic]
body

type表示数据类型。

characteristic表示特征值，特征值目前只是提供信息给服务器，定义如下：

LANGUAGE SQL
|[NOT] DETERMINISTIC
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'

• LANGUAGE SQL：说明body是使用SQL编写的。该值是默认的。
• [NOT] DETERMINISTIC：说明是确定的，即是否同样的输入产生同样的输出。默认为NOT DETERMINISTIC。
• CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA：目前并没有根据此特征值约束存储过程实际对数据的使用。CONTAINS SQL表示不包含读写语句；NO SQL表示不包含SQL语句；READS SQL DATA表示包含读语句不包含写语句；MODIFIES SQL DATA表示包含写语句。默认为CONTAINS SQL。
• SQL SECURITY {DEFINER|INVOKER}：表示使用创建者的权限来执行，还是使用调用者的权限来执行。默认为DEFINER。
• COMMENT 'string'：注释。

body由BEGIN和END包起，其中可以定义变量、条件、处理、游标，以及使用流程控制语句跳转逻辑。

存储过程和函数的CREATE语法不支持OR REPLACE。

存储过程和函数的区别：函数一定有返回值，存储过程一定没有返回值；函数的参数只能使用IN类型，存储过程的参数可使用IN、OUT、INOUT类型。

MySQL的存储过程和函数中允许包含DDL语句，允许执行提交或回滚，允许调用其他存储过程或函数，但是是不允许执行LOAD DATA INFILE语句。

通常在执行创建存储过程或函数前，使用DELIMITER ;;命令将语句的结束符从;修改成其他符号（示例使用;;）。在创建完毕后，通过DELIMITER ;命令将结束符还原。这样可避免存储过程定义中的;被错误解析为结束符。

修改存储过程或函数

ALTER PROCEDURE|FUNCTION name
[characteristic]

characteristic特征值如下：

{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'

删除存储过程或函数

DROP PROCEDURE|FUNCTION [IF EXISTS] name

一次只能删除一个存储过程或函数。

调用存储过程或函数

CALL name(param[, ...])

如果是输出参数，需传入变量，如：@xxx。

变量、条件、处理、游标

变量、条件、处理、游标都通过DECLARE定义，但它们之间是有先后顺序的：变量和条件必须在最前面声明，然后才是游标的声明，最后才是处理的声明。

变量

变量不区分大小写，作用范围只能在BEGIN ... END块中，可以用在嵌套的块中。变量的定义必须写在复合语句的开头，并且在其他语句的前面。

DECLARE var[, ...] type [DEFAULT value]

变量直接赋值：

SET {var = value}[, ...]

变量通过查询赋值，这要求查询返回的结果只有一行：

SELECT colname[, ...] INTO var[, ...] FROM ...

FROM后面的子句同普通的SELECT查询，在此省略。

条件

DECLARE condition_name CONDITION FOR
{SQLSTATE [VALUE] value}|mysql_error_code

条件枚的含义如下：

• SQLSTATE [VALUE] value：SQLSTATE代码，为一个字符串。
• mysql_error_code：mysql_error_code值，为一个整数。

处理

DECLARE CONTINUE|EXIT|UNDO HANDLER FOR
{SQLSTATE [VALUE] value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code}[, ...]
sp_statement

处理类型枚举值的含义：

• CONTINUE：继续执行后面的语句。
• EXIT：执行终止。
• UNDO：前面已执行的语句撤销，目前还不支持。

条件枚的含义如下：

• SQLSTATE [VALUE] value：SQLSTATE代码，为一个字符串。
• condition_name：DECLARE定义的CONDITION名。
• SQLWARNING：所有以01开头的SQLSTATE代码的速记。
• NOT FOUND：所有以02开头的SQLSTATE代码的速记。
• SQLEXCEPTION：所有没有被SQLWARNING和NOT FOUND捕获的SQLSTATE代码的速记。
• mysql_error_code：mysql_error_code值，为一个整数。

游标

声明游标：

DECLARE cursor_name CURSOR FOR select_statement

打开游标：

OPEN cursor_name

读取游标：

FETCH cursor_name INTO var[, ...]

关闭游标：

CLOSE cursor_name

流程控制

IF

IF condition1 THEN statement_list1
[ELSEIF condition2 THEN statement_list2]
[...]
[ELSE statement_list3]
END IF

CASE

CASE case_value
WHEN value1 THEN statement_list1
[...]
[ELSE statement_list2]
END CASE

或：

CASE
WHEN condition1 THEN statement_list1
[...]
[ELSE statement_list2]
END CASE

LEAVE

从标注的流程中退出，通常和BEGIN ... END或循环一起使用。

LEAVE label

ITERATE

ITERATE必须用在循环中，作用是跳过当前循环的剩下语句，进入下一轮循环。

ITERATE label

LOOP

通常需在statement_list中指定退出条件，否则为死循环。

[begin_label:] LOOP
	statement_list
END LOOP [end_label]

WHILE

当满足条件时执行循环。会在首次循环执行前判断条件，故循环最少执行0次。

[begin_label:] WHILE condition DO
statement_list
END WHILE [end_label]

REPEAT

当满足条件时退出循环。会在首次循环执行后判断条件，故循环最少执行1次。

[begin_label:] REPEAT
	statement_list
UNTIL condition
END REPEAT [end_label]

示例

存储过程定义示例如下：

DELIMITER ;;
 
CREATE PROCEDURE sp_test(IN id INT, OUT sum INT)
BEGIN
	DECLARE var_value, var_sum INT DEFAULT 0;
	DECLARE flag INT DEFAULT 1;
	DECLARE cur CURSOR FOR SELECT value FROM t WHERE tid = id;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 0;
	OPEN cur;
	loop_cur: LOOP
		FETCH cur INTO var_value;
		IF flag = 0 THEN
			LEAVE loop_cur;
		END IF;
		var_sum = var_sum + var_value;
	END LOOP;
	CLOSE cur;
END;;
 
DELIMITER ;