This time I'll go on to talk about MySQL stored procedures:

Let's first look at its multi-branch control structure case:

The statement of case is simple:

case variable name

when condition 1 then output result 1;

when condition 2 then outputs result 2;

......

end case;

 

So let's build a stored procedure to implement it:

create procedure p10()
begin
declare pos int default 0;
set pos:= floor(5*rand());
case pos
when 1  then select'I can fly';
when 2 then select'I fell into the sea';
when 3 then select'I am on the island';
else select'I don't know where I am.';
end case;
end$

Since we set the random number, it will output the result according to the number we generate.

 

Next, take a look at the repeat loop:

The grammar of repeat:

repeat

sql statement;

sql statement;

...

until condition end repeat;

create procedure p11()
begin
declare total int default 0;
declare i int default 0;
repeat
set i:=i+1;
set total:=total+i;
until i>=100 
end repeat;
select total;
end$

 

Cursor:

A sql, corresponding to N resources, takes the interface/handle of the resources, which is the cursor.

Along the cursor, you can take out a row at a time.

The grammar of building cursors:

declare cursor for sql statement;

open cursor name

fetch cursor name into variable 1, variable 2,... Variable N;

close cursor name

create procedure p12()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare getgoods cursor for select gid,num,name from goods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
close getgoods;
end$

 

So we see nothing special about this output, so if we change the cursor?

create procedure p13()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare getgoods cursor for select gid,num,name from goods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
close getgoods;
end$

We see that the error is reported after three lines are output, so we can combine the loop with the cursor:

create procedure p14()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare cnt int default 0; #Declare a variable to count
declare i int default 0;
declare getgoods cursor for select gid,num,name from goods;
select count(*) into cnt from goods; #Assign the total number of rows to cnt
open getgoods;
repeat
set i:=i+1;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until i>=cnt end repeat;
close getgoods;
end$

In fact, we can use declare continue handler to operate a crossing mark when the cursor crosses the boundary.

declare continue handler for NOT FOUND executable statement;

create procedure p15()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare continue handler for NOT FOUND set you:=0;
open getgoods;
repeat
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until you=0 end repeat;
close getgoods;
end$

In the results above, we found that the last line was taken twice. Why?

Answer: Because we declare a continue handler, it will execute the following sql statement after changing the variable you to zero. If we change continue to exit, this will not happen.

create procedure p15()
begin
declare row_gid int default 0;
declare row_num int default 0;
declare row_name varchar(20);
declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare exit handler for NOT FOUND set you:=0;
open getgoods;
repeat
fetch getgoods into row_gid,row_num,row_name;
select row_name,row_num;
until you=0 end repeat;
close getgoods;
end$

So there's another way we can actually modify it: we can make it logically smoother.

create procedure p17()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int default 1;
declare getgoods cursor for select gid,num,name from goods;
declare continue handler for NOT FOUND set you:=0;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
repeat
select row_name,row_num;
fetch getgoods into row_gid,row_num,row_name;
until you=0 end repeat;
close getgoods;
end$

Or change repeat to while loop, which I won't list here.