Mysql存储过程与事务总结

mysql中的变量

系统变量

select @@变量名

自定义变量

select @变量名

存储过程

  1. 存储过程的概念

所谓存储过程就是封装一个完整业务的操作，其中包括：变量、逻辑控制以及操作数据的SQL语句(类似于Java中的方法)

  1. 存储过程的语法
-- 10.发送添加群的消息
 -- 1)插入数据到消息信息表MessageInfo中
 -- 2)插入数据到群消息信息表GroupMessage中
   drop procedure if exists send_message_of_add_group;
   delimiter $$

   create procedure send_message_of_add_group(cus_id int, add_group_id int)
   begin
        -- 定义一个保存消息名称的变量
        declare  my_message text;
        -- 定义一个保存消息类别的变量
        declare my_message_type_id int;
        -- 定义一个保存消息的id
        declare my_message_id int;
        -- 定义表示错误信息的变量
        declare error int default 0;
        -- 定义一个变量保存群管理员的id
        declare group_manager_id int;
        declare flag int default 0;
        -- 定义一个保存消息类型和名称的游标
        declare message_list cursor for select id, name from MessageType where name = '申请加群';
        -- 定义出现sql异常时的操作
        declare continue handler for sqlexception set error = 1;
        -- 定义游标找不到数据的操作
        declare continue handler for not found set flag = 1;
    
        -- 开启游标
        open message_list;
        -- 赋值
        fetch message_list  into my_message_type_id, my_message;
        
        -- 插入数据到消息信息表MessageInfo中
        insert into MessageInfo (id, fromCusId, message, msgTypeId, sendTime) values (default, cus_id, my_message, my_message_type_id, default);
        if row_count() < 0 then
            set error = 1;
        end if;
        
        -- 插入数据到群消息信息表GroupMessage中
        -- 先得到MessageInfo中添加的数据id
        select id into @temp_id from MessageInfo where fromCusId = cus_id order by id desc limit 1;
        set my_message_id = @temp_id;
        -- 关闭游标
        close message_list;
        -- 得到要添加群的群主的id
        select cusId into @temp_group_id from GroupInfo where id = add_group_id;
        set group_manager_id = @temp_group_id;
        
        insert into GroupMessage (id, msgId, groupId, toCusId, `status`) values (default, my_message_id, add_group_id, group_manager_id, default );
        if row_count() < 0 then
            set error = 1;
        end if;
  end
  $$
  delimiter ;
  -- 调用存储过程
  call send_message_of_add_group(38,  1);

2.1 把一个表中的数据插入到当前表中的方法

利用游标记录查询的结果,对游标进行循环

-- 声明一个变量来保存游标循环退出的标志
-- 默认为0
declare flag int default 0;
-- 声明一个游标变量保存查询的结果
declare result_list cursor select column_1, column_2... from table_name where conditions;
-- 声明游标找不到值的处理操作
-- flag = 1 表示退出游标的循环
declare continue handler for not found set flag = 1;
-- 游标的使用
-- 先开启游标
open result_list;
--循环游标
while flag <> 1 do 
-- 取值 
fetch result_list into var_1, var_2...;
end while;
-- 关闭游标
close result_list;

2.2 保存单个查询结果的方法

select ... into var_1 from table_name where conditions;

事务

  1. 事务的概念
  2. 事务的特性

特性

说明

原子性

事务是一个整体，多个操作要么同时成功，要么同时失败

一致性

事务执行完毕后，数据是一致的(正确的结果)

隔离性

事务的操作，彼此之间是相互独立的，互不影响

永久性

事务完成后，数据的结果会被永久保存到文件中

  1. 事务的使用
-- 模拟银行卡转账的事务
-- 新建银行卡的表：系统Id和账号
drop table  if exists `test`;
create table `test`(
	id int primary key auto_increment not null comment '系统id',
	account varchar(20) not null comment '银行卡号',
	money decimal(10,2) not null comment '余额'

)charset=utf8;
-- 插入2条数据
insert into `test` (id, account, money) values 
	(default, 'A0001', 1000),
	(default, 'A0002', 0);
	
	
-- 存储过程代码示例
-- 新建一个存储过程：模拟转账
drop procedure if exists transfer_money_version01;
-- 语句结束的标志符号，临时改变，需要改回来
delimiter $$
-- 存储过程的定义语法格式，参数写在括号里面
create procedure transfer_money_version01()
-- 开始与结束成对出现
	begin
	select  * from test where id = 1;
	end $$
delimiter ;

-- 调用存储过程
call  transfer_money_version01();


drop procedure if exists transfer_money_version02;
-- 模拟银行卡转账的事务
delimiter $$
create procedure transfer_money_version02(account1 varchar(20), account2 varchar(20), balence int)
begin
	declare temp int default 0;
	-- 定义一个变量，用来记录是否有错误
	declare error int default 0;
	-- 定义异常错误的处理
	declare continue handler for sqlexception 
	begin
	set error = 1;
	end;
	-- 开启事务
	start transaction;
	-- 1、先从卡号1取出1000元（扣钱）
	update `test` set money = money - balence where account = account1;
	
	if row_count = 0  then  
	set error = 1;
	end if;
	
	set temp = (select money from (select money from test where account = account1) as t1 where money > 0);
	if temp < 0 then 
	set error = 1;
	end if;
	
	-- 2、把1000块存储到卡号2中
	update `test` set money = money + balence where account = account2;
	if row_count = 0 then  
	set error = 1;
	end if;
	-- 判断是否提交或回滚
	if error = 0 then
	commit;
	else
	rollback;
	end if;
	
end $$
delimiter ;

call transfer_money_version02('A0001', 'A0002', 9000);