- #在存储过程中使用事务
- DROP PROCEDURE IF EXISTS sp5;
- CREATE PROCEDUREsp5 (INpINT,OUT rst_codeINT,OUT rst_msgVARCHAR(50)) COMMENT'在存储过程中使用事务'
- BEGIN
- DECLAREerr_codeINT DEFAULT 0;
- DECLARE CONTINUEHANDLERFORSQLEXCEPTIONSETerr_code=1;
- SETrst_code=err_code, rst_msg= 'success';
- IFp=0 THEN
- SIGNAL SQLSTATE '45000' SETMESSAGE_TEXT= rst_msg;
- SETrst_code=err_code, rst_msg= 'When p=0 is not allowed';
- END IF;
- IFp=1 THEN
- SIGNAL SQLSTATE '45000' SETMESSAGE_TEXT= rst_msg;
- SETrst_code=err_code, rst_msg= 'When p=1 is not allowed';
- END IF;
- START TRANSACTION;
- INSERT INTOtbl(title,author)VALUES('Learn CSharp','James');
- INSERT INTOtbl(title,author)VALUES('Learn CSharp','James');
- INSERT INTOtbl(title,author)VALUES('Learn CSharp','James');
- IFerr_code= 1 THEN
- ROLLBACK;
- ELSE
- COMMIT;
- END IF;
- END
- #调用
- CALL sp5(2,@rst_code,@rst_msg);
- SELECT @rst_code asrst_code,@rst_msg asrst_msg
来源: