mysql 存储过程 集合 mysql存储过程查询集合

1，简介
游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。可以理解为查询的结果集，就是一个集合。

2，语法
游标的使用包括：声明、开启（open）、获取（fetch）和关闭（close）
①游标声明：
declare 游标名称 cursor for 查询语句; ②游标开启：
open 游标名称; ③获取游标记录
fetch 游标名称 into 变量[,变量]; ④游标关闭
close 游标名称;

3，练习
根据传入的id，来查询t_input表中id小于50的姓名和手机号，并插入一张新表(id,name,phone)中

游标的声明必须在变量之后

sql

create procedure p9(in tid int)
begin
    declare n_name varchar(20) default null;
    declare n_phone varchar(11) default null;

    # A 声明游标，存储查询结果集
    declare c_user cursor for select name,phone from t_input where id < tid;

    # B 准备：创建表结构
    drop table if exists `new_user`;
    create table if not exists `new_user`(
        `id` int not null auto_increment,
        `name` varchar(20) ,
        `phone` varchar(11) ,
       primary key(`id`)
    )engine=INNODB DEFAULT CHARSET=utf8mb4;

    # C 开启游标
    open c_user;



    while true do
        # D 获取游标中的记录
        fetch c_user into n_name ,n_phone ;

        # E 插入数据到新表中
        insert into new_user values (null,n_name ,n_phone );
    end while;

    # F 关闭游标
    close c_user;
end;

drop procedure p9;

call p9(50);

【发现问题】

此时发现，在调用存储过程的时候报错，但现象是表new_user已经创建，并且里面还有一些数据，说明存储过程是执行了的，那为什么还会报错呢？

【原因】

原因在于，while true ，一直没有退出循环，在循环的过程中，每循环一次，执行 fetch c_user into n_name,n_phone;从游标c_user当中去获取它的下一条记录并赋值给n_name和n_phone。当循环到最后一条之后，再进入下一次循环，此时游标当中已经没有数据，就会报错[02000][1329]No data...

while true do
        # D 获取游标中的记录
        fetch c_user into n_name,n_phone;
        # E 插入数据到新表中
        insert into new_user values (null,name,phone);
    end while;

【解决思路】
使用条件处理程序做控制，可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤，在本例子中一旦发现游标中没有数据了，就要退出循环，关闭游标。

4，条件处理程序（简介、语法）

【实现方式】

声明一个exit类型的条件处理程序，当满足状态码为[02000]的时候，执行关闭游标的sql语句。（当然，在本例子中不限于使用SQLSTATE、也可以使用not found 或者 sqlexception）

sql

create procedure p9(in tid int)
begin
    declare n_name varchar(20) default null;
    declare n_phone varchar(11) default null;

    # A 声明游标，存储查询结果集
    declare c_user cursor for select name,phone from t_input where id < tid;

    # 使用条件处理程序，控制在while循环时从游标拿完数据在循环体未结束再次循环并从游标中获取数据时退出，并关闭游标
    declare exit handler for SQLSTATE '02000' close c_user;

    # B 准备：创建表结构
    drop table if exists `new_user`;
    create table if not exists `new_user`(
        `id` int not null auto_increment,
        `name` varchar(20) ,
        `phone` varchar(11) ,
       primary key(`id`)
    )engine=INNODB DEFAULT CHARSET=utf8mb4;

    # C 开启游标
    open c_user;



    while true do
        # D 获取游标中的记录
        fetch c_user into n_name,n_phone;
        # E 插入数据到新表中
        insert into new_user values (null,n_name,n_phone);
    end while;

    # F 关闭游标
    close c_user;
end;

drop procedure p9;

call p9(50);

附录：sql状态码参考
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html