mysql 存储过程
1.变量赋值：
方式一：set @memid = (SELECT member_id from member_info where member_account = memberAccount limit 1);
方式二：select fund_id ,balance_income into @fundid, @balanceincome from member_fundinfo where member_id=@memid and identity_type = 1 limit 1 ;
2.终止运行
使用leave
label_pro:BEGIN
select fund_id ,balance_income into @fundid, @balanceincome from member_fundinfo where member_id=@memid and identity_type = 1;
IF @fundid is null || @balanceincome is null THEN
SELECT 'fundid balanceincome 错误' as error,@fundid as fundid,@balanceincome as balanceincome;
leave label_pro;
END IF;
END
3.输入参数使用
不能带@符号；
CREATE DEFINER=`root`@`%` PROCEDURE `addmoney`(IN memberAccount varchar(30),in memberId bigint(20),in money decimal(18,2),in dingdingNo varchar(50))
label_pro:BEGIN
-- 会员查找
set @memid = (SELECT member_id from member_info where member_account = memberAccount limit 1);
IF @memid is null || @memid != memberId THEN
SELECT 'memberId 错误' as error,@memid as dataid,memberId as inputid;
leave label_pro;
END IF;
END
4.异常处理
方式一：一直运行在最后进行rollback
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=-1;
--------------------------
IF t_error = -1 THEN
SELECT '执行失败';
ROLLBACK;
ELSE
SELECT '执行成功';
COMMIT;
END IF;
-------------------------------
方式二：未测试
DECLARE errno INT;
DECLARE msg TEXT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
--5.6之后才可以使用
GET DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT '执行异常',@errno,@msg;
ROLLBACK;
END;
https://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html
https://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html
5.事务处理
开启事务
START TRANSACTION;
回滚
ROLLBACK;
提交
COMMIT;
事务中请不要使用 create table等会执行隐式事务提交的命令，否则在会导致隐式提交事务，而不会回滚操作。
6.手动触发异常
SIGNAL SQLSTATE '45001'SET MYSQL_ERRNO=2000,MESSAGE_TEXT='故意触发异常';
7.临时表
START TRANSACTION;
insert into test_table(name) values('张三');
drop temporary table if exists tmp_table; -- 如果这里替换为 drop table if exists tmp_table同样可以删除临时表，但是会导致事务自动提交
create temporary table tmp_table select * from test_table;
ROLLBACK;
https://www.cnblogs.com/sdlz/p/9061144.html