mysql存儲進程 游標 輪回應用引見。本站提示廣大學習愛好者：（mysql存儲進程 游標 輪回應用引見）文章只能為提供參考，不一定能成為您想要的結果。以下是mysql存儲進程 游標 輪回應用引見正文
Mysql的存儲進程是從版本5才開端支撐的，所以今朝普通應用的都可以用到存儲進程。明天分享下本身關於Mysql存儲進程的熟悉與懂得。
一些簡略的挪用和語律例則這裡就不在贅述，網上有很多例子。這裡重要說說年夜家經常使用的游標加輪回的嵌套應用。
起首先引見輪回的分類：
(1)WHILE ... END WHILE
(2)LOOP ... END LOOP
(3)REPEAT ... END REPEAT
(4)GOTO
這裡有三種尺度的輪回方法：WHILE輪回，LOOP輪回和REPEAT輪回。還有一種非尺度的輪回方法：GOTO(不做引見)。
(1)WHILE ... END WHILE
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輪回的方法。它跟IF語句類似，應用"SET v = 0;"語句使為了避免一個罕見的毛病，假如沒有初始化，默許變量值為NULL，而NULL和任何值操作成果都為NULL。
(2)REPEAT ... END REPEAT
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輪回的例子，功效和後面WHILE輪回一樣。差別在於它在履行後檢討成果，而WHILE則是履行前檢討。相似於do while語句。留意到UNTIL語句前面沒有分號，在這裡可以不寫分號，固然你加上額定的分號更好。
(3)LOOP ... END LOOP
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輪回的例子。LOOP輪回不須要初始前提，這點和WHILE輪回類似，同時它又和REPEAT輪回一樣也不須要停止前提。
ITERATE 迭代
假如目的是ITERATE（迭代）語句的話，就必需用到LEAVE語句
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語句一樣也是在輪回外部的輪回援用， 它有點像C說話中 的“Continue”，異樣它可以湧現在復合語句中，援用復合語句標號，ITERATE（迭代）意思 是從新開端復合語句。
以上是關於輪回的幾種情形的引見。接著就是引見一個帶游標的例子來具體說明。
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;//聲名一個游標變量
declare continue handler for not found set done=1;//聲名輪回停止的標記位
set done=0;
select date_format(now(),'%Y') into p_year;
open feeCodeCursor;//翻開游標
loop_label:LOOP
fetch feeCodeCursor into p_feeCode;//將游標拔出聲名的變量
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;//輪回停止後須要封閉游標
end
以上只是一個簡略的例子來講明若何應用，年夜家不須要存眷詳細營業邏輯，只須要存眷的是個中標記位值的修正情形，曾經輪回什麼時候分開。和游標若何聲明，若何應用，至於外面詳細的操作和通俗的sql語句沒有太年夜差別。此處是用一層輪回，至於龐雜營業須要須要兩層三層，可以持續用異樣的辦法持續嵌套。以下給出雙層嵌套輪回的，異樣年夜家只須要存眷嵌套構造便可。
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
