MySQL存储过程中游标的使用

距上一次更新已经过去大半年的时间,今天重新回归,更新一个小例子:MySQL存储过程中游标的使用。

关于存储过程及游标的使用有很多种方法,这里写的是很简单的一种方法:
1.查询数据集合
2.循环数据集合并对集合中的每一条数据进行判断及修改操作

话不多说,直接上代码:

循环集合并修改数据

drop procedure if exists test;    #如果存在test存储过程则删除
create procedure test()    #创建无参存储过程,名称为test
begin
    declare done int default 0;
    declare t_id int;
    declare t_status varchar(1);
    declare t_name int;
    / 声明游标 /
    declare rs cursor for SELECT id, test_status, test_name from testTable;
    / 异常处理 /
    declare continue handler for sqlstate '02000' set done = 1;
    / 打开游标 /
    open rs;
    / 逐个取出当前记录 /
    fetch next from rs into t_id, t_status, t_name;
    / 遍历数据表 /
    repeat
        if not done then
            set t_name = t_name + 1;
            if t_name > 2 then
                update testTable set test_status = 'Y', test_name = t_name where id = t_id;
            else
                update testTable set test_status = 'T', test_name = t_name where id = t_id;
            end if;
        end if;
    fetch next from rs into t_id, t_status, t_name;
    until done end repeat;
    / 关闭游标 /
    close rs;
end

call test()

循环集合将数据插入临时表并查询

/ 创建临时表 /
drop table if exists userTemp;
create temporary table if not exists userTemp
(
    id int,
    status varchar(1),
    name int
);
drop procedure if exists test;    #如果存在test存储过程则删除
create procedure test()    #创建无参存储过程,名称为test
begin
    declare done int default 0;
    declare t_id int;
    declare t_status varchar(1);
    declare t_name int;
    / 声明游标 /
    declare rs cursor for SELECT id, test_status, test_name from testTable;
    / 异常处理 /
    declare continue handler for sqlstate '02000' set done = 1;
    / 打开游标 /
    open rs;
    / 逐个取出当前记录 /
    fetch next from rs into t_id, t_status, t_name;
    / 遍历数据表 /
    while done <> 1 do
        if not done then
            insert into userTemp(id, status, name) 
            values(t_id, t_status, t_name);
        end if;
        fetch next from rs into t_id, t_status, t_name;
    end while;
    / 关闭游标 /
    close rs;
    select * from userTemp;
end

call test()
Last modification:May 7th, 2020 at 02:29 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment