当前位置：首页 > article >正文

数据库中的逐行数据处理

article 2024/11/17 19:36:39

在数据库开发中，标准的SQL操作通常是面向集合的，意味着我们一次可以处理多条记录。然而，如果你需要逐条处理数据，就需要用到一种特别的工具——游标。游标允许我们在处理多行数据时，一次处理一行，从而解决了SQL语句与应用程序之间的数据处理差异。本文将带你深入了解如何在MySQL中使用游标，轻松掌握声明、打开、提取数据以及关闭游标的基本操作。

什么是游标？为什么需要游标？

在标准SQL操作中，一次操作通常会返回一个包含多条记录的集合。但SQL的变量一次只能存储一条记录，这种“一次一集合”的操作方式与应用程序逐行处理数据的需求并不匹配。为了解决这个问题，引入了游标（Cursor）的概念。

游标是指向内存中缓冲区（上下文区）的指针，指向的记录称为当前记录。通过移动指针，应用程序可以逐行处理查询结果。MySQL 只支持显式游标，且游标只能在存储过程或函数中使用。

游标的四个步骤

使用游标的过程包括四个主要步骤：声明游标、打开游标、提取数据和关闭游标。我们将逐一进行讲解。

1. 声明游标

在SQL块的 DECLARE 部分声明游标，指明游标的名称和对应的 SELECT 语句。声明游标的语法格式如下：

DECLARE 游标名 CURSOR FOR SELECT 语句;

每个游标必须有唯一的名称。
SELECT 语句不能包含 INTO 子句。

2. 打开游标

打开游标就是执行游标对应的 SELECT 语句，将其结果存入缓冲区，并将指针指向缓冲区的首部。打开游标的语法格式如下：

OPEN 游标名;

3. 提取数据

提取数据是指将游标指向的当前记录中的数据存入输出变量中。提取数据的语法格式如下：

FETCH 游标名 INTO 变量列表;

游标刚启动时，指针指向第一条记录。
第一次执行 FETCH 语句时提取第一行数据，并将数据存储到变量列表中。
每次执行 FETCH 语句只能提取一条数据，需要循环语句来遍历整个结果集。

注意： 游标是向前只读的，不能跳过或回退到某条记录。

4. 关闭游标

当提取和处理完游标结果集中的数据后，应及时关闭游标，以释放系统资源。关闭游标的语法格式如下：

CLOSE 游标名;

示例： 关闭 stu_cursor 游标。

CLOSE stu_cursor;

游标的实际应用：两个例子

为了更好地理解游标的使用，我们来看两个实际的例子。

例子1：输出指定学院的所有学生信息

任务： 创建一个存储过程 student_browse，利用游标 stu_cursor 输出指定学院的所有学生的学号和姓名。

DELIMITER $$

CREATE PROCEDURE student_browse(v_dno CHAR(2))
BEGIN
    DECLARE founddata BOOLEAN DEFAULT TRUE;
    DECLARE v_sno CHAR(2);
    DECLARE v_sname VARCHAR(10);
  
    DECLARE stu_cursor CURSOR FOR SELECT sno, sname FROM Student WHERE dno = v_dno;
  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET founddata = FALSE;

    OPEN stu_cursor;
  
    FETCH stu_cursor INTO v_sno, v_sname;
  
    WHILE founddata DO
        SELECT v_sno, v_sname;
        FETCH stu_cursor INTO v_sno, v_sname;
    END WHILE;
  
    CLOSE stu_cursor;
END$$

DELIMITER ;

调用示例：

CALL student_browse('D1');

输出： 该语句会输出 D1 学院的所有学生的学号和姓名。

例子2：更新低于80分的课程学分

任务： 创建一个存储过程 course_update，利用游标 sc_cursor 将平均成绩低于80分的课程的学分减1。

DELIMITER $$

CREATE PROCEDURE course_update()
BEGIN
    DECLARE founddata BOOLEAN DEFAULT TRUE;
    DECLARE v_cno CHAR(2);
  
    DECLARE sc_cursor CURSOR FOR 
    SELECT DISTINCT cno FROM sc GROUP BY cno HAVING AVG(score) < 80;
  
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET founddata = FALSE;

    OPEN sc_cursor;
  
    FETCH sc_cursor INTO v_cno;
  
    WHILE founddata DO
        UPDATE Course SET credit = credit - 1 WHERE cno = v_cno;
        FETCH sc_cursor INTO v_cno;
    END WHILE;
  
    CLOSE sc_cursor;
END$$

DELIMITER ;