mysql流程控制语句IF、CASE、While

• 数据准备

-- 创建表
CREATE TABLE t_user(
  id int PRIMARY KEY COMMENT '编号',
  sex TINYINT not null DEFAULT 1 COMMENT '性别,1:男,2:女',
  name VARCHAR(16) not NULL DEFAULT '' COMMENT '姓名'
)COMMENT '用户表';
-- 插入数据
INSERT INTO t_user VALUES
  (1,1,'路人甲Java'),
  (2,1,'张学友'),
  (3,2,'王祖贤'),
  (4,1,'郭富城'),
  (5,2,'李嘉欣');
-- 创建表
CREATE TABLE test1 (a int not null);
CREATE TABLE test2 (a int not null,b int NOT NULL );

IF函数

• 语法
  • if(条件表达式,值1,值2);
    • 当参数1为true的时候，返回值1 ，否则返回值2。

SELECT id 编号,if(sex=1,'男','女') 性别,name 姓名 FROM t_user;
/*
+--------+--------+---------------+
| 编号 | 性别 | 姓名 |
+--------+--------+---------------+
| 1 | 男 | 路人甲Java |
| 2 | 男 | 张学友 |
| 3 | 女 | 王祖贤 |
| 4 | 男 | 郭富城 |
| 5 | 女 | 李嘉欣 |
+--------+--------+---------------+
5 rows in set (0.00 sec)
*/

---

CASE结构

case第一种使用方式

• case 表达式
• when 值1 then 结果1或者语句1 （如果是语句需要加分号）
• when 值2 then 结果2或者语句2
• ...
• else 结果n或者语句n
• end [case]（如果是放在begin end之间需要加case，如果在select后则不需要）
• 在select中使用case

-- case语句
SELECT 
  id 编号,
  (
    CASE sex 
      WHEN 1 THEN '男' 
      ELSE '女' 
    END  -- 这里没有case关键字
  ) 性别,name 姓名
FROM t_user;
-- case语句
SELECT 
  id 编号,
  (
    CASE sex 
      WHEN 1 then '男' 
      WHEN 2 then '女' 
    END
  ) 性别,name 姓名 
FROM t_user;

• 在begin end中使用case

-- begin end中使用
DROP PROCEDURE IF EXISTS proc1; -- 删除存储过程proc1
DELETE FROM t_user WHERE id=6; -- 删除id=6的记录
DELIMITER $ -- 声明结束符为$
-- 创建存储过程proc1
CREATE PROCEDURE proc1(id int,sex_str varchar(8),name varchar(16))
  BEGIN
    /*声明变量v_sex用于存放性别*/
    DECLARE v_sex TINYINT UNSIGNED;
    /*根据sex_str的值来设置性别*/
    CASE sex_str
      when '男' THEN
      SET v_sex = 1;
    WHEN '女' THEN
      SET v_sex = 2;
    END CASE ;  -- 这里有case关键字
    /*插入数据*/
    INSERT INTO t_user VALUES (id,v_sex,name);
  END $
/*结束符置为;*/
DELIMITER ;
-- 调用存储过程
CALL procl(6, '男', '郭富城')

• 在函数中使用case

-- 函数中使用（写一个函数，根据t_user表sex的值，返回男女）
DROP FUNCTION IF EXISTS fun1; -- 删除存储过程proc1
DELIMITER $  -- 声明结束符为$
CREATE FUNCTION fun1(sex TINYINT UNSIGNED)
  RETURNS varchar(8)
  BEGIN
    /*声明变量v_sex用于存放性别*/
    DECLARE v_sex VARCHAR(8);
    CASE sex
    WHEN 1 THEN
      SET v_sex:='男';
    ELSE
      SET v_sex:='女';
    END CASE;
    RETURN v_sex;
  END $
/*结束符置为;*/
DELIMITER ;
-- 使用案例
select sex, fun1(sex) 性别,name FROM t_user;
/*
+-----+--------+---------------+
| sex | 性别 | name |
+-----+--------+---------------+
| 1 | 男 | 路人甲Java |
| 1 | 男 | 张学友 |
| 2 | 女 | 王祖贤 |
| 1 | 男 | 郭富城 |
| 2 | 女 | 李嘉欣 |
| 1 | 男 | 郭富城 |
+-----+--------+---------------+
6 rows in set (0.00 sec)
*/

case第二种使用方式

• case
• when 条件1 then 结果1或者语句1（如果是语句需要加分号）
• when 条件2 then 结果2或者语句2
• ...
• else 结果n或者语句n
• end [case] （如果是放在begin end之间需要加case，如果是在select后面case可以省略）

