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分隔符转换成符号“//”，最后在转换回符号“;”。
1 /*将结束标志符更改为// */ 2 delimiter // 3 /*创建函数或存储过程*/ 4 -- Todo，这边写下你的sql语句 5 end // 6 /*重置脚本的结束符为; */ 7 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
小结
存储过程的优点开篇已经说过了，这边就不赘述了，个人使用的最大感触是，尽量不要在应用代码中写大量的脚本逻辑，做成存储过程或者函数会更高效简洁且易于维护。
