delimiter $$

create function fn_liangzifunction() returns int

no sql

begin

    set @row_no = @row_no + 1;

    return @row_no;

end;

$$

delimiter ;

delimiter $$

create function fn_get_name(student_num int) returns char(50)

reads sql data

begin

    declare student_names char(50);

    select student_name into student_names from student where student_no = student_num;

    return student_names;

    end;

$$

delimiter ;

#将查询结果赋予变量。

delimiter $$

create function fn_get_choose_count(student_no1 int) returns int(50)

reads sql data

begin

    declare choose_num int(50);

    select count(*) into choose_num from choose where student_no = student_no1;

    return choose_num;

end;

$$

delimiter ;

delimiter $$

create function fn_get_name(no int,role char(50)) returns char(50)

reads sql data

begin

    declare name char(50);

    if('student'=role) then

        select student_name into name from student where student_no=no;

    elseif('teacher'=role) then

        select teacher_name into name from teacher where teacher_no=no;

    else set name = '输入有误';

    end if;

    return name;

end;

$$

delimiter ;

#if else

delimiter  $$

create function fn_get_name(no int,role char(20)) returns char(20)

reads sql data

begin

    declare name char(20);

    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 ;

#switch case

delimiter $$

create function fn_get_week(week_no int) returns char(20)

no sql

begin

    declare week char(20);

    case week_no

        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 ;

#根据现在的时间判断问候语

delimiter $$

create function fn_get_hello(time int) returns char(20)

no sql

begin

    declare hello char(20);

    if(time < 9) then

        set hello = '早上好';

    elseif( 9 < time and time <= 12) then

        set hello = '上午好';

    elseif( 12 < time and time <= 18) then

        set hello = '下午好';

    elseif(18 < time and time < 22)   then

        set hello = '晚上好';

    else

        set hello = '晚安';

    end if;

    return hello;

end;

$$

delimiter ;

#练习switch case 语句

delimiter $$

create function fn_get_myweek(day int) returns char(50)

no sql

begin

    declare weekname char(50);

    case day

        when 1 then set weekname = '星期一';

        when 2 then set weekname = '星期2';

        when 3 then set weekname = '星期3';

        when 4 then set weekname = '星期4';

        when 5 then set weekname = '星期5';

        else set weekname = '星期日';

    end case;

    return weekname;

end;

$$

delimiter ;

#while语句

#和其他编程一样，当条件表达式值为true时，就一直执行循环体，直到条件表达式为false。

delimiter $$

create function fn_get_mysum(num int) returns int

no sql

begin

    declare sum int default 0;

    declare start int default 0;

    while start < num do

        set start = start + 1;

        set sum   = sum   + start;

    end while;

    return sum;

end;

$$

delimiter ; 

#while中的内部控制语句

#leave   相当于php循环中的break了。

# 至于下面的add_num,相当于给while的循环起一个名字。

delimiter $$

create function fn_get_sum1(n int) returns int

no sql

begin

    declare sum   int  default 0;

    declare start int  default 0;

    add_num : while true do

        set start = start + 1;

        set sum   = sum   + start;

        if(start = n) then

          leave add_num;

        end if;

    end while add_num;

    return sum;

end;

$$

delimiter;