MySQL stored procedure cursor use instance detailed

Example 1, a simple stored procedure cursor instance

The code is as follows

Copy Code

DELIMITER $$ DROP PROCEDURE IF EXISTS getuserinfo $$ CREATE PROCEDURE GetUserInfo (in Date_day datetime) -- --Example --Stored procedure name: GetUserInfo --Parameter: date_day date format: 2008-03-08 -- BEGIN DECLARE _username varchar (12); --User name declare _chinese int; --Chinese declare _math int; --Mathematics declare done int; --Defining cursors DECLARE rs_cursor cursor for SELECT username,chinese,math from UserInfo where DateDiff (createdate, date_day) = 0; DECLARE CONTINUE HANDLER for not FOUND SET done=1; --Get yesterday's date If Date_day is null then Set date_day = Date_add (now (), Interval-1 Day); End If; Open rs_cursor; Cursor_loop:loop FETCH rs_cursor into _username, _chinese, _math; --Take data If Done=1 Then Leave Cursor_loop; End If; --Update table Update infosum set total=_chinese+_math where Username=_username; End Loop Cursor_loop; Close rs_cursor; end$$ DELIMITER;

Example 2, the stored procedure cursor loop hop appears

In a MySQL stored procedure, a conitnue operation is required to perform a cursor operation. As we all know, there are three kinds of cursors used in MySQL, loop,repeat,while three loops, the same way. Never used before, so write it down, Convenient to consult later.

1.REPEAT

The code is as follows	Copy Code
REPEAT statements; UNTIL expression End REPEAT Demo DECLARE num INT; DECLARE my_string VARCHAR (255); REPEAT SET my_string =concat (my_string,num, ', '); SET num = num +1; UNTIL Num <5 End REPEAT;

2.WHILE

The code is as follows	Copy Code
While expression do statements; End While Demo DECLARE num INT; DECLARE my_string VARCHAR (255); SET num = 1; SET str = '; While Num < Span>10do SET my_string =concat (my_string,num, ', '); SET num = num +1; End while;

3.LOOP (which has very important iterate,leave)

The code is as follows	Copy Code
DECLARE num INT; DECLARE str VARCHAR (255); SET num = 1; SET my_string = '; Loop_label:loop IF Num <10then LEAVE Loop_label; ENDIF; SET num = num +1; IF (num mod3) THEN Iterate Loop_label; ELSE SET my_string =concat (my_string,num, ', '); ENDIF; End LOOP;

PS: This can be understood iterate is our program commonly used in Contiune, and iterate is break. Of course, in the MySQL stored procedure, you need to have a name for the loop structure, all the others are the same.

Example 3,mysql using multiple cursors in stored procedures

First create a table and insert some test data:

The code is as follows

Copy Code

DROP TABLE IF EXISTS netingcn_proc_test; CREATE TABLE ' Netingcn_proc_test ' ( ' ID ' INTEGER (one) not NULL auto_increment, ' Name ' VARCHAR (20), ' Password ' VARCHAR (20), PRIMARY KEY (' id ') ) Engine=innodb; Insert into netingcn_proc_test (name, password) values (' Procedure1 ', ' Pass1 '), (' Procedure2 ', ' pass2 '), (' Procedure3 ', ' pass3 '), (' Procedure4 ', ' PASS4 ') The following is an example of a simple stored procedure: drop procedure IF EXISTS Test_proc; Delimiter// CREATE PROCEDURE Test_proc () Begin --Declare a flag done to determine whether the cursor is completed DECLARE done INT DEFAULT 0; --Declares a variable that holds the data extracted from the cursor --Pay special attention to the fact that the name here cannot be the same as the one used in the cursor, otherwise the resulting data is null DECLARE tname varchar (m) DEFAULT NULL; DECLARE tpass varchar (m) DEFAULT NULL; --Declaring a cursor corresponding to the SQL statement DECLARE cur CURSOR for Select name, password from netingcn_proc_test; --Set the done to 1 at the end of the cursor loop DECLARE CONTINUE HANDLER for does FOUND SET done = 1; --Execute Query Open cur; --traverse the cursor for each row REPEAT --Put a line of information in the corresponding variable FETCH cur into tname, tpass; If not done then --Here you can use Tname, tpass the corresponding information. Select Tname, Tpass; End If; UNTIL do end REPEAT; Close cur; End // delimiter; --Execute the stored procedure Call Test_proc ();

Note that the declaration of the variable, the declaration of the cursor, and the order of the handler declaration cannot be mistaken, you must declare the variable first, then declare the cursor, and the final declaration handler. There is only one cursor in the example of the stored procedure mentioned above, so if you want to use two or more cursors, it's very simple, so to speak, how to use two. Examples are as follows:

The code is as follows

Copy Code

drop procedure IF EXISTS test_proc_1; Delimiter// CREATE PROCEDURE Test_proc_1 () Begin DECLARE done INT DEFAULT 0; DECLARE Tid int (one) DEFAULT 0; DECLARE tname varchar (m) DEFAULT NULL; DECLARE tpass varchar (m) DEFAULT NULL; DECLARE cur_1 CURSOR for Select name, password from netingcn_proc_test; DECLARE cur_2 CURSOR for Select ID, name from netingcn_proc_test; DECLARE CONTINUE HANDLER for does FOUND SET done = 1; Open cur_1; REPEAT FETCH cur_1 into Tname, Tpass; If not done then Select Tname, Tpass; End If; UNTIL do end REPEAT; Close cur_1; --note here, be sure to reset the done value to 0 Set done = 0; Open cur_2; REPEAT FETCH cur_2 into Tid, tname; If not done then Select Tid, Tname; End If; UNTIL do end REPEAT; Close cur_2; End // delimiter; Call Test_proc_1 ();

The above code is basically the same as in the first example, with one more cursor declaration and traversal cursor. Note here that the set done = 0 is used before traversing the second cursor, because when the first cursor traverses the play, its value is set to 1, and the second cursor is not traversed if it is set to 0 without the set handler. Of course, the good habit is to use the statement before each open cursor operation to ensure that the cursor is truly traversed. Of course, you can also work with multiple cursors in a nested way with the BEGIN statement block, for example:

The code is as follows

Copy Code

drop procedure IF EXISTS test_proc_2; Delimiter// CREATE PROCEDURE test_proc_2 () Begin DECLARE done INT DEFAULT 0; DECLARE tname varchar (m) DEFAULT NULL; DECLARE tpass varchar (m) DEFAULT NULL; DECLARE cur_1 CURSOR for Select name, password from netingcn_proc_test; DECLARE cur_2 CURSOR for Select ID, name from netingcn_proc_test; DECLARE CONTINUE HANDLER for does FOUND SET done = 1; Open cur_1; REPEAT FETCH cur_1 into Tname, Tpass; If not done then Select Tname, Tpass; End If; UNTIL do end REPEAT; Close cur_1; Begin DECLARE done INT DEFAULT 0; DECLARE Tid int (one) DEFAULT 0; DECLARE tname varchar (m) DEFAULT NULL; DECLARE cur_2 CURSOR for Select ID, name from netingcn_proc_test; DECLARE CONTINUE HANDLER for does FOUND SET done = 1; Open cur_2; REPEAT FETCH cur_2 into Tid, tname; If not done then Select Tid, Tname; End If; UNTIL do end REPEAT; Close cur_2; End End // delimiter; Call test_proc_2 ();