The MySQL stored procedure is resolved using an example
- 2020-06-01 11:11:39
- OfStack
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();