cbear小站
large picture of the cover

2-8-PROCEDURE存储过程

1. 存储过程基础

存储过程 : 多表SQL的语句的集合 , 可以独立执行 , 存储过程保存在mysql.proc表中。

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来 , 这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时 , 只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

存储过程优势 : 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程 , 提高了运行速度 , 同时降低网络数据传输量

存储过程与自定义函数的区别 存储过程实现的过程要复杂一些,而函数的针对性较强 存储过程可以有多个返回值,而自定义函数只有一个返回值 存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用

2. 存储过程语法

创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
​
​
proc_parameter : [IN|OUT|INOUT] parameter_name type
​
type:
    Any valid MySQL data type
​
routine_body:
    Valid SQL routine statement
​

说明: 其中IN表示输入参数, OUT表示输出参数, INOUT表示既可以输入也可以输出; param_name表示参数名称; type表示参数的类型

参考存储过程列表

SHOW PROCEDURE STATUS;

查看存储过程定义

SHOW CREATE PROCEDURE sp_name

调用存储过程

CALL sp_name ([ proc_parameter [,pro_parameter ...]])

说明: 当无参时, 可以省略"()", 当有参数时,不可省略"()"

存储过程修改

ALTER 语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改存储过程体,所以要修改存储过程,方法就是删除重建

删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

3. 存储过程示例

3.1 空参存储过程

# 创建无参存储过程
delimiter //
CREATE PROCEDURE showTime()
BEGIN
    SELECT now();
END//
delimiter ;  
​
CALL showTime;
# 案例 : 往表中插入3条记录
delimiter $
create procedure myp1()
begin
    insert into admin(username, `password`)
    values('tom','123'),('tom','123'),('tom','123');
end $
# 调用 , 尽量在命令行中使用
call myp1() $ # 如果报语法错误的话 , 则使用call myp1();
​

执行call myp1()$语句的时候 , 如果报错Thread stack overrun。。。 , 则表示MySQL中的线程栈太小了 , 不能执行这个函数 , 默认是128k。可以修改这个值为256k。

（1）sudo vim /etc/my.cnf
（2）找到thread_stack = 128K这一行 , 将其修改为
thread_stack = 256K
（3）退出 , 保存 , 并且重启MySQL服务。即可重新运行。

delimiter $这条语句只在连接内有效 , 重开一个连接又恢复成了默认的delimiter ;

3.2 带in模式的存储过程

范例

#创建含参存储过程: 只有一个IN参数  
delimiter //  
CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED)  
BEGIN 
	SELECT * FROM students WHERE stuid = id; 
END//
delimiter ; 
CALL selectById(2);

范例

delimiter // 
CREATE PROCEDURE dorepeat(n INT) 
BEGIN  
	SET @i = 0; 
	SET @SUM = 0;
	REPEAT SET @sum = @sum+@i; 
	SET @i = @i +1 ;
	UNTIL @i > n END REPEAT;  
END//
delimiter ; 
CALL dorepeat(100) ; 
SELECT @sum;
# 案例1 : 创建存储过程 , 实现 : 根据女神名 , 查询对应的男神信息
# 一个参数
delimiter $
create procedure myp2(in beautyName VARCHAR(20))
begin
	select bo.*
	from beauty b
	left join boys bo
	on b.boyfriend_id = bo.id
	where b.name = beautyName;
end $
# 调用
CALL myp2('小昭');

# 案例2 : 创建存储过程 , 实现 : 用户是否登录成功
# 多个参数
# 使用变量
delimiter $
create procedure myp3(in username VARCHAR(20), in `password` varchar(20))
begin
	# 声明并初始化
	declare result int default 0;
	select count(*) into result # 赋值
	from admin a
	where a.username=username and a.`password` = `password`;
	SELECT if(result>0, '登录成功', '登录失败');
end $
CALL myp3('john','888');
#######不使用变量########
delimiter $
create procedure myp4(in username VARCHAR(20), in `password` varchar(20))
begin
	select if(count(*)>0, '登录成功', '登录失败')
	from admin a
	where a.username=username and a.`password` = `password`;
end $
CALL myp4('john','888');

3.3 带out模式的存储过程

范例

