来源:网易云课堂《精通MySQL存储过程、函数和触发器》课程
讲师:huangxifeng607(黄锡峰)
笔记:
(1)case分支
子程序中的case不同于select语句中的case分支
create procedure testcase(my_uid int)
begin
declare my_lev int default 0;
select level into my_lev from user where uid=my_uid;
case my_lev
when 1 then update user set level=10 where uid=my_uid; #执行sql语句
when 2 then update user set level=20 where uid=my_uid;
when 3 then update user set level=30 where uid=my_uid;
else update user set level=40 where uid=my_uid;
end case;
end;
(2)流程控制的查询条件应用
任何流程控制的条件中,可以是复杂的sql查询混合表达式
if (select salary from 'user' where uid=1)<3000 then
update 'user' set salary=salary+1000 where uid=1;
end if;
begin
declare my_uid int default 0;
while (select count(*) from 'user' where isadd=1)>0 do
begin
select uid into my_uid from 'user' where isadd=1 limit 1;
update 'user' set isadd=0 where uid=my_uid;
end;
end while;
end;
(3)小结
1.case分支不同于select的case分支
2.流程控制中的条件可能是复杂的查询sql