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

发布于 2022-7-27 15:25
浏览
0收藏

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

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

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

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

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

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

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

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

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

使用步骤
第一步声明;第二步赋值;第三步使用(调用、比较和运算)

分类
包含用户变量和局部变量,我们一个个来看:

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

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

使用
声明并初始化

 set @variable=value;
 or
 set @variable:=value;
 or
 select @variable:=value;


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

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

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

 select columnname into @variable from tname; 

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

实践一下

 mysql> set @var1='num1';
 set @var2:='num2';
  select @var3:='num3';
 select @var1,@var2,@var3;
  Query OK, 0 rows affected
  
  Query OK, 0 rows affected
  
  +---------------+
 | @var3:='num3' |
 +---------------+
 | num3          |
 +---------------+
 1 row in set

+-------+-------+-------+
| @var1 | @var2 | @var3 |
 +-------+-------+-------+
 | num1  | num2  | num3  |
 +-------+-------+-------+
 1 row in set


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

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

使用
声明语法
1 declare variable type [default default_value]; 
declare 变量名 变量类型,后面的 [ 默认值] 为可选;

赋值方式

 set variable=value;
 set variable:=value;
 select variable:=value;
 
 或者
 
 select cname into variable from tname; 


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

查看变量的值

 select variable; 


实践一下

  mysql>

  /*这边声明脚本的结束符为// */
 DELIMITER //
  DROP PROCEDURE IF EXISTS sp_avg;
  CREATE PROCEDURE sp_avg()
 BEGIN
  /*声明了一个局部变量 avg_score*/
   DECLARE avg_score int;
   select AVG(score) into avg_score from students;
  select avg_score;
   -- Todo
 END //
 /*重置脚本的结束符为; */
 DELIMITER ;
 Query OK, 0 rows affected
 
 mysql>
 /*调用存储过程*/
 call sp_avg();
 +-----------+
 | avg_score |
+-----------+
 |        87 |
 +-----------+
 1 row in set

 Query OK, 0 rows affected 

MySQL全面瓦解16:存储过程相关-开源基础软件社区

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

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

详细解释: 

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

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

mysql> select * from tname; 

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

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

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

mysql>delimiter // 


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

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

 

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

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

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

 

存储过程操作
存储过程的操作包含创建

创建存储过程

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


参数模式有3种:

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

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

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

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

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

无参情况
编写存储过程

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

 

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

  mysql> select *  from students;
  +-----------+-------------+-------+---------+
  | studentid | studentname | score | classid |
  +-----------+-------------+-------+---------+
  |         1 | brand       | 97.5  |       1 |
  |         2 | helen       | 96.5  |       1 |
  |         3 | lyn         | 96    |       1 |
  |         4 | sol         | 97    |       1 |
  |         7 | b1          | 81    |       2 |
 |         8 | b2          | 82    |       2 |
 |        13 | c1          | 71    |       3 |
 |        14 | c2          | 72.5  |       3 |
 |        19 | lala        | 53    |       0 |
 +-----------+-------------+-------+---------+
 9 rows in set
 
 mysql> call sp_test1();
 Query OK, 1 row affected
 
 mysql> select *  from students;
 +-----------+-------------+-------+---------+
 | studentid | studentname | score | classid |
 +-----------+-------------+-------+---------+
 |         1 | brand       | 97.5  |       1 |
 |         2 | helen       | 96.5  |       1 |
 |         3 | lyn         | 96    |       1 |
 |         4 | sol         | 97    |       1 |
 |         7 | b1          | 81    |       2 |
 |         8 | b2          | 82    |       2 |
 |        13 | c1          | 71    |       3 |
|        14 | c2          | 72.5  |       3 |
 |        19 | lala        | 54    |       0 |
 +-----------+-------------+-------+---------+
 9 rows in set

 

带in参数
编写存储过程:

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

 

调用实现:

 

 

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

 

带out参数
编写存储过程

 

 

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

 

调用实现

 

 

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


带inout参数
自己试试吧,小伙子们

调用存储过程

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

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

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

删除存储过程

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

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

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

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

查看存储过程

 show create procedure 存储过程名称;

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

 mysql> show create procedure sp_test3;
  +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  | Procedure | sql_mode                                                                                                               | Create Procedure                                                                                                                                                                                                                                  | character_set_client | collation_connection | Database Collation |
  +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  | 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)
  BEGIN
  INSERT INTO students(studentname,score,classid) VALUES (sname,score,classid);
  select LAST_INSERT_ID() into lastid;
  END | utf8                 | utf8_general_ci      | utf8_general_ci    |
 +-----------+------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 1 row in set


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

 

文章转载自公众号:架构与思维

已于2022-7-27 15:25:17修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