MYSQL Midstream, transaction, terminate stored Procedure method summary

Recently in the project development, there is a logic processing, need to run in the website, app, backstage, so that the maintenance of the late to bring a lot of inconvenience, easy to omit the app side or the background, so the Java code to convert to stored procedures, the logical processing written in the MySQL side, which encountered cursors, transaction processing. The problem is not difficult, just easy to forget, do a summary:

DECLARE err INT default 0; #声明一个整形变量err, default value is 0
DECLARE orderdone INT DEFAULT false;--traversal cursor End identity
DECLARE cur_order CURSOR for SELECT id,uid,productid,amount from P_order WHERE state=0 and Orderno=i_orderno LIMIT 0, 1; --Creation of cursors
DECLARE CONTINUE HANDLER for not FOUND SET orderdone=true; --Binding cursor end identification (not used in this program)
DECLARE CONTINUE HANDLER for SQLEXCEPTION set err=1, #当sqlexception HANDLER when snapping to an exception, setting err=1

Pro_begin:begin-In order to jump out of the preparation

Start transaction;--BEGIN TRANSACTION (Create transaction point)

--Take value from it
OPEN Cur_order; --Open cursor
FETCH Cur_order into Intid,intuid,intpid,intoamount; --Using Cursors
CLOSE Cur_order; --Close cursor

The above is a simple cursor usage because only the first value is taken, so there is no use of the loop value, the basic use flow of the cursor: Define the cursor, open, use, close

IF Num>0 Then
SET O_result = 3; --Mobile phone number already exists
LEAVE Pro_begin; --This is the step out of the stored procedure and not continue
END IF;

The following is the rollback and commit of a transaction, usually written at the end of the stored procedure.

IF Err = 1 Then
ROLLBACK;
ELSE
COMMIT;
END IF;

End

MYSQL Midstream, transaction, terminate stored Procedure method summary