目录

一、存储过程

1. 概述

2. 存储过程的优点

3. 语法格式

3.1 创建存储过程

3.2 调用存储过程 

3.3 查看存储过程 

3.4 显示状态信息 

3.5 查看指定存储过程信息 

3.6 删除存储过程  

二、传参

1. 输入参数 in

2. 输出参数 out

3. 输入输出参数 inout

---

一、存储过程

1. 概述

MySQL 存储过程是一组为了完成特定任务而预先编译并存储在数据库中的 SQL 语句集合。这些存储过程可以被多次调用，有点类似shell脚本里的函数，从而简化重复性任务的执行，并提高数据库的性能和安全性。有两个重要工具：触发器（定时任务）和判断。

2. 存储过程的优点

① 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率

② SQL语句加上控制语句的集合，灵活性高

③ 在服务器端存储，客户端调用时，降低网络负载

④ 可多次重复被调用，可随时修改，不影响客户端调用

⑤ 可完成所有的数据库操作，也可控制数据库的信息访问权限

3. 语法格式

3.1 创建存储过程

创建格式：

delimiter $$    # 更改语句结束符号因为存储过程可能包含多个 SQL 语句，需要使用不同于分号的结束符。这里将结束符更改为 &&，也可以用 // @@ 
create procedure 存储过程名(可选参数)   # 创建一个新的存储过程，可选的参数列表，用括号括起来，可以接受零个或多个输入参数
-> begin                          # 过程体以关键字 BEGIN 开始
-> ……                             # 可选，包括了存储过程的特性，例如安全性、权限等设置
-> …… <过程体语句>                 # 包含了实际的存储过程代码，可以包括各种 SQL 语句、条件判断和循环等
-> end $$						 # 过程体以关键字 end 结束
delimiter ;						 # 将语句的结束符号恢复为分号，以便继续执行其他 SQL 语句，分号前面有空格

 示例：

mysql> delimiter $$
mysql> create procedure name1()
    -> begin
    -> create table stu1 (id int(5),name char(15),score decimal(4,2));
    -> insert into stu1 values (1,'zhao',90.5);
    -> select * from stu1;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

3.2 调用存储过程 

在 MySQL 中，存储过程不会自动执行，除非显式地调用。一旦创建了存储过程，它将保留在数据库中，但不会自动执行或生效。只有在明确调用存储过程时，其中的代码才会被执行。您可以通过使用 call 语句来调用存储过程。

格式：
mysql> call 存储过程名();
示例：
mysql> call name1();
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    1 | zhao | 90.50 |
+------+------+-------+
1 row in set (0.05 sec)

3.3 查看存储过程 

格式：
show create procedure [数据库.]存储过程名;		#查看某个存储过程的具体信息
示例：
mysql> mysql> show create procedure school.name1;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                                                                                 | Create Procedure                                                                                                                                                                             | character_set_client | collation_connection | Database Collation |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| name1     | PIPES_AS_CONCAT,ANSI_QUOTES,NO_AUTO_VALUE_ON_ZERO,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 "name1"()
begin
create table stu1 (id int(5),name char(15),score decimal(4,2));
insert into stu1 values (1,'zhao',90.5);
select * from stu1;
end | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

3.4 显示状态信息 

mysql> show procedure status;

3.5 查看指定存储过程信息 

mysql> show procedure status like '%name1%'\G;
*************************** 1. row ***************************
                  Db: school
                Name: name1
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2024-03-27 18:58:42
             Created: 2024-03-27 18:58:42
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

3.6 删除存储过程  

drop procedure [if exists] 存储过程名;

二、传参

在 MySQL 中，存储过程可以接受参数，这使得存储过程更加灵活和通用。存储过程的参数可以分为输入参数、输出参数和输入输出参数三种类型。在创建存储过程时，可以定义这些参数，并在存储过程的调用中传递相应的参数值。参数可以是任何数据类型，如整数、字符串、日期等。

1. 输入参数 in

输入参数是存储过程接受的值，但存储过程在执行完毕后不会改变这些参数的值。输入参数允许向存储过程传递数据以供处理。表示调用者向过程传入值（传入值可以是字面量或变量）。

mysql> delimiter $$
mysql> create procedure name2(in inname varchar(40))
    -> begin
    -> select * from student where name=inname;
    -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select * from student;
+----+---------+----------+------+-------+
| id | name    | address  | age  | score |
+----+---------+----------+------+-------+
|  1 | zhangs  | nanjin   | 10   |  90.5 |
|  2 | lisi    | nanjin   | 15   |  65.0 |
|  3 | wangwu  | beijin   | 30   |  50.0 |
|  4 | zhaoliu | shanghai | 30   |  50.0 |
|  5 | zhouqi  | hangzhou | 40   |  55.0 |
+----+---------+----------+------+-------+
mysql> call name2('lisi');
+----+------+---------+------+-------+
| id | name | address | age  | score |
+----+------+---------+------+-------+
|  2 | lisi | nanjin  | 15   |  65.0 |
+----+------+---------+------+-------+

2. 输出参数 out

输出参数是存储过程在执行过程中会修改其值，并在存储过程执行完毕后将这些值传递回调用者。输出参数通常用于返回存储过程执行结果或特定计算的结果。表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）。

mysql> delimiter $$
mysql> create procedure name3(out outname varchar(40))
    -> begin
    -> select count(*) into outname from student; # 查询student表中的总行数，并将结果存储在输出参数outname中
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call name2(@outname); # 调用该存储过程时，传入一个变量@outname来接收查询结果
mysql> select @outname;
+----------+
| @outname |
+----------+
| 5        |
+----------+
# 创建一个名为 name3 的存储过程，该存储过程接受一个输出参数 outname，并从 student 表中获取行数并将结果存储在 outname 中。调用 name3 存储过程。执行 SELECT 语句，查看存储过程中设置的输出变量的值

3. 输入输出参数 inout

输入输出参数既允许传递数据给存储过程，又在存储过程执行过程中可以修改其值，并将修改后的值传递回调用者。既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）。

mysql> delimiter $$
mysql> create procedure name4(in student_name varchar(40),out student_score varchar(40))
    -> begin
    -> select score into student_score from student where name=student_name;  # 从student表查询学生的分数，并将结果存储在输出参数中。
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call name4('zhangs',@score);
Query OK, 1 row affected (0.00 sec)
mysql> select @score;
+--------+
| @score |
+--------+
| 90.5   |
+--------+
# in 用于指定输入参数 student_name，它接受一个学生姓名作为输入
# out 用于指定输出参数 student_score，存储过程将查询到的学生分数存储在这个输出参数中
# 名为 name4 的存储过程，该存储过程接受学生的姓名作为输入参数，并根据学生姓名查询其成绩，并将结果存储在输出参数中。