In MySQL, cursors can be created inside a stored program to handle the result set which is returned by a query. MySQL Cursors process a resultset row-by-row. The properties of cursors are that they are asensitive(means, a permanent copy of data as it is created using stored procedure, although, you can remove the store procedure containing the cursor), non-updatable, and non-scrollable.
Also read: Perl MySQL: How to Delete Data from MySQL table using Perl
The syntax for creating cursors:
CREATE PROCEDURE procedureName()
BEGIN
DECLARE cursor1;
DECLARE cursor2;
OPEN cursor1;
OPEN cursor2;
declare_loop: LOOP
condition_definition;
END LOOP;
CLOSE cursor1;
CLOSE cursor2;
END;Code language: PHP (php)Steps to create MySQL Cursors
There are four steps for creating a cursor:
- Declare a Cursor
- Open a Cursor
- Fetch the Cursor
- Close the Cursor
Declare MySQL Cursors
The first step is to declare a cursor. For using a cursor in our stored procedure we first need to declare a cursor. There can be more than one cursor declaration when creating a stored procedure for a cursor.
We use DECLARE keyword to declare or define a cursor.
Firstly, we need to declare a variable name for the cursor so that we could use it to fetch the cursor data. After that, we must declare the cursors and handlers.
Syntax
DECLARE cursorName CURSOR FOR selectStatement;Code language: PHP (php)Open MySQL Cursors
We need to open the cursor in order to use it for fetching the result set.
Syntax
OPEN cursorName;Fetch MySQL Cursors
We need to access the data from the cursor, for this reason, we use the FETCH keyword. We know that the cursor iterates the result set row-by-row, therefore, we generally use the FETCH statement inside a loop.
Syntax
FETCH cursorName INTO variableNames;Close MySQL Cursors
After writing the fetch condition, we close the cursor at the end. CLOSE query ensures that the cursor is deactivated and the memory is released.
Syntax
CLOSE cursorName;Simple Example of Creating MySQL Cursors
Now we’ll be writing a simple SQL code to create a stored procedure for the cursor. In this example, we will create a cursor using an employee table. Where we’ll fetch the first name and last names of all the employees present in the table.
We will incorporate all the steps that we studied in the last step. We first create a procedure which in our code is proc_employee(), BEGIN is the keyword that is used to start the procedure.
The delimiter is $$, it is used when we will call this stored procedure, we will learn about it in the next section. One thing to remember about the delimiter is that we have to close the delimiter at the end of the code using the END keyword.
DELIMITER $$
CREATE PROCEDURE proc_employee()
BEGIN
DECLARE var_name VARCHAR(50);
DECLARE var_lname VARCHAR(50);
DECLARE var_check_row INTEGER DEFAULT 0;
DECLARE c1 CURSOR FOR SELECT fname, lname FROM EMPLOYEE;
DECLARE CONTINUE HANDLER FOR NOT FOUND set var_check_row=1;
OPEN c1;
get_emp : LOOP
FETCH c1 INTO var_name, var_lname;
IF var_check_row=1 THEN
LEAVE get_emp;
END IF;
SELECT CONCAT(var_name, var_lname);
END LOOP get_emp;
CLOSE c1;
END$$Code language: PHP (php)How to call a cursor?
As we have created a cursor and know a cursor is created as a stored procedure, let’s see how to call a cursor. To call the cursor we will use the call procedure query:
CALL proc_employee();When you will press enter you will see -> sign, which means in order to get the output you must give some value to SQL. Now the value SQL is asking for is the delimiter, just as explained in the last section we used $$ as delimiter sing, we will provide $$ as shown in the below example, and you’ll be able to view the cursor.
Conclusion
Cursors are used for fetching data row-wise which enables row-wise validation, although, cursors are time-consuming as they create overhead of result set viewing they are seemingly faster than while statement. Therefore, cursors are not used extensively but might be useful in some cases.