mysql中根据自身数据进行判断_在MySQL数据库中使用判断条件

存储过程if控制结构*/delimiter//create procedure marks_level

(

in stu_no int(4),in cla_no int(4),out _level varchar(4)

)

begindeclare _mark int(4) default 0;

select mark into _mark from marks where sid=stu_no and cid =cla_no;if _mark >= 90then

set _level= "A";elseif _mark>= 80then

set _level= "B";elseif _mark >= 70then

set _level= "C";elseif _mark >= 60then

set _level= "D";elseset _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)

)

begindeclare _mark int(4) default 0;

select mark into _mark from marks where sid=stu_no and cid =cla_no;casewhen _mark>= 90then

set _level= "A";

when _mark>= 80then

set _level= "B";

when _mark>= 70then

set _level= "C";

when _mark>= 60then

set _level= "D";elseset _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)

)

beginwhile n>0 doset 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

repeatif n>0then

set sums= sums +n;end if;

set n= n-1;

until n<0

endrepeat;end;//set @result=0;

call sums_repeat(10,@result);

select @result;

/*存储过程游标控制结构*/delimiter//create procedure pro_cursor()

begindeclare 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 forselect 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 dofetch cur1 into stu_no,cla_mark;if cla_mark >= 90then

set moneys= 500;elseif cla_mark >=80then

set moneys= 400;elseif cla_mark >=70then

set moneys= 300;elseset moneys= 100;end if;

update student set moneys=moneys where sid=stu_no;end while;

close cur1;end;//

在MySQL数据库中使用判断条件

标签：cas   end   efault   exit   blog   money   out   handler   l数据库

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/shuo-128/p/6892146.html