MySQL - Cursor OPEN Statement

A cursor in database is a construct which allows you to iterate/traversal the records of a table. In MySQL you can use cursors with in a stored program such as procedures, functions etc.

In other words, you can iterate though the records of a table from a MySQL stored program using the cursors. The cursors provided by MySQL are embedded cursors. They are −

  • READ ONLY − Using these cursors you cannot update any table.

  • Non-Scrollable − Using these cursors you can retrieve records from a table in one direction i.e., from top to bottom.

  • Asensitive − These cursors are insensitive to the changes that are made in the table i.e. the modifications done in the table are not reflected in the cursor.

    Which means if we have created a cursor holding all the records in a table and, meanwhile if we add some more records to the table, these recent changes will not be reflected in the cursor we previously obtained.

While Declaring cursors in a stored program you need to make sure these (cursor declarations) always follow the variable and condition declarations.

To use a cursor, you need to follow the steps given below (in the same order)

  • Declare the cursor using the DECLARE Statement.
  • Declare variables and conditions.
  • Open the declared cursor using the OPEN Statement.
  • Retrieve the desired records from a table using the FETCH Statement.
  • Finally close the cursor using the CLOSEstatement.
CLOSE

The Cursor OPEN Statement

This statement is used open the cursor declared earlier. The OPEN statement initializes the result set for the cursor hence we must have to call OPEN statement before fetching rows from the result set.

Syntax

Following is the syntax of the MySQL Cursor OPEN Statement −

OPEN cursor_name;

Example

Assume we have created a table named dispatches as shown below −

CREATE TABLE Dispatches( Product_Name VARCHAR(255), 
Name_Of_Customer VARCHAR(255), Month_Of_Dispatch VARCHAR(255), 
Price INT, Location VARCHAR(255));

Let us insert 5 records in it using the INSERT statement.

Insert into dispatches values
('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 
'Hyderabad'),
('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 
'Vishakhapatnam'),
('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 
'Vijayawada'),
('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 
'Chennai'),
('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 
'Goa');

If you verify the contents of the above created table, you can observe the inserted records as −

SELECT * FROM Dispatches;

Output

The above mysql query generates the following output −

Product_Name Name_Of_Customer Month_Of_Dispatch Price Location
Key-Board Raja 2019-05-04 15:02:45 7000 Hyderabad
Earphones Roja 2019-06-26 14:13:12 2000 Vishakhapatnam
Mouse Puja 2019-12-07 07:50:37 3000 Vijayawada
Mobile Vanaja 2018-03-21 16:00:45 9000 Chennai
Headset Jalaja 2018-12-30 10:49:27 6000 Goa

Following query creates a procedure that retrieves the Product_Name and location. In here we are using two OUT parameters to store these values −

DELIMITER //
Create procedure cursorExample (INOUT list VARCHAR(5000))
   BEGIN
      DECLARE val INT DEFAULT FALSE;
      DECLARE prodName VARCHAR(150) DEFAULT "";
      DECLARE cur CURSOR FOR SELECT Product_Name FROM Dispatches;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET val = TRUE;
      OPEN cur;
         REPEAT
            FETCH cur INTO prodName;
            SET list = CONCAT(prodName, ", ", list);
            UNTIL val = 1
         END REPEAT;
      CLOSE cur;
      END //
DELIMITER ;

You need to call this procedure by passing a variable and you can retrieve values from it using SELECT statement.

SET @products="";
CALL cursorExample(@products);
SELECT @products;

Output

Following is the output of the above query −

@products
Headset, Headset, Mobile, Mouse, Earphones, Key-Board,
Advertisements