Mysql——存储过程

一、存储过程简介
 存储过程是一组预先编译好的SQL语句的集合，类似于Java中封装的方法，存储过程在创建之后，可以多次调用，提高了代码的重用性，同时减少了SQL语句的编译次数和与服务器的连接次数(将多个SQL语句封装成一个存储过程则这多个语句的执行只需要与数据库服务器建立一次连接)，提高了效率。

二、存储过程的创建和调用
 1、创建
  格式：

DELIMITER $ #定义结束符
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体;
END $ #标识存储过程定义结束
DELIMITER ; #恢复结束符为Mysql默认的;

  格式说明：
   1️⃣DELIMITER 的作用是定义结束符，因为Mysql默认的结束符是;，而在存储过程体中的SQL语句会用到;，若不重新定义结束符，Mysql遇到;符号时会认为;之前的内容为一个完整的执行语句，就会去执行，这显然是错误的，因此需要我们重新定义结束符告诉Mysql服务器语句在哪儿结束，需要注意的是在定义完结束符之后记得最好再将结束符置为默认的分号;。
   2️⃣存储过程的参数列表分为三部分——参数模式、参数名、数据类型，比如：

IN param VARCHAR(20)

   3️⃣Mysql中存储过程的参数模式有三种：IN、OUT、INOUT

参数模式	说明
IN	该参数仅作为传入值
OUT	该参数仅作为返回值，存储过程可以有多个返回值
INOUT	该参数即是传入值，也是返回值

   4️⃣存储过程体放在BEGIN…END块中，存储过程体是一组合法的SQL语句，若存储过程体中只有一条执行语句，BEGIN和END可以省略(不建议省略)，存储过程体中的每条SQL语句都必须以;结尾，存储过程的结尾则使用DELIMITER定义的结束符
 2、调用
  格式：

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

  注意：
   1️⃣调用存储过程时传入的参数个数必须和定义时的个数一致，也就是说返回值也必须传入(这一点和Java有所不同)，且若参数是返回值时传入的参数必须是一个变量而不能是一个常量，这就需要我们定义一个变量去接收存储过程的返回值
   2️⃣若没有将使用DELIMITER定义的结束符恢复成默认的;，则我们必须使用DELIMITER定义的结束符来结束调用(也就是说之后所有结束的标识都以DELIMITER为准)

 3、示例
  ①无参的存储过程

DELIMITER $ #自定义结束符
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username,`password`)
	VALUES('john','1111'),('lily','2222');#存储过程体：向admin表中插入两条数据
END $ #存储过程定义结束
DELIMITER ; #恢复结束符为默认的;

#调用存储过程
CALL myp1();#调用后才会向表中插入数据

  ②只有一个IN模式的参数

DELIMITER $ #自定义结束符
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;#存储过程体：根据女神名查询对应的男神信息
END $ #存储过程定义结束
DELIMITER ; #恢复结束符为默认的;

#调用存储过程
CALL myp2('柳岩');#调用

  ③有两个IN模式的参数

DELIMITER $ #自定义结束符
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN password VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0;#声明并初始化一个局部变量
	SELECT COUNT(*) INTO result #给局部变量赋值
	FROM admin
	WHERE admin.username = username
	AND admin.password = password; #校验用户名和密码
	SELECT IF(result > 0,'成功','失败');#打印结果
END $ #存储过程定义结束
DELIMITER ; #恢复结束符为默认的;

#调用存储过程
CALL myp3('柳岩','1111');#调用

  ④带一个OUT模式参数的存储过程

DELIMITER $ #自定义结束符
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $ #存储过程定义结束
DELIMITER ; #恢复结束符为默认的;

#调用存储过程
CALL myp4('小昭',@bname);#调用
SELECT @bname; #打印结果

   说明：此处并没有预先声明@bname这个变量，而是直接使用了该变量，这说明在调用的时候Mysql会自动为我们创建这个用户变量，我们也可以预先声明这个变量，然后将该变量传入到参数中

SET @bgold = ''; #用户变量声明时必须初始化，否则会报错
CALL myp4 ('赵敏' ,@bgold);
SELECT @bgold;

  ⑤带两个OUT模式参数的存储过程

DELIMITER $ #自定义结束符
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
	SELECT bo.boyName,bo.userCP INTO boyName,userCP #同时给两个参数赋值
	FROM boys bo
	INNER JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name = beautyName;
END $ #存储过程定义结束
DELIMITER ; #恢复结束符为默认的;

#调用存储过程
CALL myp5('小昭',@bname,@userCP);#调用
SELECT @bname,@userCP; #打印结果

  ⑥带两个INOUT模式参数的存储过程

DELIMITER $ #自定义结束符
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;
END $ #存储过程定义结束
DELIMITER ; #恢复结束符为默认的;

#调用存储过程
SET @m = 10; #先定义两个用户变量，不然返回值没有地方存放
SET @n = 20;
CALL myp6(@m,@n);#调用
SELECT @m,@n; #打印结果

三、存储过程的查看和删除
 1、查看

SHOW CREATE PROCEDURE 存储过程名;

 2、删除

DROP PROCEDURE 存储过程名;

 Tip：
  1️⃣存储过程不支持一次删除多个，只能一个个的删除
  2️⃣存储过程不支持修改。

关于存储过程的优缺点参看：Mysql存储过程优缺点