MySQL cursor cursors

The following instructions are based on MySQL 5.5.

Overview: I know that most people don't use much for MySQL cursors. In most cases, MySQL can use the "set" operation to meet the 90% requirement. MySQL cursor as a "record" operation, is a complement to operational data.

MySQL cursor three major features (Junior "pit"):

1. Read only: Cursor itself does not provide the operation to modify data, only fetch columns into variables. (Of course you can take the data out, then use the UPDATE statement to operate, but there is a pit, the 3rd note).

2, can not scroll: can only traverse the data in one direction, can not roll back, not at random, can not skip some records.

3. Data-sensitive (Tai hang): cursors are divided into two categories, one for data-sensitive types, and one for non-data-sensitive types .

• Data-sensitive type cursors: Cursors point to the actual data, while traversing the data, if the other session modifies the current cursor traversal of the data collection, it affects the behavior of current cursors.
• Non-data-sensitive type cursors: The data collection of a cursor query is placed into a temporary table, and the cursor points to a collection of records for the staging table.
• MySQL cursor: is a data-sensitive type of cursor.
• based on the above instructions: Do not update the data on the table that the MySQL cursor opens . (There is also some data that says: Do not update the Where Condition field of SELECT statement in a cursor declaration statement)

MySQL cursor doesn't have a trivial feature:

1. Cursors can only be used in stored procedures and are included in the begin ... END statement.

2. Need close cursor to close cursors to release resources. In fact, if not released, the cursor is automatically released after End statement.

3. The declaration statement of a cursor must be placed after the declaration statement of all variables.

4. The declaration statement of the cursor must be associated with SELECT statement.

5. Define a continue HANDLER for not FOUND to help the cursor jump out of the fetch loop when fetch no data.

6. CONTINUE HANDLER for not FOUND This condition is triggered not only for the cursor not finding the data, but also for the other SELECT statement (in the case where no data is retrieved).

MySQL cursor cursors