Sql 简明教程
SQL - Cursors
数据库光标解决了阻抗不匹配问题。它充当 SQL 查询结果与处理该结果的语句之间的过滤器。
A database cursor solves the problem of impedance mismatch. It acts as a filter between the result of a SQL query and the statements that process this result.
Cursors in SQL
Cursor 是由数据库服务器在对表执行 *D*ata *M*anipulation *L*anguage 操作（如 INSERT、UPDATE 和 DELETE 等）时分配的临时内存。它用来检索和操作存储在 SQL 表中的数据。
A Cursor is a temporary memory that is allocated by the database server at the time of performing the *D*ata *M*anipulation *L*anguage operations on a table, such as INSERT, UPDATE and DELETE etc. It is used to retrieve and manipulate data stored in the SQL table.
使用光标，我们可以对结果集中的每一行执行多个操作，不论是否返回原始数据。
Using cursors, we can perform multiple operations on each row of a result set, with or without returning the original data.
Properties of Cursors
以下是 MySQL 光标的属性 −
Following are the properties of MySQL Cursors −
-
READ ONLY − We cannot update or modify any records in the table using the MySQL cursors. We can just fetch and process data from a table.
-
Non-Scrollable − We can retrieve records from a table in a single direction, i.e. from the first record or the last. We cannot move backward or jump to a specific position within the result set.
-
Asensitive Cursor − An asensitive cursor operates directly on the actual data in the database, it does not create a copy of the data. If any change is made to the data by other connections, it can affect the data that the cursor is working with.
Life Cycle of the Cursor
管理这些光标有四个步骤。下图说明了 SQL 光标的生命周期 −
There are four steps to manage these cursors. Following diagram illustrates the lifecycle of an SQL cursor −
现在，让我们逐一讨论光标生命周期的阶段。
Now, let us discuss the phases of life cycle of the cursor one-by-one.
Declare Cursor Statement
在 MySQL 中，我们可以使用 @{s} 语句声明一个光标并将其与 SELECT 语句关联，以便从数据库表中检索记录。
In MySQL we can declare a cursor using the DECLARE statement and associate it with a SELECT statement to retrieve records from a database table.
但是，与游标关联的此 SELECT 语句并不使用 INTO 子句，因为其目的是获取和处理行，而不是将值分配给变量。
However, this SELECT statement associated with a cursor does not use the INTO clause, as it’s purpose is to fetch and process rows rather than assigning values to variables.
Open Cursor Statement
在 MySQL 中声明一个游标后，下一步需要使用 OPEN 语句打开游标。它会初始化 result-set，使我们能够从游标中关联的 SELECT 语句中提取和处理行。
After declaring a cursor in MySQL, the next step is to open the cursor using the OPEN statement. It initializes the result-set, allowing us to fetch and process rows from the associated SELECT statement in the cursor.
Fetch Cursor Statement
然后，我们可以使用 FETCH 语句来检索游标指向的当前行，并且每次使用 FETCH 时，游标都会移动到 result set 中的下一行。这使我们能够逐个处理各行。
Then, we can use the FETCH statement to retrieve the current row pointed by the cursor, and with each FETCH, the cursor moves to the next row in the result set. This allows us to process each row one by one.
Close Cursor Statement
提取所有行后，我们必须关闭游标以释放与其关联的内存。我们可以使用 CLOSE 语句来执行此操作。
Once all the rows are fetched, we must close the cursor to release the memory associated with it. We can do this using the CLOSE statement.
Syntax
以下是关闭 MySQL 数据库游标的语法：
Following is the syntax to close a cursor in MySQL database −
CLOSE cursor_name;Example
在此示例中，我们了解如何在存储过程中管理游标。
In this example, let us see how to manage a cursor in a stored procedure.
假设我们使用 CREATE TABLE 语句创建了一个名为 CUSTOMERS 的表，如下所示：
Assume we have created a table with the name CUSTOMERS using the CREATE TABLE statement as follows −
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 表中插入一些记录，如下所示：
Now, let us insert some records into the CUSTOMERS table using the INSERT statement as follows −
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”的备份表来存储客户数据：
Now, we will create a backup table named 'CUSTOMERS_BACKUP' to store customer data −
CREATE TABLE CUSTOMERS_BACKUP (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
PRIMARY KEY (ID)
);在此，我们创建了一个名为 FetchCustomers 的存储过程，用于从 CUSTOMERS 表中提取客户姓名，并将它们逐个插入到 BACKUP 表中。我们使用游标来迭代行，并使用处理程序检测 result-set 的结尾，确保处理所有名称：
Here, we are creating a stored procedure named FetchCustomers to fetch customer names from the CUSTOMERS table and inserting them one by one into the BACKUP table. We are using a cursor to iterate through the rows and a handler to detect the end of the result-set, ensuring all names are processed −
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 语句执行它，如下所示：
Once we create the procedure successfully, we can execute it using the CALL statement as shown below −
CALL FetchCustomers();