Cursors

Cursors

In the previous lesson on iterative processing we looped through a set of rows. Cursors is a kind of a loop which is used to traverse the rows returned by a query. Just as the cursor on the computer screen shows the current position, a database cursor also shows its current position in the result-set. Cursors can only be used in stored procedures, functions and triggers to loop through the results of a query one row at a time. Cursors have some properties which will be discussed next.

Cursors are read-only meaning they can only be used to view the result-set and not update it. They are non-scrollable meaning they can only show rows in the result-set in a sequential manner. It is not possible to view rows in a different order than the one returned by the query or to skip some rows to reach a specific row. MySQL cursors are asensitive meaning they point to the actual data in the table. There is another type of cursor which creates a temporary table for the result-set. This type is called insensitive cursors and these are not supported by MySQL. A potential drawback of working with the actual data in the table and not a temporary copy is that any changes made to the data from another connection can affect the results of the asensitive cursor.

To use a cursor, it is first declared using a DECLARE statement which mentions the query with which the cursor is associated. It is necessary to declare the variables that will be used to manipulate the results returned by the query before declaring the cursor. Failure to do so will result in an error. The OPEN statement is used to initialize the cursor by fetching the rows resulting from the execution of the query. Next, to process each row of the result-set, the FETCH statement is used. This statement retrieves the row pointed to by the cursor and moves the pointer to the next row. As we are fetching rows one after the other, the cursor points to the next row in the result set. After fetching the last row, a condition is raised when the cursor cannot find the next row. To handle this situation we must define the NOT FOUND handler which sets a variable LastRowFetched to 1. This variable is checked in every iteration as the terminating condition of the loop. Lastly, the CLOSE statement is used to deactivate the cursor and release memory associated with it.

The following diagram illustrates the working of MySQL cursors:

Syntax

DECLARE CursorName CURSOR FOR

SELECTStatement;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET LastRowFetched = 1;

OPEN cursor_name;

FETCH cursor_name INTO variables list;

CLOSE cursor_name;

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/56lesson.sh and wait for the MySQL prompt to start-up.