1、创建一个stu表
create table stu (id int,name varchar(50),class varchar(50));
插入信息
insert into stu values(1,'lucy','class1'),(2,'tom','class1'),(3,'rose','class2');
2、创建一个存储过程
mysql> delimiter //
mysql> create procedure addcount(out count int)
-> begin
-> declare itmp int; *声明变量itmp
-> declare cur_id cursor for select id from stu; *声明光标cur_id
-> select count(*) into count from stu;
-> set @sum=0;
-> open cur_id; *打开光标
-> repeat *创建一个带有条件判断的循环过程
-> fetch cur_id into itmp; *使用光标
-> if itmp<10
-> then set @sum=@sum+itmp;
-> end if;
-> until 0 end repeat;
-> close cur_id; *关闭光标
-> end //
Query OK, 0 rows affected (0.01 sec)
mysql> call addcount(@count) //
如果报错:ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
则在声明光标下面加一行:-> declare continue handler for sqlstate '02000' set p_id=null;
mysql> select @count,@sum //
mysql> delimiter ;