Mysql (14) usage of the cursor _ MySQL

Mysql (14) use bitsCN.com with the cursor

Mysql usage of those things (14) cursor

Related links:

Mysql (1) installation of mysql

Http: // database/201210/162314 .html;

Mysql-related operations (2)

Http: // database/201210/162315 .html;

Mysql operations on data tables (3)

Http: // database/201210/162316 .html;

Mysql (4) Data Table query operations

Http: // database/201210/162317 .html;

Mysql operations (5)

Http: // database/201210/162318 .html;

Mysql (6) string pattern matching

Http: // database/201210/163969 .html;

Mysql (7) in-depth select query

Http: // database/201210/163970 .html;

Mysql Index (8)

Http: // database/201210/163971 .html;

Mysql common functions

Http: // database/201210/164229 .html;

Mysql (10) trigger 1

Http: // database/201210/164516 .html;

Mysql things (11) trigger 2

Http: // database/201210/164766 .html;

Mysql stored procedures

Http: // database/201210/164795 .html;

Mysql (13) usage of variables and conditions

Http: // database/201211/165662 .html

In the stored procedure, you can use the cursor to process the result set cyclically. the use of the cursor includes the declaration, open, fetch, and close of the cursor.

Syntax:

SQL code

-- Declare the cursor

DECLARE cur_name cursor for select_statement

-- Open cursor

OPEN cursor_name

-- FETCH cursor

FETCH cursor_name INTO var_name [, var_name...]

-- Close cursor

CLOSE cursor_name

Example:

SQL code

Delimiter $ -- change; end to $

-- Create a stored procedure

Create procedure payment_amount ()

BEGIN

--- Declare variables

DECLARE I _staff_id int;

DECLARE d_amount decimal (5, 2 );

--- Declare a cursor and obtain the value of staff_id and amount columns in the table payment.

DECLARE cur_payment cursor for select staff_id, amount from payment;

--- Condition processing: the condition for judging the end of a loop is to capture the not found condition.

--- When fecth cannot find the next record, it will close the cursor and exit the process.

Declare exit handler for not found close cur_payment;

Set @ x1 = 0;

Set @ x2 = 0;

---- Open the cursor

OPEN cur_payment;

REPEAT

FETCH cur_payment INTO I _staff_id, d_amount;

If I _staff_id = 2 then

Set @ x1 = @ x1 + d_amount;

Else

Set @ x2 = @ x2 + d_amount;

End if;

UNTIL 0 end repeat;

CLOSE cur_payment;

END;

$

Delimiter;

The DECLARE definition is ordered: variables and conditions must be placed before the declaration of the cursor, and finally the declaration of the handler.

BitsCN.com