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:
- Declare a cursor that defines a result set.
- Open the cursor to establish the result set.
- Fetch the data into local variables as needed from the cursor, one row
at a time.
- Close the cursor when done
To work with cursors you must use the following SQL statements:
- DECLARE 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.