The MySQL Cursors

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.

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

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

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

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

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.

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

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

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

The following SQL query creates a cursor on the CUSTOMERS table −

DECLARE MY_CURSOR CURSOR FOR SELECT * FROM CUSTOMERS;

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

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;

The BACKUP table obtained is as follows −

ID	NAME	AGE	ADDRESS	SALARY
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
7	Muffy	24	Indore	10000.00