从系表里获得人数,再从学生表里获得该系人数,判断是否一致。
CREATE DEFINER=`root`@`localhost` PROCEDURE `SumOfStu`(OUT Sdeptno int,OUT Sdeptname char(20),OUT sumBefore int,OUT sumAfter int)
BEGIN
declare l_sum int;
declare l_Sdeptno int;
declare l_sno int;
declare l_sumBefore int;
declare done int default false;
declare cur_out cursor for select SdeptNo,population from Department;
declare cur_inner cursor for select Sno from student where SdeptNo=l_Sdeptno;
open cur_out;
out_loop: loop
set l_sum=0;
fetch cur_out into l_Sdeptno,l_sumBefore;
if done=true then leave out_loop;
end if;
open cur_inner;
inner_loop:LOOP
fetch cur_inner into l_sno;
if done =true then
leave inner_loop;
end if;
set l_sum=l_sum+1;
end loop inner_loop;
close cur_inner;
if(l_sum<>l_sumBefore) then
update department set population=l_sum where SdeptNo=l_Sdeptno;
select l_Sdeptno into Sdeptno;
select SdeptName into Sdeptname from Department where SdeptNo=l_Sdeptno;
select l_sumBefore into sumBefore;
select l_sum into sumAfter;
end if;
set done=false;
end loop out_loop;
close cur_out;
END似乎跳出innerloop后l_sum就没有值了,而且大循环也只循环一次,本来使用out输出发现更有问题,好无奈啊
CREATE DEFINER=`root`@`localhost` PROCEDURE `SumOfStu`(OUT Sdeptno int,OUT Sdeptname char(20),OUT sumBefore int,OUT sumAfter int)
BEGIN
declare l_sum int;
declare l_Sdeptno int;
declare l_sno int;
declare l_sumBefore int;
declare done int default false;
declare cur_out cursor for select SdeptNo,population from Department;
declare cur_inner cursor for select Sno from student where SdeptNo=l_Sdeptno;
open cur_out;
out_loop: loop
set l_sum=0;
fetch cur_out into l_Sdeptno,l_sumBefore;
if done=true then leave out_loop;
end if;
open cur_inner;
inner_loop:LOOP
fetch cur_inner into l_sno;
if done =true then
leave inner_loop;
end if;
set l_sum=l_sum+1;
end loop inner_loop;
close cur_inner;
if(l_sum<>l_sumBefore) then
update department set population=l_sum where SdeptNo=l_Sdeptno;
select l_Sdeptno into Sdeptno;
select SdeptName into Sdeptname from Department where SdeptNo=l_Sdeptno;
select l_sumBefore into sumBefore;
select l_sum into sumAfter;
end if;
set done=false;
end loop out_loop;
close cur_out;
END似乎跳出innerloop后l_sum就没有值了,而且大循环也只循环一次,本来使用out输出发现更有问题,好无奈啊
解决方案 »
- MYSQL函数能返回结果集吗?
- 我创建的调度器报语法错误大虾,帮忙看一下!
- mysql 按照指定规则进行排序!
- CMD每隔5分钟判断mysql是否正常,不正常则net stop mysql,再net start mysql,怎么写,谢谢!
- 救急!!断电把数据库给毁坏了,那位有办法帮着给恢复了!!!
- MYSQL双机热备
- 急~~~~~mysql中update语句中如何更新变量,给变量赋值
- 如何把phpMyAdmin里面的mysql数据库放到mssql?
- 有用过MySQL Migration Toolkit的前辈吗
- mysql启动1053错误
- 打开phpMyAdmin出现问题
- 请各路神仙路过给仙人指路一下!谢谢!
CREATE PROCEDURE SumOfStu(OUT Sdeptno int,OUT Sdeptname char(20),OUT sumBefore int,OUT sumAfter int)
BEGIN
DECLARE l_sum int;
DECLARE l_Sdeptno int;
DECLARE l_sno int;
DECLARE l_sumBefore int;
DECLARE done, done2 INT DEFAULT 0;
DECLARE cur_out cursor for select SdeptNo,population from Department;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur_out;
REPEAT
SET l_sum=0;
FETCH cur_out INTO l_Sdeptno,l_sumBefore;
IF NOT done THEN
BEGIN
DECLARE cur_inner cursor for select Sno from student where SdeptNo=l_Sdeptno;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;
OPEN cur_inner;
REPEAT
FETCH cur_inner into l_sno;
IF NOT done2 THEN
SET ps_count = 0;
SET l_sum=l_sum+1;
END IF;
UNTIL done2
END REPEAT;
CLOSE cur_inner;
SET done2 = 0;
END;
IF(l_sum<>l_sumBefore) THEN
update department SET population=l_sum where SdeptNo=l_Sdeptno;
select l_Sdeptno INTO Sdeptno;
select SdeptName INTO Sdeptname from Department where SdeptNo=l_Sdeptno;
select l_sumBefore INTO sumBefore;
select l_sum INTO sumAfter;
end IF;
END IF;
UNTIL done
END REPEAT;
CLOSE cur_out;
END看看这样行不?如果不行 如果方便的话共享一下表结构与表数据 。
亲 不好意思 ,多了一个 “ SET ps_count = 0; ”