Simple practices for MySQL cursors

Q: Why use cursors?

A:

In a stored procedure (or function), if a SELECT statement returns only 1 rows in the result set, you can use the SELECT INTO statement (described in the previous blog) to get the row processed, and if there are multiple rows in the result set, a simple SELECT statement is processed in batches. You need to forward or rewind one or more rows in the retrieved row ... If you want to process each of these rows, you must use cursors.

Q: What is a cursor?

A:

A cursor is a database query stored on a MySQL server, which is not a SELECT statement but a result set retrieved by the statement, and can be viewed as a pointer to a query result set, and a row can be processed from the result set one line at a time through the cursor.

Note: MySQL cursors can only be used for stored procedures and functions.

Cursor Process: 4 steps

① declares cursor declare: No data is retrieved, just defines the SELECT statement to use

② opening a cursor open: Opens a cursor for use and retrieves the data actually using the SELECT statement defined in the previous step

③ retrieving cursor Fetch: Remove (retrieve) rows as needed for cursors filled with data

④ closing cursors Close: Cursors must be closed when the cursor is used to end

1. Declaring cursors

DECLARE cursor_name cursor for select_statement;

Declares a cursor cursor_name that points to the result set of the query select_statement.

Attention:

The ① cursor declaration must appear after the variable and the conditional declaration, but before the exception-handling declaration

② can have multiple cursor declarations in a process

2. Open cursor

OPEN cursor_name;

Cursor_name is the name defined in the declaration, and the corresponding select_statement is executed when the cursor is opened.

3. Retrieving cursors

FETCH cursor_name into Var_name [, Var_name] ...

Take a row from the cursor cursor_name and save the row's individual column values to each variable.

Analytical:

Take one line at a time, and then move the pointer to the next line automatically;

If you do not get the row, you will throw an exception, its SQLState code value is ' 02000 ', this time to detect the situation, you need to declare the exception handler (for the condition not found also can), usually need to execute a FETCH statement in a loop, by detecting the above exception to end the loop.

4. Close the cursor

CLOSE cursor_name;

Reclaim the memory the cursor occupies, and don't waste resources.

Example 1: Creating a procedure to calculate the number of rows in a players table

Mysql> delimiter $ $mysql > CREATE PROCEDURE number_of_players (out Pnumber int), begin      declare A_playerno int;　　-DECLARE found bool default true;  loop control variable with a value of false when end of loop---DECLARE c_players cursor for-, select Playerno from players;  ① declaration Cursor----Declare continue handler for not found, set found=false;    Declaring exception handlers, set pnumber=0;  Open c_players;  ② open cursor, fetch c_players into A_playerno;    ③ retrieves a cursor (retrieves the first row), while found does, set pnumber=pnumber+1;    --fetch c_players into A_playerno;  and end while;  Loop retrieves the remaining lines, close c_players; ④ close cursor, end$ $mysql > Delimiter;mysql> call number_of_players (@pnumber);mysql> select @pnumber; +---------- +|       @pnumber |+----------+| |+----------+mysql> Select COUNT (*) from players;+----------+|       COUNT (*) |+----------+| |+----------+ 

Example 2: Create a process to calculate the number of fines for a player--the cursor declaration can contain variables

 mysql> delimiter $ $mysql > CREATE PROCEDURE number_penalties (-in P_playerno int,-out Pnumb    ER int)-DECLARE a_playerno int;  -DECLARE found bool default true;    loop control variable--declare c_players cursor for declaration cursor, select Playerno, from penalties  where Playerno = P_playerno;  Contains variable P_playerno, declare continue handler for not found, set found=false;    Declaring exception handlers, set pnumber=0;  Open c_players;    Open cursor, fetch c_players into A_playerno;    The while found do loop retrieves the set pnumber=pnumber+1, each row of the cursor;    --fetch c_players into A_playerno;    and end while;  Close c_players; Close Cursors--end$ $mysql > Delimiter;mysql> call number_penalties (@pnumber);mysql> select @pnumber; +--------- -+|        @pnumber |+----------+| 3 |+----------+ 

Simple practices for MySQL cursors