本文共 4998 字，大约阅读时间需要 16 分钟。
Mysql的存储过程是从版本5才开始支持的，所以目前一般使用的都可以用到存储过程。今天分享下自己对于Mysql存储过程的认识与了解。 一些简单的调用以及语法规则这里就不在赘述，网上有许多例子。这里主要说说大家常用的游标加循环的嵌套使用。 首先先介绍循环的分类： (1)WHILE ... END WHILE (2)LOOP ... END LOOP (3)REPEAT ... END REPEAT (4)GOTO 这里有三种标准的循环方式：WHILE循环，LOOP循环以及REPEAT循环。还有一种非标准的循环方式：GOTO(不做介绍)。 (1)WHILE ... END WHILECREATE PROCEDURE p14()BEGINDECLARE v INT;SET v = 0;WHILE v < 5 DOINSERT INTO t VALUES (v);SET v = v + 1;END WHILE;END;
CREATE PROCEDURE p15 ()BEGINDECLARE v INT;SET v = 0;REPEATINSERT INTO t VALUES (v);SET v = v + 1;UNTIL v >= 5END REPEAT;END;CREATE PROCEDURE p16 ()BEGINDECLARE v INT;SET v = 0;loop_label: LOOPINSERT INTO t VALUES (v);SET v = v + 1;IF v >= 5 THENLEAVE loop_label;END IF;END LOOP;END;
这是REPEAT循环的例子，功能和前面WHILE循环一样。区别在于它在执行后检查结果，而WHILE则是执行前检查。类似于do while语句。注意到UNTIL语句后面没有分号，在这里可以不写分号，当然你加上额外的分号更好。
(3)LOOP ... END LOOP
以上是LOOP循环的例子。LOOP循环不需要初始条件，这点和WHILE循环相似，同时它又和REPEAT循环一样也不需要结束条件。 ITERATE 迭代 如果目标是ITERATE（迭代）语句的话，就必须用到LEAVE语句CREATE PROCEDURE p20 ()BEGINDECLARE v INT;SET v = 0;loop_label: LOOPIF v = 3 THENSET v = v + 1;ITERATE loop_label;END IF;INSERT INTO t VALUES (v);SET v = v + 1;IF v >= 5 THENLEAVE loop_label;END IF;END LOOP;END;
begindeclare 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:LOOPfetch feeCodeCursor into p_feeCode;//将游标插入申明的变量if done = 1 thenleave loop_label;elseset 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 thenset done = 0;end if;if p_schemeMoney=0 thenset p_totalCompeleteRate=-1.0;elseset 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
begindeclare 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 thenbegindeclare 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 thenleave loop_label;elseset flag = 0;end if;if not done2 thenset 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 thenset done2 = 0;end if;if p_projectSchemeMoney=0 thenset p_projectFeeCompeleteRate=-1;elseset 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 done1end repeat;close feeCodeCursor;end
转载地址：http://tsxdn.baihongyu.com/