MySQL —— Stored Procedure (MySQL存储过程的快速上手与使用囊括应用案例)

最新推荐文章于 2024-08-04 13:53:21 发布

Alascanfu

最新推荐文章于 2024-08-04 13:53:21 发布

阅读量1.9k

点赞数 3

分类专栏： MySQL45讲与原理应用基础掌握文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/fuijiawei/article/details/124914190

版权

MySQL45讲与原理应用基础掌握专栏收录该内容

10 篇文章 8 订阅

订阅专栏

📖 MySQL —— Stored Procedure (MySQL存储过程)

推荐学习资料—— MySQL 存储过程 | 菜鸟教程

同时也参考了吕维尧大佬的 —— MySQL的存储过程

做出的知识笔记 —— 用于自身学习

📑 什么是 MySQL 存储过程

MySQL 存储过程概念

MySQL 5.0 版本开始支持存储过程。存储过程(Stored Procedure) 是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。(有点类似于 Java 中的方法给定参数然后执行)
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

📑 存储过程的优缺点以及特性

优点

存储过程可以封装、代码复用、模块化、隐藏复杂的业务逻辑等函数的普遍特性。
存储过程可以接受参数，也可以回传值，同时也可以在其中声明变量，还可以通过逻辑控制语句进行编写实现复杂的逻辑功能等。
速度快，只有在首次执行需要经过编译和优化步骤，后续被调用可以直接执行，省去编译和优化步骤，缩短运行时间。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

📑 快速创建存储过程和调用

步骤一：创建一个 storedprocedure_test 数据库进行测试

-- 创建一个名为 storedprocedure_test 的数据库
mysql> create database `storedprocedure_test`;
-- 选择使用 storedprocedure_test 数据库
mysql> use storedprocedure_test
Database changed

步骤二：创建一张 user 表用于测试 procedure

-- 创建一个 简单的 user 表 包括了u_id 和 username 的表
mysql> create table `user`(
    -> `u_id` int(11) not null auto_increment ,
    -> `username` varchar(64) not null ,
    -> primary key(`u_id`)
    -> )engine = innodb default charset = utf8;
Query OK, 0 rows affected, 2 warnings (0.38 sec)

步骤三：为我们创建好的表添加数据

INSERT INTO `storedprocedure_test`.`user`(`u_id`, `username`) VALUES (1, 'Alascanfu'),(2, 'HHXF'),(3, 'LeeAnKang');

步骤四：创建存储过程并调用

mysql> delimiter //
mysql> create procedure selectUser()
    -> begin
    ->  select u_id , username from user;
    -> end //
Query OK, 0 rows affected (0.11 sec)

mysql> delimiter ;
mysql> call selectUser();
+------+-----------+
| u_id | username  |
+------+-----------+
|    1 | Alascanfu |
|    2 | HHXF      |
|    3 | LeeAnKang |
+------+-----------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

📚这样一个简单的存储过程创建与调用就完成了~

📑 存储过程的创建和调用

🔖 存储过程语法结构

存储过程语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

咱们来简化一下

#自定义 delimiter_character 可以是 // 或 $$
DELIMITER delimiter_character
# 创建一个名为 procedure_name 的 PROCEDURE 
# ([IN | OUT | INPUT] param_name type[,...]) 
# 可以给存储过程传入 多个参数 包括参数名param_name 以及参数类型 type
CREATE PROCEDURE procedure_name([IN | OUT | INPUT] param_name type[,...])
# routine_body 是由 
# begin
# 	sql语句
# end delimiter_character 组成的语句体
[characteristic ...] routine_body

🍼 声明局部变量并赋值输出

创建一个叫做echoLocalVariable()的存储过程在其中声明局部变量并赋值输出

mysql> delimiter //
mysql> CREATE PROCEDURE echoLocalVariable()
    -> begin
    ->	# 声明局部变量 nickname 类型 varchar 大小20 默认值为 alascanfu
    ->  declare nickname varchar(20) default 'alascanfu';
    ->	# 给局部变量赋值
    ->  set nickname = 'Alascanfu';
    ->  # 输出局部变量 nickname 的值
    ->  select nickname;
    -> end //
