MYSQL transactions, cursors, and loops

For MySQL I am also a beginner, write in here I know the fur, if there is a mistake straight tube shoot brick, I also good study, first thanked!

First MySQL does not have SQL sever the same as BEGIN Tran end Tran begin catch end catch. The MySQL transaction mechanism I understand is to catch the wrong handle, if there should be rollback, does not exist can commit. No nonsense on the code:

1 DECLARESys_errorINT DEFAULT 0;//defines an error variable that represents an error return2 3  DECLARE CONTINUEHANDLER forSQLEXCEPTIONSETSys_error=1;//Define settings If you have an error handle, set Sys_error to 14 5STARTTRANSACTION;//Start a transaction6 7 8 /*statements that write processing logic*/9 Ten    IFSys_error=1  Then         One         ROLLBACK;//there was an error rolling back A         ELSE     -         COMMIT;//No error committed -         END IF;

The following is a MySQL cursor and loop

In fact, the cursor is also a cyclic read data so I'm following the problem of the loop

Does the Mysql loop end in fact also by Mark, what do I give an example when we have two layers of loops (nested)

It is generally:

     DECLARE  i INT DEFAULT 1;     DECLARE  J  INT DEFAULT 1;        While I<5     do         BEGIN
             SET j=1;             While J<4             do                BEGIN                  SELECT i,j;                  SET j=j+1;               END;             END while;               SET i=i+1;                     END;     

This only executes the results of the i=1,j=1,2,3 i=2.

The loop in MySQL is controlled by the state handle, similar to the transaction, no nonsense on the code

　　

     DECLARE done INT DEFAULT 0;     DECLARE  i INT DEFAULT 1;     DECLARE  J  INT DEFAULT 1;      DECLARE CONTINUE HANDLER for SQLSTATE ' 02000 ' SET done = 1; The personal understanding is that the loop is over and set to 1, just like the cursor read state in SQL (@ @FETCH_STATUS) As for 02000 I can only guess the loop about the status code. While     i<5     Do         BEGIN            SET j=1;             While J<4             do                BEGIN                  SELECT i,j;                  SET j=j+1;               END;             END while;               SET i=i+1;                           SET done =0;  If this step is less, the outer loop will also exit directly, just like the result above.            END;     

Okay, that's about it, and one of those is the local and global variables in MySQL, especially the user's third-party MySQL tool client, where the global variable keeps its original value without disconnecting the client. Specifically, you can find MySQL information.

MYSQL transactions, cursors, and loops