if 条件 then sql语句 [elseif 条件 then sql语句] [else sql语句] end if;
示例:
-- create table pass(id int primary key auto_increment,name varchar(15),score int ); create table unpass(id int primary key auto_increment,name varchar(15),score int); -- 使用存储过程来 create procedure myif(in name varchar(15),in score int) begin if score >=60 then insert into pass(name,score) values(name,score); else insert into unpass(name,score) values(name,score); end if; end; -- 调用,并查看结果 call myif("lilei",61); call myif("hanmeimei",95); select * from pass; select * from unpass; call myif("tuhao",59); select * from unpass;
if中的条件基本可以参照select语句的while子句的条件。什么innot in = != 等都可以用。
create procedure myif3(in a char(1)) begin if a in('a','b') then select 1; else select 2; end if; end; call myif3('a'); call myif3('b'); call myif3('c');
循环名:while 条件 do sql语句; leaveiterate 循环名; end while;
示例:
-- 无意义的一个例子,仅作演示 create table whilenum(id int); -- 自带条件的 create procedure mywhile() begin declare num int; set num=10; c1:while num>0 do insert into whilenum values(num); set num=num-1; end while; end; -- 以传入参数作为条件的 create procedure mywhile2(in num int) begin c1:while num>0 do insert into whilenum values(num); set num=num-1; end while; end; -- 带中断的 create procedure mywhile3(in num int) begin c1:while num>0 do if num%2=0 then set num=num-1; iterate c1; end if; insert into whilenum values(num); set num=num-1; end while; end;