Working with SQL Cursors

In SQL cursors serve as a pointer that enables application programming language to deal with query results one row at a time. This article quickly explores the concept behind and show how to declare cursors, open, retrieve data from them, and then close them.

SQL Cursors

The data in relational database are managed in the form of sets. As a result, query results return by SQL SELECT statements are referred to as result sets. The result sets are nothing but combinations of one or more rows and columns extracted from one or more tables. You can scroll through the result sets to extract the information you need. The data elements returned are used by programming languages like Java or any other for specific application purposes. But here lies the problem of impedance mismatch due to the difference in construct between database model and programming language model.

A SQL database model has three main constructs:

◉ columns (or attributes) and their data types
◉ rows (records or tuples)
◉ tables (collection of records)

Therefore, primary mismatch between two models are:

1. The attribute data types available in database model is not the same as the variable types used in programming languages. There are many host languages, and each have a different data type. For example, the data types of C/C++ and Java are different and so is SQL data types. Hence a binding mechanism is necessary to mitigate the incompatibility issue.

2. The result returned by SQL SELECT statements are multi-sets of records where each record is a collection of attributes. Host programming languages typically works on individual data values of tuple returned by the query. Therefore, it is essential that SQL query result maps with the data structure supported by the programming language. The mechanism of looping over tuples is necessary to iterate over tuples and their attribute values.

The cursor acts like an iterator variable to loop over tuples returned by the SQL query and extract individual values within each tuple which then can be mapped to appropriate type of program variables.

The cursor, therefore, serves as a pointer that enables programming language to process query result one record at a time. A cursor can traverse through all rows of a query result focusing on one row at a time. Consider the following SQL query:

SELECT emp_no, first_name, last_name, birth_date
FROM employees
WHERE MONTH(birth_date) = MONTH(CURRENT_DATE)
AND DAY(birth_date) = DAY(CURRENT_DATE);

The query result from the above statement returns employee details of all those employees whose birth date falls on the current day of a particular month. The result may contain many rows, but the host application language can deal with one row at a time. As a result, cursor is declared as an embedded SQL statement within the application programming language. The cursor then is opened much like a file and extract single row from the query result. Other rows are extracted subsequently, in sequence until the cursor is closed.

Declaring a Cursor

Cursors are declared much like a variable. A name is given, there are statements to open the cursor, retrieve the query result, and finally close the cursor. Note that, different SQL implementations support the use of cursors in a different way. But there is a general agreement on how the cursor should be written.

We must use SQL statements to fully implement cursor functionality because simply declaring a cursor is not enough to extract data from a SQL database. There are four basic steps to declare a cursor:

DECLARE CURSOR: The declaration begins by giving cursor a name and assigning the query expression to be invoked when the cursor is opened.

OPEN: The open statement executes the query expression assigned and make ready query result for subsequent FETCH.

FETCH: Retrieves data values into variables which then can be passed to host programming language or to other embedded SQL statements.

CLOSE: The cursor is closed from fetching any more query result.

The syntax is as follows:

DECLARE <cursor_name>
[SENSITIVE | INSENSITIVE | ASENSITIVE]
[SCROLL | NO SCROLL] CURSOR
[ WITH HOLD | WITHOUT HOLD]
[ WITH RETURN | WITHOUT RETURN]
FOR <sql_query_expression>
[ ORDER BY <sort_expression>]
[ FOR {READ ONLY | UPDATE [ OF <list_of_column>]}]

The essential part of a cursor declaration is as follows:

 DECLARE <cursor_name> FOR <sql_query_expression>

The optional part such as [SENSITIVE | INSENSITIVE | ASENSITIVE] signifies whether the cursor is sensitive to changes and whether to reflect them in the query result. SENSITIVE means cursor is affected by changes, INSENSITIVE means cursor is not affected and ASENSITIVE means changes may or may not be visible to the cursor. If not specified it assumes ASENSITIVE option.

The optional [SCROLL | NOSCROLL] defines the scroll ability of the cursor.  If not specified it assumes NO SCROLL option.

The optional [ WITH HOLD | WITHOUT HOLD] defines whether to hold or automatically close when the transaction due to the cursor is committed. If not specified it maintains WITHOUT HOLD option.

The optional [ WITH RETURN | WITHOUT RETURN] determines whether to return the cursor result set to the invoker such as another SQL routine or host language. If not specified it means WITHOUT RETURN.

The ORDER BY clause is used to sort the query result returned according to the specified sorting technique.

The UPDATE option refers to use of UPDATE or DELETE statement is association with the rows returned by the cursor’s SELECT statement. Any such modification is not possible if we specify READ ONLY option. If not specified, then by default UPDATE option is assumed.

Therefore, a simple cursor can be declared as follows:

DECLARE mycursor CURSOR
 FOR
SELECT emp_no, first_name, last_name, birth_date
  FROM employees
 WHERE MONTH(birth_date) = MONTH(CURRENT_DATE)
  AND DAY(birth_date) = DAY(CURRENT_DATE);

Cursors in MySQL

Typically, there are two types of cursors found in MySQL:  read-only and forward-only cursors. These cursors can be used for MySQL stored procedure. These cursors help us to iterate over query results one row at a time and fetch into variables for further processing. It is possible to declare more than one cursor and nest them in loops. Note that cursors are read-only because they are used to iterate over temporary tables. The cursor typically executes the query as we open it.

One of the problems with cursor in MySQL is that they might slow down the performance of the query due to extra I/O operations they perform. This is particularly for true large data types such as BLOB and TEXT. As cursors works with temporary tables, these types are not supported in in-memory tables. Therefore, while working with these types MySQL has to create temporary tables on disk and that requires lot of I/O operation and that too in slow devices like disks. This is the primary reason of slow performance of cursor.

MySQL also do not support client-side cursors however the client API can emulate them if necessary. But then this is not much different from fetching the result in an array in programming language like Java and manipulate them there instead.

Here is a sample on how to write cursors in MySQL.

CREATE PROCEDURE 'cursor_demo'()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE id INT(11);
    DECLARE fn varchar(14);
    DECLARE ln varchar(16);
    DECLARE bdate date;
  DECLARE mycursor CURSOR FOR
  SELECT emp_no, first_name, last_name, birth_date
    FROM employees
    WHERE MONTH(birth_date)=MONTH(CURRENT_DATE)
      AND DAY(birth_date)=DAY(CURRENT_DATE);
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN mycursor;
  fetch_loop: LOOP
    FETCH mycursor INTO id, fn, ln, bdate;
  IF done THEN
      LEAVE fetch_loop;
    END IF;
    SELECT id, fn, ln, bdate;
  END LOOP;
  CLOSE mycursor;
END

Call the is stored procedure as follows:

mysql> CALL cursor_demo

The procedure fetches the rows from a table named employee whose birth date matches the current day and month in a cursor named mycursor and simply prints them using SELECT statement.