mysql procedure 游标嵌套 mysql游标实例

 

目录

一、简介

1、概念

2、优点

3、原理

 二、使用游标

1、声明游标

2、游标OPEN语句

3、游标FETCH语句

4、游标CLOSE语句

三、实例

1、实例1

2、实例2

五、补充

---

一、简介

1、概念

 游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

• 使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。
•  游标充当指针的作用。
•    尽管游标能遍历结果中的所有行，但他一次只指向一行。
• 用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。
• 游标机制允许用户逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。

2、优点

允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。

2、提供对基于游标位置的表中的行进行删除和更新的能力。

3、游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。

3、原理

游标就是把数据按照指定要求提取出相应的数据集，然后逐条进行数据处理。

 二、使用游标

1、声明游标

DECLARE cursor_name CURSOR FOR select_statement (table)

这个语句声明一个游标。也可以在子程序中定义多个游标，但是一个块中的每一个游标必须有唯一的名字 

2、游标OPEN语句

-- 打开之前声明的游标
OPEN cursor_name

3、游标FETCH语句

-- 这个语句用指定的打开游标读取下一行，并且前进游标指针
FETCH cursor_name INTO var_name[,var_name]

4、游标CLOSE语句

-- 关闭之前打开的
CLOSE cursor_name

三、实例

1、实例1

1.需求 

实例说明：编写两个表 sys_user和 user, 编写存储过程，当 两个表的 id 相同时 将 user 表中的 name 更新为 sys_user 中的 user_name

2. sys_user 和 user 表的创建 

-- sys_user 表的创建
CREATE TABLE `sys_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

-- user 表的创建
CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3. 数据的对比，编写的存储过程会将 user表中的 name 全部更新 

4. 存储过程的编写

DELIMITER $$
CREATE PROCEDURE user_test()
BEGIN
  -- 定义变量
  DECLARE sys_user_id BIGINT;
  DECLARE sys_user_name VARCHAR(11);
  DECLARE done INT;
  -- 创建游标，并存储数据
  DECLARE cur_test CURSOR FOR 
      SELECT id AS user_id,user_name AS sys_user_name  FROM `sys_user`;
  -- 游标中的内容执行完后将 done 设置为 1 
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  -- 打开游标
  OPEN cur_test;
  -- 执行循环
  posLoop:LOOP
  -- 判断是否结束循环
        IF done=1 THEN
        LEAVE posLoop;
        END IF;
  -- 取游标中的值
  FETCH cur_test INTO sys_user_id,sys_user_name;
  -- 执行更新操作
    UPDATE `user` SET NAME=sys_user_name WHERE id=sys_user_id;
  END LOOP posLoop;
  -- 释放游标 
  CLOSE cur_test; 
END $$
DELIMITER


DROP PROCEDURE user_test;
-- 调用存储过程
CALL user_test;

我们再次对比两张表的数据变化

利用存储过程和游标我们将 user 表的 name 字段进行了 更新。

2、实例2

1.需求：统计 tb_user 表中 香港演员的数量

2.tb_user 表的创建

CREATE TABLE `tb_user` (
  `user_id` int(12) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `gender` varchar(2) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8

 3. tb_user 表中的数据

4.存储过程的创建 

-- 统计 香港的演员有多少个
SELECT  COUNT(*) AS username_cnt FROM mybatisdb.`tb_user` WHERE address='香港';

DELIMITER $$
   CREATE PROCEDURE statisticUser()
   BEGIN
     --  创建接收游标数据的变量
     DECLARE c INT;
     
     -- 创建总数变量
     DECLARE total INT DEFAULT 0;
     
     -- 创建结束标志变量
     DECLARE done INT DEFAULT 0;
     
     -- 创建游标
     DECLARE cur_test CURSOR FOR
                SELECT  COUNT(*) AS username_cnt FROM mybatisdb.`tb_user` WHERE address='香港';
     -- 指定游标循环结束时的返回值
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
     
     -- 设置初始值
     SET total = 0;
     -- 打开游标
     OPEN cur_test;
     -- 开始循环游标里的数据
     read_loop:LOOP
     -- 根据游标当前指向的一条数据
     FETCH cur_test INTO c;
     -- 判断游标的循环是否结束
     IF done THEN
       LEAVE read_loop; -- 跳出游标循环
     END IF;
     -- 获取一条数据时，将 count 值进行累加操作
     SET total = total + c;
     
     -- 结束游标循环
     END LOOP;
     CLOSE cur_test;
     
     -- 输出结果
     SELECT total;
      
   END $$
DELIMITER

DROP PROCEDURE statisticUser;

CALL statisticUser();

5. 结果

五、补充

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

read_loop:LOOP
FETCH cur_test INTO c;
SET total = total + c;
END LOOP;

在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件，指定这个事件发生时修改done变量的值。

所以在循环时加上了下面这句代码：

--判断游标的循环是否结束  
if done then  
    leave read_loop;    --跳出游标循环  
end if;

如果done的值是true，就结束循环。继续执行下面的代码