Mysql 简明教程
MySQL - Cursors
The MySQL Cursors
MySQL cursor 是用于遍历表记录的指针。它们用于存储程序中,例如过程和函数,并具有以下特性:
A MySQL cursor is a pointer that is used to iterate through a table’s records. They are used within stored programs such as procedures and functions and have the following features −
-
READ ONLY − Cursors only allow you to read data; you can’t make changes to it.
-
Non-Scrollable − Cursors move through records in one direction, from the top to the bottom.
-
Asensitive − Cursors are sensitive to the changes made in the table. Any modification done in the table will be reflected in the cursor.
以下四个操作用于管理 MySQL 中的游标:
The following four operations are used to manage cursors in MySQL:
-
Declare Cursor
-
Open Cursor
-
Fetch Cursor
-
Close Cursor
现在让我们详细了解每个操作。
Let us now see each of these operations in detail.
Declare Cursor
DECLARE 语句用于在 MySQL 中声明游标。声明后,它会与 SELECT 语句关联以从表中检索记录。
The DECLARE statement is used to declare a cursor in a MySQL. Once declared, it is then associated with a SELECT statement to retrieve the records from a table.
以下是声明游标的语法:
Following is the syntax to declare a cursor −
DECLARE cursor_name CURSOR FOR select_statement;
Open Cursor
OPEN 语句用于在声明后初始化游标以检索数据。
The OPEN statement is used to initialize the cursor to retrieve the data after it has been declared.
以下是打开游标的语法:
Following is the syntax to open a cursor −
OPEN cursor_name;
Fetch Cursor
然后使用 FETCH 语句检索由游标指向的记录。检索后,游标将移至下一条记录。
The FETCH statement is then used to retrieve the record pointed by the cursor. Once retrieved, the cursor moves to the next record.
以下是获取游标的语法:
Following is the syntax to fetch a cursor −
FETCH cursor_name INTO variable_list;
Close Cursor
CLOSE 语句用于在检索所有记录后释放与游标关联的内存。
The CLOSE statement is used to release the memory associated with the cursor after all the records have been retrieved.
以下是关闭游标的语法:
Following is the syntax to close a cursor −
CLOSE cursor_name;
Example
在此示例中,我们了解如何在存储过程中管理光标。
In this example, we see how to manage a cursor in a stored procedure.
假设使用 CREATE TABLE 语句创建了两个表 CUSTOMERS 和 BACKUP。CUSTOMERS 表保存以下信息,如 ID、姓名、年龄、地址和薪水 −
Assume two tables, CUSTOMERS and BACKUP, are created using the CREATE TABLE statement. The CUSTOMERS table holds information like ID, name, age, address, and salary as shown below −
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)
);
现在,正在创建 BACKUP 表,它具有与 CUSTOMERS 表相同的结构,以存储 CUSTOMERS 表中记录的副本 −
Now, we are creating the BACKUP table, having the same structure as the CUSTOMERS table to store a copy of the records from the CUSTOMERS table −
CREATE TABLE BACKUP (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2)
);
现在,让我们使用 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 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
以下 SQL 查询在 CUSTOMERS 表上创建光标 −
The following SQL query creates a cursor on the CUSTOMERS table −
DECLARE MY_CURSOR CURSOR FOR SELECT * FROM CUSTOMERS;
现在,创建一个名为 ExampleProc 的存储过程来管理从声明到关闭的光标 −
Now, we are creating a stored procedure named ExampleProc to manage the cursor from declaration to closure −
DELIMITER //
CREATE PROCEDURE ExampleProc()
BEGIN
-- Variable declarations
DECLARE done INT DEFAULT 0;
DECLARE cust_id, cust_age INTEGER;
DECLARE cust_name VARCHAR(20);
DECLARE cust_address CHAR(25);
DECLARE cust_salary DECIMAL(18,2);
-- Cursor declaration
DECLARE cur CURSOR FOR SELECT * FROM CUSTOMERS;
-- Handler for no more records
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- Open the cursor
OPEN cur;
-- Loop to fetch and insert records
label: LOOP
FETCH cur INTO cust_id, cust_name, cust_age, cust_address, cust_salary;
INSERT INTO backup VALUES(cust_id, cust_name, cust_age, cust_address, cust_salary);
IF done = 1 THEN LEAVE label;
END IF;
END LOOP;
-- Close the cursor
CLOSE cur;
END//
DELIMITER ;
Output
最后,如果我们使用 CALL ExampleProc(); 调用过程并检查 BACKUP 表的内容,它将包含与 CUSTOMERS 相同的记录 −
Finally, if we call the procedure using CALL ExampleProc(); and check the contents of the BACKUP table, it will contain the same records as CUSTOMERS −
SELECT * FROM BACKUP;
获得的 BACKUP 表如下 −
The BACKUP table obtained is as follows −