MySQL stored procedure usage example _ MySQL

MySQL stored procedure instance details bitsCN.com

Example 1: a simple stored procedure cursor instance

DELIMITER $
Drop procedure if exists getUserInfo $
Create procedure getUserInfo (in date_day datetime)
--
-- Instance
-- 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;

Example 2: cursor loop jump in a stored procedure
In the MySQL stored procedure, a conitnue operation needs to be executed during the cursor operation. as we all know, there are three commonly used cursor LOOP operations in MySQL: LOOP, REPEAT, WHILE. there are three cycles in the same way. I have never used it before, so write it down for future reference.
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 (here there is a very important ITERATE, LEAVE)
The code is as follows:
DECLARE num INT;
DECLARE str VARCHAR (255 );
SET num = 1;
SET my_string = '';
Loop_label: LOOP
IF num <10 THEN
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: It can be understood that ITERATE is the common contiune in our program, and ITERATE is the break. of course, in the MySQL stored procedure, there must be a name in the loop structure, and everything else is the same.
Example 3: Use multiple cursors in the mysql stored procedure

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
('Processure1', 'pass1 '),
('Processure2', 'pass2 '),
('Processure3', 'pass3 '),
('Processure4', 'pass4 '); below is an example of a simple stored procedure:
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 be the declaration of variables, the declaration of cursors, and finally the declaration of 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:

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:

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 ();

BitsCN.com