定义变量与赋值
delimiter //
drop procedure if exists assignmentPro;
create procedure assignmentPro()
begin
declare i int;
declare name VARCHAR(255) DEFAULT "xiaoai";
-- 赋值
set i = 10;
select i;
select @i:=20;
select 30 into i;
select i;
select @j:="jjj";
select @j="jjj"; -- 结果=1
end //
call assignmentPro();
分支
if
格式:
if 条件 then
分支1
else if 条件 then
分支2
else
分支3;
end if;
delimiter //
drop procedure if exists ifPro;
create procedure ifPro(in sexNum int)
begin
if sexNum = 0 then
select "男" as sex;
elseif sexNum = 1 then
select "女" as sex;
else
select "未知" as sex;
end if;
end //
call ifPro(0);
call ifPro(1);
call ifPro(2);
case
格式1:
case 变量|表达式|字段
when 值1 then
分支语句1;
when 值2 then
分支语句2;
else
分支语句3;
end
delimiter //
drop procedure if exists case1Pro;
create procedure case1Pro(in sexNum int)
begin
case sexNum
when 0 then
select "男" as sex;
when 1 then
select "女" as sex;
else
select "未知" as sex;
end case;
end //
call case1Pro(0);
call case1Pro(1);
call case1Pro(2);
格式2:
case
when 【变量|表达式|字段】= 值1 then
分支语句1;
when 【变量|表达式|字段】= 值2 then
分支语句2;
else
分支语句3;
end
delimiter //
drop procedure if exists case2Pro;
create procedure case2Pro(in sexNum int)
begin
case
when sexNum = 0 then
select "男" as sex;
when sexNum = 1 then
select "女" as sex;
else
select "未知" as sex;
end case;
end //
call case2Pro(0);
call case2Pro(1);
call case2Pro(2);
循环
repeat
格式:
repeat
循环操作;
until 结束条件 end repeat;
-- 定义一个带返回参数的存储过程,在存储过程中让返回参数循环从1加到10
delimiter //
drop procedure if exists repeatPro;
create procedure repeatPro(out sum int)
begin
set sum = 0;
repeat
set sum = sum+1;
until sum = 10 end repeat;
select sum;
end //
call repeatPro(@sum);
select @sum; -- 结果:10
while
格式:
while 循环条件 do
循环操作;
end while;
-- 定义一个带返回参数的存储过程,在存储过程中让返回参数循环从1加到10
delimiter //
drop procedure if exists endPro;
create procedure endPro(out sum int)
begin
set sum = 0;
while sum <10 do
set sum = sum+1;
end while;
end //
call endPro(@sum);
select @sum; -- 结果:10
loop
格式:
结束标志 : loop
循环操作;
leave 结束标志;(即:相当于循环条件,使用后会退出循环)
end loop;
-- 定义一个带返回参数的存储过程,在存储过程中让返回参数循环从1加到10
delimiter //
drop procedure if exists loopPro;
create procedure loopPro(out sum int)
begin
set sum = 0;
bk:loop
set sum = sum+1;
if sum = 10 then
leave bk;
end if;
end loop;
end //
call loopPro(@sum);
select @sum; -- 结果:10
loop常与if一起使用