mysql中transaction_在mysql中的存储过程中使用事务transaction【以下内容仅供参考】...

http://blog.sina.com.cn/s/blog_6dd65c6f0100t3u5.html

一.在处理事务时，使用SQLException捕获SQL错误，然后处理； 按照这个推论，我们必须在MySQL存储过程中捕获SQL错误，最后判断是回滚(ROLLBACK)还是提交(COMMIT)。 所以存储过程为：

DELIMITER $$

DROPPROCEDUREIF EXISTS  test_sp1 $$

CREATEPROCEDUREtest_sp1( )

BEGIN

DECLAREt_errorINTEGERDEFAULT0;

DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETt_error=1;

STARTTRANSACTION;

INSERTINTOtestVALUES(NULL,'test sql 001');

INSERTINTOtestVALUES('1','test sql 002');

IF t_error = 1THEN

ROLLBACK;

ELSE

COMMIT;

ENDIF;

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

set p_balance_site = (select cMoney from m_memberMoney where cMEMID=p_cMEMID_site);

INSERT INTO cashDetail ......(此处省略);

else

set p_intBalance_site = (select cIntegral from m_memberMoney where cMEMID=p_cMEMID_site);

INSERT INTO integralDetail ......(此处省略);

end if;

update m_memberMoney set totalConsMoney=totalConsMoney+p_curTotal,

totalConsIntegral=totalConsIntegral+p_curTotalcIntegral where cMEMID=p_cMEMID;

IF t_error = 1 THENROLLBACK;ELSECOMMIT;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 & ",'" & _

Now() & "'," & _

moneyAfterTax & "," & _

integralAfterTax & "," & _

sellerID & "," & _

cashOrPoints & ",'" & _

siteLoginName & "','" & _

transactionID_site & "','" & _

orderID & "','" & _

remarks_site & "','" & _

transactionID_seller & "'," & _

cMEMID & "," & _

lMoney & "," & _

lIntegral & ",'" & _

remarks & "')"

set rs1 = Connection.Execute(sqlConfirmPay) '将执行存储过程后的结果集赋给rs1

result = cInt(rs1("t_error"))  '得到返回的事务的状态

'response.Write("result:" & cStr(rs1("t_error")) & "
")

Connection.close

set Connection=nothing

rs1.close

set rs1 = nothing

'判断执行的存储过程是否出错

if result = 0 then

'如果没有出错，

......

else

'如果出错了

......

end if