SQL使用游标

最新推荐文章于 2025-02-17 09:31:51 发布

Lu01

最新推荐文章于 2025-02-17 09:31:51 发布

阅读量1.8k

点赞数 28

分类专栏： -Core SQL to Master 文章标签： sql oracle 数据库

本文链接：https://blog.csdn.net/weixin_44643352/article/details/144947748

版权

-Core SQL to Master 专栏收录该内容

30 篇文章

订阅专栏

本文将讲授什么是游标，以及如何使用游标。

1. 游标

SQL 检索操作返回一组称为结果集的行，这组返回的行都是与 SQL 语句相匹配的行（零行到多行）。简单地使用 SELECT 语句，没有办法得到第一行、下一行或前 10 行。但这是关系 DBMS 功能的组成部分。

结果集（Result Set）：SQL 查询所检索出的结果。

有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

说明：SQLite 支持

SQLite 支持的游标称为步骤（step），本课讲述的基本概念适用于 SQLite 的步骤，但语法可能完全不同。

不同的 DBMS 支持不同的游标选项和特性。常见的一些选项和特性如下：

能够标记游标为只读，使数据能读取，但不能更新和删除。
能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置和相对位置等）。
能标记某些列为可编辑的，某些列为不可编辑的。
规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。
指示 DBMS 对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

2. 使用游标

使用游标涉及几个明确的步骤：

在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句和游标选项。
一旦声明，就必须打开游标以供使用。这个过程用前面定义的 SELECT 语句把数据实际检索出来。
对于填有数据的游标，根据需要取出（检索）各行。
在结束游标使用时，必须关闭游标，可能的话，释放游标（有赖于具体的 DBMS）。

声明游标后，可根据需要频繁地打开和关闭游标。在游标打开时，可根据需要频繁地执行取操作。

2.1 声明游标

使用 DECLARE 语句创建游标，这条语句在不同的 DBMS 中有所不同。DECLARE 命名游标，并定义相应的 SELECT 语句，根据需要带 WHERE 和其他子句。为了说明，我们创建一个游标来检索没有电子邮件地址的所有顾客，作为应用程序的组成部分，帮助操作人员找出空缺的电子邮件地址。

下面是创建此游标的 DB2、MariaDB、MySQL 和 SQL Server 版本：

DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;

下面是 Oracle 和 PostgreSQL 版本：

DECLARE CURSOR CustCursor
IS
SELECT * FROM Customers
WHERE cust_email IS NULL;

分析：在上面两个版本中，DECLARE 语句用来定义和命名游标，这里为 CustCursor。SELECT 语句定义一个包含没有电子邮件地址（NULL 值）的所有顾客的游标。定义游标之后，就可以打开它了。

2.2 使用游标

使用 OPEN CURSOR 语句打开游标，这条语句很简单，在大多数 DBMS 中的语法相同：

OPEN CURSOR CustCursor

分析：在处理 OPEN CURSOR 语句时，执行查询，存储检索出的数据以供浏览和滚动。现在可以用 FETCH 语句访问游标数据了。FETCH 指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）。

2.3 关闭游标

游标在使用完毕时需要关闭。此外，SQL Server 等 DBMS 要求明确释放游标所占用的资源。下面是 DB2、Oracle 和 PostgreSQL 的语法：

CLOSE CustCursor

下面是 Microsoft SQL Server 的版本：

CLOSE CustCursor
DEALLOCATE CURSOR CustCursor

CLOSE 语句用来关闭游标。一旦游标关闭，如果不再次打开，将不能使用。第二次使用它时不需要再声明，只需用 OPEN 打开它即可。

3. 游标实例

以下是一个简单的示例，我们以 SQL Server 为例，说明如何使用游标。

场景：假设我们有一个员工表 Employees，表中包含 EmployeeID 和 Salary 两列。我们希望逐行处理每个员工的薪资，并给他们加薪 10%。

步骤：

声明游标：首先，你需要声明一个游标，这个游标将关联一个查询。
打开游标：然后打开游标，这样 SQL Server 就会开始执行查询并返回结果集。
获取数据：通过游标逐行读取数据。
关闭游标：处理完所有数据后，关闭游标。
释放游标：最后，释放游标资源。

示例代码：

-- 创建一个示例的员工表
CREATE TABLE Employees (
    EmployeeID INT,
    Salary DECIMAL(10, 2)
);

-- 插入一些数据
INSERT INTO Employees (EmployeeID, Salary)
VALUES (1, 5000.00), (2, 6000.00), (3, 7000.00);

-- 声明变量
DECLARE @EmployeeID INT, @Salary DECIMAL(10, 2);

-- 声明游标
DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, Salary
FROM Employees;

-- 打开游标
OPEN EmployeeCursor;

-- 获取第一行数据
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @Salary;

-- 循环遍历游标
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 这里可以处理数据，例如给每个员工加薪
    UPDATE Employees
    SET Salary = @Salary * 1.1
    WHERE EmployeeID = @EmployeeID;

    -- 获取下一行数据
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @Salary;
END;

-- 关闭游标
CLOSE EmployeeCursor;

-- 释放游标
DEALLOCATE EmployeeCursor;

-- 查看结果
SELECT * FROM Employees;

解释：

1.声明游标：

DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, Salary
FROM Employees;

这里声明了一个游标 EmployeeCursor，它将关联一个查询，查询所有员工的 EmployeeID 和 Salary。

2.打开游标：

OPEN EmployeeCursor;

打开游标开始执行查询。

3.获取数据：

FETCH NEXT FROM EmployeeCursor INTO @EmployeeID, @Salary;

通过 FETCH NEXT 从游标中获取一行数据，并将 EmployeeID 和 Salary 存储到变量中。

4.处理数据：

UPDATE Employees
SET Salary = @Salary * 1.1
WHERE EmployeeID = @EmployeeID;

对每一行数据执行加薪操作。通过更新语句将每个员工的薪水增加 10%。

5.循环和关闭游标：

使用 WHILE @@FETCH_STATUS = 0 来继续处理每一行数据，直到游标中的所有数据都被处理完毕。最后，通过 CLOSE 和 DEALLOCATE 关闭并释放游标。

注意事项：

游标会消耗一定的系统资源，因此应该谨慎使用，特别是在数据量较大的情况下。
在可能的情况下，应该使用 JOIN 或子查询等集合操作来替代游标，因为集合操作通常效率更高。

4. 小结

我们在本文讲解了什么是游标，为什么使用游标。你使用的 DBMS 可能会提供某种形式的游标，以及这里没有提及的功能。更详细的内容请参阅具体的 DBMS 文档。