MySQL cursor functions and usage

definition

We often encounter a situation where we need to traverse the results of our query and process each piece of data traversed. This is when a cursor is used.
So: A cursor is a database query method for processing data stored on a MySQL server. In order to view or process the data in a result set, it provides the ability to traverse the data in the result set one row at a time.
Cursors are mainly used in loop processing, stored procedures, functions, and triggers.

The role of the cursor

For example, for the students above, we need to traverse each user and then add or subtract points based on their other comments. At this time we need to query all student information (including grades).

select studentid,studentname,score from students;

After execution, a set of student data is returned. If we need to traverse the student data one by one and then add points based on the specific situation, we need to use a cursor.
The cursor is equivalent to a pointer that points to the first row of data selected. The subsequent data can be traversed by moving the pointer.

Use of cursor

• Declare a cursor: Create a cursor and specify the select query that the cursor needs to traverse. The SQL will not be executed when the cursor is declared.
• Open the cursor: When the cursor is opened, the select statement corresponding to the cursor will be executed.
• Traverse data: Use a cursor to loop through each row of data in the select result and then process it.
• Business operation: the process of operating each row of data traversed, you can place any statement that needs to be executed (add, delete, modify, check): this depends on the specific situation
• Close the cursor: The cursor must be released after use.

Note: The temporary fields used need to be declared before defining the cursor.

Declaring a cursor

DECLARE cursor_name CURSOR FOR select_statement;

Declare a cursor. You can also define multiple cursors in a subroutine, but each cursor in a block must have a unique name. After declaring a cursor, it is also a single operation, but the SELECT statement cannot have an INTO clause.
Only one cursor can be declared in a begin end.

Open cursor

OPEN cursor_name;

Opens a previously declared cursor.

Traversing cursor data

FETCH cursor_name INTO var_list;

This statement fetches the next row (if any) using the specified open cursor and advances the cursor pointer. Get the result of the current row, put the result in the corresponding variable, and point the cursor pointer to the data of the next row.
When fetch is called, the data of the current row will be obtained. If there is no data in the current row, a NOT FOUND error will be triggered inside MySQL.

Close cursor

CLOSE cursor_name;

Remember to close the cursor after you have finished using it.

Cursor Example

Write a function that includes the calculation of students' scores and additional points

Data foundation

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 97.5 | 1 |
| 2 | helen | 96.5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | sol | 97 | 1 |
| 5 | b1 | 81 | 2 |
| 6 | b2 | 82 | 2 |
| 7 | c1 | 71 | 3 |
| 8 | c2 | 72.5 | 3 |
| 9 | lala | 73 | 0 |
| 10 | A | 99 | 3 |
| 16 | test1 | 100 | 0 |
| 17 | trigger2 | 107 | 0 |
| 22 | trigger1 | 100 | 0 |
+-----------+-------------+-------+---------+
13 rows in set

Writing functions involving cursors

The annotations here are very clear

mysql>
/* If the function exists, delete it */
DROP FUNCTION IF EXISTS fun_test;
/* The end of the statement is $*/
DELIMITER $
/*Create a function to add points to each student who meets the requirements. The added points cannot exceed the given value max_score*/
CREATE FUNCTION fun_test(max_score decimal(10,2))
RETURNS int
BEGIN
/*Define the real-time StudentId variable*/
DECLARE var_studentId int DEFAULT 0;
/*Define the variable for the calculated score*/
DECLARE var_score decimal(10,2) DEFAULT 0;
/*Define the cursor end mark variable*/
DECLARE var_done int DEFAULT FALSE;
/*Create a cursor*/
DECLARE cur_test CURSOR FOR SELECT studentid,score from students where classid<>0;
/*When the cursor ends, var_done will be set to true. You can use var_done to determine whether the cursor has ended later*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
/*Open cursor*/
OPEN cur_test;
/*Use Loop to loop through the cursor*/
select_loop:LOOP
/*First get the data of the current row, then put the data of the current row into var_studentId, var_score. If there is no data row, var_done will be set to true*/
FETCH cur_test INTO var_studentId,var_score;
/*Use var_done to determine whether the cursor has ended and exit the loop*/
IF var_done THEN
LEAVE select_loop;
END IF;
/*Add a random value to the var_score value, which cannot exceed the given score*/
set var_score = var_score + LEAST(ROUND(rand()*10,0),max_score);
update students set score = var_score where studentId = var_studentId;
END LOOP;
/*Close the cursor*/
CLOSE cur_test;
/*Return result: You can return the required content according to the actual situation*/
RETURN 1;
END $
/*End character is set to ;*/
DELIMITER ;
Query OK, 0 rows affected

