MySQL游标

1.统计tb_student表中的数据行数；

方法1：//不使用句柄：

create procedure p_sum1(out row int)
begin
declare sno int;
declare cnt int;
declare search boolean default true;
declare c_sum cursor for select studentno from db_school.tb_student;
set row=0;
select count(*) into cnt from db_school.tb_student;
select cnt;
open c_sum;#//创建存储过程
repeat
fetch c_sum into sno;
set row=row+1;
until row=cnt end repeat;
close c_sum;
end
//
call p_sum1(@row)//
select @row//

 

方法2：//使用句柄，用repeat循环：

create procedure p_sum2(out row int)
begin
declare sno int;
declare cnt int;
declare search boolean default true;
declare c_sum cursor for select studentno from db_school.tb_student;
declare continue handler for not found set search=false;
set row=0;
select count(*) into cnt from db_school.tb_student;
select cnt;
open c_sum;
fetch c_sum into sno;
repeat
set row=row+1;
fetch c_sum into sno;
until search=false end repeat;
close c_sum;
end
//
call p_sum2(@row)//
select @row//

 

方法3：//使用句柄，用loop循环，结束整个循环

create procedure p_sum3(out row int)
begin
declare sno int;
declare cnt int;
declare search boolean default true;
declare c_sum cursor for select studentno from db_school.tb_student;
declare continue handler for not found set search=false;
set row=0;
select count(*) into cnt from db_school.tb_student;
select cnt;
open c_sum;
fetch c_sum into sno;
loop_lable:loop 
set row=row+1;
fetch c_sum into sno;
if row=cnt then
leave loop_lable;
end if;
end loop loop_lable;
close c_sum;
end
//
call p_sum3(@row)//
select @row//

 

方法4：//使用句柄，用while循环：

create procedure p_sum4( out row int)
begin
declare sno int;
declare cnt int;
declare search boolean default true;
declare c_sum cursor for select studentno from db_school.tb_student;
declare exit handler for not found set search=false;
select count(*) into cnt from db_school.tb_student;
select cnt;
set row=0;
open c_sum;
fetch c_sum into sno;
while search do
set row=row+1;
fetch c_sum into sno;
end while;
close c_sum;
end
//
call p_sum4(@row)//
select @row//

 

例2：//使用loop循环，结束本次迭代循环和整个循环
把tb_student表中的第1和地3条数据的学号，姓名插入表student1中，并统计tb_student表中的数据的行数；

create procedure p_sum5(out row int)
begin
declare sno int;
declare sname varchar(10);
declare cnt int;
declare search boolean default true;
declare c_sum cursor for select studentno,studentname from db_school.tb_student;
declare continue handler for not found set search=false;
set row=0;
select count(*) into cnt from db_school.tb_student;
select cnt;
open c_sum;
fetch c_sum into sno,sname;
loop_lable:loop 
set row=row+1;

if row=1 then
insert into student1
select sno,sname;
iterate loop_lable;
end if;
fetch c_sum into sno,sname;

if row=3 then
insert into student1
select sno,sname;
iterate loop_lable;
end if;

if row=cnt then
leave loop_lable;
end if;
end loop loop_lable;
close c_sum;
end
//
call p_sum5(@row)//
select * from student1//

create procedure p_sum1(out row int)
begin
declare sno int;
declare cnt int;
declare search boolean default true;
declare c_sum cursor for select studentNo from db_school.tb_student;
declare continue handler for not found set search=false;
select count(*) into cnt from db_school.tb_student;
select cnt;
set row=0;
open c_sum;
fetch c_sum into sno;
repeat
set row=row+1;
fetch c_sum into sno;
until row=cnt end repeat;
close c_sum;
end//