MySQL数据库存储过程介绍

2024-04-18 14:42:58

目录

一、存储过程

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 的存储过程，该存储过程接受学生的姓名作为输入参数，并根据学生姓名查询其成绩，并将结果存储在输出参数中。
上一篇：【python】flask各种版本的项目，终端命令运行方式的实现


下一篇：伦敦金实战日内短线交易技巧