Query OK, 0 rows affected (0.13 sec)

调用存储过程查看结果

mysql> call echoLocalVariable();
+-----------+
| nickname  |
+-----------+
| Alascanfu |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

🍼 声明成员变量并赋值输出

创建一个叫做 echoMemberVariable() 的存储过程，并设置一个叫做 @nickname 的成员变量赋值输出

mysql> delimiter //
mysql> CREATE PROCEDURE echoMemberVariable()
    -> begin
    ->  set @nickname = 'AlascanfuMemberVariable';
    ->  select @nickname;
    -> end //
Query OK, 0 rows affected (0.15 sec)

mysql> delimiter ;
mysql> call echoMemberVariable();
+-------------------------+
| @nickname               |
+-------------------------+
| AlascanfuMemberVariable |
+-------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

在存储过程外部也是可以直接调用这个成员变量 @nickname

mysql> select @nickname ;
+-------------------------+
| @nickname               |
+-------------------------+
| AlascanfuMemberVariable |
+-------------------------+
1 row in set (0.00 sec)

⭐️ 系统变量——全局变量与会话变量

MySQL系统变量简介

MySQL系统变量分为两种，一种是global全局变量，另一种是Session会话变量，全局变量全局有效，而会话变量则只在当前会话（MySQL客户端与MySQL服务器交互的过程被称为一个会话）有效。

会话变量在每次建立一个新的连接的时候,由MYSQL来初始化,MYSQL会将当前所有全局变量的值复制一份,来作为会话变量
对全局变量的修改会影响整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)

例如查看全局变量

-- 查看所有全局变量
mysql> select global variables;

查看某全局变量

-- 查看某个全局变量
mysql> select @@global.auto_increment_increment;
+-----------------------------------+
| @@global.auto_increment_increment |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

修改全局变量的值

mysql> set @@global.sort_buffer_size = 40000;
Query OK, 0 rows affected (0.00 sec)

🔖 存储过程的重要语法

以案例来进行理解

步骤一：创建一张表 matches

CREATE TABLE `matches`  (
  `matchNo` int(11) NOT NULL AUTO_INCREMENT,
  `teamNo` int(11) NOT NULL,
  `playerNo` int(11) NOT NULL,
  `won` int(11) NOT NULL,
  `lost` int(11) NOT NULL,
  PRIMARY KEY (`matchNo`)
) ENGINE = InnoDB CHARACTER SET = utf8;

步骤二：添加一部分数据用于对存储过程的测试

INSERT INTO `storedprocedure_test`.`matches`(`matchNo`, `teamNo`, `playerNo`, `won`, `lost`) VALUES (1,1,6,3,1),(2, 1, 14, 3, 1),(3,6,77,5,2),(7,1,57,3,0),(8,1,8,0,3);

步骤三：编写一个存储过程用于删除给定球员参与的所有比赛记录

mysql> delimiter //
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    ->  DELETE FROM matches
    ->  WHERE playerNo = p_playerno;
    -> END //
Query OK, 0 rows affected (0.13 sec)

mysql> delimiter ;

步骤四：调用存储过程

-- 调用存储过程之前
mysql> select * from matches;
+---------+--------+----------+-----+------+
| matchNo | teamNo | playerNo | won | lost |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       2 |      1 |       14 |   3 |    1 |
|       3 |      6 |       77 |   5 |    2 |
|       7 |      1 |       57 |   3 |    0 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
7 rows in set (0.00 sec)
-- 调用存储过程
mysql> call delete_matches(8);
Query OK, 1 row affected (0.04 sec)

-- 查看是否完成存储过程的调用
mysql> select * from matches;
+---------+--------+----------+-----+------+
| matchNo | teamNo | playerNo | won | lost |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       2 |      1 |       14 |   3 |    1 |
|       3 |      6 |       77 |   5 |    2 |
|       7 |      1 |       57 |   3 |    0 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+
6 rows in set (0.00 sec)

解析：在存储过程中设置了需要传参的变量p_playerno，调用存储过程的时候，通过传参将8赋值给p_playerno，然后进行存储过程里的SQL操作。

