如何创建和使用SQL游标

  • Z时代
  • 2024-01-10
  • 分类：综合

database

目录

  • 一、游标
  • 二、使用游标
    • 2.1 创建游标
    • 2.2 使用游标
    • 2.3 关闭游标
  • 三、小结

本文介绍什么是 SQL 游标，为什么使用游标，如何使用游标。你使用的 DBMS 可能会提供某种形式的游标，以及这里没有提及的功能。更详细的内容请参阅具体的 DBMS 文档。

一、游标

SQL 检索操作返回一组称为结果集的行，这组返回的行都是与 SQL 语句相匹配的行（零行到多行）。

简单地使用 SELECT 语句，没有办法得到第一行、下一行或前 10 行。但这是关系 DBMS 功能的组成部分。

结果集（result set）

SQL 查询所检索出的结果。

有时，需要在检索出来的行中前进或后退一行或多行，这就是游标的用途所在。

游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。

说明：SQLite 支持

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

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

  • 能够标记游标为只读，使数据能读取，但不能更新和删除。

  • 能控制可以执行的定向操作（向前、向后、第一、最后、绝对位置和相对位置等）。

  • 能标记某些列为可编辑的，某些列为不可编辑的。

  • 规定范围，使游标对创建它的特定请求（如存储过程）或对所有请求可访问。

  • 指示 DBMS 对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化。

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

二、使用游标

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

  • 在使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的 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 指出要检索哪些行，从何处检索它们以及将它们放于何处（如变量名）。

第一个例子使用 Oracle 语法从游标中检索一行（第一行）：

DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
    OPEN CustCursor;
    FETCH CustCursor INTO CustRecord;
    CLOSE CustCursor;
END;

在这个例子中，FETCH 用来检索当前行（自动从第一行开始），放到声明的变量 CustRecord 中。对于检索出来的数据不做任何处理。

下一个例子（也使用 Oracle 语法）中，从第一行到最后一行，对检索出来的数据进行循环：

DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Customers%ROWTYPE;
DECLARE CustRecord Customers%ROWTYPE
BEGIN
    OPEN CustCursor;
    LOOP
    FETCH CustCursor INTO CustRecord;
    EXIT WHEN CustCursor%NOTFOUND;
       ...
    END LOOP;
    CLOSE CustCursor;
END;

与前一个例子一样，这个例子使用 FETCH 检索当前行，放到一个名为 CustRecord 的变量中。

但不一样的是，这里的 FETCH 位于 LOOP 内，因此它反复执行。

代码 EXIT WHEN CustCursor%NOTFOUND 使在取不出更多的行时终止处理（退出循环）。

这个例子也没有做实际的处理，实际例子中可用具体的处理代码替换省略号。

下面是另一个例子，这次使用 Microsoft SQL Server 语法：

DECLARE @cust_id CHAR(10),
        @cust_name CHAR(50),
        @cust_address CHAR(50),
        @cust_city CHAR(50),
        @cust_state CHAR(5),
        @cust_zip CHAR(10),
        @cust_country CHAR(50),
        @cust_contact CHAR(50),
        @cust_email CHAR(255)
OPEN CustCursor
FETCH NEXT FROM CustCursor
    INTO @cust_id, @cust_name, @cust_address,
         @cust_city, @cust_state, @cust_zip,
         @cust_country, @cust_contact, @cust_email
   ...
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CustCursor
        INTO @cust_id, @cust_name, @cust_address,
             @cust_city, @cust_state, @cust_zip,
             @cust_country, @cust_contact, @cust_email
...
END
CLOSE CustCursor

在此例中，为每个检索出的列声明一个变量，FETCH 语句检索一行并保存值到这些变量中。

使用 WHILE 循环处理每一行，条件 WHILE @@FETCH_STATUS = 0 在取不出更多的行时终止处理（退出循环）。

这个例子也不进行具体的处理，实际代码中，应该用具体的处理代码替换其中的“...”。

2.3 关闭游标

如前面几个例子所述，游标在使用完毕时需要关闭。此外，SQL Server 等 DBMS 要求明确释放游标所占用的资源。

下面是 DB2、Oracle 和 PostgreSQL 的语法。

CLOSE CustCursor

下面是 Microsoft SQL Server 的版本。

CLOSE CustCursor
DEALLOCATE CURSOR CustCursor

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

三、小结

本文介绍了什么是游标，为什么使用游标。

你使用的 DBMS 可能会提供某种形式的游标，以及这里没有提及的功能。更详细的内容请参阅具体的 DBMS 文档。

原文链接：https://www.developerastrid.com/sql/sql-cursor/

（完）

以上是 如何创建和使用SQL游标 的全部内容， 来源链接： utcz.com/z/536540.html

回到顶部