mysql 存储过程详解

啥是存储过程

存储过程就是把多条sql语句包装起来，起个名字，存放到mysql中，然后通过它的名字调用它就会执行背包装的sql语句了。说白了就是python中的自定函数，但不能这么叫，可以这么理解

先来说说三种开发模型

1. 第一种
   开发人员只管开发。
   DBA编写存储的过程，然后给开发人员用。
   优点：开发效率和执行的效率都高
   缺点：人为元素，跨部门沟通等问题，这样会导致扩展性会很差
2. 第二种
   开发人员除了写程序，还要编写原生的sql语句
   优点：比第一种的扩张性好
   缺点：开发效率和执行的效率都不如第一种，编写mysql语句太过复杂，而且需要考虑到sql语句的优化问题
3. 第三种
   开发人员只编写程序，然后sql用别人写好的orm框架
   优点：开发效率高，扩展性高
   缺点：执行效率就低了

创建存储过程

无参数的存储过程

delimiter $$
create procedure fun()
begin
    select * from tname from teacher;
end $$
delimiter ;

有参数的存储过程

delimiter $$
create procedure fun(
  in m int, # in 表示只能传入的参数
  out res int, # out 表示可以被返回出去的参数,必须传入一个变量
  inout turn int  # inout 表示可以传入也可以返回,必须传入一个变量
)
begin
  select tname from teacher where tid > m;
  set res = 0;
  set turn = 0;
end $$
delimiter ;

声明变量

set @res = 1 # set声明 @+变量名 并进行赋值

调用存储过程

set @res=1;
set @turn=1;
call fun(2,@res,@turn);

mysql> set @res=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @turn=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call fun(2,@res,@turn);
+-----------------+
| tname           |
+-----------------+
| 刘海燕老师      |
| 朱云海老师      |
| 李杰老师        |
+-----------------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

删除存储过程

drop procedure pro_name;

存储过程与事务来举例

delimiter //
create PROCEDURE p5(
    OUT p_return_code tinyint
)
BEGIN
    DECLARE exit handler for sqlexception # 声明如果出现异常则执行
    BEGIN
        -- ERROR
        set p_return_code = 1;
        rollback;
    END;

  DECLARE exit handler for sqlwarning # 声明出现警告则执行
  BEGIN
      -- WARNING
      set p_return_code = 2;
      rollback;
  END;

  START TRANSACTION; # 开始事务
      update user set balance=900 where id =1;
      update user123 set balance=1010 where id = 2;
      update user set balance=1090 where id =3;
  COMMIT; # 提交事务

  -- SUCCESS
  set p_return_code = 0; #0代表执行成功

END //
delimiter ;

注意

存储过程在那个库下面创建的只能在对应的库下面才能使用