声明语句结束符

DELIMITER $$
或
DELIMITER //

切记当创建完成后一定要 DELIMITER ;

创建mysql 存储过程、存储函数

create procedure 存储过程名(参数)

存储过程体

create function 存储函数名(参数)

调用存储过程

call sp_name[(传参)];

🔖 存储过程的参数

⭐️ in 输入参数——传入参数

实参与形参理解清楚这里就和 Java 函数差不多了

创建一个简单的存储过程传入参数 int 类型名为 p_in 形参的函数 in_params(int p_in)

CREATE DEFINER=`root`@`localhost` PROCEDURE `in_params`(IN p_in INTEGER)
BEGIN
	#Routine body goes here...
	SELECT p_in;
	SET p_in=2;
	SELECT p_in;
END

进行测试

-- 设置了一个 用户变量 @p_in=1; 作为实参
mysql> set @p_in=1;
Query OK, 0 rows affected (0.00 sec)
-- 调用存储过程 传入实参作为 函数的形参进行调用
mysql> call in_params(@p_in);
+------+
| p_in |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

+------+
| p_in |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.02 sec)
-- 实参不会因形参的改变而改变
mysql> select @p_in;
+-------+
| @p_in |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

📚 小结：p_in 在存储过程中被修改，但并不影响 @p_in 的值，因为前者为局部变量、后者为全局变量。

⭐️ out 输出参数——输出值

创建一个存储过程用于返回一个输出参数

delimiter //
create procedure out_param(out p_out int)
    begin
        select p_out;
        set p_out = 2;
        select p_out;
    end //
delimiter ;

mysql> set @p_out=1;
 
mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
#因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
|     2 |
+-------+

mysql> select @p_out;
+--------+
| @p_out |
+--------+
|      2 |
+--------+

#调用了out_param存储过程，输出参数，改变了p_out变量的值

⭐️ inout 输入参数

创建一个存储过程 inout输入参数测试

delimiter //
create procedure inout_param(inout p_inout int)
    begin
        select p_inout;
        set p_inout = 2;
        select p_inout;
    end //
delimiter ;

测试

mysql> set @p_inout=1;
 
mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
 
+---------+
| p_inout |
+---------+
|       2 |
+---------+
 
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

⭐️ 总结

保证参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理。

📚 建议：

输入值使用 in 参数
返回值使用out 参数
inout 参数尽量少用或不用

🔖 存储过程的流程控制语句

⭐️ 流程控制——判断 if

需求给定一个学生成绩判定其处于的等级

DELIMITER //
CREATE PROCEDURE ifProcedure(in score int )
BEGIN
    if score < 60 then select 'D';
    elseif score >= 60 and score < 80 then select 'C';
    elseif score >= 80 and score < 90 then select 'B';
    elseif score >= 90 and score <= 100 then select 'A';
    else select 'error by parameter';
    end if;
END //
DELIMITER ;

set @score=77;
call ifProcedure(@score);

⭐️ 流程控制——循环 while

语法

while 条件 do
    --循环体
end while

简单的循环输出 0 - 5

DELIMITER //
CREATE PROCEDURE whileProcedure()
BEGIN
    declare var int ;
    set var = 0;
    while var < 6 do
        select var ;
        set var = var + 1;
        end while;
END //
DELIMITER ;

call whileProcedure();

📑 存储过程的相关操作

🔖 MySQL 存储过程的修改删除与查询

MySQL 存储过程的查询

-- 方式一
show procedure status where db='storedprocedure_test';

-- 方式二
select ROUTINE_NAME from information_schema.routines where routine_schema='storedprocedure_test';

-- 查看存储过程的详细信息
show create procedure storedprocedure_test.inout_param;

MySQL 存储过程的修改

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

更改用 CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

MySQL 存储过程的删除

drop procedure inout_param;

从 MySQL 的表格中删除一个或者多个存储的过程。

📑 参考文章

https://www.cnblogs.com/geaozhang/p/6797357.html

http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html

https://www.runoob.com/w3cnote/mysql-stored-procedure.html

https://blog.csdn.net/weixin_60589106/article/details/122704881