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//

 

MySQL游标

上一篇:SQL Server 2012安装


下一篇:MySql索引优化