MySQL数据库 存储过程 & 存储函数
前言一、什么是存储过程 & 存储函数二、存储过程的创建和调用三、存储函数的创建和调用前言
本环境是基于 Centos 7.8 系统构建MySQL-5.7.14
具体构建，请参考 MySQL-5.7.14 环境构建
一、什么是存储过程 & 存储函数
存储过程 & 存储函数
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。
存储过程和函数的区别：
函数必须有返回值，而存储过程没有。存储过程的参数可以是IN、OUT、INOUT类型，函数的参数只能是IN
优点
存储过程只在创建时进行编译；而SQL语句每执行一次就编译一次，所以使用存储过程可以提高数据库执行速度。简化复杂操作，结合事务一起封装。复用性好安全性高，可指定存储过程的使用权。
注：1、并发量少的情况下，很少使用存储过程。
2、并发量高的情况下，为了提高效率，用存储过程比较多
二、存储过程的创建和调用
存储过程的参数包括IN、OUT、INOUT类型
无参数
# 准备student表mysql> select * from student;+--------------+-----------+---------+---------+-----------+--------------+| stu_id | stu_name | stu_sex | stu_age | stu_major | stu_college |+--------------+-----------+---------+---------+-----------+--------------+| 04550101 | 郭奎| 男|22 | 计科| 信工学院|| 04550102 | 吕宇航 | 男|18 | 计科| 信工学院|| 04550103 | 张豪辉 | 女|19 | 计科| 信工学院|| 04550107 | 丁志杰 | 男|17 | 金融学 | 金贸学院|| 04550109 | 范伟| 男|19 | 金融学 | 金贸学院|| 04550116 | 张依婷 | 女|17 | 大数据 | 信工学院|| 04550120 | 张维| 男|19 | 计科| 信工学院|| 04550121 | 朱柳阳 | 女|20 | 计科| 信工学院|| 04550144 | 谭兵炎 | 男|20 | 大数据 | 信工学院|| 04550153 | 杨志强 | 男|17 | 大数据 | 信工学院|+--------------+-----------+---------+---------+-----------+--------------+10 rows in set (0.00 sec)# 针对student表创建存储过程mysql> create procedure p1()-> begin-> select * from student;-> end$$Query OK, 0 rows affected (0.05 sec)mysql> \d ;# 调用存储过程mysql> call p1();+--------------+-----------+---------+---------+-----------+--------------+| stu_id | stu_name | stu_sex | stu_age | stu_major | stu_college |+--------------+-----------+---------+---------+-----------+--------------+| 04550101 | 郭奎| 男|22 | 计科| 信工学院|| 04550102 | 吕宇航 | 男|18 | 计科| 信工学院|| 04550103 | 张豪辉 | 女|19 | 计科| 信工学院|| 04550107 | 丁志杰 | 男|17 | 金融学 | 金贸学院|| 04550109 | 范伟| 男|19 | 金融学 | 金贸学院|| 04550116 | 张依婷 | 女|17 | 大数据 | 信工学院|| 04550120 | 张维| 男|19 | 计科| 信工学院|| 04550121 | 朱柳阳 | 女|20 | 计科| 信工学院|| 04550144 | 谭兵炎 | 男|20 | 大数据 | 信工学院|| 04550153 | 杨志强 | 男|17 | 大数据 | 信工学院|+--------------+-----------+---------+---------+-----------+--------------+10 rows in set (0.03 sec)Query OK, 0 rows affected (0.03 sec)# 查看存储过程的创建mysql> show create procedure p1\G*************************** 1. row ***************************Procedure: p1sql_mode: 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_SUBSTITUTIONCreate Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()beginselect * from student;endcharacter_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)
# 创建tb1表mysql> create table tb1-> (id int,-> name varchar(30)-> );Query OK, 0 rows affected (0.06 sec)# 创建存储过程mysql> \d $$mysql> create procedure pro1_insert_tb1()-> begin-> declare i int default 1;-> while(i<=50000)do-> insert into tb1 values(i,md5(i));-> set i=i+1;-> end while;-> end $$Query OK, 0 rows affected (0.06 sec)mysql> \d ;# 查看存储过程mysql> show create procedure pro1_insert_tb1\G*************************** 1. row ***************************Procedure: pro1_insert_tb1sql_mode: 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_SUBSTITUTIONCreate Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro1_insert_tb1`()begindeclare i int default 1;while(i<=50000)doinsert into tb1 values(i,md5(i));set i=i+1;end while;endcharacter_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)# 调用存储过程mysql> call pro1_insert_tb1();Query OK, 1 row affected (10.10 sec)# 查看插入数据mysql> select count(1) from tb1;+----------+| count(1) |+----------+| 50000 |+----------+1 row in set (0.01 sec)mysql> select * from tb1-> where id=10000 or id=20000 or id=30000;+-------+----------------------------------+| id | name |+-------+----------------------------------+| 10000 | b7a782741f667201b54880c925faec4b || 20000 | d9798cdf31c02d86b8b81cc119d94836 || 30000 | 5ecc613150de01b7e6824594426f24f4 |+-------+----------------------------------+3 rows in set (0.01 sec)
IN 参数
# 清空表数据mysql> delete from tb1;Query OK, 50000 rows affected (0.34 sec)mysql> select * from tb1;Empty set (0.00 sec)# 创建存储过程mysql> \d $$mysql> create procedure in_insert_tb1(in num int)-> begin-> declare i int default 1;-> while(i<=num)do->insert into tb1 values(i,md5(i));->set i=i+1;-> end while;-> end $$Query OK, 0 rows affected (0.00 sec)mysql> \d ;# 查看存储过程的创建mysql> show create procedure in_insert_tb1\G*************************** 1. row ***************************Procedure: in_insert_tb1sql_mode: 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_SUBSTITUTIONCreate Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `in_insert_tb1`(in num int)begindeclare i int default 1;while(i<=num)doinsert into tb1 values(i,md5(i));set i=i+1;end while;endcharacter_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)# 调用存储过程mysql> call in_insert_tb1(80000);Query OK, 1 row affected (15.42 sec)查看插入数据mysql> select count(1) from tb1;+----------+| count(1) |+----------+| 80000 |+----------+1 row in set (0.01 sec)mysql> select * from tb1-> where id=3000 or id=6000 or id =80000;+-------+----------------------------------+| id | name |+-------+----------------------------------+| 3000 | e93028bdc1aacdfb3687181f2031765d || 6000 | a8c6dd98fce8701ce1aef8a2d40a || 80000 | 144fdd8be8005ab7206deaaedc515e71 |+-------+----------------------------------+3 rows in set (0.02 sec)
OUT 参数
mysql> \d $$mysql> create procedure count_tb1(out count int)-> begin-> select count(1) into count from tb1;-> end$$Query OK, 0 rows affected (0.00 sec)mysql> \d ;# 查看创建过程mysql> show create procedure count_tb1\G*************************** 1. row ***************************Procedure: count_tb1sql_mode: 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_SUBSTITUTIONCreate Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `count_tb1`(out count int)beginselect count(1) into count from tb1;endcharacter_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)# 定义变量接受count值mysql> select @v;+------+| @v |+------+| NULL |+------+1 row in set (0.00 sec)# 调用存储过程mysql> call count_tb1(@v);Query OK, 1 row affected (0.02 sec)mysql> select @v;+-------+| @v |+-------+| 80000 |+-------+1 row in set (0.00 sec)
IN & OUT
---student表创建存储过程（性别：男，年龄：19 的学生个数）# student 查询mysql> select count(1) from student where stu_sex='男' and stu_age=19;+----------+| count(1) |+----------+| 2 |+----------+1 row in set (0.00 sec)# 创建存储过程mysql> \d $$mysql> create procedure count_num(in p1 enum('男','女'),in p2 tinyint(255) unsigned,out p3 int)-> begin -> select count(1) into p3 from student where stu_sex=p1 and stu_age=p2;-> end$$Query OK, 0 rows affected (0.00 sec)mysql> \d ;# 查看存储过程的创建mysql> show create procedure count_num\G*************************** 1. row ***************************Procedure: count_numsql_mode: 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_SUBSTITUTIONCreate Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `count_num`(in p1 enum('男','女'),in p2 tinyint(255) unsigned,out p3 int)begin select count(1) into p3 from student where stu_sex=p1 and stu_age=p2;endcharacter_set_client: utf8collation_connection: utf8_general_ciDatabase Collation: utf8_general_ci1 row in set (0.00 sec)# 调用存储过程，传入、接受参数mysql> call count_num('男',19,@V);Query OK, 1 row affected (0.00 sec)# 产看参数@V的值mysql> select @V;+------+| @V |+------+| 2 |+------+1 row in set (0.00 sec)
INOUT
mysql> \d $$mysql> create procedure proce_param_inout(inout p1 int)-> begin-> if (p1 is not null) then->set p1=p1+1;-> else->select 100 into p1;-> end if;-> end$$Query OK, 0 rows affected (0.00 sec)mysql> \d ;mysql> select @M;+------+| @M |+------+| NULL |+------+1 row in set (0.00 sec)mysql> call proce_param_inout(@M);Query OK, 1 row affected (0.00 sec)mysql> select @M;+------+| @M |+------+| 100 |+------+1 row in set (0.00 sec)mysql> call proce_param_inout(@M);Query OK, 0 rows affected (0.00 sec)mysql> select @M;+------+| @M |+------+| 101 |+------+1 row in set (0.00 sec)mysql> call proce_param_inout(@M);Query OK, 0 rows affected (0.00 sec)mysql> select @M;+------+| @M |+------+| 102 |+------+1 row in set (0.00 sec)
删除存储过程
mysql> drop procedure count_num;Query OK, 0 rows affected (0.00 sec)mysql> drop procedure count_tb1;Query OK, 0 rows affected (0.00 sec)mysql> drop procedure pro1_insert_tb1;Query OK, 0 rows affected (0.00 sec)
三、存储函数的创建和调用
无参数有返回值
# 创建存储函数--- 统计student表学生个数mysql> \d $$mysql> create function stu_num()-> returns int-> begin-> declare n int default 0;-> select count(1) into n from student;-> return n;-> end $$Query OK, 0 rows affected (0.00 sec)mysql> \d ;# 调用函数mysql> select stu_num();+-----------+| stu_num() |+-----------+| 10 |+-----------+1 row in set (0.00 sec)
有参数、有返回值
---根据学生姓名、返回学生年龄mysql> create function my_age(my_name varchar(50))-> returns int-> begin-> declare s_age int;-> select stu_age into s_age from student-> where stu_name=my_name;-> return s_age;-> end $$Query OK, 0 rows affected (0.00 sec)mysql> \d ;mysql> select my_age('范伟');+------------------+| my_age('范伟') |+------------------+|19 |+------------------+1 row in set (0.00 sec)mysql> select my_age('杨志强');+---------------------+| my_age('杨志强') |+---------------------+| 17 |+---------------------+1 row in set (0.00 sec)
---根据专业名称，返回该专业平均年龄mysql> create function stu_avg_age(major char(9))-> returns int-> begin-> declare avg_age int;-> select avg(stu_age) into avg_age from student-> where stu_major=major;-> return avg_age;-> end $$Query OK, 0 rows affected (0.00 sec)mysql> \d ;mysql> select stu_avg_age('计科');+-----------------------+| stu_avg_age('计科') |+-----------------------+|20 |+-----------------------+1 row in set (0.03 sec)mysql> select stu_avg_age('大数据');+--------------------------+| stu_avg_age('大数据') |+--------------------------+| 18 |+--------------------------+1 row in set (0.00 sec)mysql> select stu_avg_age('金融学');+--------------------------+| stu_avg_age('金融学') |+--------------------------+| 18 |+--------------------------+1 row in set (0.00 sec)
删除存储函数
mysql> drop function stu_avg_age;Query OK, 0 rows affected (0.00 sec)mysql> drop function my_age;Query OK, 0 rows affected (0.00 sec)mysql> drop function stu_num;Query OK, 0 rows affected (0.00 sec)
如果觉得《MySQL 案例实战--MySQL数据库 存储过程 存储函数》对你有帮助，请点赞、收藏，并留下你的观点哦！
