【基础】游标的使用

前言

Github：https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql

概述

在函数和存储过程中，可以使用游标对结果集进行循环处理。游标使用包括，游标声明，OPEN，FETCH，CLOSE。

1 游标使用语法

# 声明游标

DECLARE cursor_name CURSOR FOR select_statement

# 打开游标

OPEN cursor_name;

# 抓取游标

FETCH cursor_name INTO var_name [, var_name] ...;

# 关闭游标

CLOSE cursor_name;

2、实例准备条件

创建表：

CREATE TABLE `t_user_main` (
  `f_userId` int(10) NOT NULL AUTO_INCREMENT COMMENT '用户id,作为主键',
  `f_userName` varchar(5) DEFAULT NULL COMMENT '用户名',
  `f_age` int(3) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`f_userId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO t_user_main (f_userName, f_age) 
VALUES('one',24),('two',25),('three',26),('four',27),('five',28),('six',29);

3、loop、while、repeat三种流程控制方式循环游标

1）LOOP实现

DELIMITER $$
CREATE FUNCTION user_main_fn1(v_id INT)
RETURNS INT(11)
BEGIN
  #定义变量
  DECLARE v_age INT(11);
  DECLARE v_userId INT(11);
  DECLARE v_count INT(11) DEFAULT 0;
  #声明游标
  DECLARE cursor_user_main CURSOR FOR 
  SELECT f_userId,f_age FROM t_user_main 
  WHERE f_userId > v_id ORDER BY f_userId;
  #打开游标
  OPEN cursor_user_main;
  #抓取游标
  userMainLoop:LOOP
     FETCH cursor_user_main INTO v_userId, v_age;
     IF v_userId < 5 THEN
        SET v_count = v_count + v_age;
     ELSEIF v_userId = 6 THEN
        LEAVE userMainLoop;
     END IF;
  END LOOP userMainLoop;
  #关闭游标
  CLOSE cursor_user_main;
  #函数返回值
  RETURN v_count;
END $$
DELIMITER;

调用：

2）WHILE 实现

DELIMITER $$
CREATE FUNCTION user_main_fn2(v_id INT)
RETURNS INT(11)
BEGIN
  #定义变量
  DECLARE v_age INT(11);
  DECLARE v_userId INT(11);
  DECLARE v_count INT(11) DEFAULT 0;
  #声明游标
  DECLARE cursor_user_main CURSOR FOR 
  SELECT f_userId,f_age FROM t_user_main 
  WHERE f_userId > v_id ORDER BY f_userId;
  #打开游标
  OPEN cursor_user_main;
  #抓取游标
  FETCH cursor_user_main INTO v_userId, v_age;
  WHILE (v_userId < 5) DO
     SET v_count = v_count + v_age;
     FETCH cursor_user_main INTO v_userId, v_age;
  END WHILE;
  #关闭游标
  CLOSE cursor_user_main;
  #函数返回值
  RETURN v_count;
END $$
DELIMITER;

调用：

3）REPEAT 实现

DELIMITER $$
CREATE FUNCTION user_main_fn3(v_id INT)
RETURNS INT(11)
BEGIN
  #定义变量
  DECLARE v_age INT(11);
  DECLARE v_userId INT(11);
  DECLARE v_count INT(11) DEFAULT 0;
  #声明游标
  DECLARE cursor_user_main CURSOR FOR 
  SELECT f_userId,f_age FROM t_user_main 
  WHERE f_userId > v_id ORDER BY f_userId;
  #打开游标
  OPEN cursor_user_main;
  #抓取游标
  REPEAT
    FETCH cursor_user_main INTO v_userId, v_age;
    SET v_count = v_count + v_age;
  UNTIL v_userId > 5 END REPEAT;
  #关闭游标
  CLOSE cursor_user_main;
  #函数返回值
  RETURN v_count;
END $$
DELIMITER;

调用：