#创建含参存储过程:包含IN参数和OUT参数
delimiter //
CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
BEGIN
DELETE FROM students WHERE stuid >= id;
SELECT row_count() into num;
END//
delimiter ;
call deleteById(2,@Line);
SELECT @Line;
#说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删除的ID和保存被修改的行
数值的用户变量@Line,select @Line;输出被影响行数
#row_count() 系统内置函数 , 用于存放前一条SQL修改过的表的记录数
# 案例1 : 根据女神名 , 返回对应的男神名
# out有一个参数
delimiter $
create procedure myp5(in beautyName VARCHAR(20), out boyName varchar(20))
begin
	select bo.boyName into boyName
	from beauty b
	left join boys bo
	on b.boyfriend_id = bo.id
	where b.name = beautyName;
end $
# 调用 
# set @bName; # 可以省略
CALL myp5('小昭',@bName);
select @bName;

# 案例2 : 根据女神名 , 返回对应的男神名和男神魅力值
# out有多个参数 , 要写多个out
delimiter $
create procedure myp6(in beautyName VARCHAR(20), out boyName varchar(20),out userCP int)
begin
	select bo.boyName, bo.userCP into boyName, userCP
	from beauty b
	left join boys bo
	on b.boyfriend_id = bo.id
	where b.name = beautyName;
end $
# 调用
# set @bName; # 可以省略
CALL myp6('小昭',@bName,@usercp);
select @bName, @usercp;

3.4 带inout模式的存储过程

delimiter $
create procedure myp7(inout a int, inout b int)
begin
	set a = a * 2;
	set b = b * 2;
end $
# 调用
# 先设置用户变量
set @x=10;
set @y=20;
CALL myp7(@x,@y);
select @x, @y;

4 流程控制

存储过程和函数中可以使用流程控制来控制语句的执行

  • IF : 用来进行条件判断。根据是否满足条件 , 执行不同语句

  • CASE : 用来进行条件判断 , 可实现比IF语句更复杂的条件判断

  • LOOP : 重复执行特定的语句 , 实现一个简单的循环

  • LEAVE : 用于跳出循环控制 , 相当于SHELL中break

  • ITERATE : 跳出本次循环 , 然后直接进入下一次循环 , 相当于SHELL中continue

  • REPEAT : 有条件控制的循环语句。当满足特定条件时 , 就会跳出循环语句

  • WHILE : 有条件控制的循环语句

4.1 IF

4.1.1 IF语句

IF语句用来进行条件判断 , 根据是否满足条件(可包含多个条件) , 来执行不同的语句 , 是流程控制中最常用的判断语句。其语法的基本形式如下:

IF语句语法

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list]...
    [ELSE statement_list]
END IF

其中 , search_condition参数表示条件判断语句 , 如果返回值为TRUE , 相应的SQL语句列表(statement_list ) 被执行; 如果返回值为 FALSE , 则ELSE子句的语句列表被执行。statement_list 可以包括一个或多个语句。

IF语句

IF expression THEN 
   statements;
END IF;

IF ELSE 语句

IF expression THEN
   statements;
ELSE
   else-statements;
END IF;

IF-ELSEIF-ELSE语句

IF expression THEN
   statements;
ELSEIF elseif-expression THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF;

范例

create procedure test(in v_id int)
begin
    if (v_id > 0) then
        select '> 0';
    elseif (v_id = 0) then
        select '= 0';
    else
        select '< 0';
    end if;
end //

MariaDB [hellodb]> call test(0); 
+-----+
| = 0 |
+-----+
| = 0 |
+-----+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

范例

DELIMITER $$
 
CREATE PROCEDURE GetCustomerLevel(
    in  p_customerNumber int(11), 
    out p_customerLevel  varchar(10))
BEGIN
    DECLARE creditlim double;
 
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;
 
    IF creditlim > 50000 THEN
        SET p_customerLevel = 'PLATINUM';
    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
        SET p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
        SET p_customerLevel = 'SILVER';
    END IF;
 
END $$

DELIMITER ; 

mysql> call GetCustomerLevel(141,@p_customerLevel);
Query OK, 1 row affected (0.00 sec)

mysql> select @p_customerLevel;
+------------------+
| @p_customerLevel |
+------------------+
| PLATINUM         |
+------------------+
1 row in set (0.00 sec)

范例

