赞
踩
DELIMITER//createprocedurecompute_ops()begindeclaretSumintdefault0;--declaredonebooldefaultfalse;--willcontainsthedatafetchedfromcursor;declaretNoVARCHAR(50);declaretName...
DELIMITER //
create procedure compute_ops ()
begin
declare tSum int default 0;
--declare done bool default false;
-- will contains the data fetched from cursor;
declare tNo VARCHAR(50);
declare tName VARCHAR(50);
declare tGender ENUM('male','female');
declare tIsPhysic bool;
declare sNo VARCHAR(50);
declare totalScore int;
declare m1Score int;
declare m2Score int;
declare m3Score int;
declare bonusScore int;
declare o1Name ENUM('H','Phy');
declare o1Score int;
declare o1Rank ENUM('D','C','B','B+','A','A+');
declare o2Name ENUM('Ph','G','C','B');
declare o2Score int;
declare o2Rank ENUM('D','C','B','B+','A','A+') ;
declare n1Name ENUM('Ph','G','C','B');
declare n1Rank ENUM('D','C','B','A');
declare n1Score int;
declare n2Name ENUM('Ph','G','C','B');
declare n2Rank ENUM('D','C','B','A');
declare n2Score int;
declare n3Name ENUM('Ph','G','C','B');
declare n3Rank ENUM('D','C','B','A');
declare n3Score int;
declare n4Name ENUM('Ph','G','C','B');
declare n4Rank ENUM('D','C','B','A');
declare n4Score int;
declare o1_ranking int;
declare o2_ranking int;
declare o1_rate double;
declare o2_rate double;
declare o1R ENUM('D','C','B','B+','A','A+');
declare o2R ENUM('D','C','B','B+','A','A+');
declare additionScore int default 0;
declare is4A bool default true;
-- cursor
declare cur cursor for select i from testee;
-- end sign
declare done bool default false;
-- bind the end sign to cursor
declare continue handler for not found set done = true;
select count(*) from testee INTO tSum;
-- open loop
open cur1;
-- loop begin
read_loop: loop
--get cursor data
fetch cur into tNo,tName,tGender,tIsPhysic,sNo,totalScore,m1Score,m2Score,m3Score,bonusScore,
o1Name,o1Score,o1Rank,o2Name,o2Score,o2Rank,
n1Name,n1Rank,n1Score,n2Name,n2Rank,n2Score,n3Name,n3Rank,n3Score,
n4Name,n4Rank,n4Score;
-- loop finished
if done then
leave read_loop;
end if;
-- loop
select count(*) from testee where opt1_score>o1Score INTO o1_ranking;
select count(*) from testee where opt2_score>o2Score INTO o2_ranking;
update testee set opt1_rank=o1R, opt2_rank=o2R, bonus=additionScore,
total=m1Score+m2Score+m3Score+additionScore
where t_no=tNo;
end loop;
-- close cursor
close cur1;
end//
DELIMITER ;
报得错误为:
展开
赞
踩
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。