- 由 zhongluqiang创建, 最后修改于2月 02, 2022
指组合在一起的一堆SQL语句,用于简化数据处理流程。
可以将某个常用功能对应的一些语句封装成一个存储程序,之后只需要调用这个存储程序就可以完成这个常用功能,从而免去每次都要写好多语句的麻烦。
存储程序包含以下分类:
用户自定义变量
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 ;
注意自定义变量和局部变量的区别,局部变量只在当前作用域有效,而自定义变量可以在函数体外生效,相当于全局变量,如下:
mysql> DELIMITER $ mysql> CREATE FUNCTION user_defined_var_demo() -> RETURNS INT -> BEGIN -> SET @abc = 10; -> return @abc; -> END $ Query OK, 0 rows affected (0.03 sec) mysql> DELIMITER ; mysql> mysql> SELECT @abc; +------------+ | @abc | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> SELECT user_defined_var_demo(); +-------------------------+ | user_defined_var_demo() | +-------------------------+ | 10 | +-------------------------+ 1 row in set (0.02 sec) mysql> SELECT @abc; +------+ | @abc | +------+ | 10 | +------+ 1 row in set (0.00 sec)
存储函数的参数
定义存储函数时可以指定多个参数,每个参数都要指定对应的数据类型,像这样:
参数名 数据类型
示例:
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语句,如下:
CREATE FUNCTION sum_all(n INT UNSIGNED) RETURNS INT BEGIN DECLARE result INT DEFAULT 0; DECLARE i INT DEFAULT 1; flag:LOOP IF i > n THEN LEAVE flag; END IF; SET result = result + i; SET i = i + 1; END LOOP flag; RETURN result END
其实也可以在BEGIN ... END、REPEAT和WHILE这些语句上打上标记,以便于跳转到指定的语句。
- 无标签