MySQL全面瓦解16：存储过程相关

概述

大多数SQL语句都是针对一个或多个表的单条语句。但并非所有业务都这么简单，经常会有复杂的操作需要多条语句才能完成。

比如用户购买一个商品，要删减库存表，要生成订单数据，要保存支付信息等等，他是一个批量的语句执行行为。

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

优点：
提高代码的复用性：把一些通用操作内容封装到一个存储过程中，可以不断的给业务功能复用。

简化操作：避免在业务中写大量的代码

提高效率：减少执行次数和数据库服务器连接次数。

提高安全性：通过存储过可以减少对基础数据的误操作，参数化的存储过程一定程度上可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。

说存储过程之前，先来了解两个重要的知识点：自定义变量和 delimiter关键字。

自定义变量

概念

变量由用户自定义的，而非系统已经存在的。

使用步骤

第一步声明；第二步赋值；第三步使用（调用、比较和运算）

分类

包含用户变量和局部变量，我们一个个来看：

用户变量

作用域

针对当前会话有效，作用域同会话变量。

用户变量可以在任何地方使用，既可以是包含的begin和end，也可以是在这之外。

使用

声明并初始化

1 set @variable=value;
2 or
3 set @variable:=value;
4 or
5 select @variable:=value;

这边需要注意：使用了@符号来定义变量，set中=号前面冒号是可选的，select方式=前面必须有冒号。

赋值方式

一种方式就是跟声明并初始化一致，直接set、select进行赋值，

另外一种就是直接从其他表、视图或变量中查询并赋值，如下：

1 select columnname into @variable from tname;

这边需要注意两种select的使用方式

实践一下

 1 mysql> set @var1='num1';
 2 set @var2:='num2';
 3 select @var3:='num3';
 4 select @var1,@var2,@var3;
 5 Query OK, 0 rows affected
 6 
 7 Query OK, 0 rows affected
 8 
 9 +---------------+
10 | @var3:='num3' |
11 +---------------+
12 | num3          |
13 +---------------+
14 1 row in set
15 
16 +-------+-------+-------+
17 | @var1 | @var2 | @var3 |
18 +-------+-------+-------+
19 | num1  | num2  | num3  |
20 +-------+-------+-------+
21 1 row in set

局部变量

作用域

declare用于定义局部变量，在存储过程和函数中通过declare定义变量在begin…end中，且在语句之前。并且可以通过重复定义多个变量

declare变量的作用范围同编程里面类似，在这里一般是在对应的begin和end之间。在end之后这个变量就没有作用了，不能使用了。这个同编程一样。

使用

声明语法

1 declare variable type [default default_value];

declare 变量名变量类型，后面的 [ 默认值] 为可选;

赋值方式

1 set variable=value;
2 set variable:=value;
3 select variable:=value;
4 
5 或者
6 
7 select cname into variable from tname;

注意自定义变量和局部变量的区别，一个前面有@符号，一个没有。

查看变量的值

1 select variable;

实践一下

1 mysql>

 2 /*这边声明脚本的结束符为// */
 3 DELIMITER //
 4 DROP PROCEDURE IF EXISTS sp_avg;
 5 CREATE PROCEDURE sp_avg()
 6 BEGIN
 7   /*声明了一个局部变量 avg_score*/
 8   DECLARE avg_score int;
 9   select AVG(score) into avg_score from students;
10   select avg_score;
11   -- Todo
12 END //
13 /*重置脚本的结束符为; */
14 DELIMITER ;
15 Query OK, 0 rows affected
16 
17 mysql>
18 /*调用存储过程*/
19 call sp_avg();
20 +-----------+
21 | avg_score |
22 +-----------+
23 |        87 |
24 +-----------+
25 1 row in set
26 
27 Query OK, 0 rows affected

变量类型	作用域	定义位置	语法格式
用户变量	当前会话都有效	会话的任一地方	加`@`符号，无需指定类型
局部变量	所属定义的begin end之间	begin...end中的第一个位置，紧跟在begin后面	不加`@`符号，需指定类型

delimiter 关键字的使用

简介

delimiter是mysql分隔符，在mysql客户端中分隔符默认是分号;。如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的分隔符。

其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

详细解释:

其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。

默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。如输入下面的语句：

1 mysql> select * from tname;

然后回车，那么MySQL将立即执行该语句。

使用

