MySQL 案例实战--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  |
+--------------+-----------+---------+---------+-----------+--------------+
| 201804550101 | 郭奎      | 男      |      22 | 计科      | 信工学院     |
| 201804550102 | 吕宇航    | 男      |      18 | 计科      | 信工学院     |
| 201804550103 | 张豪辉    | 女      |      19 | 计科      | 信工学院     |
| 201804550107 | 丁志杰    | 男      |      17 | 金融学    | 金贸学院     |
| 201804550109 | 范伟      | 男      |      19 | 金融学    | 金贸学院     |
| 201804550116 | 张依婷    | 女      |      17 | 大数据    | 信工学院     |
| 201804550120 | 张维      | 男      |      19 | 计科      | 信工学院     |
| 201804550121 | 朱柳阳    | 女      |      20 | 计科      | 信工学院     |
| 201804550144 | 谭兵炎    | 男      |      20 | 大数据    | 信工学院     |
| 201804550153 | 杨志强    | 男      |      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  |
+--------------+-----------+---------+---------+-----------+--------------+
| 201804550101 | 郭奎      | 男      |      22 | 计科      | 信工学院     |
| 201804550102 | 吕宇航    | 男      |      18 | 计科      | 信工学院     |
| 201804550103 | 张豪辉    | 女      |      19 | 计科      | 信工学院     |
| 201804550107 | 丁志杰    | 男      |      17 | 金融学    | 金贸学院     |
| 201804550109 | 范伟      | 男      |      19 | 金融学    | 金贸学院     |
| 201804550116 | 张依婷    | 女      |      17 | 大数据    | 信工学院     |
| 201804550120 | 张维      | 男      |      19 | 计科      | 信工学院     |
| 201804550121 | 朱柳阳    | 女      |      20 | 计科      | 信工学院     |
| 201804550144 | 谭兵炎    | 男      |      20 | 大数据    | 信工学院     |
| 201804550153 | 杨志强    | 男      |      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: p1
            sql_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_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
begin
  select * from student;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 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_tb1
            sql_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_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` 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
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 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_tb1
            sql_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_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` 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
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 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 | a8c6dd982010fce8701ce1aef8a2d40a |
| 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_tb1
            sql_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_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `count_tb1`(out count int)
begin
  select count(1) into count from tb1;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 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_num
            sql_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_SUBSTITUTION
    Create 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;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 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)