SQL - 游标

---

---

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

SQL 中的游标

游标是在执行表上的数据操纵语言操作（例如 INSERT、UPDATE 和 DELETE 等）时由数据库服务器分配的临时内存。它用于检索和操作存储在 SQL 表中的数据。

在 MySQL 中，不能直接在存储过程或函数外部声明游标。游标通常在 MySQL 数据库中的存储过程、函数或 SQL 代码块中声明。

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

游标的属性

以下是 MySQL 游标的属性：

• 只读 - 我们不能使用 MySQL 游标更新或修改表中的任何记录。我们只能从表中提取和处理数据。

• 不可滚动 - 我们只能单向检索表中的记录，即从第一条记录或最后一条记录开始。我们不能向后移动或跳转到结果集中的特定位置。

• 非敏感游标 - 非敏感游标直接操作数据库中的实际数据，它不会创建数据的副本。如果其他连接对数据进行了任何更改，则可能会影响游标正在处理的数据。

除了非敏感游标之外，还有另一种称为不敏感游标的类型。不敏感游标使用数据的临时副本。因此，这些游标对表中所做的更改不敏感（不受影响）。

游标的生命周期

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

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

声明游标语句

在 MySQL 中，我们可以使用DECLARE语句声明游标，并将其与 SELECT 语句关联以从数据库表中检索记录。

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

语法

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

DECLARE cursor_name CURSOR FOR select_statement;

打开游标语句

在 MySQL 中声明游标后，下一步是使用 OPEN 语句打开游标。它初始化结果集，允许我们从游标中关联的 SELECT 语句提取和处理行。

语法

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

OPEN cursor_name;

提取游标语句

然后，我们可以使用 FETCH 语句检索游标指向的当前行，并且每次 FETCH 时，游标都会移动到结果集中的下一行。这允许我们逐行处理。

语法

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

FETCH cursor_name INTO variable_list;

关闭游标语句

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

语法

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

CLOSE cursor_name;

示例

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

假设我们使用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表中。我们使用游标来迭代行，并使用处理程序来检测结果集的结尾，确保所有名称都被处理：

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();

验证

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

SELECT * FROM CUSTOMERS_BACKUP;

表的内容将是：