---

IF结构

• if 条件语句1 then 语句1;
• elseif 条件语句2 then 语句2;
• ...
• else 语句n;
• end if;
  • 只能使用在begin end之间。
• 存储过程使用IF

-- 实现用户数据的插入和新增，如果id存在，则修改，不存在则新增，并返回结果
DELETE FROM t_user WHERE id=7; -- 删除id=7的记录
DROP PROCEDURE IF EXISTS proc2; -- 删除存储过程
DELIMITER $  -- 声明结束符为$
-- 创建存储过程
CREATE PROCEDURE proc2(v_id int,v_sex varchar(8),v_name varchar(16),OUT result TINYINT)
  BEGIN
    DECLARE v_count TINYINT DEFAULT 0;  /*用来保存user记录的数量*/
    /*根据v_id查询数据放入v_count中*/
    select count(id) into v_count from t_user where id = v_id;
    /*v_count>0表示数据存在，则修改，否则新增*/
    IF v_count > 0 THEN
      BEGIN
        DECLARE lsex TINYINT;
        select if(lsex='男',1,2) into lsex;
        update t_user set sex = lsex,name = v_name where id = v_id;
        /*获取update影响行数*/
        select ROW_COUNT() INTO result;  
      END;
    else
      BEGIN
        DECLARE lsex TINYINT;
        select if(lsex='男',1,2) into lsex;
        insert into t_user VALUES (v_id,lsex,v_name);
        select 0 into result;
      END;
    END IF;
  END $
/*结束符置为*/
DELIMITER ;

-- 测试
SELECT * FROM t_user;
/*
+----+-----+---------------+
| id | sex | name |
+----+-----+---------------+
| 1 | 1 | 路⼈甲Java |
| 2 | 1 | 张学友 |
| 3 | 2 | 王祖贤 |
| 4 | 1 | 郭富城 |
| 5 | 2 | 李嘉欣 |
| 6 | 1 | 郭富城 |
+----+-----+---------------+
6 rows in set (0.00 sec)
*/
CALL proc2(7,'男','黎明',@result);
SELECT @result;
/*
+---------+
| @result |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)
*/
SELECT * FROM t_user;
/*
+----+-----+---------------+
| id | sex | name |
+----+-----+---------------+
| 1 | 1 | 路⼈甲Java |
| 2 | 1 | 张学友 |
| 3 | 2 | 王祖贤 |
| 4 | 1 | 郭富城 |
| 5 | 2 | 李嘉欣 |
| 6 | 1 | 郭富城 |
| 7 | 2 | 黎明 |
+----+-----+---------------+
7 rows in set (0.00 sec)
*/
CALL proc2(7,'男','梁朝伟',@result);
SELECT @result;
/*
+---------+
| @result |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
*/
SELECT * FROM t_user;
/*
+----+-----+---------------+
| id | sex | name |
+----+-----+---------------+
| 1 | 1 | 路⼈甲Java |
| 2 | 1 | 张学友 |
| 3 | 2 | 王祖贤 |
| 4 | 1 | 郭富城 |
| 5 | 2 | 李嘉欣 |
| 6 | 1 | 郭富城 |
| 7 | 2 | 梁朝伟 |
+----+-----+---------------+
7 rows in set (0.00 sec)
*/

---

循环

• while
• repeat 类似于 do while
• loop 类似于while(true)
• 循环控制
  • 结束本次循环 iterate 循环标签; 类似于continue;
  • 退出循环 leave 循环标签; 类似于break;
• while
  • 语法
    • [标签:] while 循环条件 do
    • 循环体
    • end while [标签];
      • 标签：是给while取的名字、标签和iterate、leave结合用于在循环内部对循环控制
      • 注意：这个循环先判断条件，条件成立后，才执行循环体，每次执行都会先进行判断。

无循环控制语句

-- 无循环控制语句（根据传入参数v_count向test1表插入指定数量的数据）
DELETE FROM test1; -- 删除test1表记录
DROP PROCEDURE IF EXISTS proc3; -- 删除存储过程
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc3(v_count int)
  BEGIN
    DECLARE i int DEFAULT 1;
    a:WHILE i<=v_count DO
      INSERT into test1 values (i);
      SET i=i+1;
    END WHILE;
  END $
/*结束符置为;*/
DELIMITER ;
-- 调用函数
CALL proc3(5);
/*Query OK, 1 row affected (0.01 sec)*/
SELECT * from test1;
/*
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---+
5 rows in set (0.00 sec)
*/

