/* 存储过程if控制结构 */

delimiter //

create procedure marks_level

(

    in stu_no int(4),

    in cla_no int(4),

    out _level varchar(4)

)

begin

  declare _mark int(4) default 0;

  select mark into _mark from marks where sid=stu_no and cid = cla_no;

  if _mark >= 90 then

    set _level = "A";

  elseif _mark>= 80 then

    set _level = "B";

  elseif _mark >= 70 then

    set _level = "C";

  elseif _mark >= 60 then

    set _level = "D";

  else

    set _level = "E";

  end if;

end;//

set @result = "";//

call marks_level(1,1,@result);//

select @result;//
 

/* 存储过程case控制结构 */
delimiter //
create procedure marks_level_case
(
    in stu_no int(4),
    in cla_no int(4),
    out _level varchar(4)
)
begin
  declare _mark int(4) default 0;
  select mark into _mark from marks where sid=stu_no and cid = cla_no;
  case
    when _mark >= 90 then
        set _level = "A";
    when _mark>= 80 then
        set _level = "B";
    when _mark >= 70 then
        set _level = "C";
    when _mark >= 60 then
        set _level = "D";
    else
        set _level = "E";
  end case;
end;//
set @result = "";//
call marks_level_case(1,1,@result);//
select @result;//

 /* 存储过程while控制结构 */
delimiter //
create procedure sums_while
(
    in n int(4),
    inout sums int(4)
)
begin
    while n>0 do
        set sums = sums + n;
        set n = n-1;
    end while;
end;//
set @result=0;
call sums_while(10,@result);
select @result;

delimiter //
create procedure sums_repeat
(
    in n int(4),
    inout sums int(4)
)
begin
    repeat
        if n>0 then
            set sums = sums + n;
        end if;
        set n = n-1;
    until n<0
    end repeat;
end;//
set @result=0;
call sums_repeat(10,@result);
select @result;

 /* 存储过程游标控制结构 */
delimiter //
create procedure pro_cursor()
begin
    declare stu_no int(11) default 0;
    declare cla_mark int(11) default 0;
    declare tag int(11) default 0;
    declare moneys int(11) default 0;
    declare cur1 cursor  for 
    select s.sid,m.mark from student s left join marks m on s.sid = m.sid;
    declare exit handler for sqlstate "02000" set tag =1;
    open cur1;
    while !tag do
        fetch cur1 into stu_no,cla_mark;
        if cla_mark >= 90 then
            set moneys = 500;
        elseif cla_mark >=80 then
            set moneys = 400;
        elseif cla_mark >=70 then
            set moneys = 300;
        else
            set moneys = 100;
        end if;
        update student set moneys=moneys where sid=stu_no;
    end while;
    close cur1;
end;//