An Introduction to Cursors in MySQL 5.0

With the addition of stored functions and stored procedures in MySql 5.0 comes the implementation of cursors. If you have programmed stored programs in other databases such as Oracle you know what cursors are. Although the MySQL version is not as powerful as other databases cursors in MySQL are still very useful.

For programmers who have not used cursors before they are essentially named SQL statements that are defined in your stored function or procedure. Cursors let you fetch one or more rows from the database and allow you to perform a row-by-row processing of the result set.

Declare A Cursor
A cursor is a variable and needs to be declared before it can be used. The syntax for declaring a cursor is as follows:

DECLARE cursor_name CURSOR FOR select_statement;

The cursor_name is the variable name and will be used to refer to it in your stored program. The select_statement is the SQL select statement you wish to execute to fetch data from. This statement can be any valid sql select statement, select into is not valid, and can use variables that you either declare within your stored program or pass in as parameters.

DECLARE getEmployee CURSOR FOR select emp_sal 
   from employee where emp_name = p_emp_name;

Where p_emp_name is a parameter or variable within your stored program.

Open and Close A Cursor
To use a cursor within your stored program you must first open it and once you are done using the cursor it is best practice to close it. The syntax is as follow:

OPEN cursor_name
CLOSE cursor_name

Fetch A Cursor
There are a few ways of fetching the data from a cursor. If for example you wish to fetch only the first row of the result set and a simple SELECT INTO statement will not work due to the nature of the result three lines of code can do it

OPEN getEmployee;
FETCH getEmployee INTO v_emp_name;
CLOSE getEmployee;

If you need to fetch the entire result set, as should be the case most of the time, first you have to create a handler for the cursor that will alert us when there are no more rows to be fetched from the cursor. A handler must be declared after the cursor. The following example will show how to use a cursor along with a handler in a stored procedure.

CREATE PROCEDURE cursor_demo(p_emp_name varchar(25))
BEGIN
   DECLARE done INT DEFAULT 0;
   DECLARE v_emp_sal FLOAT;
   DECLARE getEmployee CURSOR FOR select emp_salary from employee where emp_name = p_emp_name;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

   OPEN getEmployee;
   REPEAT
      FETCH getEmployee INTO v_emp_sal;
      IF NOT done THEN
         INSERT INTO test_table VALUES(v_emp_sal);
      END IF;
   UNTIL done END REPEAT;
   CLOSE getEmployee;
END

The above example uses a REPEAT loop to traverse through the cursor. Your code can use any type of loop so long as the loop checks the done variable set by the handler before each iteration to exit the loop once all rows have been read.

It is also an extremely bad idea, even though it is possible, to use a cursor in a trigger as you will notice a huge performance degradation by doing so.

 

Related Articles

• MySQL Stored Programs - Functions
• MySQL Stored Programs - Triggers

 

blog comments powered by Disqus

Lijit Search