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);