声明光标:
declare cursor_name cursor for select_statement
打开光标:
open cursor_name
使用光标:
FETCH cursor_name INTO var_name [, var_name] ...{参数名称}
关闭光标:
CLOSE cursor_name{光标名称}
mysql中光标只能在存储过程或者函数中使用。
if语句:
IF expr_condition THEN statement_list [ELSEIF expr_condition THEN statement_list] ... [ELSE statement_list] END IF
delimiter //
create procedure pro1(in p1 int)
begin
if p1 <= 10 then select 'p1<=10';
else select 'p1>10';
end if;
end//
delimiter ;
call pro1(2);
![](https://i-blog.csdnimg.cn/blog_migrate/199ffa22020a98e9340aec139c90e271.png)
case语句:
drop procedure if exists pro1;
delimiter //
create procedure pro1()
begin
declare name1 varchar(10) default null;
declare price1 float default 0.0;
declare name2 varchar(10) default null;
declare price2 float default 0.0;
declare cur1 cursor for select fruitName,price from fruit;
open cur1;
fetch cur1 into name1,price1;
fetch cur1 into name2,price2;
case name2
when '苹果' then select 'name1 is 苹果';
when '香蕉' then select 'name1 is 香蕉';
else select '都不是';
end case;
end//
delimiter ;
call pro1;
loop语句:
drop procedure if exists pro2;
delimiter //
create procedure pro2()
begin
declare id int default 0;
my_loop:loop
if id < 10 then select id;
else leave my_loop;
end if;
set id=id+1;
end loop my_loop;
end//
delimiter ;
call pro2();
iterate语句:
drop procedure if exists pro1;
delimiter //
create procedure pro1()
begin
declare p1 int default 0;
my_loop:loop
set p1=p1+1;
if p1 < 2 then iterate my_loop;
elseif p1 > 5 then leave my_loop;
end if;
select 'the p1 between 2 and 5';
end loop my_loop;
end//
delimiter ;
call pro1();
相当于其他程序语言中的continue,跳过本次循环后面的语句,直接进入下一次循环。