MySQL 存储过程和函数

2020-08 MySQL

存储过程就是一条或者多条 SQL 语句的集合,可视为批文件,但是其作用不仅限于批处理.

创建存储过程和函数

存储程序可以分为存储过程和函数, MySQL 中创建存储过程和函数使用的语句分别是:CREATE PROCEDURE 和 CREATE FUNCTION 使用 CALL 语句来调用存储过程,只能用输出变量返回值.函数可以从语句外调用(即通过引用函数名),也能返回标量值.存储过程也可以调用其他存储过程.

创建存储过程

基本语法格式

CREATE PROCEDURE sp_name ( [proc_parameter] )
[characteristics ...] routine_body
# CREATE PROCEDURE 创建存储函数的关键字
# sp_name 存储过程名称
# proc_parameter 存储过程参数列表
## [ IN | OUT | INOUT ] param_name type
# routine_body sql代码内容

代码示例

DELIMITER //
CREATE PROCEDURE AvgFruitPrice()
BEGIN
SELECT AVG(f_price) AS avgprice FROM fruits;
END//
DELIMITER ;

• DELIMITER // 语句的作用是将MySQL的结束符设置为//,因为MySQL默认的语句结束符为分号;,为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER改变存储过程的结束符,并以END//结束存储过程.存储过程定义完毕之后再使用DELIMITER;恢复默认结束符.DELIMITER也可以指定其他符号作为结束符.
• 当使用DELIMITER命令时,应该避免使用反斜杠\字符,因为反斜杠是MySQL的转义字符.

创建存储函数

DELIMITER //
CREATE FUNCTION NameByZip()
RETURNS CHAR(50)
RETURN (SELECT s_name FROM suppliers WHERE s_call = '48075');
//
DELIMITER ;

变量的使用

定义条件和处理程序

光标的使用

流程控制的使用

调用存储过程和函数

调用存储过程

CALL CountProcl (101, @num);

调用存储函数

SELECT CountProc2(101);

查看存储过程和函数

使用 SHOW STATUS 语句查看存储过程和函数的状态

SHOW PROCEDURE STATUS LIKE `C%` \G

使用 SHOW CREATE 语句查看存储过程和函数的定义

SHOW CREATE FUNCTION test.CountProc \G

从 information_schema.Routines 表中查看存储过程和函数信息

SELECT * FROM infomation_schema.Routines WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE = 'FUNCTION' \G

修改存储过程和函数

ALTER PROCEDURE CountProc MODIFIES SQL DATA SQL SECURITY INVOKER;
ALTER FUNCTION CountProc READS SQL DATA COMMENT 'FIND NAME';

删除存储过程和函数

DROP PROCEDURE CountProc;
DROP FUNCTION CountPpoc;

参考资料:
MySQL5.7从入门都精通