mysql> 
 /*如果存在函数func_test2 , 则删除*/
 DROP FUNCTION IF EXISTS fun_if;
 /*声明结束符为$*/
 DELIMITER $
 /*创建函数*/
 CREATE FUNCTION fun_if(score DECIMAL(10,2))
		RETURNS CHAR
 BEGIN
 		DECLARE score_grad VARCHAR(5) DEFAULT '';
 		IF score>100 THEN SET score_grad='S'; 
 		ELSEIF (score BETWEEN 91 AND 100) THEN SET score_grad='A';
 		ELSEIF (score BETWEEN 81 AND 90) THEN SET score_grad='B';
 		ELSEIF (score BETWEEN 71 AND 80) THEN SET score_grad='C' ;
 		ELSE set score_grad='D';
 		END IF;
 return score_grad;
 END $
 /*重置结束符为;*/
 DELIMITER ;
 Query OK, 0 rows affected 
 
mysql> select fun_if(101),fun_if(100),fun_if(90),fun_if(80),fun_if(70);
 +-------------+-------------+------------+------------+------------+
 | fun_if(101) | fun_if(100) | fun_if(90) | fun_if(80) | fun_if(70) |
 +-------------+-------------+------------+------------+------------+
 | S           | A           | B          | C          | D          |
 +-------------+-------------+------------+------------+------------+
 1 row in set

4.1.2 IF函数

  • IF(value , value1 , value2) : 如果value的值为TRUE , 返回value1 ,否则返回value2

  • IFNULL(value1 , value2) : 如果value1不为NULL , 返回value1 , 否则返回value2

示例

MariaDB [hellodb]> create procedure xx()  begin     set @i = if (1>2, '错' , '对')     ;  end;
Query OK, 0 rows affected (0.001 sec)

MariaDB [hellodb]> call xx;
Query OK, 0 rows affected (0.000 sec)

MariaDB [hellodb]> select @i;
+------+
| @i   |
+------+
| 对   |
+------+
1 row in set (0.000 sec)
MariaDB [hellodb]> create procedure zz()  begin     set @i = ifnull (null , 1  )     ;  end//
Query OK, 0 rows affected (0.002 sec)
MariaDB [hellodb]> call zz;
Query OK, 0 rows affected (0.000 sec)

MariaDB [hellodb]> select @i;
+------+
| @i   |
+------+
|    1 |
+------+

4.2 CASE

CASE语句也是用来进行条件判断的 , 它提供了多个条件进行选择 , 可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]...
    [ELSE statement_list]
END CASE

其中

  • case_value参数表示条件判断的变量 , 决定了哪一个WHEN子句会被执行 ;

  • when_value参数表示变量的取值 , 如果某个when_value 表达式与case_value变量的值相同 , 则执行对应的THEN关键字后的 statement_list中的语句 ;

  • statement_list参数表示when_value值没有与case_value相同值时的执行语句。

  • CASE语句都要使用END CASE 结束。

CASE语句还有另一种形式。该形式的语法如下:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

其中 , search_condition参数表示条件判断语句 ; statement_list参数表示不同条件的执行语句。

与上述语句不同的是 , 该语句中的WHEN语句将被逐个执行 , 直到某个search_condition表达式为真 , 则执行对应THEN关键字后面的 statement_list语句。如果没有条件匹配 , ELSE子句里的语句被执行。

这里介绍的 CASE 语句与“控制流程函数”里描述的 SQL CASE 表达式的 CASE 语句有轻微的不同。
这里的 CASE 语句不能有 ELSE NULL 语句 , 并且用 END CASE 替代 END 来终止。

范例

MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.000 sec)

MariaDB [hellodb]> select Name,CASE Gender WHEN 'F' then   '女' when 'M' then '男' end as  sex from students;
+---------------+------+
| Name          | sex  |
+---------------+------+
| Shi Zhongyu   | 男   |
| Shi Potian    | 男   |
| Xie Yanke     | 男   |
| Ding Dian     | 男   |
| Yu Yutong     | 男   |
| Shi Qing      | 男   |
| Xi Ren        | 女   |
| Lin Daiyu     | 女   |
| Ren Yingying  | 女   |
| Yue Lingshan  | 女   |
| Yuan Chengzhi | 男   |
| Wen Qingqing  | 女   |
| Tian Boguang  | 男   |
| Lu Wushuang   | 女   |
| Duan Yu       | 男   |
| Xu Zhu        | 男   |
| Lin Chong     | 男   |
| Hua Rong      | 男   |
| Xue Baochai   | 女   |
| Diao Chan     | 女   |
| Huang Yueying | 女   |
| Xiao Qiao     | 女   |
| Ma Chao       | 男   |
| Xu Xian       | 男   |
| Sun Dasheng   | 男   |
+---------------+------+
25 rows in set (0.000 sec)



