从系表里获得人数,再从学生表里获得该系人数,判断是否一致。
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语句优化
- postgresql 远程连接的命令?
- 升级mysql4.1升级到5.0
- MySql在运行如下sql自动停止
- 新手问题,mysql用什么命令去执行一个sql脚本文件
- 请高手帮忙:不知有没有谁用过mysql5.0的GROUP BY 子句?有问题急待解决。
- Mysql key Partitioning
- 建立一个表有一个列是由6位字母组成的字符串,并且是唯一的
- Mysql的时间怎么看?
- delphi刚入门,以前都是用自带的paradox,现在发现好像access还要好些,不知mysql或者sql server,oracle是不是更好?请问大家,用什么数据库更好些,如果考虑到过渡到C/S?
- 打开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; ”