How to use mysql cursors

I personally think it is a cursor, which is an identifier used to identify where the data is obtained. You can also understand it as a subscript in an array ..

I personally think it is a cursor, which is an identifier used to identify where the data is obtained. You can also understand it as a subscript in an array ..

Use cursor)

1. Declare a cursor

DECLARE cursor_name cursor for select_statement
This statement declares a cursor. You can also define multiple cursors in a subroutine, but each cursor in a block must have a unique name. A single operation is performed after the cursor is declared, but the SELECT statement cannot have an INTO clause.

2. cursor OPEN statement

OPEN cursor_name
This statement opens the previously declared cursor.

3. cursor FETCH statement

FETCH cursor_name INTO var_name [, var_name]...
This statement reads the next row with the specified open cursor (if there is a next row) and advances the cursor pointer.

4. cursor CLOSE statement

CLOSE cursor_name
This statement closes the previously opened cursor.

Example 1

The Code is as follows:

// Create a table structure Create table person (name varchar (10 )); Insert into person values (values a values ); Insert into person values (values B values ); Insert into person values (into c segments ); Insert into person values (values d values ); Insert into person values (values e values ); // Initialization Drop procedure if exists cursorTest // Create Create procedure cursorTest () BEGIN // Define variables Declare name1 varchar (10) default zookeeper; Declare name2 varchar (100) default zookeeper; Declare mycursor cursor for select name from person; Declare continue handler for sqlstate limit 02000 sorted SET tmpname = null; // Open the cursor OPEN mycursor; // Cursors FETCH mycursor INTO name1; // Add the names queried by the cursor and separate them with the; Sign WHILE (name1 is not null) DO Set name1 = CONCAT (name1 ,";"); Set name2 = CONCAT (name2, name1 ); FETCH mycursor INTO name1; End while; CLOSE mycursor; Select mycursor; END; // Call the cursor Call mycursor ()

Running result:

Mysql> call mycursor ()
+ -------------------------------------- +

| Name2 |

+ -------------------------------------- +

| A; B; c; d; e; |

+ -------------------------------------- +

1 row in set (0.01 sec)

Example 2

A complete example:

The Code is as follows:

-- Define local variables DECLARE o varchar (128 ); -- Define a cursor DECLARE ordernumbers CURSOR FOR SELECT callee_name FROM account_tbl where acct_timedurl = 10800; Declare continue handler for not found set no_more_attributes = 1; SET no_more_departments = 0; -- Open the cursor OPEN ordernumbers; -- Loop all rows REPEAT -- Get order number FETCH ordernumbers INTO o; Update account set allMoney = allMoney + 72, lastMonthConsume = lastMonthConsume-72 where NumTg = @ o; -- Loop ends UNTIL no_more_administrative ments End repeat; -- Close the cursor CLOSE ordernumbers;

Cursor features

1. Read-only, cannot be updated.
2. Do not scroll
3. No sensitivity. No Sensitivity means the server can survive and cannot copy its result table.

Cursor must be declared before the handler is declared, and variables and conditions must be declared before the cursor or handler is declared