MySQL：存储过程

1. 含义

一组预先编译好的SQL语句的集合

2. 好处

提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

3. 语法

创建

CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
存储过程体（一组合法的SQL语句）
END

注意：

参数列表包含三部分：参数模式参数名参数类型（例：IN stuname VARCHAR(20) ）
参数模式有三种：

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

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

INOUT：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

如果存储过程体只有一条语句，则BEGIN和END可以省略
存储过程体中的每条SQL语句的结尾要求必须加分号，存储过程的结尾可以使用 DELIMITER 重新设置，例：DELIMITER $

调用

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

4. 实例（使用命令行）

空参列表

插入到admin表中五条记录

-- 创建，将语句的结束标记改成$
DELIMITER $ 
CREATE PROCEDURE myp1 () 
BEGIN

INSERT INTO `admin` (`username`, `password`) 
VALUES
  ('费渡', '1234'),
  ('简隋英', '1234'),
  ('李玉', '1234'),
  ('骆闻舟', '1234'),
  ('赵锦辛', '1234') ;

END $
-- 调用
CALL myp1 () $
-- 查看
select * from admin $

带IN模式参数的存储过程

根据女神名去查询对应的男神信息

-- 创建
CREATE PROCEDURE myp2 (IN beauty_name VARCHAR (20)) 
BEGIN

SELECT 
  bo.* 
FROM
  `beauty` AS b 
  RIGHT JOIN `boys` AS bo 
    ON b.`boyfriend_id` = bo.`id` 
WHERE b.`name` = beauty_name ;

END $
-- 调用
CALL myp2 ('费渡') $

判断用户是否登录成功

-- 创建
CREATE PROCEDURE myp3 (
  IN username VARCHAR (10),
  IN `password` VARCHAR (10)
) 
BEGIN

DECLARE nums INT ;
    
SELECT 
  COUNT(*) INTO nums 
FROM
  `admin` AS a 
WHERE a.`username` = username 
  AND a.`password` = `password` ;

SELECT 
  IF (
    nums = 1,
    "登录成功",
    "登录失败"
  ) AS result ;

END $ 
-- 调用
CALL myp3 ('费渡', '1234') $

带OUT模式的存储过程

根据女神名，返回对应的男神名

-- 创建
CREATE PROCEDURE myp4 (
  IN beauty_name VARCHAR (50),
  OUT boy_name VARCHAR (20)
) 
BEGIN  

SELECT 
  bo.`boyName` INTO boy_name
FROM
  `beauty` AS b 
  RIGHT JOIN `boys` AS bo 
    ON b.`boyfriend_id` = bo.`id` 
WHERE b.`name` = beauty_name ;

END $ 
-- 调用
CALL myp4 ('费渡', @name) $ 
SELECT 
  @name $

根据根据女神名，返回对应的男神名和魅力值

-- 创建
CREATE PROCEDURE myp5 (
  IN beauty_name VARCHAR (50),
  OUT boy_name VARCHAR (20),
  OUT user_cp INT
) 
BEGIN  

SELECT 
  bo.`boyName`,
  bo.`userCP` INTO boy_name,
  user_cp 
FROM
  `beauty` AS b 
  RIGHT JOIN `boys` AS bo 
    ON b.`boyfriend_id` = bo.`id` 
WHERE b.`name` = beauty_name ;

END $ 
-- 调用
CALL myp5 ('费渡', @name, @`value`) $ 
SELECT 
  @name,
  @`value` $

带INOUT模式参数的存储过程

传入两个值，得到两个值的二倍

-- 创建
CREATE PROCEDURE myp6 (INOUT a INT, INOUT b INT) 
BEGIN

SET a = a * 2 ;
SET b = b * 2 ;
 
END $ 
-- 调用
SET @a = 3 $
SET @b = 4 $
CALL myp6 (@a, @b) $
SELECT 
  @a,
  @b $

5. 其他操作

删除存储过程

-- 不支持一次删除多个
DROP PROCEDURE myp1 ;

查看存储过程的信息

SHOW CREATE PROCEDURE myp2 ;