目录
一、存储过程
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 的存储过程，该存储过程接受学生的姓名作为输入参数，并根据学生姓名查询其成绩，并将结果存储在输出参数中。
本文含有隐藏内容，请 开通VIP 后查看