快捷導(dǎo)航

mysql存儲(chǔ)過程游標(biāo) 循環(huán)使用介紹

更新時(shí)間：2012年11月24日 19:37:34 作者：

今天分享下自己對于Mysql存儲(chǔ)過程的認(rèn)識(shí)與了解,這里主要說說大家常用的游標(biāo)加循環(huán)的嵌套使用

Mysql的存儲(chǔ)過程是從版本5才開始支持的，所以目前一般使用的都可以用到存儲(chǔ)過程。今天分享下自己對于Mysql存儲(chǔ)過程的認(rèn)識(shí)與了解。

一些簡單的調(diào)用以及語法規(guī)則這里就不在贅述，網(wǎng)上有許多例子。這里主要說說大家常用的游標(biāo)加循環(huán)的嵌套使用。
首先先介紹循環(huán)的分類：
(1)WHILE ... END WHILE
(2)LOOP ... END LOOP
(3)REPEAT ... END REPEAT
(4)GOTO
這里有三種標(biāo)準(zhǔn)的循環(huán)方式：WHILE循環(huán)，LOOP循環(huán)以及REPEAT循環(huán)。還有一種非標(biāo)準(zhǔn)的循環(huán)方式：GOTO(不做介紹)。
(1)WHILE ... END WHILE

復(fù)制代碼代碼如下:

 
CREATE PROCEDURE p14() 
BEGIN 
DECLARE v INT; 
SET v = 0; 
WHILE v < 5 DO 
INSERT INTO t VALUES (v); 
SET v = v + 1; 
END WHILE; 
END; 

這是WHILE循環(huán)的方式。它跟IF語句相似，使用"SET v = 0;"語句使為了防止一個(gè)常見的錯(cuò)誤，如果沒有初始化，默認(rèn)變量值為NULL，而NULL和任何值操作結(jié)果都為NULL。
(2)REPEAT ... END REPEAT

復(fù)制代碼代碼如下:

 
CREATE PROCEDURE p15 () 
BEGIN 
DECLARE v INT; 
SET v = 0; 
REPEAT 
INSERT INTO t VALUES (v); 
SET v = v + 1; 
UNTIL v >= 5 
END REPEAT; 
END; 

這是REPEAT循環(huán)的例子，功能和前面WHILE循環(huán)一樣。區(qū)別在于它在執(zhí)行后檢查結(jié)果，而WHILE則是執(zhí)行前檢查。類似于do while語句。注意到UNTIL語句后面沒有分號(hào)，在這里可以不寫分號(hào)，當(dāng)然你加上額外的分號(hào)更好。
(3)LOOP ... END LOOP

復(fù)制代碼代碼如下:

 
CREATE PROCEDURE p16 () 
BEGIN 
DECLARE v INT; 
SET v = 0; 
loop_label: LOOP 
INSERT INTO t VALUES (v); 
SET v = v + 1; 
IF v >= 5 THEN 
LEAVE loop_label; 
END IF; 
END LOOP; 
END; 

以上是LOOP循環(huán)的例子。LOOP循環(huán)不需要初始條件，這點(diǎn)和WHILE循環(huán)相似，同時(shí)它又和REPEAT循環(huán)一樣也不需要結(jié)束條件。
ITERATE 迭代
如果目標(biāo)是ITERATE（迭代）語句的話，就必須用到LEAVE語句

復(fù)制代碼代碼如下:

 
CREATE PROCEDURE p20 () 
BEGIN 
DECLARE v INT; 
SET v = 0; 
loop_label: LOOP 
IF v = 3 THEN 
SET v = v + 1; 
ITERATE loop_label; 
END IF; 
INSERT INTO t VALUES (v); 
SET v = v + 1; 
IF v >= 5 THEN 
LEAVE loop_label; 
END IF; 
END LOOP; 
END; 

ITERATE（迭代）語句和LEAVE語句一樣也是在循環(huán)內(nèi)部的循環(huán)引用，它有點(diǎn)像C語言中的“Continue”，同樣它可以出現(xiàn)在復(fù)合語句中，引用復(fù)合語句標(biāo)號(hào)，ITERATE（迭代）意思是重新開始復(fù)合語句。
以上是對于循環(huán)的幾種情況的介紹。接著就是介紹一個(gè)帶游標(biāo)的例子來詳細(xì)解釋。

