MySQL存儲過程02

這次接著說MySQL存儲過程：

我們先看它的多分支控制結構case:

case的語句很簡單： 

case 變數名

when 條件1 then 輸出結果1;

when 條件2 then 輸出結果2;

......

end case;

那我們就來建立一個存儲過程實現它：

create procedure p10()
begin
declare pos int default 0;
set pos:= floor(5*rand());
case pos
when 1  then select'我會飛';
when 2 then select'我掉到海裡';
when 3 then select'我在小島';
else select'我不知道我在哪';
end case;
end$

由於我們設置的是隨機數，所以它會根據我們生成的數來輸出結果

接著看一下repeat迴圈：

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$

游標：

一條sql，對應N條資源，取出資源的介面/句柄，就是游標

沿著游標，可以一次取出一行。

建立游標的語法：

declare 游標名 cursor for sql語句;

open 游標名

fetch 游標名 into 變數1，變數2，...變數N；

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$

那麼我們看到這個輸出結果沒有什麼特別的地方，那麼如果我們把游標改一改？

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$

我們看到在輸出了三行之後報錯，那麼我們可以把迴圈和游標結合起來：

create procedure p14()
begin
declare row_gid int;
declare row_name varchar(20);
declare row_num int;
declare cnt int default 0; #聲明一個變數用來計數
declare i int default 0;
declare getgoods cursor for select gid,num,name from goods;
select count(*) into cnt from goods; #將總行數賦給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$

其實游標在越界時我們可以用declare continue handler 來操作一個越界標誌

declare continue handler for NOT FOUND 可執行語句;

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$

在上面的結果中我們發現最後一行被取了兩次，這是為什麼？

答：因為我們聲明的是continue型handler，那麼它在把you 這個變數改為0後還會接著執行後面的sql語句，我們把continue改為exit就不會出現這種情況了：

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$

那麼其實我們還有另一種方法來修改：我們使邏輯上更加通順

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$

或者是將repeat改為while迴圈，在這裡我就不列舉了。