【MySQL笔记】存储过程（PROCEDURE）与存储函数（FUNCTION）的创建与使用

MySQL 从 5.0 版本开始支持存储过程和函数。存储过程和函数能够将复杂的 SQL 逻辑封装在一起，应用程序无须关注存储过程和函数内部复杂的 SQL 逻辑，而只需要简单地调用存储过程和函数即可

目录

存储过程

存储过程的优点

和视图、函数的对比

创建存储过程

代码举例

存储函数 

创建存储函数

​代码举例

对比存储函数和存储过程

---

存储过程

存储过程的优点

和视图、函数的对比

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向对象的操作方式，能够实现一些更复杂的数据处理

一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的

创建存储过程

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 数据类型,...)
[characteristics...]
BEGIN
    存储过程体
END

代码举例

#查看 emps 表的所有数据
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
    SELECT * FROM emps;
END $
DELIMITER;

#调用
CALL select_all_data();

#返回所有员工的平均工资
DELIMITER //
CREATE PROCEDURE avg_emp_salary()
BEGIN
    SELECT AVG(salary) AS avg_salary FROM emps;
END //
DELIMITER;

#调用
CALL avg_emp_salary();

带 OUT

#查看 emps 表的最低薪资，并返回该值，通过 OUT 参数 ms 输出
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
    SELECT MIN(salary) INTO ms FROM emps;
END //
DELIMITER;

#调用
CALL show_min_salary(@ms);

#查看变量值
SELECT @ms;

 带 IN

#查看 emps 表的某个员工的薪资，用 IN 参数 empname 输入员工姓名，用 OUT 参数 empsalary 输出薪资
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20), OUT empsalary DECIMAL(10,2))
BEGIN
    SELECT salary INTO empsalary 
    FROM emps
    WHERE last_name = empname;
END //
DELIMITER;

#调用
SET @empname = 'Abel';
CALL show_someone_salary(@empname, @empsalary);

#查看变量值
SELECT @empsalary;

存储函数 

创建存储函数

MySQL 支持自定义函数，定义好之后，调用方式与调用 MySQL 预定义的系统函数一样

CREATE FUNCTION 函数名(参数名 数据类型,...)
RETURNS 返回值类型
[characteristics...]
BEGIN
    函数体 #函数体中肯定有 RETURN 语句
END

代码举例

#查询 Abel 的 email并返回数据类型为字符串
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
BEGIN
    RETURN (SELECT email FROM emps WHERE last_name = 'Abel');
END //
DELIMITER;

#调用
SELECT email_by_name();

#创建函数前执行此语句，保证函数的创建会成功
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)
RETURNS VARCHAR(25)
BEGIN
    RETURN (SELECT email FROM emps WHERE emps_id = emp_id);
END //
DELIMITER;

#调用
SELECT email_by_id(101);
SET @emp_id := 102;
SELECT email_by_id(@emp_id);

对比存储函数和存储过程