MySQL cursor

Time:2021-3-20

MySQL cursor

 

  • SQL statement is “set oriented programming“, which focuses on “what to get”, rather than “how to get”.
  • Sometimes we don’t need to do the same operation on every query structure set, but only operate some lines in it. At this time, we need the process oriented programming method, and the cursor is the embodiment of the process oriented programming method
  • A cursor is the equivalent of a “pointer,” which points to only one line at a time
  • The function of the cursor is to traverse the records returned by the query database in order to carry out the corresponding operation

Use of cursor

Declare (given result set), open, get data through cursor, close, release cursor

  • Declare that given the result set, the cursor is cleaned up after the end of the stored procedure

    DECLARE cursor_name CURSOR FOR (SELECT...);
  • Open the cursor and send the result set to the cursor workspace

    OPEN cursor_name;
  • Get data through cursor

    The cursor first determines whether the current row is empty. If it is empty, it changes the done. If it is not empty, it stores the data in the temporary variable. After reading, it enters the next row to prepare for the next reading.

    Be sure to understand the operation mode of fetch, otherwise there may be repeated fetch to data

    FETCH cursor_ Name into (variable name s);
  • Close cursor

    CLOSE cursor_name;
  • Because every time the fetch cursor is called, it attempts the next line, so aNOT FOUNDHandler to handle the situation where the cursor cannot read the next line

    DECLARE CONTINUE HANDLER FOR not found SET done = true;

    perhaps

    DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = true;

    SQLSTATE '02000'Can be seen as andnot foundThe same,SQLSTATE '02000'Is a condition that occurs when there are no more rows to loop.

    Both of the above two methods are feasible. Whether the value of done is true or 1 depends on how done is defined. Of course, the value defined as init can be initialized to true

     

Use example 1

The following tables are available

mysql> select * from t;
+----+------+-----------------+
| id | nums | content         |
+----+------+-----------------+
|  1 |    2 | NULL            |
|2 | 6 | 2612|
|Three is nine|
+----+------+-----------------+
3 rows in set (0.00 sec)

We read each line through a cursor and implement itImport nums into a new data tableIn practical work, this way can greatly reduce the difficulty of our “sub table” operation.

DELIMITER //

CREATE PROCEDURE transferData()
BEGIN
DECLARE temp_num INT;
DECLARE done BOOLEAN DEFAULT false;
DECLARE cur CURSOR FOR select nums FROM t;
DECLARE continue HANDLER FOR not found SET done = true;

CREATE TABLE IF NOT EXISTS t2 (auto_id int PRIMARY KEY AUTO_INCREMENT, val int not null);

OPEN cur;
REPEAT
IF NOT done THEN
FETCH cur INTO temp_num;
INSERT INTO t2 (val) VALUES (temp_num);
END IF;
UNTIL done=true END REPEAT;
CLOSE cur;
SELECT * from t2;
END//

DELIMITER ;

After executing the stored procedure, the following results will be displayed automatically:

mysql> select * from t2;
    -> //
+---------+-----+
| auto_id | val |
+---------+-----+
|       1 |   2 |
|       2 |   6 |
|       3 |   3 |
|       4 |   3 |
+---------+-----+
4 rows in set (0.00 sec)

We will find out why there is one more line of data here? It seems that it has been repeatedly fed and inserted. The reason is that it has not made clear the operation mode of fetch – it has not made clear when fetch will change

If we change to

CREATE PROCEDURE transferData2()
BEGIN
DECLARE temp_num INT;
DECLARE done BOOLEAN DEFAULT false;
DECLARE cur CURSOR FOR select nums FROM t;
DECLARE continue HANDLER FOR not found SET done = true;

CREATE TABLE IF NOT EXISTS t3 (auto_id int PRIMARY KEY AUTO_INCREMENT, val int not null);

OPEN cur;
REPEAT
FETCH cur INTO temp_num;
IF NOT done THEN
INSERT INTO t3 (val) VALUES (temp_num);
END IF;
UNTIL done=true END REPEAT;
CLOSE cur;
SELECT * from t3;
END//

The result will meet our expectations

mysql> select * from t3//
+---------+-----+
| auto_id | val |
+---------+-----+
|       1 |   2 |
|       2 |   6 |
|       3 |   3 |
+---------+-----+
3 rows in set (0.00 sec)