什么是存储过程？

存储过程和函数都类似于Java中的方法。存储过程是一组预先编译好的sql指令集合。

预先编译好的，也就意味着，调用存储过程时，不再需要花时间去编译sql语句，直接就可以执行，从而提高效率。

好处：

  1. 提高代码重用性
  2. 简化操作
  3. 提高了效率

存储过程能解决什么问题？

使用存储过程

创建存储过程

语法：

  1. create procedure 存储过程名(参数列表)
  2. begin
  3.     存储过程体（一组sql语句）
  4. end

注意点：

  1. 参数列表包含三部分：参数模式 参数名 参数类型。例如，in stuname varchar(20)
  2. 参数模式：
    1. IN：表示该参数是输入参数，调用存储过程时，被 in 修饰的参数必须传入值。
    2. OUT：表示该参数可作为输出，即该参数是返回值
    3. INOUT：表示该参数即可作为输入参数，又可作为输出（即返回值）。
  3. 若存储过程体中只有一句 sql 语句，BEGIN END 可省略。
  4. 存储过程体中的每条sql语句必须以分号结尾。存储过程的结尾可以通过 delimiter 进行设置。

语法：

  1. DELIMITER 结束符号
  3. -- 如，下面的语句表示以$作为存储过程的结束符
  4. DELIMITER $

调用存储过程

  1. call 存储过程名(实参列表);

删除存储过程

  1. drop procedure 存储过名1, 存储过名2,...;

查看存储过程的结构

  1. show create procedure myp2;

存储过程使用实例

空参存储过程

  1. select * from account;
  3. -- 将结束符设为$
  4. delimiter $ 
  6. -- 创建存储过程
  7. create procedure myp1()
  8. begin
  9.     insert into account (username, balance) 
  10.   values ('john', 1000), ('mark', 2000), ('mary', 3000), ('steve', 4000);
  11. end $
  13. --将结束符还原
  14. delimiter ; 
  16. -- 调用存储过程
  17. call myp1();

初始表：

  1. +----+----------+---------+
  2. | id | username | balance |
  3. +----+----------+---------+
  4. |  1 | 张无忌   |     500 |
  5. |  2 | 赵敏     |    1000 |
  6. +----+----------+---------+

执行存储过程后：

  1. +----+----------+---------+
  2. | id | username | balance |
  3. +----+----------+---------+
  4. |  1 | 张无忌   |     500 |
  5. |  2 | 赵敏     |    1000 |
  6. |  7 | john     |    1000 |
  7. |  8 | mark     |    2000 |
  8. |  9 | mary     |    3000 |
  9. | 10 | steve    |    4000 |
  10. +----+----------+---------+

带 IN 模式的存储过程

先举个简单的实例：

  1. select * from account;
  3. delimiter $
  5. -- 参数列表中的username其实是个局部变量，就跟Java中一样
  6. create procedure myp2(in username varchar(20))
  7. begin
  8.     select * from account where account.username=username;
  9. end $
  11. delimiter ;
  13. call myp2('张无忌');

结果：

  1. MariaDB [myemployees]> call myp2('张无忌');
  2. +----+----------+---------+
  3. | id | username | balance |
  4. +----+----------+---------+
  5. |  1 | 张无忌   |     500 |
  6. +----+----------+---------+
  7. 1 row in set (0.004 sec)

再举个复杂的例子（结合了变量概念）：例如要用存储过程来实现用户登录成功与否

新建 admin 表：

  1. create table admin (
  2.   id int primary key auto_increment,
  3.   username varchar(20) not null,
  4.   password varchar(20) not null
  5. );
  7. insert into admin (username, password) values ('admin', '123'), ('hello', '123456');
  1. MariaDB [myemployees]> select * from admin;
  2. +----+----------+----------+
  3. | id | username | password |
  4. +----+----------+----------+
  5. |  1 | admin    | 123      |
  6. |  2 | hello    | 123456   |
  7. +----+----------+----------+
  8. 2 rows in set (0.000 sec)

创建存储过程：

  1. select * from admin;
  3. delimiter $
  5. create procedure myp3(in username varchar(20), in password varchar(20))
  6. begin
  7.     declare result int default 0;
  9.     select count(*) into result
  10.   from admin
  11.   where admin.username=username and admin.password=password;
  13.   select if(result>0, '登录成功', '登录失败');
  14. end $
  16. delimiter ;
  18. call myp3('admin', '123');

思路：查询 admin 表中是否有指定 username 和 password 的记录，若记录数大于 0（这一点通过变量来实现），则意味着登录成功，否则失败。

结果：

  1. MariaDB [myemployees]> call myp3('admin', '123');
  2. +--------------------------------------+
  3. | if(result>0, '登录成功', '登录失败') |
  4. +--------------------------------------+
  5. | 登录成功                             |
  6. +--------------------------------------+
  7. 1 row in set (0.003 sec)

带 OUT 模式的存储过程

例 1 ：只使用一个 OUT 修饰的参数：查询 account 表中，指定 username 对应的 balance

  1. select * from account;
  3. delimiter $
  5. create procedure myp4(in username varchar(20), out balance varchar(20))
  6. begin
  7.     select a.balance into balance
  8.   from account a
  9.   where a.username=username;
  10. end $
  12. delimiter ;
  14. call myp4('张无忌', @balance);
  16. -- 读取out修饰的参数
  17. select @balance;

结果：

  1. MariaDB [myemployees]> select @balance;
  2. +----------+
  3. | @balance |
  4. +----------+
  5. | 500      |
  6. +----------+
  7. 1 row in set (0.000 sec)

例 2 ：使用两个 OUT 修饰的参数：查询 account 表中，指定 username 对应的 balance，以及 balance-1000 后的结果

  1. select * from account;
  3. delimiter $
  5. create procedure myp5(in username varchar(20), out balance varchar(20), out balance_minus varchar(20))
  6. begin
  7.     select a.balance, (a.balance-1000) into balance, balance_minus
  8.   from account a
  9.   where a.username=username;
  10. end $
  12. delimiter ;
  14. call myp5('张无忌', @balance, @balance_minus);
  16. -- 读取out修饰的参数
  17. select @balance, @balance_minus;

结果：

  1. MariaDB [myemployees]> select @balance, @balance_minus;
  2. +----------+----------------+
  3. | @balance | @balance_minus |
  4. +----------+----------------+
  5. | 500      | -500           |
  6. +----------+----------------+
  7. 1 row in set (0.000 sec)

带 INOUT 模式的存储过程

例子：传入 a 和 b 两个值，返回二者翻倍后的结果

  1. delimiter $
  3. create procedure myp6(inout a int, inout b int)
  4. begin
  5.     set a=a*2;
  6.   set b=b*2;
  7. end $
  9. delimiter ;
  11. set @a:=2, @b:=5;
  12. call myp6(@a, @b);
  13. select @a, @b;

结果：

  1. MariaDB [myemployees]> select @a, @b;
  2. +------+------+
  3. | @a   | @b   |
  4. +------+------+
  5. |    2 |    5 |
  6. +------+------+
  7. 1 row in set (0.000 sec)
  9. MariaDB [myemployees]> call myp6(@a, @b);
  10. Query OK, 0 rows affected (0.000 sec)
  12. MariaDB [myemployees]> select @a, @b;
  13. +------+------+
  14. | @a   | @b   |
  15. +------+------+
  16. |    4 |   10 |
  17. +------+------+
  18. 1 row in set (0.000 sec)