復(fù)制代碼代碼如下:

 
begin 
declare p_feeCode varchar(20); 
declare p_feeName varchar(20); 
declare p_billMoney float(12); 
declare p_schemeMoney float(12); 
declare allMoney float(10); 
declare allUsedMoney float(10); 
declare p_year varchar(50); 
declare p_totalCompeleteRate float(12); 
declare done int(10); 
declare flag int(2); 
declare feeCodeCursor cursor for select feeCode from fee;//申明一個(gè)游標(biāo)變量 
declare continue handler for not found set done=1;//申明循環(huán)結(jié)束的標(biāo)志位 
set done=0; 
select date_format(now(),'%Y') into p_year; 
open feeCodeCursor;//打開游標(biāo) 
loop_label:LOOP 
fetch feeCodeCursor into p_feeCode;//將游標(biāo)插入申明的變量 
if done = 1 then 
leave loop_label; 
else 
set flag = 0; 
end if; 
set p_schemeMoney=0; 
set p_billMoney = 0; 
select feeName into p_feeName from fee where feeCode=p_feeCode; 
select sum(billMoney) into p_billMoney from bill_data where feeCode=p_feeCode and billDate like Concat(p_year, '%'); 
select schemeMoney into p_schemeMoney from total_scheme where feeCode=p_feeCode and schemeDate like Concat(p_year, '%') limit 1; 
if flag = 0 then 
set done = 0; 
end if; 
if p_schemeMoney=0 then 
set p_totalCompeleteRate=-1.0; 
else 
set p_totalCompeleteRate=(1.0*p_billMoney)/p_schemeMoney; 
end if; 
insert into total_summary values(p_feeCode,p_feeName,p_year,p_billMoney,p_totalCompeleteRate); 
commit; 
end LOOP; 
close feeCodeCursor;//循環(huán)結(jié)束后需要關(guān)閉游標(biāo) 
end 

以上只是一個(gè)簡單的例子來說明如何使用，大家不需要關(guān)注具體業(yè)務(wù)邏輯，只需要關(guān)注的是其中標(biāo)志位值的修改情況，已經(jīng)循環(huán)何時(shí)離開。以及游標(biāo)如何聲明，如何使用，至于里面具體的操作和普通的sql語句沒有太大區(qū)別。此處是用一層循環(huán)，至于復(fù)雜業(yè)務(wù)需要需要兩層三層，可以繼續(xù)用同樣的方法繼續(xù)嵌套。以下給出雙層嵌套循環(huán)的，同樣大家只需要關(guān)注嵌套結(jié)構(gòu)即可。

復(fù)制代碼代碼如下:

 
begin 
declare p_projectID varchar(20); 
declare p_projectName varchar(20); 
declare p_feeCode varchar(20); 
declare p_feeName varchar(20); 
declare p_projectSchemeMoney float(10); 
declare p_projectMoney float(10); 
declare p_billMoney float(10); 
declare p_year varchar(50); 
declare p_projectFeeCompeleteRate float(10); 
declare done1 int(10); 
declare done2 int(10); 
declare flag int(2); 
declare feeCodeCursor cursor for select feeCode from fee; 
declare continue handler for not found set done1=1; 
set done1=0; 
select date_format(now(),'%Y') into p_year; 
delete from project_fee_summary; 
open feeCodeCursor; 
repeat //第一層嵌套開始 
fetch feeCodeCursor into p_feeCode; 
select feeName into p_feeName from fee where feeCode=p_feeCode; 
if not done1 then 
begin 
declare projectIDCursor cursor for select projectID from project; 
declare continue handler for not found set done2 = 1; 
set done2=0; 
open projectIDCursor; 
loop_label:LOOP//第二層嵌套開始 
fetch projectIDCursor into p_projectID; 
select projectName into p_projectName from project where projectID=p_projectID; 
if done2 = 1 then 
leave loop_label; 
else 
set flag = 0; 
end if; 
if not done2 then 
set p_projectSchemeMoney=0; 
select sum(billMoney) into p_billMoney from bill_data where feeCode=p_feeCode and projectID=p_projectID and billDate like Concat(p_year, '%'); 
select projectSchemeMoney into p_projectSchemeMoney from project_scheme where feeCode=p_feeCode and projectID=p_projectID; 
if flag = 0 then 
set done2 = 0; 
end if; 
if p_projectSchemeMoney=0 then 
set p_projectFeeCompeleteRate=-1; 
else 
set p_projectFeeCompeleteRate=(1.0*p_billMoney)/p_projectSchemeMoney; 
end if; 
insert into project_fee_summary values(p_feeCode,p_projectID,p_projectName,p_feeName,p_year,p_billMoney,p_projectFeeCompeleteRate,p_projectFeeCompeleteRate); 
end if; 
end LOOP; 
select sum(billMoney) into p_projectMoney from bill_data where feeCode=p_feeCode and billDate like Concat(p_year, '%'); 
set p_projectFeeCompeleteRate=(1.0*p_projectMoney)/p_projectSchemeMoney; 
insert into project_fee_summary values(p_feeCode,"total","total",p_feeName,p_year,p_projectMoney,p_projectFeeCompeleteRate,p_projectFeeCompeleteRate); 
close projectIDCursor; 
end; 
end if; 
until done1 
end repeat; 
close feeCodeCursor; 
end 