Cursors in SQL procedures

In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.
A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result set as needed.

To use cursors in SQL procedures, you need to do the following:

1. Declare a cursor that defines a result set.
2. Open the cursor to establish the result set.
3. Fetch the data into local variables as needed from the cursor, one row at a time.
4. Close the cursor when done

To work with cursors you must use the following SQL statements:

• DECLARE CURSOR
• OPEN
• FETCH
• CLOSE

The following example demonstrates the basic use of a read-only cursor within an SQL procedure:

CREATE PROCEDURE sum_salaries(OUT sum INTEGER) 
  LANGUAGE SQL
  BEGIN
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE p_sum INTEGER;
    DECLARE p_sal INTEGER;
    DECLARE c CURSOR FOR SELECT SALARY FROM EMPLOYEE;
 
     SET p_sum = 0;

     OPEN c;

     FETCH FROM c INTO p_sal;

     WHILE(SQLSTATE = '00000') DO
        SET p_sum = p_sum + p_sal;
        FETCH FROM c INTO p_sal; 
     END WHILE;

     CLOSE c;

     SET sum = p_sum;

  END%

Here is a more complex example of use of a cursor within an SQL procedure. This example demonstrates the combined use of a cursor and SQL PL statements.