添加leave控制语句

-- 根据传入的参数v_count向test1表插入指定数量的数据，当插入超过10条，结束。
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc4;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc4(v_count int)
  BEGIN
    DECLARE i int DEFAULT 1;
    a:WHILE i <= v_count DO
      INSERT into test1 values (i);
      /*判断i=10，离开循环a*/
      IF i=10 THEN
        LEAVE a;
      END IF;
      SET i=i+1;
    END WHILE;
  END $
/*结束符置为;*/
DELIMITER ;
-- 调用存储过程
CALL proc4(20);

添加iterate控制语句

/*删除test1表记录*/
DELETE FROM test1;
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc5;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc5(v_count int)
  BEGIN
    DECLARE i int DEFAULT 0;
    a:WHILE i<=v_count DO
      SET i=i+1;
      /*如果i不为偶数，跳过本次循环*/
      IF i%2!=0 THEN
        ITERATE a;
      END IF;
      /*插入数据*/
      INSERT into test1 values (i);
    END WHILE;
  END $
/*结束符置为;*/
DELIMITER ;  
-- 调用存储函数
 CALL proc5(10);

嵌套循环

-- test2表有2个字段（a,b），写一个存储过程（2个参数：vacount，vbcount)，使用双重循环插入数据
-- 数据条件：a的范围[1,vacount]、b的范围[1,vbcount]所有偶数的组合
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc8;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc8(v_a_count int,v_b_count int)
  BEGIN
    DECLARE v_a int DEFAULT 0;
    DECLARE v_b int DEFAULT 0;
    a:WHILE v_a<=v_a_count DO
      SET v_a=v_a+1;
      SET v_b=0;
      b:WHILE v_b<=v_b_count DO
        SET v_b=v_b+1;
        IF v_a%2!=0 THEN
          ITERATE a;
        END IF;
        IF v_b%2!=0 THEN
          ITERATE b;
        END IF;
        INSERT INTO test2 VALUES (v_a,v_b);
      END WHILE b;
    END WHILE a;
  END $
/*结束符置为;*/
DELIMITER ;
-- 调用存储函数
CALL proc8(4,6);

• repeat循环
  • 语法
    • [标签:]repeat
      • 循环体;
    • until 结束循环的条件 end repeat [标签];
  • 说明：
    • repeat循环类似于java中的do...while循环,不管如何,循环都会先执行一次
    • 然后再判断结束循环的条件，不满足结束条件，循环体继续执行
    • 这块和while不同，while是先判断条件是否成立再执行循环体

无循环控制语句

-- 根据传入的参数v_count向test1表插入指定数量的数据
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc6;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc6(v_count int)
  BEGIN
    DECLARE i int DEFAULT 1;
    a:REPEAT
      INSERT into test1 values (i);
      SET i=i+1;
    UNTIL i>v_count END REPEAT;
  END $
/*结束符置为;*/
DELIMITER ;
-- 调用存储过程
CALL proc6(5);
-- repeat中 iterate 和 leave 用法和while中类似

loop循环

• 语法
  • [标签:]loop
    • 循环体
  • end loop [标签];
• loop相当于一个死循环，需要在循环体中使用 iterate 或者 leave 来控制循环的执行。
• 无循环控制语句

-- 根据传入的参数v_count向test1表插入指定数量的数据
/*删除存储过程*/
DROP PROCEDURE IF EXISTS proc7;
/*声明结束符为$*/
DELIMITER $
/*创建存储过程*/
CREATE PROCEDURE proc7(v_count int)
  BEGIN
    DECLARE i int DEFAULT 0;
    a:LOOP
      SET i=i+1;
      /*当i>v_count的时候退出循环*/
      IF i>v_count THEN
        LEAVE a;
      END IF;
      INSERT into test1 values (i);
    END LOOP a;
  END $
/*结束符置为;*/
DELIMITER ;
-- 调用
CALL proc7(5);

---

总结

• if函数常在在select中
• case语句有2种写法，主要用在select、begin end中，select中end后面可以省略case，begin end中使用不能省略case
• if语句用在begin end中
• 3种循环体的使用，while类似于java中的while循环，repeat类似于java中的do while循环，loop类似于java中的死循环，都用于begin end中
• 循环中体中的控制依靠 leave 和 iterate ， leave 类似于java中的 break 可以退出循环， iterate 类似于java中的continue可以结束本次循环