但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中包含有分号。这种情况下，就需要事先把delimiter换成其它符号，如//、$$或者;;。

更改结束标志的定义如下：

1 mysql>delimiter //

举个例子：创建一个存储过程，在创建该存储过程之前，将delimiter分隔符转换成符号“//”，最后在转换回符号“;”。

 /*将结束标志符更改为// */ delimiter // /*创建函数或存储过程*/ -- Todo，这边写下你的sql语句 end // /*重置脚本的结束符为; */ delimiter ;

上面就是，先将分隔符设置为 //, 直到遇到下一个 //,才整体执行语句。

执行完后，最后一行， delimiter ; 将mysql的分隔符重新设置为分号；

如果不修改的话，本次会话中的所有分隔符都以// 为准。

存储过程操作

存储过程的操作包含创建

创建存储过程

1 create procedure 存储过程名([参数模式] 参数名 参数类型)
2 begin
3     存储过程体
4 end

参数模式有3种：

in：该参数可以作为输入，也就是该参数需要调用方传入值。

out：该参数可以作为输出，也就是说该参数可以作为返回值。

inout：该参数既可以作为输入也可以作为输出，也就是说该参数需要在调用的时候传入值，又可以作为返回值。

参数模式默认为IN，一个存储过程可以有多个输入、多个输出、多个输入输出参数。

所以创建存储过程的时候参数可能存在一下几种情况：

无参情况

编写存储过程

 1 /*设置结束符设置为// */
 2 DELIMITER //
 3 /*存储过程如果存在先删除*/
 4 DROP PROCEDURE IF EXISTS sp_test1;
 5 /*创建无参数存储过程sp_test1*/
 6 CREATE PROCEDURE sp_test1()
 7 BEGIN
 8 update students set score = (score+1) where studentname='lala';
 9 END //
10 /*将结束符重新设置为;*/
11 DELIMITER;

调用实现：对比数据可确定调用成功

 1 mysql> select *  from students;
 2 +-----------+-------------+-------+---------+
 3 | studentid | studentname | score | classid |
 4 +-----------+-------------+-------+---------+
 5 |         1 | brand       | 97.5  |       1 |
 6 |         2 | helen       | 96.5  |       1 |
 7 |         3 | lyn         | 96    |       1 |
 8 |         4 | sol         | 97    |       1 |
 9 |         7 | b1          | 81    |       2 |
10 |         8 | b2          | 82    |       2 |
11 |        13 | c1          | 71    |       3 |
12 |        14 | c2          | 72.5  |       3 |
13 |        19 | lala        | 53    |       0 |
14 +-----------+-------------+-------+---------+
15 9 rows in set
16 
17 mysql> call sp_test1();
18 Query OK, 1 row affected
19 
20 mysql> select *  from students;
21 +-----------+-------------+-------+---------+
22 | studentid | studentname | score | classid |
23 +-----------+-------------+-------+---------+
24 |         1 | brand       | 97.5  |       1 |
25 |         2 | helen       | 96.5  |       1 |
26 |         3 | lyn         | 96    |       1 |
27 |         4 | sol         | 97    |       1 |
28 |         7 | b1          | 81    |       2 |
29 |         8 | b2          | 82    |       2 |
30 |        13 | c1          | 71    |       3 |
31 |        14 | c2          | 72.5  |       3 |
32 |        19 | lala        | 54    |       0 |
33 +-----------+-------------+-------+---------+
34 9 rows in set

带in参数

编写存储过程：

 1 /*设置结束符为// */
 2 DELIMITER //
 3 /*存储过程如果存在先删除*/
 4 DROP PROCEDURE IF EXISTS sp_test2;
 5 /*创建存储过程sp_test2*/
 6 CREATE PROCEDURE sp_test2(sname varchar(20),score DECIMAL(10,2),classid int)
 7 BEGIN
 8 INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);
 9 END //
10 /*将结束符重新置为;*/
11 DELIMITER ;

调用实现：

 1 mysql> set @uname='wzh1',@score=100,@classid=8;
 2 call sp_test2(@uname,@score,@classid);
 3 Query OK, 0 rows affected
 4 
 5 Query OK, 1 row affected
 6 
 7 mysql> select * from students;
 8 +-----------+-------------+-------+---------+
 9 | studentid | studentname | score | classid |
