example:
At present, there are three tables A, B and C. among them, A and B are one to many relationships, and B and C are one to many relationships. Now it is necessary to save the primary key of table A in B into C;
The general idea is to query out B and update table C with an update statement, but there are more than 2000 pieces of data in table B,
Do you want to implement more than 2000 times? Obviously, it's not realistic; finally, we find and write a stored procedure and update the C table through a loop,
However, the writing method in stored procedures is in the form of cursors.
[introduction]
A cursor is actually a mechanism that can extract one record at a time from a result set that contains multiple data records.
The cursor acts as a pointer.
Although the cursor can traverse all the rows in the result, it only points to one row at a time.
The function of the cursor is to traverse the records returned from the query database for corresponding operations.
[usage]
1. Declare a cursor: declare the cursor name CURSOR for table; (the table here can be any collection you query)
2. Open the defined cursor: the name of the open cursor;
3. Get the next row of data: FETCH cursor name into testrangeid, versioned;
IV. statements to be executed (add, delete, modify and check): it depends on the specific situation
5. Release cursor: name of CLOSE cursor;
Note: every sentence of mysql stored procedure must be followed by; at the end, the temporary fields used need to be declared before the cursor is defined.
[examples]
-
BEGIN
--Defining variables
declare testrangeid BIGINT;
declare versionid BIGINT;
declare done int;
--Create a cursor and store data
declare cur_test CURSOR for
select id as testrangeid,version_id as versionid from tp_testrange;
--After the contents in the cursor are executed, the done Set to 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
--Open cursor
open cur_test;
--Execute loop
posLoop:LOOP
--Determine whether to end the cycle
IF done=1 THEN
LEAVE posLoop;
END IF;
--Take the value in the cursor
FETCH cur_test into testrangeid,versionid;
--Perform update operation
update tp_data_execute set version_id=versionid where testrange_id = testrangeid;
END LOOP posLoop;
--Release cursor
CLOSE cur_test;
END
-
Example 2:
Now we need to do a function with stored procedures to count the total stock of iPhones and output the total to the console.
--stay windows When writing stored procedures in the system, use the declare To declare a variable, you need to add this keyword, otherwise an error will be reported.
delimiter //
drop procedure if exists StatisticStore;
CREATE PROCEDURE StatisticStore()
BEGIN
--Create variables to receive cursor data
declare c int;
declare n varchar(20);
--Create total variable
declare total int default 0;
--Create end flag variable
declare done int default false;
--Create cursor
declare cur cursor for select name,count from store where name = 'iphone';
--Specifies the return value at the end of the cursor loop
declare continue HANDLER for not found set done = true;
--Set initial value
set total = 0;
--Open cursor
open cur;
--Start looping data in cursor
read_loop:loop
--According to a piece of data that the cursor currently points to
fetch cur into n,c;
--Determine whether the cursor's loop ends
if done then
leave read_loop; --Jump out of cursor loop
end if;
--When getting a piece of data, the count Value to accumulate. You can do whatever you want,
set total = total + c;
--End cursor loop
end loop;
--Close cursor
close cur;
--Output results
select total;
END;
--Call stored procedure
call StatisticStore();
fetch is to get the data row that the cursor currently points to, and point the pointer to the next row. If the cursor has already pointed to the last row, continuing execution will cause cursor overflow.
When using loop loop cursors, they will not monitor whether the last data is reached. Writing like the following code will cause a dead loop;
read_loop:loop fetch cur into n,c; set total = total+c; end loop;
In mysql, when a cursor overflows, a pre-defined not found error of MySQL will be raised. Therefore, the following code is used to specify that when a not found error is raised, a continue event is defined, and the value of the done variable is modified when the event occurs.
declare continue HANDLER for not found set done = true;
So add the following code to the loop:
--Determine whether the cursor's loop ends
if done then
leave read_loop; -- jump out of cursor loop
end if;
If the value of done is true, the loop ends. Continue with the following code
How to use
There are three ways to use cursors:
The first is the above implementation, using loop loop;
The second way is to use the while loop as follows:
drop procedure if exists StatisticStore1;
CREATE PROCEDURE StatisticStore1()
BEGIN
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'iphone';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
fetch cur into n,c;
while(not done) do
set total = total + c;
fetch cur into n,c;
end while;
close cur;
select total;
END;
call StatisticStore1();
The third way is to use repeat:
drop procedure if exists StatisticStore2;
CREATE PROCEDURE StatisticStore2()
BEGIN
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'iphone';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
repeat
fetch cur into n,c;
if not done then
set total = total + c;
end if;
until done end repeat;
close cur;
select total;
END;
call StatisticStore2();
Cursor nesting
In mysql, each begin end block is an independent scope area. Because the event of the same error in MySQL can only be defined once, if there are multiple definitions, the duplicate handler declared in the sample block will be prompted during compilation.
drop procedure if exists StatisticStore3;
CREATE PROCEDURE StatisticStore3()
BEGIN
declare _n varchar(20);
declare done int default false;
declare cur cursor for select name from store group by name;
declare continue HANDLER for not found set done = true;
open cur;
read_loop:loop
fetch cur into _n;
if done then
leave read_loop;
end if;
begin
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'iphone';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
iphone_loop:loop
fetch cur into n,c;
if done then
leave iphone_loop;
end if;
set total = total + c;
end loop;
close cur;
select _n,n,total;
end;
begin
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'android';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
android_loop:loop
fetch cur into n,c;
if done then
leave android_loop;
end if;
set total = total + c;
end loop;
close cur;
select _n,n,total;
end;
begin
end;
end loop;
close cur;
END;
call StatisticStore3();
The above is to implement a nested loop. Of course, this example is far fetched. Just take a look.
Dynamic SQL
Mysql supports the function of dynamic SQL
set @sqlStr='select * from table where condition1 = ?'; prepare s1 for @sqlStr; --If there are multiple parameters separated by commas execute s1 using @condition1; --Release manually, or when the connection is closed, the server will recycle automatically deallocate prepare s1;