MySQL8.0存储过程流程控制。代码为主，文字为辅，自学用，谨慎借鉴，有错误请指正

目录

分支语句

单分支语句

双分支语句

if条件执行

case条件执行

循环语句

while循环

repeat循环

loop循环

---

分支语句

单分支语句

如果条件成立则执行

if 条件 then

        SQL

end if;

-- 如果条件为1，则向classes添加一条记录
create procedure test04(in a int)
begin
	if a = 1 then
		insert into classes(class_id,class_name,class_remark)
		values(5,'vue01','eee');
	end if;
end;

call test04(2);

双分支语句

if条件执行

如果条件成立则执行SQL1，否则执行SQL2

if 条件 then

        SQL1

else

        SQL2

end if;

-- 如果条件为1，则向classes添加一条记录，如果条件是2，则向students中添加数据
create procedure test04(in a int)
begin
	if a = 1 then
		insert into classes(class_id,class_name,class_remark)
		values(5,'vue01','eee');
	else
		insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
		values(10,'王鑫龙','男',21,4);
	end if;
end;

call test04(2);

case条件执行

case

        when 条件1 then

                sql1

        when 条件2 then

                sql2

......

        else

                sql n（如果条件都不满足，则执行此SQL）

end case;

-- 如果条件为1，则向classes添加一条记录，如果条件是2，则向students中添加数据
create procedure test02(in a int)
begin
	case
	when a = 1 then
		insert into classes(class_name,class_remark)
		values('vue02','fff');
	when a = 2 then
		insert into students(stu_num,stu_name,stu_gender,stu_age,cid)
		values(12140211,'张国芳','男',21,4);
	else
		update students set stu_name = '李亮' where stu_num = 12140211;
	end case;
end;

call test02(1);

循环语句

while循环

while 条件 do

        sql

end while;

举例：

create procedure test05(in num int)
begin
	declare i int;
	set i = 0;
	while i < num do
		case
			when i mod 1 = 1 then
				insert into classes(class_name,class_remark) values(concat('java',i),'....');
			when i mod 2 = 1 then
				insert into classes(class_name,class_remark) values(concat('vue',i),'....');
			when i mod 3 = 1  then
				insert into classes(class_name,class_remark) values(concat('python',i),'....');
			else
				insert into classes(class_name,class_remark) values(concat('html',i),'....');
			end case;
			set i = i + 1;
		end while;
end;

call test05(3);

repeat循环

语法

repeat

        sql

until 条件 end repeat;

create procedure test06(in num int)
begin
	declare i int;
	set i = 0;
	repeat
	case 
		when i mod 2 = 1 then
			insert into classes(class_id,class_name,class_remark) values(i,concat('java',i),'....');
		when i mod 3 = 1 then
			insert into classes(class_id,class_name,class_remark) values(i,concat('c++',i),'....');
		when i mod 4 = 1 then
			insert into classes(class_id,class_name,class_remark) values(i,concat('web',i),'....');
		when i mod 5 = 1 then
			insert into classes(class_id,class_name,class_remark) values(i,concat('python',i),'....');
		else
			insert into classes(class_id,class_name,class_remark) values(i,concat('springboot',i),'....');
	end case;
	set i = i + 1;
	until i = num end repeat;
end;

loop循环

语法

循环名:loop

        sql

                if 条件 then

                        leave 循环名;

                end if;

end loop;

create procedure test07(in num int)
begin
	declare i int;
	set i = 1;
	myloop:loop
		case
			when i mod 1 = 1 then
				insert into classes(class_name,class_remark) values(concat('java',i),'....');
			when i mod 2 = 1 then
				insert into classes(class_name,class_remark) values(concat('vue',i),'....');
			when i mod 3 = 1  then
				insert into classes(class_name,class_remark) values(concat('python',i),'....');
			else
				insert into classes(class_name,class_remark) values(concat('html',i),'....');
			end case;
			set i = i + 1;
		if i = num then
			leave myloop;
		end if;
	end loop;
end;

call test07(4);