Definition and use of MySQL cursor

Literally, we can understand what the cursor is. The cursor is like a mark floating on the water. This mark can swim back and forth, and then swim back and forth. The river here can be understood as a collection of data, and this marker swims back and forth between these data.

Why does MySQL have the concept of cursor? Because SQL language is a set-oriented statement, every query is a collection of data, and there is no way to deal with one of the records separately. If you want to process each record separately, you need a cursor.

Cursor is actually like a for/foreach loop in a programming language. It loops every data in an array (data collection) one by one, and then you use judgment statements in the for/foreach loop to process the data you are interested in.

Where can I use the cursor? They can be used in functions, stored procedures and triggers.

After talking about the concept, let's take a look at the fixed writing of the cursor. Regardless of whether the concept is understood or not, remember that the following fixed patterns can also complete the task of moving bricks.

1, declare cursor

SELECT statement is an ordinary query statement, such as: SELECT id, age FROM table.

2. Open the cursor

The SQL statement defined by the cursor will not be executed until the cursor is opened.

3. Take out the record

Store the currently recorded data in a variable.

When FETCH cannot find a record, it will throw an exception. The definition of an exception requires the following statement handlers.

If SELECT in the cursor statement has multiple fields, you need to receive multiple variables after INTO.

4, set the end condition

The function of this statement is to specify a condition that tells the program that all data has been circulated and can be ended. Because the cursor uses a WHILE loop to read each piece of data, it is necessary to provide an end condition for WHILE.

Processing Type: Yes, exit immediately. Continue the following processing.

Exception type: usually specified as not found, which means that no data was found.

Handling when an exception occurs: What should be done when an exception occurs? Generally, the value of a variable will be changed here to record that an exception has occurred, such as SET flat = 1.

5. Close the cursor

Actual combat code:

It's over. Do you understand? If you don't understand, it doesn't matter. Cursor handling is a set of fixed formats. Just insert it into your program according to the fixed format in the example above.