MySQL Advanced Learning knowledge Development: A cursor for the MySQL stored procedure!

first, the concept of MySQL cursors　　Cursor Description:

The cursor of MySQL is an important concept, by finding the data with its own understanding, mainly to draw the following points about their own understanding.

The idea of a data buffer: the design of a cursor is the idea of a data buffer that holds the results of SQL statement execution.

The first is the data base: A cursor is a technique that can continue to operate flexibly after retrieving data from the data table. .

Similar to a pointer: A cursor is similar to a pointer to a data structure stack, used to pop out the data pointed to, and can only fetch one at a time.

advantages and disadvantages of cursors:Advantages of cursors:

because cursors are for row operations, it is a separate idea to separate the same or different operations from each row obtained from a select query in a database. You can satisfy a particular action on a result row.

Cursor and cursor location based on the ability to modify and delete. The MySQL database does not specifically describe a single line of expression, but this is needed, so, personally, I think the cursor is drawn out of a collection-oriented system of relational databases,

Express individually for rows (also understood as web-based data: Cursors are a bridge between set-oriented and line-oriented design ideas)

Cursor Disadvantages:

The disadvantage of cursors is that for a little bit, that is, only one row of operations, in the case of large amounts of data, is not applicable, slow. Here's a metaphor: when you go to an ATM to save money, you want to save it all at once,

Or 1001 sheets of one, and here are 1001 sheets of a single cursor operation for the row. Most of the database is facing the collection, the business will be more complex, and the use of cursors will have deadlock, affecting other business operations, undesirable. When the volume of data is large, using cursors can cause out-of-memory behavior.

usage Scenarios for cursors

For the advantages and disadvantages of cursors, I summarize the use of cursors, mainly used in the loop processing, stored procedures, functions used to query the result set, for example: we need to iterate from the table and get the desired result set, the use of cursor operation is very convenient and fast.

second, the use of cursorssteps for using cursors

The use of cursors is generally divided into 5 steps, mainly: To define cursors---open cursors----use cursors, close cursors, and release cursors.

(1). Defining cursors

The syntax is:

MySQL Advanced Learning knowledge Development: A cursor for the MySQL stored procedure!