MySQL游标的使用

╰半橙微兮° 2021-09-17 16:36 419阅读 0赞

通过MySQL查询数据库，其结果可能为多条记录。在存储过程和函数中使用游标可以实现逐条读取结果集中的记录。游标的使用包括声明游标（DECLARE CURSOR）、打开游标（OPEN CURSOR）、使用游标（FETCH CURSOR）和关闭游标（CLOSE CURSOR）。值得一提的是，MySQL游标只能在存储过程和存储函数中使用。游标必须声明在处理程序之前，且声明在标量和条件之后，即顺序：变量定义-游标定义-处理程序。

### **1、声明游标** ###

在MySQL中，声明游标使用DECLARE关键字，其语法如下：

DECLARE cur_name CURSOR FOR select_statement

**参数说明：**

（1）cur\_name：游标的名称。

（2）select\_statement：是一个SELECT语句，返回一行或多行数据，SELECT子句中不能包含INTO子句。

### **2、打开游标** ###

在声明游标之后，要从游标中提取数据，必须首先打开游标。在MySQL中使用OPEN关键字来打开游标，其语法如下：

OPEN cur_name

### 3、使用游标 ###

游标在顺利打开后，可以使用FETCH...INTO语句来读取数据，其语法如下：

FETCH cur_name INTO var_name[,var_name]...

其中，var\_name是存放数据的标量。

### 4、关闭游标 ###

游标使用完毕后，要及时关闭，在MySQL中使用CLOST关键字来关闭游标，其语法如下：

CLOSE cur_name

**说明：**对于已关闭的游标，在其关闭之后则不能使用FETCH来使用游标。游标在使用完毕后一定要关闭。

**示例：**在存储过程中，使用游标读取用户信息表中的数据。

（1）先创建tb\_user（用户信息表），并添加数据。

-- 创建用户信息表
    CREATE TABLE IF NOT EXISTS tb_user
    (
    	id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
    	name VARCHAR(50) NOT NULL COMMENT '用户姓名'
    ) COMMENT = '用户信息表';
    
    -- 添加数据
    INSERT INTO tb_user(name) VALUES('pan_junbiao的博客');
    INSERT INTO tb_user(name) VALUES('KevinPan');
    INSERT INTO tb_user(name) VALUES('pan_junbiao');
    INSERT INTO tb_user(name) VALUES('阿标');
    INSERT INTO tb_user(name) VALUES('panjunbiao');
    INSERT INTO tb_user(name) VALUES('pan_junbiao的CSDN博客');
    INSERT INTO tb_user(name) VALUES('https://blog.csdn.net/pan_junbiao');

**查询数据结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70][]

（2）创建存储过程，并使用游标读取用户信息表中的数据。

-- 创建存储过程
    DROP PROCEDURE IF EXISTS proc_user;
    CREATE PROCEDURE proc_user()
    BEGIN
    	DECLARE id INT;
    	DECLARE name VARCHAR(50);
    	
    	-- 申明游标
    	DECLARE cur_user CURSOR FOR 
    		SELECT * FROM tb_user;
    
    	-- MySQL游标异常后捕捉并设置循环使用变量id为null跳出循环。
    	-- 注意：这段代码要放在申明游标的后面
    	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET id = NULL; 
    	-- 或者：DECLARE CONTINUE HANDLER FOR NOT FOUND SET id = null;
    
    	-- 打开游标
    	OPEN cur_user;
    
    	-- 使用游标
    	FETCH cur_user INTO id,name;
    
    	-- 使用WHILE循环读取数据
    	WHILE (id IS NOT NULL) DO
    		-- 输出内容
    		SELECT CONCAT('用户编号：',id,' 用户姓名：',name);
    		
    		-- 游标读取下一行记录
    		FETCH cur_user INTO id,name;
    	END WHILE;
    
    	-- 关闭游标
    	CLOSE cur_user;
    END;

（3）调用存储过程。

-- 调用存储过程
    CALL proc_user;

**解释说明：**

DECLARE CONTINUE HANDLER FOR NOT FOUND 语句：

在mysql的存储过程中经常会看到这句话：DECLARE CONTINUE HANDLER FOR NOT FOUND。

它的含义是：若没有数据返回,程序继续，并将变量 IS\_FOUND 设为0 ，这种情况是出现在 select XX into XXX from tablename的时候发生的。

**执行结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 1][]

**示例：**使用LOOP循环语句，读取游标数据。

-- 创建存储过程
    DROP PROCEDURE proc_user;
    CREATE PROCEDURE proc_user()
    BEGIN
    	DECLARE id INT;
    	DECLARE name VARCHAR(50);
    	
    	-- 申明游标
    	DECLARE cur_user CURSOR FOR 
    		SELECT * FROM tb_user;
    
    	-- MySQL游标异常后捕捉并设置循环使用变量id为null跳出循环。
    	-- 注意：这段代码要放在申明游标的后面
    	-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET id = NULL; 
    	DECLARE CONTINUE HANDLER FOR NOT FOUND SET id = null;
    
    	-- 打开游标
    	OPEN cur_user;
    
    	-- 使用LOOP循环读取数据
    	loop_label:LOOP
    		
    		-- 使用游标（读取下一行记录）
    		FETCH cur_user INTO id,name;
    		
    		IF id IS NOT NULL THEN
    			-- 输出内容
    			SELECT CONCAT('用户编号：',id,' 用户姓名：',name);
    		ELSE 
    			-- 退出LOOP循环
    			LEAVE loop_label;  
    		END IF;
    
    	END LOOP;		
    
    	-- 关闭游标
    	CLOSE cur_user;
    END;

**执行结果：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 2][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70]: /images/20210724/688194de359e4867913215f7162f9af0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 1]: /images/20210724/629c8e79316e4f9ab17847cfd73d65aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bhbl9qdW5iaWFv_size_16_color_FFFFFF_t_70 2]: /images/20210724/16998a8438ef49db96a1a0007821d475.png