Being a transactional programming language, SQL is designed to execute its work in an all-or-nothing capacity. Meanwhile, procedural programming languages such as C# and Java are often iterative in nature. As such, they tend to loop over the same code until the stack is diminished and fully processed. Cursors are a notable exception to SQL's transactional approach. Like WHILE loops, cursors allow programmers to process each row of a SELECT result set individually by iterating over them. While many SQL purists shun cursors out of disdain or fear, they have their place in database development and are well worth learning. To that end, today's blog will describe when and how to use cursors within your stored procedures.
Cursors Defined
As mentioned above, a database cursor is a special control structure that enables traversal over the records in a database in order to process individual rows of a query result set for sequential processing. In Stored Procedures, a cursor makes it possible to perform complex logic on a row by row basis.
Cursors have three important properties:
- Asensitive: The server may or may not make a copy of its result table.
- Read-only: The data may not be updated.
- Nonscrollable: Can be traversed only in one direction and cannot skip rows.
How to Use a Cursor
Using a cursor within a stored procedure is a four step process:
- Declare a cursor.
- Open a cursor.
- Fetch the data into variables.
- Close the cursor when done.
Declare a Cursor
The following statement declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor:
DECLARE cursor_name CURSOR FOR select_statement
Open a Cursor
The following statement opens a previously declared cursor.
OPEN cursor_name
Fetch the Data into Variables
This statement fetches the next row for the SELECT statement associated with the specified cursor (which must be open) and advances the cursor pointer. If a row exists, the fetched columns are stored in the named variable(s). The number of columns retrieved by the SELECT statement must match the number of output variables specified in the FETCH statement.
FETCH [[NEXT] FROM] cursor_name INTO var_name [, var_name] ...
Close the Cursor When Done
This statement closes the cursor. An error occurs if the cursor is not open.
CLOSE cursor_name
A Practical Example
Here's the definition of a stored procedure (shown in Navicat for MySQL) that employs a cursor to generate a list of emails for all staff members in the Sakila sample database:
Within the getEmail LOOP, the cursor iterates over the email list, and concatenates all emails separated by a semicolon (;). The finished variable informs the cursor to terminate the loop when there was no email fetched. Here is the value of the emailList after execution of the stored procedure:
Conclusion
In today's blog we learned when and how to use cursors within your stored procedures.
Interested in Navicat for MySQL? You can try it for 14 days completely free of charge for evaluation purposes!
