Mysql系列的目标是：通过这个系列从入门到全面掌握一个高级开发所需要的全部技能。
欢迎大家加我微信itsoku一起交流java、算法、数据库相关技术。
这是Mysql系列第17篇。
环境：mysql5.7.25，cmd命令中进行演示。
代码中被[]包含的表示可选，|符号分开的表示可选其一。
需求背景介绍
线上程序有时候出现问题导致数据错误的时候，如果比较紧急，我们可以写一个存储来快速修复这块的数据，然后再去修复程序，这种方式我们用到过不少。
存储过程相对于java程序对于java开发来说，可能并不是太好维护以及阅读，所以不建议在程序中去调用存储过程做一些业务操作。
关于自定义函数这块，若mysql内部自带的一些函数无法满足我们的需求的时候，我们可以自己开发一些自定义函数来使用。
所以建议大家掌握mysql中存储过程和自定义函数这块的内容。
本文内容
- 详解存储过程的使用
- 详解自定义函数的使用
准备数据
/*建库javacode2018*/drop database if exists javacode2018;create database javacode2018;/*切换到javacode2018库*/use javacode2018;/*建表test1*/DROP TABLE IF EXISTS t_user;CREATE TABLE t_user (id INT NOT NULL PRIMARY KEY COMMENT '编号',age SMALLINT UNSIGNED NOT NULL COMMENT '年龄',name VARCHAR(16) NOT NULL COMMENT '姓名') COMMENT '用户表';
存储过程
概念
一组预编译好的sql语句集合，理解成批处理语句。
好处：
- 提高代码的重用性
- 简化操作
- 减少编译次数并且减少和数据库服务器连接的次数，提高了效率。
创建存储过程
create procedure 存储过程名([参数模式] 参数名 参数类型)begin存储过程体end
参数模式有3种：
in：该参数可以作为输入，也就是该参数需要调用方传入值。
out：该参数可以作为输出，也就是说该参数可以作为返回值。
inout：该参数既可以作为输入也可以作为输出，也就是说该参数需要在调用的时候传入值，又可以作为返回值。
参数模式默认为IN。
一个存储过程可以有多个输入、多个输出、多个输入输出参数。
调用存储过程
call 存储过程名称(参数列表);
注意：调用存储过程关键字是
call。
删除存储过程
drop procedure [if exists] 存储过程名称;
存储过程只能一个个删除，不能批量删除。
if exists：表示存储过程存在的情况下删除。
修改存储过程
存储过程不能修改，若涉及到修改的，可以先删除，然后重建。
查看存储过程
show create procedure 存储过程名称;
可以查看存储过程详细创建语句。
示例
示例1：空参列表
创建存储过程
/*设置结束符为$*/DELIMITER $/*如果存储过程存在则删除*/DROP PROCEDURE IF EXISTS proc1;/*创建存储过程proc1*/CREATE PROCEDURE proc1()BEGININSERT INTO t_user VALUES (1,30,'路人甲Java');INSERT INTO t_user VALUES (2,50,'刘德华');END $/*将结束符置为;*/DELIMITER ;
delimiter用来设置结束符，当mysql执行脚本的时候，遇到结束符的时候，会把结束符前面的所有语句作为一个整体运行，存储过程中的脚本有多个sql，但是需要作为一个整体运行，所以此处用到了delimiter。
mysql默认结束符是分号。
上面存储过程中向t_user表中插入了2条数据。
调用存储过程：
CALL proc1();
验证效果：
mysql> select * from t_user;+----+-----+---------------+| id | age | name |+----+-----+---------------+| 1 | 30 | 路人甲Java || 2 | 50 | 刘德华 |+----+-----+---------------+2 rows in set (0.00 sec)
存储过程调用成功，test1表成功插入了2条数据。
示例2：带in参数的存储过程
创建存储过程：
/*设置结束符为$*/DELIMITER $/*如果存储过程存在则删除*/DROP PROCEDURE IF EXISTS proc2;/*创建存储过程proc2*/CREATE PROCEDURE proc2(id int,age int,in name varchar(16))BEGININSERT INTO t_user VALUES (id,age,name);END $/*将结束符置为;*/DELIMITER ;
调用存储过程：
/*创建了3个自定义变量*/SELECT @id:=3,@age:=56,@name:='张学友';/*调用存储过程*/CALL proc2(@id,@age,@name);
验证效果：
mysql> select * from t_user;+----+-----+---------------+| id | age | name |+----+-----+---------------+| 1 | 30 | 路人甲Java || 2 | 50 | 刘德华 || 3 | 56 | 张学友 |+----+-----+---------------+3 rows in set (0.00 sec)
张学友插入成功。
示例3：带out参数的存储过程
创建存储过程：
delete a from t_user a where a.id = 4;/*如果存储过程存在则删除*/DROP PROCEDURE IF EXISTS proc3;/*设置结束符为$*/DELIMITER $/*创建存储过程proc3*/CREATE PROCEDURE proc3(id int,age int,in name varchar(16),out user_count int,out max_id INT)BEGININSERT INTO t_user VALUES (id,age,name);/*查询出t_user表的记录，放入user_count中,max_id用来存储t_user中最小的id*/SELECT COUNT(*),max(id) into user_count,max_id from t_user;END $/*将结束符置为;*/DELIMITER ;
proc3中前2个参数，没有指定参数模式，默认为in。
调用存储过程：
/*创建了3个自定义变量*/SELECT @id:=4,@age:=55,@name:='郭富城';/*调用存储过程*/CALL proc3(@id,@age,@name,@user_count,@max_id);
验证效果：
mysql> select @user_count,@max_id;+-------------+---------+| @user_count | @max_id |+-------------+---------+| 4 | 4 |+-------------+---------+1 row in set (0.00 sec)
示例4：带inout参数的存储过程
创建存储过程：
/*如果存储过程存在则删除*/DROP PROCEDURE IF EXISTS proc4;/*设置结束符为$*/DELIMITER $/*创建存储过程proc4*/CREATE PROCEDURE proc4(INOUT a int,INOUT b int)BEGINSET a = a*2;select b*2 into b;END $/*将结束符置为;*/DELIMITER ;
调用存储过程：
/*创建了2个自定义变量*/set @a=10,@b:=20;/*调用存储过程*/CALL proc4(@a,@b);
验证效果：
mysql> SELECT @a,@b;+------+------+| @a | @b |+------+------+| 20 | 40 |+------+------+1 row in set (0.00 sec)
上面的两个自定义变量@a、@b作为入参，然后在存储过程内部进行了修改，又作为了返回值。
示例5：查看存储过程
mysql> show create procedure proc4;+-------+-------+-------+-------+-------+-------+| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |+-------+-------+-------+-------+-------+-------+| proc4 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc4`(INOUT a int,INOUT b int)BEGINSET a = a*2;select b*2 into b;END | utf8 | utf8_general_ci | utf8_general_ci |+-------+-------+-------+-------+-------+-------+1 row in set (0.00 sec)
函数
概念
一组预编译好的sql语句集合，理解成批处理语句。类似于java中的方法，但是必须有返回值。
创建函数
create function 函数名(参数名称 参数类型)returns 返回值类型begin函数体end
参数是可选的。
返回值是必须的。
调用函数
select 函数名(实参列表);
删除函数
drop function [if exists] 函数名;
查看函数详细
show create function 函数名;
示例
示例1：无参函数
创建函数：
/*删除fun1*/DROP FUNCTION IF EXISTS fun1;/*设置结束符为$*/DELIMITER $/*创建函数*/CREATE FUNCTION fun1()returns INTBEGINDECLARE max_id int DEFAULT 0;SELECT max(id) INTO max_id FROM t_user;return max_id;END $/*设置结束符为;*/DELIMITER ;
调用看效果：
mysql> SELECT fun1();+--------+| fun1() |+--------+| 4 |+--------+1 row in set (0.00 sec)
示例2：有参函数
创建函数：
/*删除函数*/DROP FUNCTION IF EXISTS get_user_id;/*设置结束符为$*/DELIMITER $/*创建函数*/CREATE FUNCTION get_user_id(v_name VARCHAR(16))returns INTBEGINDECLARE r_id int;SELECT id INTO r_id FROM t_user WHERE name = v_name;return r_id;END $/*设置结束符为;*/DELIMITER ;
运行看效果：
mysql> SELECT get_user_id(name) from t_user;+-------------------+| get_user_id(name) |+-------------------+| 1 || 2 || 3 || 4 |+-------------------+4 rows in set (0.00 sec)
存储过程和函数的区别
存储过程的关键字为procedure，返回值可以有多个，调用时用call，一般用于执行比较复杂的的过程体、更新、创建等语句。
函数的关键字为function，返回值必须有一个，调用用select，一般用于查询单个值并返回。
| 存储过程 | 函数 | |
|---|---|---|
| 返回值 | 可以有0个或者多个 | 必须有一个 |
| 关键字 | procedure | function |
| 调用方式 | call | select |
最新资料
注意：本文归作者所有，未经作者允许，不得转载
