在mySQL中的存储过程中使用事务transaction_Sean_新浪博客,Sean,
来源: 在mysql中的存储过程中使用事务transaction_Sean_新浪博客
一.
在处理事务时,使用SQLException捕获SQL错误,然后处理; 按照这个推论,我们必须在MySQL存储过程中捕获SQL错误,最后判断是回滚(ROLLBACK)还是提交(COMMIT)。 所以存储过程为:
- DELIMITER
$$ - DROP
PROCEDURE IF EXISTS test_sp1 $$ - CREATE
PROCEDURE test_sp1( ) -
BEGIN -
DECLARE t_error INTEGER DEFAULT 0; -
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; -
-
START TRANSACTION; -
INSERT INTO test VALUES(NULL, ‘test sql 001′); -
INSERT INTO test VALUES(‘1’, ‘test sql 002′); -
-
IF t_error = 1 THEN -
ROLLBACK; -
ELSE -
COMMIT; -
END IF; -
-
END$$ - DELIMITER
;
在这个例子中,我们为test_sp1() 定义了一个 SQLEXCEPTION 参数 t_error, 在遇到SQL错误时,继续执行(CONTINUE); 如果执行状态没有错误,则提交,反之回滚!
二.在调用事务时,将事务的执行状态(即:事务是提交了还是回滚了),返回给被调者。
下面给出另一个例子:
CREATE DEFINER=`3dmodelbaseadmin`@`%` PROCEDURE `p_userConfirmPay`(
in p_lID int,
in p_endTime DATETIME,
in p_moneyAfterTax decimal(10,2),
in p_integralAfterTax decimal(10,0),
in p_sellerID int unsigned,
in p_cashOrPoints int,
in p_loginName_site varchar(50),
in p_transactionID_site char(100),
in p_orderID char(100),
in p_remarks_site char(100),
in p_transactionID char(100),
in p_cMEMID INT UNSIGNED,
in p_curTotal DECIMAL(10,2),
in p_curTotalcIntegral decimal(10,0),
in p_remarks char(100))
BEGIN
DECLARE p_cMEMID_site INT;
DECLARE p_balance_site DECIMAL(10,2);
DECLARE p_balance DECIMAL(10,2);
DECLARE p_intBalance_site DECIMAL(10,0);
DECLARE p_intBalance DECIMAL(10,0);
DECLARE t_error INTEGER DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
START TRANSACTION;
set p_cMEMID_site=(select cMEMID from m_member where cLoginName=p_loginName_site);
update l_memberdownlog set lState=1,endTime=p_endTime where lID=p_lID;
update m_memberMoney set cMoney=cMoney-p_moneyAfterTax,
cIntegral = cIntegral-p_integralAfterTax where cMEMID=p_cMEMID_site;
update m_memberMoney set cMoney=cMoney+p_moneyAfterTax,
cIntegral = cIntegral+p_integralAfterTax where cMEMID=p_sellerID;
if p_cashOrPoints=0 then
else
end if;
update m_memberMoney set totalConsMoney=totalConsMoney+p_curTotal,
totalConsIntegral=totalConsIntegral+p_curTotalcIntegral where cMEMID=p_cMEMID;
IF t_error = 1 THEN
ELSE
END IF;
select t_error;
END
三.在Asp中被调者使用返回的事务状态:
set rs1 = server.CreateObject(“ADODB.RecordSet”)
set Connection = server.createobject(“adodb.connection”)
Connection.open conn
sqlConfirmPay = “p_userConfirmPay(” & ID & “,'” & _
set rs1 = Connection.Execute(sqlConfirmPay) ‘将执行存储过程后的结果集赋给rs1
result = cInt(rs1(“t_error”))
‘response.Write(“result:” & cStr(rs1(“t_error”)) & “<br />”)
Connection.close
set Connection=nothing
rs1.close
set rs1 = nothing
‘判断执行的存储过程是否出错
if result = 0 then
else
end if