Mysql 存储过程之封装查询结果

文章概要

本文主要介绍，在使用存储过程中，如何将单条或多条查询结果，赋值给变量或对其进行遍历操作。

单条查询结果处理

背景

有一张用户表，包含id，name，age，gender四个属性，通过接收id内容，返回该id对应的用户以及年龄

  create table `member` (
    `id` bigint not null auto_increment,
    `name` varchar(30) not null,
    `age` int not null,
    `gender` tinyint(1) default 1 comment '0: female, 1:male',
    primary key(`id`)
  );

存储过程

drop procedure if exists pro_get_member_info;
delimiter $
create procedure pro_get_member_info(in member_id bigint, out member_name varchar(30), out member_age int)
begin 
  declare p_name varchar(30);
  declare p_age int default 18;
  select name, age into p_name, p_age from member where id = member_id;
  set member_name = p_name;
  set member_age = p_age;
end
$
delimiter ;
call pro_get_member_info(1, @name, @age);
select @name, @age;

• declare 使用

  1. 通过declare定义变量，处理器，游标
  2. declare 只能在begin...end复合语句中使用，且必须定义在执行语句之前，即使执行语句未使用到该变量，也需要提前申明。
  3. declare 的申明是有顺序的，变量必须定义在游标与处理器申明之前，游标必须定义在处理器申明之前

  • declare文档

• set 使用

  User variables are written as @var_name， @表示用户变量

  • set 文档

• into 使用

  通过使用SELECT ... INTO var_list语法来将获取的结果存储到定义的变量中

  1. 变量定义的数量必须与查询结果字段数匹配
  2. 只能有一条返回结果

  • select into 文档

多条查询结果处理

背景

有一张大体量用户表，包含id，name，age，gender四个属性，需要按性别进行分表，gender = 0 的数据放入到member_female表中，gender = 1的放入到member_male表中

  create table `member` (
    `id` bigint not null auto_increment,
    `name` varchar(30) not null,
    `age` int not null,
    `gender` tinyint(1) default 1 comment '0: female, 1:male',
    primary key(`id`)
  );

  create table `member_female` (
    `id` bigint not null auto_increment,
    `name` varchar(30) not null,
    `age` int not null,
    primary key(`id`)
  );

  create table `member_male` (
    `id` bigint not null auto_increment,
    `name` varchar(30) not null,
    `age` int not null,
    primary key(`id`)
  );

存储过程

drop procedure if exists pro_refactor_member;
delimiter $
create procedure pro_refactor_member()
begin 
  declare done int default false; -- 标记游标位置
  declare p_name varchar(30);
  declare p_age int;
  declare p_gender tinyint(1);
  declare cur_member cursor for select name, age, gender from member; -- 定义游标
  declare continue handler for not found set done = true; -- 定义处理器
  
  open cur_member;
  read_member: loop
    fetch cur_member into p_name, p_age, p_gender;
    if done then      
       leave read_member;  -- 读取到最后，结束循环
    end if;
    if p_gender = 1 then
        insert into member_male (name, age) values (p_name, p_age);
    else 
        insert into member_female (name, age) values (p_name, p_age);
    end if;
  end loop;
  close cur_member;
end
$
delimiter ;
call pro_refactor_member();

• 游标申明

  通过declare cur_name cursor for select ···申明游标
  通过open cur_name 开启游标
  通过fetch cur_name into variables 设置变量或抛出异常
  通过close cur_name 关闭游标

  • 游标文档
  • fecht 文档
• 通过定义变量done与定义handler来控制循环

  通过fetch去获取查询的下一条结果，如果没有数据，则会触发定义的handler，设置变量为true，结束循环

  • declare-handler文档