存储过加上事务的处理
–表acountmoney
–存储过程在MySQL的前后顺序很重要吗?为啥捕获异常不放在在我现在这个位置就没用。
–顺序的摆放必须要这样吗?我换下顺序,或者错开就会错误
–希望有高手解答下
–还有varchar的长度必须声明,否则也会报错
DROP PROCEDURE IF EXISTS trade_money
;
CREATE DEFINER = breezetalk
@%
PROCEDURE trade_money
(IN fromUser
varchar(64),IN toUser
varchar(64),IN money
float(10,2),OUT flag
varchar(64))
BEGIN
#Routine body goes here…
DECLARE moneyfrom float(10,2) ;
DECLARE moneyto FLOAT(10,2);
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; –捕获异常
SELECT summoney INTO moneyfrom from acountmoney WHERE username = fromUser ;
SELECT summoney INTO moneyto from acountmoney WHERE username = toUser ;
SET moneyfrom = moneyfrom-money ;
SET moneyto = moneyto + money ;
START TRANSACTION ;
UPDATE acountmoney SET summoney = moneyfrom where username = fromUser ;
UPDATE acountmoney SET summoney = moneyto WHERE username = toUser ;
COMMIT ;
IF(t_error=1) THEN
ROLLBACK ;
SET flag = '0' ;
ELSE
COMMIT ;
SET flag = ‘1’ ;
END IF ;
END;
Java 代码调用
/*
conn 是数据库连接的接口
CallableStatement 操作存储过程
conn = DBConnnection.getConnection();
String sql = “call trade_money(?,?,?,?)” ;
CallableStatement cs = conn.prepareCall(sql);
cs.setString(1, fromusername);
cs.setString(2, tousername);
float mon = Float.valueOf(money);
cs.setFloat(3, mon);
cs.registerOutParameter(4,java.sql.Types.VARCHAR); // 注册一个返回值
cs.execute();
String i = cs.getString(4); //获取返回值
//注册和获取的下标是一样的!!!