How to use MySQL Cursors

Using cursors (cursor)

1. Declaring 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 of the cursors in a block must have a unique name. Declaring a cursor is also a single operation, but you cannot use a SELECT statement to have an into clause.

2. Cursor Open statement

OPEN cursor_name
This statement opens a cursor that was previously declared.

3. Cursor FETCH statement

FETCH cursor_name into Var_name [, Var_name] ...
This statement reads the next line with the specified open cursor, if there is a next line, 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

Copy Code

Create a table structure CREATE TABLE person (name varchar (10)); INSERT into person values (¹a¹); INSERT into person values (¹b¹); INSERT into person values (¹c¹); INSERT into person values (¹d¹); INSERT into person values (¹e¹); Class drop procedure if exists cursortest Establish CREATE PROCEDURE cursortest () BEGIN Defining variables DECLARE name1 varchar (a) default¹¹; DECLARE name2 varchar (m) default¹¹; DECLARE mycursor CURSOR for select name from person; Declare CONTINUE HANDLER for sqlstate¹02000¹set tmpname = null; Open cursor OPEN MyCursor; Traversing cursors FETCH mycursor into name1; Add and use the name of the cursor query; Number separated while (name1 isn't null) do Set name1 = CONCAT (name1, ";"); Set name2 = CONCAT (name2, name1); FETCH mycursor into name1; End while; Close MyCursor; Select MyCursor; End; Call cursor Call MyCursor ()

Run 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

Copy Code

--Define local variables DECLARE o varchar (128); --Defining cursors DECLARE ordernumbers CURSOR For SELECT callee_name from Account_tbl where acct_timeduration=10800; DECLARE CONTINUE HANDLER for not FOUND SET No_more_departments=1; SET no_more_departments=0; --Open cursor OPEN ordernumbers; --Loop all the lines REPEAT --Get Order number FETCH ordernumbers into O; Update account set allmoney=allmoney+72,lastmonthconsume=lastmonthconsume-72 where Numtg=@o; --End of Loop UNTIL no_more_departments End REPEAT; --Close cursor Close ordernumbers;

The attributes of a cursor (cursor)

1, read-only, cannot update.
2, does not scroll the
3, insensitive, insensitive to the server can live not to copy its result table

The cursor (cursor) must be declared before the handler is declared, and the variables and conditions must be declared before the cursor or handler is declared