10 +-----------+-------------+-------+---------+
11 |         1 | brand       | 97.5  |       1 |
12 |         2 | helen       | 96.5  |       1 |
13 |         3 | lyn         | 96    |       1 |
14 |         4 | sol         | 97    |       1 |
15 |         7 | b1          | 81    |       2 |
16 |         8 | b2          | 82    |       2 |
17 |        13 | c1          | 71    |       3 |
18 |        14 | c2          | 72.5  |       3 |
19 |        19 | lala        | 54    |       0 |
20 |        20 | wzh1        | 100   |       8 |
21 +-----------+-------------+-------+---------+
22 10 rows in set

带out参数

编写存储过程

 1 /*设置结束符为// */
 2 DELIMITER //
 3 /*如果存储过程存在则删除*/
 4 DROP PROCEDURE IF EXISTS sp_test3;
 5 /*创建存储过程sp_test2*/
 6 CREATE PROCEDURE sp_test3(sname varchar(20),score DECIMAL(10,2),classid int,out lastid int)
 7 BEGIN
 8 INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);
 9 select lastid = @@identity;
10 END //
11 /*将结束符重新置为;*/
12 DELIMITER ;

调用实现

 1 mysql> set @uname='wzh3',@score=104,@classid=10;
 2 call sp_test3(@uname,@score,@classid,@lastid);
 3 select @lastid;
 4 Query OK, 0 rows affected
 5 
 6 Query OK, 1 row affected
 7 
 8 +---------+
 9 | @lastid |
10 +---------+
11 |      22 |
12 +---------+
13 1 row in set
14 
15 mysql> select * from students;
16 +-----------+-------------+-------+---------+
17 | studentid | studentname | score | classid |
18 +-----------+-------------+-------+---------+
19 |         1 | brand       | 97.5  |       1 |
20 |         2 | helen       | 96.5  |       1 |
21 |         3 | lyn         | 96    |       1 |
22 |         4 | sol         | 97    |       1 |
23 |         7 | b1          | 81    |       2 |
24 |         8 | b2          | 82    |       2 |
25 |        13 | c1          | 71    |       3 |
26 |        14 | c2          | 72.5  |       3 |
27 |        19 | lala        | 54    |       0 |
28 |        20 | wzh1        | 100   |       8 |
29 |        21 | wzh2        | 101   |       9 |
30 |        22 | wzh3        | 104   |      10 |
31 +-----------+-------------+-------+---------+
32 12 rows in set

带inout参数

自己试试吧，小伙子们

调用存储过程

1 call 存储过程名称(参数列表);

注意：调用存储过程关键字是call。

如上所示，所有的call都是这样的额

删除存储过程

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

存储过程只能一个个删除，不能批量删除。

if exists：表示存储过程存在的情况下删除，我们上面演示的存储过程都是判断如果存在就先删除。

修改存储过程

存储过程不能修改，若涉及到修改的，可以先删除，然后重建。

查看存储过程

1 show create procedure 存储过程名称;

可以查看存储过程详细创建语句。

 1 mysql> show create procedure sp_test3;
 2 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 3 | Procedure | sql_mode                                                                                                               | Create Procedure                                                                                                                                                                                                                                  | character_set_client | collation_connection | Database Collation |
 4 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 5 | sp_test3  | 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 `sp_test3`(sname varchar(20),score DECIMAL(10,2),classid int,out lastid int)
 6 BEGIN
 7 INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);
 8 select LAST_INSERT_ID() into lastid;
 9 END | utf8                 | utf8_general_ci      | utf8_general_ci    |
10 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
11 1 row in set

小结

存储过程的优点开篇已经说过了，这边就不赘述了，个人使用的最大感触是，尽量不要在应用代码中写大量的脚本逻辑，做成存储过程或者函数会更高效简洁且易于维护。

MySQL全面瓦解16：存储过程相关原创

概述

自定义变量

概念

使用步骤

分类

用户变量

作用域

使用

声明并初始化

赋值方式

实践一下

局部变量

作用域

使用

声明语法

赋值方式

实践一下

delimiter 关键字的使用

简介

使用

存储过程操作

创建存储过程

无参情况

带in参数

带out参数

带inout参数

调用存储过程

删除存储过程

修改存储过程

查看存储过程

小结

为你推荐

日常Bug排查-偶发性读数据不一致

推荐阅读

专题推荐

本月精选性能专题

本月精选线上案例

本月精选原创好文

联系我们

网媒渠道

友情链接