MariaDB [hellodb]>  select Name,ClassID from students;
+---------------+---------+
| Name          | ClassID |
+---------------+---------+
| Shi Zhongyu   |       2 |
| Shi Potian    |       1 |
| Xie Yanke     |       2 |
| Ding Dian     |       4 |
| Yu Yutong     |       3 |
| Shi Qing      |       5 |
| Xi Ren        |       3 |
| Lin Daiyu     |       7 |
| Ren Yingying  |       6 |
| Yue Lingshan  |       3 |
| Yuan Chengzhi |       6 |
| Wen Qingqing  |       1 |
| Tian Boguang  |       2 |
| Lu Wushuang   |       3 |
| Duan Yu       |       4 |
| Xu Zhu        |       1 |
| Lin Chong     |       4 |
| Hua Rong      |       7 |
| Xue Baochai   |       6 |
| Diao Chan     |       7 |
| Huang Yueying |       6 |
| Xiao Qiao     |       1 |
| Ma Chao       |       4 |
| Xu Xian       |    NULL |
| Sun Dasheng   |    NULL |
+---------------+---------+
25 rows in set (0.000 sec)

MariaDB [hellodb]>  select Name,ClassID , CASE WHEN ClassID < 2 THEN '低级' WHEN ClassID <5 THEN '中级' ELSE '高级' END '等级 ' from students;
+---------------+---------+---------+
| Name          | ClassID | 等级    |
+---------------+---------+---------+
| Shi Zhongyu   |       2 | 中级    |
| Shi Potian    |       1 | 低级    |
| Xie Yanke     |       2 | 中级    |
| Ding Dian     |       4 | 中级    |
| Yu Yutong     |       3 | 中级    |
| Shi Qing      |       5 | 高级    |
| Xi Ren        |       3 | 中级    |
| Lin Daiyu     |       7 | 高级    |
| Ren Yingying  |       6 | 高级    |
| Yue Lingshan  |       3 | 中级    |
| Yuan Chengzhi |       6 | 高级    |
| Wen Qingqing  |       1 | 低级    |
| Tian Boguang  |       2 | 中级    |
| Lu Wushuang   |       3 | 中级    |
| Duan Yu       |       4 | 中级    |
| Xu Zhu        |       1 | 低级    |
| Lin Chong     |       4 | 中级    |
| Hua Rong      |       7 | 高级    |
| Xue Baochai   |       6 | 高级    |
| Diao Chan     |       7 | 高级    |
| Huang Yueying |       6 | 高级    |
| Xiao Qiao     |       1 | 低级    |
| Ma Chao       |       4 | 中级    |
| Xu Xian       |    NULL | 高级    |
| Sun Dasheng   |    NULL | 高级    |
+---------------+---------+---------+
25 rows in set (0.000 sec)

MariaDB [hellodb]>  select Name ,ClassID , CASE WHEN ClassID <= 2 THEN '低级' WHEN ClassID <5 THEN '中级' ELSE '高级' END '等级 ' from students order by CASE  WHEN ClassID <= 2 THEN 1 WHEN ClassID < 5 THEN 2 ELSE 3 END ;
+---------------+---------+---------+
| Name          | ClassID | 等级    |
+---------------+---------+---------+
| Shi Zhongyu   |       2 | 低级    |
| Xiao Qiao     |       1 | 低级    |
| Xu Zhu        |       1 | 低级    |
| Tian Boguang  |       2 | 低级    |
| Wen Qingqing  |       1 | 低级    |
| Xie Yanke     |       2 | 低级    |
| Shi Potian    |       1 | 低级    |
| Lin Chong     |       4 | 中级    |
| Duan Yu       |       4 | 中级    |
| Lu Wushuang   |       3 | 中级    |
| Ding Dian     |       4 | 中级    |
| Yu Yutong     |       3 | 中级    |
| Yue Lingshan  |       3 | 中级    |
| Ma Chao       |       4 | 中级    |
| Xi Ren        |       3 | 中级    |
| Xu Xian       |    NULL | 高级    |
| Huang Yueying |       6 | 高级    |
| Diao Chan     |       7 | 高级    |
| Xue Baochai   |       6 | 高级    |
| Hua Rong      |       7 | 高级    |
| Shi Qing      |       5 | 高级    |
| Yuan Chengzhi |       6 | 高级    |
| Ren Yingying  |       6 | 高级    |
| Lin Daiyu     |       7 | 高级    |
| Sun Dasheng   |    NULL | 高级    |
+---------------+---------+---------+
25 rows in set (0.000 sec)

