该如何使用MySQL的存储过程？

118 阅读 0 评论 78 点赞

我是靠谱客的博主清脆大树，这篇文章主要介绍该如何使用MySQL的存储过程？，现在分享给大家，希望可以做个参考。

在这里插入图片描述

写在前面：跟随B站的康师傅学习mysql的笔记摘要和自我总结。

1、参数分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

1、没有参数（无参数无返回）
2、仅仅带 IN 类型（有参数无返回）
3、仅仅带 OUT 类型（无参数有返回）
4、既带 IN 又带 OUT（有参数有返回）
5、带 INOUT（有参数有返回）

注意： IN、OUT、INOUT 都可以在一个存储过程中带多个。

2、创建存储过程

语法：

复制代码

1
2
3
4
5
6
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END

说明：

IN：当前参数为输入参数，也就是表示入参；

存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。
OUT：当前参数为输出参数，也就是表示出参；

执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
INOUT：当前参数既可以为输入参数，也可以为输出参数。

编写存储过程并不是一件简单的事情，可能存储过程中需要复杂的 SQL 语句。

复制代码

1
2
3
4
5
6
7
1. BEGIN…END：
   BEGIN…END 中间包含了多个语句，每个语句都以（;）号为结束符。
2. DECLARE：
   DECLARE 用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
3. SET：赋值语句，用于对变量进行赋值。
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。

设置新的结束标记

复制代码

1
2
DELIMITER 新的结束标记

因为MySQL默认的语句结束符号为分号是 ; 。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。

示例：

复制代码

1
2
3
4
5
6
7
8
9
10
#建议新的结束标记不要太花里胡哨了
DELIMITER $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型,...)
[characteristics ...]
BEGIN
	sql语句1;
	sql语句2;
END $

3、调用存储过程

语法：

复制代码

1
2
CALL 存储过程名(实参列表)

格式：

1、调用in模式的参数：

复制代码

1
2
CALL sp1('值');

2、调用out模式的参数：

复制代码

1
2
3
4
SET @name;
CALL sp1(@name);
SELECT @name;

3、调用inout模式的参数：

复制代码

1
2
3
4
SET @name=值;
CALL sp1(@name);
SELECT @name;

4、删除存储过程

语法结构：

复制代码

1
2
DROP PROCEDURE [IF EXISTS] 存储过程名

5、查看存储过程

1. 使用SHOW CREATE语句查看存储过程和函数的创建信息

语法结构：

复制代码

1
2
SHOW CREATE PROCEDURE  存储过程名

2. 使用SHOW STATUS语句查看存储过程和函数的状态信息

语法结构:

复制代码

1
2
SHOW PROCEDURE  STATUS [LIKE 'pattern']

这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

[LIKE 'pattern']匹配存储过程的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程的信息。

举例：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#查询名称以select开头的存储过程
mysql> SHOW PROCEDURE STATUS LIKE 'SELECT%';
*************************** 1. row ***************************
                  Db: test_db
                Name: SelectAllData
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2021-10-16 15:55:07
             Created: 2021-10-16 15:55:07
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci

3. 从information_schema.Routines表中查看存储过程的信息

MySQL中存储过程的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。

语法结构：

复制代码

1
2
3
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程的名' [AND ROUTINE_TYPE = 'PROCEDURE'];

说明： 如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。

6、修改存储过程

修改存储过程，不影响存储过程功能，只是修改相关特性。使用ALTER语句实现。

复制代码

1
2
ALTER PROCEDURE  存储过程名;

7、练习题加强

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#准备工作
CREATE TABLE admin (
          id INT PRIMARY KEY AUTO_INCREMENT,
          user_name VARCHAR (15) NOT NULL,
          pwd VARCHAR (25) NOT NULL
) ;

 CREATE TABLE beauty (
          id INT PRIMARY KEY AUTO_INCREMENT,
          NAME VARCHAR (15) NOT NULL,
          phone VARCHAR (15) UNIQUE,
          birth DATE
) ;

INSERT INTO beauty(NAME,phone,birth) VALUES 
('朱茵','13201233453','1982-02-12'), 
('孙燕姿','13501233653','1980-12-09'), 
('田馥甄','13651238755','1983-08-21'), 
('邓紫棋','17843283452','1991-11-12'), 
('刘若英','18635575464','1989-05-18'), 
('杨超越','13761238755','1994-05-11');

复制代码

1
2
3
4
5
6
7
8
#1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中 
DELIMITER $  #‘$’代表着语句的结束
CREATE PROCEDURE insert_user(IN username VARCHAR(15),IN `password` VARCHAR(25))
BEGIN 
	INSERT INTO admin VALUES (username,`password`);
END $  #存储过程结束
DELIMITER ;  #修改语句结束的标识符为‘;’

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#2. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话 
DELIMITER $
CREATE PROCEDURE get_phone(IN id INT,OUT bname VARCHAR(15),OUT bphone VARCHAR(15))
BEGIN
	SELECT beauty.name , beauty.phone 
	       INTO bname,bphone
	FROM beauty WHERE beauty.id = id;
END $
DELIMITER ;

#调用
CALL get_phone(1,@name,@phone);
SELECT @name , @phone;