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