Calling a function

mysql>
/* The parameter is 8, indicating that the upper limit of bonus points is 8 */
select fun_test(8);
+-------------+
| fun_test(8) |
+-------------+
| 1 |
+-------------+
1 row in set

View Results

Comparing the original score value, it is found that the score has been added with a random value, but it does not exceed the given score 8

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 105.5 | 1 |
| 2 | helen | 98.5 | 1 |
| 3 | lyn | 97 | 1 |
| 4 | sol | 97 | 1 |
| 5 | b1 | 89 | 2 |
| 6 | b2 | 90 | 2 |
| 7 | c1 | 76 | 3 |
| 8 | c2 | 73.5 | 3 |
| 9 | lala | 73 | 0 |
| 10 | A | 100 | 3 |
| 16 | test1 | 100 | 0 |
| 17 | trigger2 | 107 | 0 |
| 22 | trigger1 | 100 | 0 |
+-----------+-------------+-------+---------+
13 rows in set

View trigger log

There are 9 data that meet the conditions and have their scores modified, and they have all been recorded in the log by the trigger.

mysql>
/*In the previous article, I wrote a trigger that triggers logging when the students table is modified*/
select * from triggerlog;
+----+--------------+---------------+-----------------------------------------+
| id | trigger_time | trigger_event | memo |
+----+--------------+---------------+-----------------------------------------+
| 1 | after | insert | new student info,id:21 |
| 2 | after | update | update student info,id:21 |
| 3 | after | update | delete student info,id:21 |
| 4 | after | update | from:test2,101.00 to:trigger2,106.00 |
| 5 | after | update | from:trigger2,106.00 to:trigger2,107.00 |
| 6 | after | update | delete student info,id:11 |
| 7 | after | update | from:brand,97.50 to:brand,105.50 |
| 8 | after | update | from:helen,96.50 to:helen,98.50 |
| 9 | after | update | from:lyn,96.00 to:lyn,97.00 |
| 10 | after | update | from:sol,97.00 to:sol,97.00 |
| 11 | after | update | from:b1,81.00 to:b1,89.00 |
| 12 | after | update | from:b2,82.00 to:b2,90.00 |
| 13 | after | update | from:c1,71.00 to:c1,76.00 |
| 14 | after | update | from:c2,72.50 to:c2,73.50 |
| 15 | after | update | from:A,99.00 to:A,100.00 |
+----+--------------+---------------+-----------------------------------------+
15 rows in set

Cursor execution process

According to the above example, analyze the execution process of this cursor.
1. We created a cursor, and the data source was taken from the student table.
2. There is a pointer in the cursor. When the cursor is opened, the select statement corresponding to the cursor will be executed, and this pointer will point to the first row of records in the select result.
3. When the fetch cursor name is called, the data of the current row will be obtained. If there is no data in the current row, a NOT FOUND exception will be triggered.
When a NOT FOUND exception is triggered, we can use a variable to mark it, as shown above: DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_done=TRUE;
Set the value of the variable var_done to TRUE. The value of var_done can be used to control the exit of the loop: LEAVE select_loop;.
If there is data in the current row, the data of the current row will be stored in the corresponding variable, and the cursor pointer will point to the next row of data, as shown in the following statement: FETCH cur_test INTO var_studentId,var_score;

Summarize

1. The cursor is used to traverse the query results.
2. The process of using the cursor: declare the cursor, open the cursor, traverse the cursor, and close the cursor.
3. Cursors are mainly used in loop processing, stored procedures, and functions to query result sets.
4. The disadvantage of the cursor is that it can only operate row by row. It is not applicable when the amount of data is large and the speed is too slow. Most databases are collection-oriented, and the business is relatively complex. The use of cursors may cause deadlocks, affecting other business operations, which is not advisable. When the amount of data is large, using a cursor may cause insufficient memory.

The above is a detailed analysis of MySQL cursors. For more information about MySQL cursors, please pay attention to other related articles on 123WORDPRESS.COM!