Sql 简明教程

SQL - Cursors

数据库光标解决了阻抗不匹配问题。它充当 SQL 查询结果与处理该结果的语句之间的过滤器。

Cursors in SQL

Cursor 是由数据库服务器在对表执行 *D*ata *M*anipulation *L*anguage 操作（如 INSERT、UPDATE 和 DELETE 等）时分配的临时内存。它用来检索和操作存储在 SQL 表中的数据。

使用光标，我们可以对结果集中的每一行执行多个操作，不论是否返回原始数据。

Properties of Cursors

以下是 MySQL 光标的属性 −

  1. READ ONLY − 我们无法使用 MySQL 光标更新或修改表中的任何记录。我们只能从表中提取和处理数据。

  2. Non-Scrollable − 我们只能单向从表中检索记录，即从第一条记录或最后一条记录开始。我们无法向后移动或跳转到结果集中的特定位置。

  3. Asensitive Cursor − 非敏感光标直接对数据库中的实际数据进行操作，它不会创建数据副本。如果其他连接对数据进行了任何更改，它可能会影响光标正在使用的数据。

Life Cycle of the Cursor

管理这些光标有四个步骤。下图说明了 SQL 光标的生命周期 −

cursor lifecycle

现在，让我们逐一讨论光标生命周期的阶段。

Declare Cursor Statement

在 MySQL 中，我们可以使用 @{s} 语句声明一个光标并将其与 SELECT 语句关联，以便从数据库表中检索记录。

但是，与游标关联的此 SELECT 语句并不使用 INTO 子句，因为其目的是获取和处理行，而不是将值分配给变量。

Syntax

以下是 MySQL 数据库中声明游标的语法 −

DECLARE cursor_name CURSOR FOR select_statement;

Open Cursor Statement

在 MySQL 中声明一个游标后，下一步需要使用 OPEN 语句打开游标。它会初始化 result-set，使我们能够从游标中关联的 SELECT 语句中提取和处理行。

Syntax

以下是打开 MySQL 数据库游标的语法：

OPEN cursor_name;

Fetch Cursor Statement

然后，我们可以使用 FETCH 语句来检索游标指向的当前行，并且每次使用 FETCH 时，游标都会移动到 result set 中的下一行。这使我们能够逐个处理各行。

Syntax

以下是取用 MySQL 数据库游标的语法：

FETCH cursor_name INTO variable_list;

Close Cursor Statement

提取所有行后，我们必须关闭游标以释放与其关联的内存。我们可以使用 CLOSE 语句来执行此操作。

Syntax

以下是关闭 MySQL 数据库游标的语法：

CLOSE cursor_name;

Example

在此示例中，我们了解如何在存储过程中管理游标。

假设我们使用 CREATE TABLE 语句创建了一个名为 CUSTOMERS 的表，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

现在，我们使用 INSERT 语句向 CUSTOMERS 表中插入一些记录，如下所示：

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 );

现在，我们创建一个名为“CUSTOMERS_BACKUP”的备份表来存储客户数据：

CREATE TABLE CUSTOMERS_BACKUP (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   PRIMARY KEY (ID)
);

在此，我们创建了一个名为 FetchCustomers 的存储过程，用于从 CUSTOMERS 表中提取客户姓名，并将它们逐个插入到 BACKUP 表中。我们使用游标来迭代行，并使用处理程序检测 result-set 的结尾，确保处理所有名称：

DELIMITER //
CREATE PROCEDURE FetchCustomers()
BEGIN
   DECLARE done INT DEFAULT FALSE;
   DECLARE customer_id INT;
   DECLARE customer_name VARCHAR(255);
   DECLARE auto_id INT;

   -- Declare cursor
   DECLARE MY_CURSOR CURSOR FOR
   SELECT id, name FROM CUSTOMERS;

   -- Declare exit handler
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

   -- Open cursor
   OPEN MY_CURSOR;

   -- Fetch and insert rows
   read_loop: LOOP
      FETCH MY_CURSOR INTO customer_id, customer_name;
      IF done = 1 THEN
         LEAVE read_loop;
      END IF;

      -- Insert the fetched data into the backup table
      INSERT INTO customers_backup VALUES (customer_id, customer_name);

      -- Get the last auto-generated ID used in the insertion
      SET auto_id = LAST_INSERT_ID();

   END LOOP;

    -- Close cursor
    CLOSE MY_CURSOR;
END //
DELIMITER ;

成功创建过程后，我们可以使用 CALL 语句执行它，如下所示：

CALL FetchCustomers();

Verification

你可以使用 SELECT 语句验证 CUSTOMERS_BACKUP 表的内容，如下所示：

SELECT * FROM CUSTOMERS_BACKUP;

表中的内容将是：

ID

NAME

1

Ramesh

2

Khilan

3

Kaushik

4

Chaitali