1、if 语句：

if 条件表达式1 then 语句块1;[elseif 条件表达式2 then 语句块2;]...[else 语句块n;]end if;

例如：输入角色，学号，查询名字；

delimiter $$create function get_name_fn1(no char(11), role char(10)) returns char(10)begin    declare name char(10);    if role = "student"     then select student_name into name from student where student_no = no;    elseif role = "teacher"    then select teacher_name into name from teacher where teacher_no = no;    else    set name = "输入错误";    end if;    return name;end;$$delimiter ;#--测试：select get_name_fn1("2023002", "student");select get_name_fn1("001","teacher");select get_name_fn1("007","teacher");select get_name_fn1("2023004","stu");

2、case语句：

case 表达式    when value1 then 语句块1;    when value2 then 语句块2;    when value3 then 语句块3;    ...    else 语句块n;end case;

例如，输入日期，查询周几；

delimiter $$create function get_week_fn(d date) returns char(20)begin    declare week char(20);    case weekday(d)      when 0 then set week = "星期一";      when 1 then set week = "星期二";      when 2 then set week = "星期三";      when 3 then set week = "星期四";      when 4 then set week = "星期五";      else set week = "休息";    end case;    return week;end;$$delimiter ;

3、循环语句：

while、repeat、loop；MySQL还提供了iterate语句和leave语句，用于循环的内部控制；

(相关资料图)

[循环标签:]while 条件表达式 do    循环体;end while [循环标签];

例如:实现求和函数，1到n

delimiter $$create function get_sum_fn(n int) returns intbegin    declare sum int;    declare cur int;    set sum = 0;    set cur = 1;    while cur <= n do     set sum = sum + cur;     set cur = cur + 1;    end while;    return sum;end;$$delimiter ;#--测试：select get_sum_fn(10);select get_sum_fn(0);select get_sum_fn(100);

3.1、leave语句：用于提前退出循环语句；相当于break语句；

leave 循环标签;

我们设置此while为死循环，当内部if条件成立就退出；

delimiter $$create function get_sum1_fn(n int) returns intbegin    declare sum int;    declare cur int;    set sum = 0;    set cur = 1;    add_sum: while true do     set sum = sum + cur;     if cur = n then leave add_sum;     end if;     set cur = cur + 1;    end while add_sum;    return sum;end;$$delimiter ;

3.2、iterate语句：用于跳出本次循环继续下次循环；相当于continue语句；

iterate 循环标签;

例如，我们将1~10之间的偶数相加；

delimiter $$create function get_sum2_fn(n int) returns intbegin    declare sum int;    declare cur int;    set sum = 0;    set cur = 0;    add_sum : while cur <= n do     set cur = cur + 1;     if cur%2 = 1 then iterate add_sum;     end if;     set sum = cur + sum;    end while add_sum;    return sum;end;$$

4、repeat语句：

当表达式值为false时，反复执行循环，直到条件表达式为true；

[循环标签:]repeat    循环体：    until 条件表达式end repeat[循环标签];

同样我们实现相加；注意until后的条件表达式没有结束符；

delimiter $$create function get_sum3_fn(n int) returns intbegin    declare sum int default 0;    declare cur int default 0;    repeat     set cur = cur + 1;     set sum = sum + cur;     until cur = n    end repeat;    return sum;end;$$delimiter ;

5、loop语句：

loop循环语句本身没有停止循环的语句，因此loop本身通常借助leave语句跳出loop循环；

[循环标签:] loop    循环体;    if 条件表达式 then        leave 循环标签;    end if;end loop [循环标签];

还是实现相加：

delimiter $$create function get_sum4_fn(n int) returns intbegin    declare sum int default 0;    declare cur int default 0;    add_sum: loop     set cur = cur + 1;     set sum = sum + cur;     if cur = n then leave add_sum;     end if;    end loop add_sum;    return sum;end;$$;delimiter ;

总结：五种控制流程语句，查看创建函数；

show function status like "%\_fn"\G

关键词：