MySQL之存储过程

四：存储过程 (优先掌握)

1. 什么是存储过程

是任意的sql语句的组合，被放到某一个存储过程中，类似于一个函数，有一个函数，有参数，还是函数体

2. 为什么使用存储过程

包含任何的sql语句，逻辑处理，事务处理。所有的我们学过的sql都可以放到里面

3. 三种开发方式的对比

第一种：应用程序只关注业务逻辑，所有与数据相关的逻辑封装到mysql中

​ 优点：应用程序要处理的事情变少了，可以减少网络传输

​ 缺点：增加了人力成本和沟通成本，降低了开发效率

第二种：应用程序既要处理业务逻辑，还有自己编写sql语句

​ 优点：降低了沟通成本，人力成本

​ 缺点：网络传输增加，sql语句编写非常繁琐，易出错

第三种：通过ORM框架。对象关系映射，自动生成sql语句并执行

​ 优点：简化编写sql语句的过程，提升了开发速度

​ 缺点：不够灵活。数据库和应用程序开发者完全隔离，可能导致开发者仅关注上层开发，而不清楚底层原理

4. 如何使用存储过程

1. 语法
-----------创建--------
create procedure pro_name(p_Type p_name data_type)
begin
sql语句 ... 流程控制
end

-----------参数---------------
p_Type：
	in 输入
	out 输出
	inout 输入输出
p_name	参数的名字
data_type：参数的数据类型 如 int float

-----------删除----------------
drop procedure pro_name;

-----------查看----------------
show create procedure pro_name;

-----------查看某个库中的所有存储过程----------------
select name from mysql.proc where db = "库名" and type = "procedure";

select name from mysql.proc where db = "day41_1" and type = "procedure";


# 测试数据
create table account(id int primary key auto_increment,name char(10),money float);
insert into account values(null,"big",1000),(null,"dog",2000),(null,"cat",3000),(null,"me",0);


2. 示例
---------------------示例1-----------------------
delimiter //
create procedure plf(in a int,in b int,out c float)
begin
set c = a + b;
end //
delimiter ;

set @res = 0;		# 设置变量
call plf(1,2,@res);	# 调用该存储对象。ps：out类型的数据，在调用时必须给定变量。


---------------------示例2-----------------------
// 我们需要把将更新语句写错，同时让其自动触发一个异常报错，然后执行回滚操作
delimiter //
create procedure transfer(in aid int,in bid int,in m float,out p_return_code int)
begin
	DECLARE exit handler for sqlexception
	BEGIN
		# ERROR
		set p_return_code = 1;
		rollback;
	END;
	# exit 也可以换成continue 表示发生异常时继续执行
	DECLARE exit handler for sqlwarning 
	BEGIN 
        # WARNING 
        set p_return_code = 2; 
        rollback; 
    END; 
	
	
	# 事务执行
	START TRANSACTION; 
	update account set money = money - m where id = aid;
	# 故意将money字段写错，让其执行报错，从而执行上面的异常捕捉中的代码
	update account set moneys = money + m where id = bid;
	COMMIT;
	
	# 如果成功，p_return_code为0
	set p_return_code = 0;
	
end //
delimiter;


set @p_return_code = 10;	// 需要提前设置变量
call transfer(1,2,100,@p_return_code);	# 1,2分别为account表中对应的id，100为钱，@p_return_code代表上面的变量