WHILE循环
语法:
[begin_label:] WHILE search_condition DO
statement_list;
[begin_label];
1
2
3
语义:首先判断循环开始条件search_condition 是否为true,如果为true,则执行循环体中的语句statement_list。每执行完一次,都要重新判断条件search_condition是否为true。如果条件search_condition为false,则循环结束。因此,条件search_condition又是循环结束条件
本循环的特点:先判断,后执行
循环必须具备的:
1.条件
2.SQL语句体
3.程序体里面需要对条件中的变量进行处理变化:条件的变化(一定要有,否则死循环)
例8:创建过程,计算两个日期之间相差的年数
DELIMITER $$
CREATE PROCEDURE age(
IN start_date DATE,
IN end_date DATE,
OUT years INTEGER)
BEGIN
DECLARE next_date DATE;
SET years = 0;
SET next_date = start_date + INTERVAL 1 YEAR;
WHILE next_date < end_date DO
SET years = years + 1;
– 循环计数器加1
SET next_date = next_date + INTERVAL 1 YEAR;
END WHILE;
END$$
DELIMITER ;
客户端调用:
SET @start = ‘1991-01-12’;
SET @end = ‘1999-07-09’;
CALL age(@start,@end,@year);
SELECT @start,@end,@year;
在这里插入图片描述
While循环案例:
6.计算输入的任意两个数之间的连续和,例如:输入1,100过程实现将1+2+3+…+100的和输出到变量中
DROP PROCEDURE IF EXISTS yaoqiu6;
DELIMITER CREATEPROCEDUREyaoqiu6(INaINT,INbINT,OUTcINT)BEGINCASEWHENa<=bTHENSETc=a;WHILE(a+1)<=bDOSETc=c+a+1;SETa=a+1;ENDWHILE;WHENa>bTHENSELECT′输入错误!请重新输入!′;ENDCASE;ENDCREATE PROCEDURE yaoqiu6(IN a INT,IN b INT,OUT c INT) BEGIN CASE WHEN a<=b THEN SET c=a; WHILE (a+1)<=b DO SET c=c+a+1; SET a=a+1; END WHILE; WHEN a>b THEN SELECT '输入错误!请重新输入!'; END CASE; END CREATEPROCEDUREyaoqiu6(INaINT,INbINT,OUTcINT)BEGINCASEWHENa<=bTHENSETc=a;WHILE(a+1)<=bDOSETc=c+a+1;SETa=a+1;ENDWHILE;WHENa>bTHENSELECT′输入错误!请重新输入!′;ENDCASE;END
DELIMITER ;
CALL yaoqiu6(1,100,@f);
SELECT @f;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
7.计算输入的任意两个数之间的偶数的和,且打印一共计算了多少次
DROP PROCEDURE IF EXISTS yaoqiu7;
DELIMITER CREATEPROCEDUREyaoqiu7(INaINT,INbINT,OUTcINT,OUTdINT)BEGINSETc=0;SETd=−1;CASEWHENa<=bTHENWHILEa<=bDOCASEWHEN(aTHENSETc=c+a;SETd=d+1;SETa=a+2;ELSESETa=a+1;ENDCASE;ENDWHILE;ELSESELECT′输入错误!请重新输入!′;ENDCASE;ENDCREATE PROCEDURE yaoqiu7(IN a INT,IN b INT,OUT c INT,OUT d INT ) BEGIN SET c=0; SET d=-1; CASE WHEN a<=b THEN WHILE a<=b DO CASE WHEN (a%2)=0 THEN SET c=c+a; SET d=d+1; SET a=a+2; ELSE SET a=a+1; END CASE; END WHILE; ELSE SELECT '输入错误!请重新输入!'; END CASE; END CREATEPROCEDUREyaoqiu7(INaINT,INbINT,OUTcINT,OUTdINT)BEGINSETc=0;SETd=−1;CASEWHENa<=bTHENWHILEa<=bDOCASEWHEN(aTHENSETc=c+a;SETd=d+1;SETa=a+2;ELSESETa=a+1;ENDCASE;ENDWHILE;ELSESELECT′输入错误!请重新输入!′;ENDCASE;END
DELIMITER ;
CALL yaoqiu7(2,8,@f,@s);
SELECT @f,@s;
1
2
8.计算任意两个数之间,是5的倍数,但不是6的倍数之间的和,且打印出一共计算了多少次?
DROP PROCEDURE IF EXISTS yaoqiu8;
DELIMITER CREATEPROCEDUREyaoqiu8(INaINT,INbINT,OUTcINT,OUTdINT)BEGINSETd=−1;SETc=0;CASEWHENa<=bTHENWHILEa<=bDOCASEWHEN(aTHENCASEWHEN(aTHENSETc=c+a;SETd=d+1;SETa=a+1;ELSESETa=a+1;ENDCASE;ELSESETa=a+1;ENDCASE;ENDWHILE;IF(d=−1)THENSETd=0;ENDIF;WHENa>bTHENSELECT′输入错误!请重新输入!′;ENDCASE;ENDCREATE PROCEDURE yaoqiu8(IN a INT,IN b INT,OUT c INT,OUT d INT ) BEGIN SET d=-1; SET c=0; CASE WHEN a<=b THEN WHILE a<=b DO CASE WHEN (a%5)=0 THEN CASE WHEN (a%6)!=0 THEN SET c=c+a; SET d=d+1; SET a=a+1; ELSE SET a=a+1; END CASE; ELSE SET a=a+1; END CASE; END WHILE; IF (d=-1) THEN SET d=0; END IF; WHEN a>b THEN SELECT '输入错误!请重新输入!'; END CASE; END CREATEPROCEDUREyaoqiu8(INaINT,INbINT,OUTcINT,OUTdINT)BEGINSETd=−1;SETc=0;CASEWHENa<=bTHENWHILEa<=bDOCASEWHEN(aTHENCASEWHEN(aTHENSETc=c+a;SETd=d+1;SETa=a+1;ELSESETa=a+1;ENDCASE;ELSESETa=a+1;ENDCASE;ENDWHILE;IF(d=−1)THENSETd=0;ENDIF;WHENa>bTHENSELECT′输入错误!请重新输入!′;ENDCASE;END
DELIMITER ;
CALL yaoqiu8(1,15,@f,@s);
SELECT @f,@s;
1
2
3
4
5
6
7
8
36
REPEAT循环
语法:
[begin_label:] REPEAT
statement_list ;
UNTIL search_condition
END REPEAT [begin_label];
1
2
3
4
**语义:**反复执行循环体中的语句statement_list ,直到until条件search_condition 为true时,循环结束
本循环特点:先执行,再判断
例9:
DELIMITER CREATEPROCEDUREdorepeat(p1INT)BEGINSET@x=0;REPEATSET@x=@x+1;UNTIL@x>p1ENDREPEAT;ENDCREATE PROCEDURE dorepeat(p1 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; ENDCREATEPROCEDUREdorepeat(p1INT)BEGINSET@x=0;REPEATSET@x=@x+1;UNTIL@x>p1ENDREPEAT;END
DELIMITER ;
mysql> call dorepeat1(10);
mysql> select @x;
| @x |
±-----+
| 11 |
1
2
3
16
Repeat循环案例:
1.计算1+2+3+…+100
DROP PROCEDURE IF EXISTS proc_repeat1;
DELIMITER //
CREATE PROCEDURE proc_repeat1()
BEGIN
– 声明变量(单行注释方式一)
#声明变量(单行注释方式二)
/**(多行注释方式三)
kbdgsl
bfkhsd
**/
DECLARE sum_repeat INT; – 累加和
DECLARE i INT; – 控制循环i
– 初始化变量
SET sum_repeat = 0;
SET i = 0;
– repeat 循环,实现累加功能
repeat_sum:REPEAT
SET sum_repeat = sum_repeat+i;
SET i = i+1;
UNTIL i>100
END REPEAT repeat_sum;
SELECT sum_repeat;
END //
DELIMITER ;
CALL proc_repeat1();
1
28
2.累加(如果累加和是5的倍数,则停止累计)
DROP PROCEDURE IF EXISTS proc_repeat2;
DELIMITER //
CREATE PROCEDURE proc_repeat2()
BEGIN
– 声明变量
DECLARE sum_repeat INT; – 累加和
DECLARE i INT; – 控制循环i
– 初始化变量
SET sum_repeat = 1;
SET i = 0;
– repeat 循环,实现累加功能
repeat_sum:REPEAT
IF sum_repeat%5 != 0 THEN
SET sum_repeat = sum_repeat+i;
SET i = i+1;
ELSE
SET i = 10000;
END IF ;
UNTIL i>100
END REPEAT repeat_sum;
SELECT sum_repeat-1;
END //
DELIMITER ;
CALL proc_repeat2();
1
29
3.-- 练习 –
/**
1.输入任意一个数值,作为累计的起始值;
2.输入一个数值,表示累加的次数
3.要求:
从该起始值开始连续累加,累加的次数为输入的第二个变量;累加的数值为随机生成的任意值;
使用repeat实现;
4.举例:
call proc_name(123,3);
累加的和为:123+随机数1+随机数2+随机数3
**/
代码如下:
DROP PROCEDURE IF EXISTS test_sum;
DELIMITER $$
CREATE PROCEDURE test_sum(IN f_number INT,IN s_number INT)
BEGIN
DECLARE s_count INT; – 累加和
DECLARE p_count INT ; – 随机数
DECLARE l_count INT; – 累加次数
SET s_count=f_number; – 原累加和为输入的任意值
SET l_count=0; – 原累加次数为0
SET p_count=0; – 原随机数为0
repeat_sum1:REPEAT
SET p_count=FLOOR(RAND()*100); – 生成随机数
SET s_count= s_count + p_count; – 累加
SET l_count=l_count+1; – 累加次数加一
UNTIL l_count=s_number
END REPEAT repeat_sum1;
SELECT s_count;
END $$
DELIMITER ;
CALL test_sum(123,3);
1
24
注意:
1、mysql中注释可以用单行注释,如:–(空格)注释、#注释、也可以用多行注释,如/注释/;
2、在存储过程中使用变量一般用declare先声明变量,然后用set初始化变量;
LEAVE语句
语法: LEAVE label ;-- 跳出整个循环体
该语句用来退出带标签的语句块或者循环
该语句用在 BEGIN … END中或者循环中 (LOOP, REPEAT, WHILE)
例10:创建过程,其中的一个语句块较早的结束
DELIMITER $$
CREATE PROCEDURE small_exit(OUT p1 INTEGER,OUT p2 INTEGER)
BEGIN
SET p1 = 1;
SET p2 = 1;
block1:BEGIN
LEAVE block1; --离开块block1,跳出begin end
SET p2 = 3; – 不会执行
END block1;
SET p1 = 4; --执行
END$$
DELIMITER ;
call small_exit(@a,@b);
mysql> select @a,@b;
±-----±-----+
| @a | @b |
±-----±-----+
| 4 | 1 |
1
20
Leave语句案例:
– 实现累加,但是累加和超过4000就退出
DROP PROCEDURE IF EXISTS proc_leave1;
DELIMITER //
CREATE PROCEDURE proc_leave1(OUT out_sum_repeat INT)
BEGIN
– 声明变量
DECLARE sum_repeat INT; – 累加和
DECLARE i INT; – 控制循环i
– 初始化变量
SET sum_repeat = 0;
SET i = 0;
– repeat 循环,实现累加功能
repeat_sum:REPEAT
– 判断和是否大于4000
IF sum_repeat > 4000 THEN
LEAVE repeat_sum;
SET i = 10000;
ELSE
SET sum_repeat = sum_repeat+i;
SET i = i+1;
END IF ;
UNTIL i>100
END REPEAT repeat_sum;
SELECT sum_repeat INTO out_sum_repeat;
END //
DELIMITER ;
CALL proc_leave1(@out_sum_repeat);
SELECT @out_sum_repeat;
1
30
LOOP循环
语法:
[begin_label:] LOOP
statement_list ;
END LOOP [begin_label];
1
2
3
**语义:**反复执行循环体中的语句,直到循环结束。循环的结束使用leave语句
例11:创建过程,它等待指定的秒数后结束
DELIMITER CREATEPROCEDUREwaitn(INwaitsecondsINTEGER)BEGINDECLAREendtimeINTEGERDEFAULTnow()+INTERVALwaitsecondsSECOND;waitloop:LOOPIFnow()>endtimeTHENLEAVEwaitloop;ENDIF;ENDLOOPwaitloop;ENDCREATE PROCEDURE wait_n(IN wait_seconds INTEGER) BEGIN DECLARE end_time INTEGER DEFAULT now() + INTERVAL wait_seconds SECOND; wait_loop:LOOP IF now() > end_time THEN LEAVE wait_loop; END IF; END LOOP wait_loop; ENDCREATEPROCEDUREwaitn(INwaitsecondsINTEGER)BEGINDECLAREendtimeINTEGERDEFAULTnow()+INTERVALwaitsecondsSECOND;waitloop:LOOPIFnow()>endtimeTHENLEAVEwaitloop;ENDIF;ENDLOOPwaitloop;END
DELIMITER ;
call wait_n(10);
1
2
3
4
5
6
7
8
9
10
11
12
13
Loop语句案例:
– 练习 –
/**
1.累加输入的两个参数之间的偶数的和,一旦累加和大于1000或者累加的次数超过100次,就:
累加两个参数之间是3的倍数但不是偶数的数值和,累计次数不得超过20次;
计算两个累计和的差异值;
要求:
1.代码简洁漂亮
2.逻辑正确
**/
DROP PROCEDURE IF EXISTS proc_loop2;
DELIMITER //
CREATE PROCEDURE proc_loop2(OUT out_sum INT)
BEGIN
– 声明变量
DECLARE sum_repeat INT; – 累加和
DECLARE i INT; – 控制循环i
– 初始化变量
SET sum_repeat = 0;
SET i = 0;
wait_loop:LOOP
IF sum_repeat > 4000 THEN
LEAVE wait_loop;
ELSE
SET sum_repeat = sum_repeat+i;
SET i = i+1;
END IF;
END LOOP wait_loop;
SET out_sum = sum_repeat;
END //
DELIMITER ;
CALL proc_loop2(@out_sum);
SELECT @out_sum;
1
2
27
ITERATE语句
**语法:**ITERATE label; – 跳出本次循环,继续下一次循环
只能出现在循环LOOP、REPEAT和WHILE 中
它的含义是:跳出本次循环,开始一次新的循环
例12:
DELIMITER CREATEPROCEDUREdoiterate(p1INT)BEGINlabel1:LOOPSETp1=p1+1;IFp1<10THENITERATElabel1;−−开始下一次循环ENDIF;LEAVElabel1;ENDLOOPlabel1;SET@x=p1;ENDCREATE PROCEDURE doiterate(p1 INT) BEGIN label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN ITERATE label1; -- 开始下一次循环 END IF; LEAVE label1; END LOOP label1; SET @x = p1; ENDCREATEPROCEDUREdoiterate(p1INT)BEGINlabel1:LOOPSETp1=p1+1;IFp1<10THENITERATElabel1;−−开始下一次循环ENDIF;LEAVElabel1;ENDLOOPlabel1;SET@x=p1;END
DELIMITER ;
1
2
3
4
5
6
7
8
9
10
11
12
13
案例:
1、
DELIMITER $$
CREATE PROCEDURE doiterate3(p1 INT,p2 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
SET p2 = p2 + 1;
IF p1 < 10 THEN
select p1;
ITERATE label1;
select p2;
END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END$$
DELIMITER ;
mysql> call doiterate3(10,10);
mysql> call doiterate3(1,1);
±-----+
| p1 |
±-----+
| 2 |
±-----+
| p1 |
±-----+
| 3 |
±-----+
| p1 |
±-----+
| 4 |
1
33
2、
DROP PROCEDURE IF EXISTS proc_iterate1;
DELIMITER //
CREATE PROCEDURE proc_iterate1(OUT out_sum INT)
BEGIN
– 声明变量
DECLARE sum_loop INT; – 累加和
DECLARE i INT; – 控制循环i
– 初始化变量
SET sum_loop = 1;
SET i = 0;
wait_loop:LOOP
IF sum_loop<=5000 THEN
IF i%6 = 0 THEN
SET i = i+1;
ITERATE wait_loop;
ELSE
SET sum_loop = sum_loop+i;
SET i = i+1;
END IF;
ELSE
LEAVE wait_loop;
END IF;
END LOOP wait_loop;
SET out_sum = sum_loop-1;
END //
DELIMITER ;
CALL proc_iterate1(@out_sum);
SELECT @out_sum;
MySQL中,常用的循环有三种,while循环、loop循环、repeat循环。
MySQL循环语句的作用与其他编程语言中的循环是一样的,不同的是在于语法。如在PHP语言中,要将一句话同样的话输出100次,毫无疑问就用到循环了,MySQL中也是如此。别忘了SQL也是一种编程。
while循环语法[标识名称:] WHILE 条件 DO
SQL语句;
END WHILE [标识名称]
存储过程-案例# 形式一 无标识
DELIMITER //
DROP PROCEDURE IF EXISTS twhile//
CREATE PROCEDURE twhile(IN c INT)
BEGIN
DECLARE count INT DEFAULT 0;
WHILE count
SELECT count;
SET count = count + 1;
END WHILE;
END//
CALL twhile(5)//
形式二 有标识
DELIMITER //
DROP PROCEDURE IF EXISTS twhile1//
CREATE PROCEDURE twhile1(IN c INT)
BEGIN
DECLARE count INT DEFAULT 0;
tt: WHILE count
SELECT count;
SET count = count + 1;
END WHILE tt;
END//
函数-案例-使用函数求1-100之和DELIMITER //
DROP FUNCTION IF EXISTS tfunc//
CREATE FUNCTION tfunc(quantity INT) RETURNS INT(10)
BEGIN
DECLARE count INT DEFAULT 0;
DECLARE num INT DEFAULT 0;
WHILE count
SET count = count+1;
SET num = num + count;
END WHILE;
RETURN num;
END//
SELECT tfunc(100)//
repeat循环
语法[标识名称:] REPEAT
SQL语句;
UNTIL 结束循环的条件 REPEAT [标识名称]
DELIMITER //
DROP PROCEDURE IF EXISTS demo02//
CREATE PROCEDURE demo02(IN c INT)
BEGIN
DECLARE i INT DEFAULT 0;
REPEAT
SELECT i
SET i =i+1;
UNTIL i >= c END REPEAT;
END//
CALL demo02(3)// # 结果 1 2 3
MySQL中使用leave与iterate来控制循环语句的走向,leave终止循环语句的执行,iterate跳过本次循环。相当于PHP中的continue与break。
loop循环
Loop循环没有自带的终止循环的条件,需要自定义条件退出循环。使用loop循环要退出时,loop循环必须声明循环标识名称。
语法[标识名称:] LOOP
sql语句;
END LOOP [标识名称];
案例
DELIMITER //
DROP PROCEDURE IF EXISTS demo02//
CREATE PROCEDURE demo02(IN c INT)
BEGIN
DECLARE i INT DEFAULT 0;
a: LOOP
SELECT i;
SET i=i+1;
当i>=5 时终退出循环
IF i >= c THEN LEAVE a;
END IF;
END LOOP a;
END//
iterate案例–输出偶数DELIMITER //
DROP PROCEDURE IF EXISTS demo02//
CREATE PROCEDURE demo02(IN c INT)
BEGIN
DECLARE i INT DEFAULT 0;
a: WHILE i
SET i=i+1;
IF i>c THEN LEAVE a;
ELSEIF MOD(i,2) != 0 THEN ITERATE a;
END IF;
SELECT i;
END WHILE a;
END//
三中循环的特点
while循环:先判断条件后执行语句;
repeat循环:先执行语句后判断条件;
loop循环:无条件的死循环
/* 建立存储过程 */
CREATE PROCEDURE useCursor()
BEGIN
/局部变量的定义/
declare tmpName varchar(20) default ‘’ ;
declare allName varchar(255) default ‘’ ;
declare cur1 CURSOR FOR SELECT name FROM test.level ;declare CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = null;#也可以这么写#DECLARE CONTINUE HANDLER FOR NOT FOUND SET tmpname = null;OPEN cur1;FETCH cur1 INTO tmpName;WHILE ( tmpname is not null) DOset tmpName = CONCAT(tmpName ,";") ;set allName = CONCAT(allName ,tmpName) ;FETCH cur1 INTO tmpName;END WHILE;CLOSE cur1;select allName ;
END;
call useCursor()