The MySQL stored procedure is resolved using an example

Example 1, an instance of a simple stored procedure cursor

 
DELIMITER $$ 
DROP PROCEDURE IF EXISTS getUserInfo $$
CREATE PROCEDURE getUserInfo(in date_day datetime)
-- 
--  The instance 
--  Stored procedure name: getUserInfo
--  Parameters as follows: date_day The date format :2008-03-08
--
    BEGIN
declare _userName varchar(12); --  The user name 
declare _chinese int ; --  Chinese language and literature 
declare _math int ;    --  mathematics 
declare done int;
--  Define the 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 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 the data 

   if done=1 then
    leave cursor_loop;
   end if;
   --  Update the table 
   update infoSum set total=_chinese+_math where UserName=_userName;
end loop cursor_loop;
close rs_cursor;
    END$$
DELIMITER ;
 

Example 2: stored procedure cursor looping occurs
In the stored procedure of MySQL, a cursor operation is required to perform an conitnue operation. As we all know, there are three common cursor loops in MySQL,LOOP,REPEAT and WHILE.
1.REPEAT

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

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( There's something very important here ITERATE,LEAVE)
  The following code   Copy the 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: ITERATE is contiune, and ITERATE is break. Of course, in MySQL stored procedure, the loop structure needs to have a name, and everything else is the same.
Example 3,mysql stored procedure USES multiple cursors

Create a table and insert some test data:

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
('procedure1', 'pass1'),
('procedure2', 'pass2'),
('procedure3', 'pass3'),
('procedure4', 'pass4'); The following are 1 An example of a simple stored procedure: 
drop procedure IF EXISTS test_proc;
delimiter //
create procedure test_proc()
begin
 --  The statement 1 A sign done .   Used to determine whether the cursor has traversed 
 DECLARE done INT DEFAULT 0;
 --  The statement 1 Is used to hold the data extracted from the cursor 
 --  Note in particular that the name here cannot be the same as the listing used in the cursor, otherwise the resulting data is NULL
 DECLARE tname varchar(50) DEFAULT NULL;
 DECLARE tpass varchar(50) DEFAULT NULL;
 --  Declare the corresponding for the cursor  SQL  statements 
 DECLARE cur CURSOR FOR
  select name, password from netingcn_proc_test;
 --  In the cursor loop to the end will  done  Set to  1
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 --  Execute the query 
 open cur;
 --  Traverse each cursor 1 line 
 REPEAT
  --  the 1 The row information is stored in the corresponding variable 
  FETCH cur INTO tname, tpass;
  if not done then
   --  You can use it here  tname .  tpass  The corresponding information 
   select tname, tpass;
  end if;
  UNTIL done END REPEAT;
 CLOSE cur;
end
//
delimiter ;
--  Executing stored procedures 
call test_proc();
 

Note that the variable declaration, cursor declaration and HANDLER declaration must not be in the wrong order. Declare the variable first, then declare the cursor, and finally declare HANDLER. In the stored procedure example above, only one cursor is used, so it is easy to use two or more cursors. Examples are as follows:

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 FOR
  select name, password from netingcn_proc_test;
 DECLARE cur_2 CURSOR FOR
  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;
 --  Notice here, 1 Need to reset done The value of  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 code above and the basic one in the first example are one more cursor declaration and traversal cursor. It is important to note that set done = 0 is used before traversing the second cursor, because when the first cursor traverses the play, its value is set to 1 by handler. If it is not set to 0 by set, then the second cursor will not be traversed. Of course, it is a good practice to use this statement before each open cursor operation to ensure that the cursor actually traverses. Of course, you can also use the nested begin statement block to handle multiple cursors, such as:

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 FOR
  select name, password from netingcn_proc_test;
 DECLARE cur_2 CURSOR FOR
  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 FOR
   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();