Chapter 12: Cursors

 

Cool! We’ve come to the final chapter before the project.

 

In this chapter, we are going to look at a major use of the LOOP statement that we learned in the previous chapter.

 

Specifically, we’ll be looking at cursors.

 

What is a Cursor?

 

First off, what is a cursor?

 

A cursor is a mechanism that allows us to step through the rows returned by a SQL statement.

 

So far, we have been learning to use SELECT statements to retrieve information from our tables. These statements return all the rows that match the stated criteria. However, there is no way for us to step through the rows one at a time.

 

If we want to do that, we have to use cursors.

 

The syntax for declaring a cursor is:

 

DECLARE cursor_name CURSOR FOR

SELECT statement

 

This cursor declaration must be done after any variable declaration.

 

After declaring the cursor, we also need to declare a handler that defines what the cursor should do when it reaches the last row of the results returned by the SELECT statement. Normally, we want the cursor to set a variable to a certain value. For instance, we may want it to set a variable called v_done to 1.

 

The syntax is:

 

...

Example

 

Let’s look at an example:

 

Suppose we want to get the names and genders of all employees in our employees table and combine them into a single line of text, here’s how we do it:

 

DELIMITER $$

CREATE FUNCTION get_employees () RETURNS VARCHAR(255) DETERMINISTIC

BEGIN

DECLARE v_employees VARCHAR(255) DEFAULT '';

DECLARE v_name VARCHAR(255);

DECLARE v_gender CHAR(1);

DECLARE v_done INT DEFAULT 0;

DECLARE cur CURSOR FOR

      SELECT em_name, gender FROM employees;

 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

 

OPEN cur;

 

employees_loop: LOOP

      FETCH cur INTO v_name, v_gender;

      IF v_done = 1 THEN LEAVE employees_loop;

            ELSE SET v_employees = concat(v_employees, ', ', v_name, ': ', v_gender...