存储过程加游标一起使用的基本操作

-- 存储过程  加  游标

-- 建表
create table student(
sid varchar(8) primary key,
sname varchar(10),
sex varchar(2),
age int,
classno varchar(6)
);
-- 表中的数据
insert into student values
('20170101','张石瑞','男','19','201701'),
('20170201','李佛','女','20','201702'),
('20170202','王法无','男','19','201702')

/* 创建游标和关闭游标的四个步骤
-- 1、创建游标  （cur_name 游标名字）
declare cur_name cursor for select 语句;
-- 2、打开游标
open cur_name;
-- 3、提取游标数据
fetch cur_name [into 变量1,变量2,、、、、、];
-- 4、关闭（释放）游标
close cur_name;
*/
-- 游标的基本提取操作
create PROCEDURE proc1()
BEGIN
declare cur_sid varchar(20);
declare cur_sname varchar(20);
declare cur_sex varchar(20);
declare cur_age varchar(20);
declare cur_classno varchar(20);
-- 1、
declare student_cur1 CURSOR for
select sid,sname,sex,age,classno from student;
-- 2、
open student_cur1;
-- 3、
fetch student_cur1 into cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
select cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
-- 4、
close student_cur1;
END

-- 执行存储过程
call proc1();
-- 删除存储过程
drop procedure if exists proc1



-- 游标的循环遍历‘
create PROCEDURE proc2()
BEGIN
declare cur_sid varchar(20);
declare cur_sname varchar(20);
declare cur_sex varchar(20);
declare cur_age varchar(20);
declare cur_classno varchar(20);

declare sum int default 0;
declare i int default 0;
-- 1、
declare student_cur1 CURSOR for
select sid,sname,sex,age,classno from student;
-- 2、
open student_cur1;
-- 3、 最简单的  while 遍历方法
select count(sid) into sum from student;
while i<sum DO
    fetch student_cur1 into cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
    select cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
    set i=i+1;
end while;
-- 4、
close student_cur1;
END

-- 执行存储过程
call proc2();
-- 删除存储过程
drop procedure if exists proc2


-- 使用 loop 遍历游标
create PROCEDURE proc3()
BEGIN
declare cur_sid varchar(20);
declare cur_sname varchar(20);
declare cur_sex varchar(20);
declare cur_age varchar(20);
declare cur_classno varchar(20);

declare state int default false; -- 定义表示用于判断游标是否溢出
-- 1、
declare student_cur1 CURSOR for
select sid,sname,sex,age,classno from student;
-- 2、
open student_cur1;
-- 3、 loop 遍历游标
cur_loop:loop -- 循环开始
-- 循环开始的时候提取一次
        fetch student_cur1 into cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
        select cur_sid,cur_sname,cur_sex,cur_age,cur_classno;

        if state then
            leave cur_loop; 
        end if; 
end loop; -- 循环结束
-- 4、
close student_cur1;
END

-- 执行存储过程
call proc3()
-- 删除存储过程
drop procedure if exists proc3




/*
fetch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。
使用loop循环游标时，他本身是不会监控是否到最后一条数据了，像下面代码这种写法，就会造成死循环；

read_loop:loop  
fetch cur into n,c;  
set total = total+c;  
end loop;  
在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件，指定这个事件发生时修改done变量的值。

declare continue HANDLER for not found set done = true;  
也有这样写的
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true; 
所以在循环时加上了下面这句代码：

--判断游标的循环是否结束  
if done then  
    leave read_loop;    --跳出游标循环  
end if;  
如果done的值是true，就结束循环。继续执行下面的代码。

*/








-- 性别翻转

create PROCEDURE proc4()
BEGIN
declare cur_sid int;
declare cur_sex varchar(1);

declare state int default false;
-- 1、
declare sex_cur cursor for select sid,sex from student;
-- 
declare CONTINUE HANDLER for not found set state=true;
-- 2、
open sex_cur;
-- 3、
sex_loop:LOOP -- 循环开始
    fetch sex_cur into cur_sid,cur_sex;
        if state then
            leave sex_loop;
        end if;
         if cur_sex='男' then
            update student set sex='女' where sid=cur_sid;
        end if;
         if cur_sex='女' then
            update student set sex='男' where sid=cur_sid;
        end if;
end loop; -- 循环结束
-- 4、
close sex_cur;
END






-- 执行存储过程
call proc4()
-- 删除存储过程
drop procedure if exists proc4





-- 表的查询
select * from student