范例

 mysql> 
 /*如果存在函数func_test2 , 则删除*/
 DROP FUNCTION IF EXISTS fun_case;
 /*声明结束符为$*/
 DELIMITER $
 /*创建函数*/
 CREATE FUNCTION fun_case(sex INT)
 		RETURNS VARCHAR(20)
 BEGIN
 		DECLARE sexStr VARCHAR(20) DEFAULT '';
		CASE sex 
			WHEN 0 then set sexStr='女';
 			WHEN 1 then set sexStr='男';
 			ELSE set sexStr='不确定';
 		END CASE;
 		return sexStr;
 END $
 /*重置结束符为;*/
 DELIMITER ;
 
 Query OK, 0 rows affected 
 
 mysql> select studentname,fun_case(sex) from students where classid<>0;
 +-------------+---------------+
 | studentname | fun_case(sex) |
 +-------------+---------------+
 | brand       | 男            |
 | helen       | 女            |
 | lyn         | 女            |
 | sol         | 男            |
 | b1          | 男            |
 | b2          | 男            |
 | c1          | 女            |
 | c2          | 女            |
 | A           | 男            |
 +-------------+---------------+
 9 rows in set

范例

mysql> DELIMITER |
mysql> CREATE PROCEDURE p(in pi_num int)
    ->   BEGIN
    ->     CASE pi_num
    ->       WHEN 2 THEN SELECT 'TWO';
    ->       WHEN 3 THEN SELECT 'THREE';
    ->       ELSE
    ->         BEGIN
    ->         END;
    ->     END CASE;
    ->   END;
    -> |
Query OK, 0 rows affected (0.04 sec)

mysql> DELIMITER ;
mysql> CALL P(2);
+-----+
| TWO |
+-----+
| TWO |
+-----+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> CALL P(3);
+-------+
| THREE |
+-------+
| THREE |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL P(4);
Query OK, 0 rows affected (0.00 sec)

4.3 循环

4.3.1 WHILE

WHILE语句也是有条件控制的循环语句。WHILE语句和REPEAT语句不同的是 , WHILE语句是当满足条件时 , 执行循环内的语句 , 否则退出循环。WHILE语句的基本语法形式如下:

[begin_label:] WHILE search_condition DO
  statement list
END WHILE [end label]

其中 , search_condition参数表示循环执行的条件 , 满足该条件时循环执行 ; statement_list参数表示循环的执行语句。WHILE循环需要使用END WHILE来结束。

范例

#0-10之间所有偶数相加
DELIMITER $
CREATE PROCEDURE TestWhile()
BEGIN
DECLARE x,sumx integer DEFAULT 0;
label1: WHILE x<10 DO
       SET x = x+1;
       IF x mod 2 THEN
            ITERATE label1;
       ELSE
            SET sumx = sumx + x ;
       END IF;
END WHILE label1;

SELECT sumx;
END;
$
DELIMITER ;

MariaDB [hellodb]> call testwhile();
+------+
| sumx |
+------+
|   30 |
+------+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

#l_n1为0时退出
DELIMITER //

CREATE PROCEDURE p5()
BEGIN
  
  declare l_n1 int default 10;
 
  
  while l_n1 > 0 DO  
     set l_n1 = l_n1 - 1;
  end while;
  
  select l_n1;  

END;
//
DELIMITER ;

MariaDB [hellodb]> call p5();
+------+
| l_n1 |
+------+
|    0 |
+------+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

4.3.2 LOOP

LOOP语句可以使某些特定的语句重复执行。与IF和CASE语句相比 , LOOP只实现了一个简单的循环 , 并不进行条件判断。

LOOP语句本身没有停止循环的语句 , 必须使用LEAVE语句等才能停止循环 , 跳出循环过程。LOOP语句的基本形式如下:

[begin_label:]LOOP
    statement_list
END LOOP [end_label]

其中 , begin_label参数和end_label参数分别表示循环开始和结束的标志 ,这两个标志必须相同 , 而且都可以省略 ; statement_list 参数表示需要循环执行的语句。

#偶数相加

DELIMITER $
CREATE PROCEDURE TestLoop() 
BEGIN
DECLARE x,sumx integer DEFAULT 0;
label1: LOOP
      SET x=x+1;
      IF x > 10 THEN
         LEAVE label1;
      END IF;
      IF x mod 2 THEN 
         ITERATE label1;
      ELSE
         SET sumx = sumx +x ;
      END IF ;
