mysql之子程序的分支CASE

1.CASE分支 

子程序的case分支不同于select语句中的case分支

delimiter $$

create procedure test_case(my_id int)
begin
    declare my_name varchar(255) default '';
    select name into my_name from user where id = my_id;

    case my_name
        when '中' then update user set name='中华人民' where id = my_id;
        when '国' then update user set name='共和国' where id = my_id;
        else update user set name='加油！中国' where id = my_id;
        end case;
end;

$$
delimiter ;

2.流程控制的查询条件应用

 

场景：循环更新

delimiter $$
create procedure testwhile()
begin
    decalre my_id int default 0;
    while(select (1) from user where number =0)>0 do
    begin
        select id into my_id from user where number = 0 limit 1;
        update user set number =0 where id = my_id;
    end;
    end while;
end;
$$
delimiter ;

任何流程控制的条件中，可以是复杂的sql查询混合表达式