Mysql Stored Procedure cursor and multi-cursor instance

Cursors are a powerful feature in the mysql stored procedure. The most commonly used is the cursors In the stored procedure. The following describes some instances of cursors In the mysql stored procedure, hope to help you.

Example

The Code is as follows:

Copy code

DELIMITER $ Drop procedure if exists getUserInfo $ Create procedure getUserInfo (in date_day datetime) -- -- Instance -- MYSQL 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; -- Define a cursor 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 the date of yesterday 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; -- get 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;

The above is an example of using cursors In the MYSQL stored procedure. How to use multiple cursors,Next I will introduce it with the same instance.

Mysql stored procedures can be easily implemented using cursors. The stored procedures are written as follows:

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 (11) not null AUTO_INCREMENT, 'Name' VARCHAR (20 ), 'Password' VARCHAR (20 ), Primary key ('id ') ) ENGINE = InnoDB; Insert into netingcn_proc_test (name, password) values ('Processure1', 'pass1 '), ('Processure2', 'pass2 '), ('Processure3', 'pass3 '), ('Processure4', 'pass4 ');

The following is an example of a simple stored procedure:

The Code is as follows:

Copy code

Drop procedure if exists test_proc; Delimiter // Create procedure test_proc () Begin -- Declare a done to determine whether the cursor traversal is complete. DECLARE done int default 0; -- Declare a variable to store the data extracted from the cursor -- Note that the name here cannot be the same as the name listed in the cursor. Otherwise, the data obtained is NULL. DECLARE tname varchar (50) default null; DECLARE tpass varchar (50) default null; -- Declare the SQL statement corresponding to the cursor DECLARE cur CURSOR Select name, password from netingcn_proc_test; -- The done is set to 1 at the end of the cursor loop. Declare continue handler for not found set done = 1; -- Execute Query Open cur; -- Traverses each row of the cursor REPEAT -- Store the information of a row in the corresponding variable FETCH cur INTO tname, tpass; If not done then -- The tname and tpass information can be used here. Select tname, tpass; End if; UNTIL done end repeat; CLOSE cur; End // Delimiter;

-- Execute the Stored Procedure
Call test_proc (); note that the declaration of variables, the declaration of cursors, and the order in which HANDLER statements are declared must first declare the variables, then declare the cursor, and finally declare HANDLER. In the above example, only one cursor is used, so what if two or more cursors are used is actually very simple? You can say that how to use two cursors is used. Example:

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 (11) DEFAULT 0; DECLARE tname varchar (50) default null; DECLARE tpass varchar (50) default null; DECLARE cur_1 CURSOR Select name, password from netingcn_proc_test; DECLARE cur_2 CURSOR Select id, name from netingcn_proc_test; Declare continue handler for not found set done = 1; Open cur_1; REPEAT FETCH cur_1 INTO tname, tpass; If not done then Select tname, tpass; End if; UNTIL done end repeat; CLOSE cur_1; -- Note that the value of done must be reset 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 done end repeat; CLOSE cur_2; End // Delimiter; Call test_proc_1 ();

The above code is basically the same as in the first example, that is, a cursor declaration and a traversal cursor are added. Set done = 0 is used before traversing the second cursor, because the value of handler is set to 1 after the first cursor traversal, if you do not need set to set it to 0, the second cursor will not be traversed. Of course, it is good to use this statement before each open cursor operation to ensure that the cursor can be truly traversed. Of course, you can also use the in statement block nesting method to process multiple cursors, 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 (50) default null; DECLARE tpass varchar (50) default null; DECLARE cur_1 CURSOR Select name, password from netingcn_proc_test; DECLARE cur_2 CURSOR Select id, name from netingcn_proc_test; Declare continue handler for not found set done = 1; Open cur_1; REPEAT FETCH cur_1 INTO tname, tpass; If not done then Select tname, tpass; End if; UNTIL done end repeat; CLOSE cur_1; Begin DECLARE done int default 0; DECLARE tid int (11) DEFAULT 0; DECLARE tname varchar (50) default null; DECLARE cur_2 CURSOR Select id, name from netingcn_proc_test; Declare continue handler for not found set done = 1; Open cur_2; REPEAT FETCH cur_2 INTO tid, tname; If not done then Select tid, tname; End if; UNTIL done end repeat; CLOSE cur_2; End; End // Delimiter; Call test_proc_2 ();

About the jump-out and continuation of the stored procedure cursor operation in mysql