解决方案 »
- 请问哪里有Ubuntu linux下的Mysql下载,给个可用的链接
- 求助,如何使用mysql实现得到汉字的首字母
- mysql定义触发器错误,:[Err] 1415 - Not allowed to return a result set from a trigger
- 为何REPEAT会多取一行呢?
- 非常棘手的问题,SQL的,大家有什么好的方法吗?
- 这条语句哪里错了?
- mysql为何默认是latin 1字符集呀?phpmyadmin中显示中文乱码?
- 100分求一个简单的sql,请高手帮忙,2天内结贴!
- 安装MYSQL出现问题
- 请问各位前辈,如何使MYSQL3.23.52-WINDOWS版的支持中文?
- mysql表数据统计
- c#客户端如何连接mysql集群?
在事务开始之前,启用SET @@autocommit = 0;
DELIMITER //
CREATE PROCEDURE proc_test
(IN i_userid VARCHAR(32))
begin
SET @@autocommit = 0; // 添加这一句,设置为手工提交、回滚事务
loop_label: loop
start transaction;
insert into test1(userid) values(i_userid);
if row_count() < 1 then
set @ret = -1;
rollback;
leave loop_label;
end if;
insert into test2(userid) values(i_userid);
if row_count() < 1 then
set @ret = -1;
rollback;
leave loop_label;
else
set @ret = 0;
commit;
leave loop_label;
end if;
end loop;
select @ret;
end
//
DELIMITER ;
SET @@autocommit = 0;
这一句我加上了
问题还是依旧
CREATE PROCEDURE proc_test
(IN i_userid VARCHAR(32))
begin
declare out_status varchar(10);
DECLARE CONTINUE HANDLER FOR 1265 SET out_status='Truncated';
loop_label: loop
start transaction;
insert into test1(userid) values(i_userid);
if out_status = 'Duplicate' then
rollback;
leave loop_label;
end if;
insert into test2(userid) values(i_userid);
if out_status = 'Duplicate' then
set @ret = -1;
rollback;
leave loop_label;
else
set @ret = 0;
commit;
leave loop_label;
end if;
end loop;
select @ret;
end
//
DELIMITER ;
CREATE PROCEDURE chenwei.pr_trans_money
( IN iFrom VARCHAR(50), -- 转账账户
IN iTo VARCHAR(50), -- 转入账户
IN iMoney DECIMAL, -- 转账金额
OUT error_code INT -- 错误代码
)
SQL SECURITY INVOKER
label_flag:
BEGIN
DECLARE vCount INT;
DECLARE vState INT;
SET @@autocommit = 0;
-- 如果转账账户为空
IF ( iFrom IS NULL ) THEN
SET error_code = 1000;
LEAVE label_flag;
END IF;
-- 如果转账账户不存在
IF NOT EXISTS ( SELECT * FROM tb_user_act WHERE userid = iFrom ) THEN
SET error_code = 1001;
LEAVE label_flag;
END IF;
SELECT act_state
INTO vState
FROM tb_user_act
WHERE userid = iFrom;
-- 如果转账账户已冻结
IF ( vState = 9 ) THEN
SET error_code = 1002;
LEAVE label_flag;
END IF;
-- 如果转账账户余额不足
IF NOT EXISTS ( SELECT * FROM tb_user_act WHERE userid = iFrom AND balance >= iMoney ) THEN
SET error_code = 1003;
LEAVE label_flag;
END IF;
-- 如果转入账户为空
IF ( iTo IS NULL ) THEN
SET error_code = 2000;
LEAVE label_flag;
END IF;
-- 如果转入账户不存在
IF NOT EXISTS ( SELECT * FROM tb_user_act WHERE userid = iTo ) THEN
SET error_code = 2001;
LEAVE label_flag;
END IF;
SELECT act_state
INTO vState
FROM tb_user_act
WHERE userid = iTo;
-- 如果转入账户已冻结
IF ( vState = 9 ) THEN
SET error_code = 2002;
LEAVE label_flag;
END IF;
-- 如果转账金额不正确
IF ( iMoney <=0 ) THEN
SET error_code = 1004;
LEAVE label_flag;
END IF;
-- 如果通过各项验证,下面开始转账
START TRANSACTION;
SAVEPOINT point1;
-- 从转账账户扣钱
UPDATE tb_user_act
SET balance = balance - iMoney
WHERE userid = iFrom;
-- 如果转账账户扣钱成功
IF ( ROW_COUNT() = 1 ) THEN
-- 向转入账户加钱
UPDATE tb_user_act
SET balance = balance + iMoney
WHERE userid = iTo;
IF ( ROW_COUNT() = 1 ) THEN
COMMIT;
SET error_code = 0;
ELSE
ROLLBACK TO SAVEPOINT point1;
SET error_code = 9999;
END IF;
ELSE
SET error_code = 9999;
END IF;
END;