END LOOP label1;
SELECT sumx ;
END $
DELIMITER ;

MariaDB [hellodb]> call TestLoop();
+------+
| sumx |
+------+
|   30 |
+------+
1 row in set (0.000 sec)

#用LOOP循环求1到10之和
DELIMITER //
CREATE PROCEDURE p3()
BEGIN
 
  declare sum int default 0;
  declare l_num int default 1;

  -- loop_1为接下来loop循环的标签
  loop_1: LOOP
    SET sum = sum + l_num;
    SET l_num = l_num + 1;
    IF l_num <= 10 THEN
      -- 条件成立,继续循环 
      ITERATE loop_1;
    END IF;
    -- 退出循环
    LEAVE loop_1;
  END LOOP loop_1;
  
  select  sum;

END;
//
DELIMITER ;

MariaDB [hellodb]> call p3();
+------+
| sum  |
+------+
|   55 |
+------+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

4.3.3 REPEAT

REPEAT语句是有条件控制的循环语句 , 每次语句执行完毕后 , 会对条件表达式进行判断 , 如果表达式返回值为TRUE , 则循环结束 , 否则重复执行循环中的语句。

REPEAT语句的基本语法形式如下:

[begin_label:] REPEAT
  statement_list
  UNTIL search_condition
END REPEAT [end_label]

其中 :

  • begin_label为REPEAT语句的标注名称 , 该参数可以省略 ;

  • REPEAT语句内的语句被重复 , 直至search_condition返回值为TRUE。

  • statement_list参数表示循环的执行语句 ;

  • search_condition参数表示结束循环的条件 , 满足该条件时循环结束。

  • REPEAT循环都用END REPEAT结束。

范例

# 0-10 所有偶数相加
DELIMITER $
CREATE PROCEDURE TestRepeat()
BEGIN
DECLARE x ,sumx int DEFAULT 0;
label1: REPEAT
      SET x = x + 1;
      IF x mod 2 THEN
          ITERATE label1;
      ELSE
          SET sumx = sumx + x ;
      END IF ;
UNTIL x = 10
END REPEAT label1;
SELECT sumx;
END ;
$
DELIMITER ;

MariaDB [hellodb]> call TestRepeat();
+------+
| sumx |
+------+
|   30 |
+------+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

#l_n2到11之后退出
DELIMITER //

CREATE PROCEDURE p4()
BEGIN
  
  declare l_n1 int default 10;
  declare l_n2 int default 1;
  
  -- 重复给l_n2加1,直到l_n2大于l_n1
  repeat 
     set l_n2 = l_n2 + 1;
  until l_n2 > l_n1 end repeat;
  
  select l_n2;  

END;
//

DELIMITER ;

MariaDB [hellodb]> call p4();
+------+
| l_n2 |
+------+
|   11 |
+------+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

4.3.4 LEAVE

LEAVE语句主要用于跳出循环控制。其语法形式如下:

LEAVE label

其中 , label参数表示循环的标志 , LEAVE语句必须跟在循环标志前面。

add_num:LOOP
    SET @count=@count+1;
    IF @count=100 THEN
        LEAVE add_num;
END LOOP add num;

该示例循环执行count加1的操作。当 count的值等于100时 , 跳出循环。

4.3.5 ITERATE

ITERATE是“再次循环”的意思 , 用来跳出本次循环 , 直接进入下一次循环。ITERATE语句的基本语法形式如下:

ITERATE label

其中 , label参数表示循环的标志 , ITERATE语句必须跟在循环标志前面。

add_num:LOOP
    SET @count=@count+1;
    IF @count=100 THEN
        LEAVE add_num;
    ELSE IF MOD(@count,3)=0 THEN
        ITERATE add_num;
    SELECT * FROM employee;
END LOOP add_num;

该示例循环执行count加1的操作 , count值为100时结束循环。如果count的值能够整除3 , 则跳出本次循环 , 不再执行下面的SELECT语句。

注 : LEAVE语句和ITERATE语句都用来跳出循环语句 , 但两者的功能是不一样的。LEAVE语句是跳出整个循环 , 然后执行循环后面的程序。而ITERATE语句是跳出本次循环 , 然后进入下一次循环。

熊熊

site logo

Hi,Friend

© 2025 cbear小站