SoFunction
Updated on 2025-03-04

Introduction and use of MySQL cursors

definition

Cursors are a method of processing data. In order to view or process data in the result set, cursors provide the ability to traverse data one row at a time in the result set.

Cursors are also a process-oriented SQL programming method, so they are generally used in stored procedures, functions, triggers, and loop processing.

Cursors are mainly used in interactive applications where users need to scroll the data on the screen and browse or make changes to the data.

The role of cursor

A cursor is equivalent to a pointer, which points to the first row of data in select, and the subsequent data can be traversed by moving the pointer.

Cursors are used to effectively process the query result set as a unit.

A cursor can be positioned on a specific row in the cell, and retrieves one or more rows from the current row of the result set.

You can modify the current row of the result set.

Generally, cursors are not used, but when data is processed one by one, cursors are very important.

Use of cursors

In mysql, cursors can be used in stored procedures, functions, triggers, and events.

Cursors need to be used with the relevant handler and defined before the handler.

A cursor has the following three properties:

  • Asensitive: The database can also choose not to copy the result set
  • Read only: Not updated, read only
  • Nonscrollable: The cursor can only move in one direction and cannot skip any row of data.

Declare cursor:

Create a cursor and specify the select query that the cursor needs to traverse. This SQL will not be executed when declaring the cursor.

Open the cursor:

When opening the cursor, the select statement corresponding to the cursor will be executed.

Traversing the data:

Use a cursor to loop through each row of data in the select result and then process it.

Business operations:

In the process of operating each line of data traversed, any execution statements that need to be executed can be placed (add, delete, modify and check): this depends on the specific situation.

Close the cursor:

It must be released after the cursor is used (the cursor occupies a bit large memory).

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

Cursor slogan

The process of using cursors: declare cursors, open cursors, traverse cursors, close cursors

Declare cursor:DECLARE Cursor Name CURSOR FOR Query Statement;

Open the cursor:open cursor name;

Traversal cursor:fetch cursor name into variable list;

Take out the result of the current row, place 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 inside mysql will be raised.

Close the cursor:close cursor name;It must be closed after the cursor is used.

Conditional processing

DECLARE CONTINE HANDLER expression 1 SET expression 2:

The purpose of this code is to define a CONTINE HANDLER. This function is to execute the statement of expression 2 when the condition of expression 1 appears.

This statement can achieve the change of conditions. The essence of using mysql exception processing is to use mysql, and it is often used on cursors to assist in determining whether the cursor data has been traversed.

For exampleDECLARE CONTINUE HANDLER FOR NOT FOUND …The statement is to handle exceptions when the cursor has no next record available for access.

Create table-test1-test2-test3

DROP TABLE IF EXISTS test1; 
CREATE TABLE test1(a int,b int); 
INSERT INTO test1 VALUES (1,2),(3,4),(5,6); 
DROP TABLE IF EXISTS test2; 
CREATE TABLE test2(a int); 
INSERT INTO test2 VALUES (100),(200),(300); 
DROP TABLE IF EXISTS test3; 
CREATE TABLE test3(b int); 
INSERT INTO test3 VALUES (400),(500),(600);

Write a function to calculate all sums of fields a and b in test1 table

/*Delete function*/ 
DROP FUNCTION IF EXISTS fun1; 
/*Declaration ending character is $*/ 
DELIMITER $ 
/*Create function*/ 
CREATE FUNCTION fun1(v_max_a int) 
  RETURNS int 
  BEGIN 
    /* Used to save the results*/ 
    DECLARE v_total int DEFAULT 0; 
    /*Create a variable to save the value of a in the current row*/
    DECLARE v_a int DEFAULT 0; 
    /*Create a variable to save the value of b in the current row*/ 
    DECLARE v_b int DEFAULT 0; 
    /*Create cursor end flag variable*/ 
    DECLARE v_done int DEFAULT FALSE; 
    /*Create cursor*/ 
    DECLARE cur_test1 CURSOR FOR SELECT a,b from test1 where a<=v_max_a; 
    /*Set the value of v_done to true when the cursor ends. You can judge whether the cursor ends*/ 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; 
    /*Set v_total initial value*/ 
    SET v_total = 0; 
    /*Open cursor*/ 
    OPEN cur_test1; 
    /* Use Loop to loop over the cursor*/ 
    a:LOOP 
      /*First get the data of the current row, and then put the data of the current row into v_a, v_b. If there is no data in the current row, v_done will be set to true*/
      FETCH cur_test1 INTO v_a, v_b; 
      /*Judge whether the cursor has ended through v_done and exit the loop*/ 
      if v_done THEN 
      LEAVE a; 
      END IF; 
      /*Accumulate v_total value*/ 
      SET v_total = v_total + v_a + v_b;
    END LOOP; 
    /*Close cursor*/ 
    CLOSE cur_test1; 
    /*Return result*/ 
    RETURN v_total; 
  END $ 
/*End Sign is set to;*/ 
DELIMITER ;

inDECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE; It is the syntax for exception handling, which means that when a NOT FOUND error is encountered, v_done is set to ture and continue to execute the current task.

test

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)
mysql> select fun1(1);
+---------+
| fun1(1) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)
mysql> select fun1(4);
+---------+
| fun1(4) |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)
mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
+------+------+
3 rows in set (0.00 sec)
mysql> select fun1(5);
+---------+
| fun1(5) |
+---------+
|      21 |
+---------+
1 row in set (0.00 sec)

Cursor process analysis

Take the above example code as an example, let’s take a look at the detailed execution process of the cursor.

There is a pointer in the cursor. When the cursor is opened, the select statement corresponding to the cursor will be executed. This pointer will point to the first line of the select result record.

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, the NOT FOUND exception will be triggered. When the NOT FOUND exception is triggered, we can use a variable to mark it, as follows:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done=TRUE;

When the cursor has no data triggers the NOT FOUND exception, set the value of the variable v_down to TURE, and the exit of the loop can be controlled through the value of v_down in the loop.

If there is data in the current row, the data in the current row is stored in the corresponding variable and the cursor pointer is pointed to the next row of data, as follows:

fetch cursor name into variable list;

This is the article about the introduction and use of MySQL cursors. For more related contents of MySQL cursors, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!