Mysql creates a stored procedure and uses Cursor to cyclically update _ MySQL

Mysql creates a stored procedure and uses Cursor to cyclically update bitsCN.com

Use cursor)

1. declare a cursor

DECLARECursor_nameCursor 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

OPENCursor_name
This statement opens the previously declared cursor.

3. cursor FETCH statement

FETCHCursor_nameINTO 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.

I checked a lot of information before writing, but I couldn't execute it all the time. I thought about it and wrote an example. it was completely correct after testing.

The following example shows how to use a cursor to update a stored procedure:

-- Procedure "useCursor" ddlcreate procedure 'usecursor '() BEGIN/* definition of local variables declare */declare aid bigint default 0; declare mdsl bigint default 0; declare stop int default 0; declare cur cursor for (select count (area_info.id) as mdsl, area_info.id as aid from area_info right join subbranch_info on region = area_info.idwhere area_info. I _record_status = 0 and region _ Status = 0 group by area_info.id);/* mysql does not know why to add an exception to the judgment? * This captures a cursor exception * and sets the loop to jump out of the loop when the variable stop is null. */Declare continue handler for sqlstate '000000' SET stop = null;/* OPEN cursor */OPEN cur;/* Move the cursor down, pay the two queried values to the two defined variables */FETCH cur INTO mdsl and aid;/* loop body, which obviously adds and uses the names queried by the cursor; no. */WHILE (stop is not null) DOupdate zlgsydrsp. area_info set I _subbran_count = mdsl where id = aid;/* Move the cursor one step down */FETCH cur INTO mdsl, aid; END WHILE; CLOSE cur; END;

BitsCN.com