MySQL stored procedure 02

Time:2019-12-24

This time, I will continue to talk about MySQL stored procedures:

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

The case statement is simple:

Case variable name

When condition 1 then output result 1;

When condition 2 then output result 2;

……

end case;

 

Let’s create 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$

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

 

Next, look at the repeat loop:

Syntax for 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:

One SQL, corresponding to n resources, takes out the interface / handle of the resource, which is the cursor

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

Syntax to create a cursor:

Declare the cursor for SQL statement;

Open title

Fetch cursor name into variable 1, variable 2,… Variable n;

Name of close

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 can see that there is nothing special about this output result, 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 an error is reported after three lines of 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
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, when the cursor is out of bounds, we can use declare continue handler to operate an out of bounds flag

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 above results, we found that the last line was taken twice. Why?

A: because we declare a continue handler, it will execute the following SQL statement after changing you to 0. 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$

In fact, we have another way to modify it: we make it more logical

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.