19. Mysql 循环语句

[while_label:] while 循环条件  do
                     循环体
               end while [while_label];

drop procedure if exists update_salary_while;
create procedure update_salary_while(out num int)
begin
    # 声明变量和赋值
  declare avg_sal double ; -- 记录平均工资
  declare while_count int default 0; -- 记录循环次数
  select avg(salary) into avg_sal from employees; -- 赋值
  while avg_sal > 5000 do
    update employees set salary = salary * 0.9 ;
    set while_count = while_count + 1;
    select avg(salary) into avg_sal from employees;
  end while;
  set num = while_count;-- 给num赋值
end;

call update_salary_while(@num); -- 调用
select @num;
select avg(salary) from employees;

[repeat_label:] repeat
　　　　         循环体的语句
                    until 结束循环的条件表达式
                end repeat [repeat_label];

drop procedure if exists update_salary_repeat;
create procedure update_salary_repeat(out num int)
begin
  #声明变量
  declare avg_sal double ; -- 记录平均工资
  declare repeat_count int default 0; -- 记录循环次数
  select avg(salary) into avg_sal from employees;
  repeat
    update employees set salary = salary * 1.15;
    set repeat_count = repeat_count + 1;
    select avg(salary) into avg_sal from employees;
    until avg_sal >= 13000
  end repeat;
  set num = repeat_count;-- 给num赋值    
end;
call update_salary_repeat(@num);
select @num;
select avg(salary) from employees;

[loop_label:] loop
                循环执行的语句
              end loop [loop_label];

drop procedure if exists update_salary_repeat;
create procedure update_salary_loop(out num int)
begin
  #声明变量
  declare avg_sal double ; #记录员工的平均工资
  declare loop_count int default 0;#记录循环的次数
  select avg(salary) into avg_sal from employees;-- 初始化条件,获取员工的平均工资
  loop_lab:loop
    # 结束循环的条件
    if avg_sal >= 12000
      then leave loop_lab;
    end if;
    # 循环体:如果低于12000，更新员工的工资
    update employees set salary = salary * 1.1;
    # 迭代条件
    select avg(salary) into avg_sal from employees;-- 更新avg_sal变量的值
    set loop_count = loop_count + 1;-- 记录循环次数
  end loop loop_lab;
  set num = loop_count;-- 给num赋值
end;
call update_salary_loop(@num);
select @num;

drop procedure iterate_leave_test;
create procedure iterate_leave_test()
begin
  declare num int default 0;
  loop_label:loop
    #赋值
    set num = num + 1;
    if num  < 10
      then iterate loop_label;-- 满足则跳出循环，重新开始循环
    elseif num > 15
      then leave loop_label;-- 满足则跳出并结束循环
    end if;
    insert into sql_test1.test(id, name, sale) values (num,'test',1);
  end loop;
end;
call iterate_leave_test(); -- 调用

drop procedure if exists generate_pwd;
create procedure generate_pwd(in len int, in cnt int)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz0123456789';
    declare return_str varchar(255) default '';
    truncate table sql_test1.pwd;
    set @j = 1;
    while @j <= cnt do
        set @i = 1;
        set @return_str = '';
        while @i <= len do
            set @return_str = concat(@return_str, substring(chars_str, floor(1 + rand() * 62), 1));
            set @i = @i + 1;
        end while;
        insert into sql_test1.pwd(id, pwd) values (@j, @return_str);
        set @j = @j + 1;
    end while;
    select * from sql_test1.pwd;
end;
call generate_pwd(30,10);
+------+--------------------------------+
| id   | pwd                            |
+------+--------------------------------+
|    1 | u5obsl9zdejker8zoppsfovz1xastz |
|    2 | hwcns3u97zqyr9ho0s85u8orelh3ze |
|    3 | 7uuxsmziu6rsbgmopwy9hr1ng5gtta |
|    4 | lnmnmvjto43vgnwrbszjjverot1vl3 |
|    5 | 2s7nslhysgxkyr8dviwllemnurhjrr |
|    6 | nfif1xzoaexojyjbflbo3habkipknr |
|    7 | xhoyvrzlteieculu9pzazf9osi2s5d |
|    8 | mrmajvvbkfeyzuqcab0jpwke6pawju |
|    9 | b2t4ncwab3ysqcc7zknox6h2vlnnqb |
|   10 | bzzow8gr4dmpgiqlri8l9yb5ibz8pb |
+------+--------------------------------+