How to Fetch SQL Data from a Single Row
A cursor enables SQL to retrieve (or update, or delete) a single row at a time so that you can use SQL in combination with an application written in any of the popular languages. Processing cursors is a three-step process:
The DECLARE CURSOR statement specifies the cursor’s name and scope.
The OPEN statement collects the table rows selected by the DECLARE CURSOR query expression.
The FETCH statement actually retrieves the data.
The cursor may point to one of the rows in the cursor’s scope, or to the location immediately before the first row in the scope, or to the location immediately after the last row in the scope, or to the empty space between two rows. You can specify where the cursor points with the orientation clause in the FETCH statement.
The syntax for the FETCH statement is
FETCH [[orientation] FROM] cursor-name INTO target-specification [, target-specification]... ;
Seven orientation options are available:
<simple value specification>
The default option is NEXT, which, incidentally, was the only orientation available in versions of SQL prior to SQL-92. The NEXT orientation moves the cursor from wherever it is to the next row in the set specified by the query expression. That means that if the cursor is located before the first record, it moves to the first record.
If it points to record n, it moves to record n+1. If the cursor points to the last record in the set, it moves beyond that record, and notification of a no data condition is returned in the SQLSTATE system variable.
The target specifications are either host variables or parameters, depending on whether embedded SQL or a module language, respectively, is using the cursor. The number and types of the target specifications must match the number and types of the columns specified by the query expression in the DECLARE CURSOR.
So in the case of embedded SQL, when you fetch a list of five values from a row of a table, five host variables must be there to receive those values, and they must be the right types.
Orientation of a scrollable cursor
Because the SQL cursor is scrollable, you have other choices besides NEXT. If you specify PRIOR, the pointer moves to the row immediately preceding its current location. If you specify FIRST, it points to the first record in the set, and if you specify LAST, it points to the last record.
When you use the ABSOLUTE and RELATIVE orientation, you must specify an integer value, as well. For example, FETCH ABSOLUTE 7 moves the cursor to the seventh row from the beginning of the set. FETCH RELATIVE 7 moves the cursor seven rows beyond its current position. FETCH RELATIVE 0 doesn’t move the cursor.
FETCH RELATIVE 1 has the same effect as FETCH NEXT. FETCH RELATIVE –1 has the same effect as FETCH PRIOR. FETCH ABSOLUTE 1 gives you the first record in the set, FETCH ABSOLUTE 2 gives you the second record in the set, and so on.
Similarly, FETCH ABSOLUTE –1 gives you the last record in the set, FETCH ABSOLUTE –2 gives you the next-to-last record, and so on. Specifying FETCH ABSOLUTE 0 returns the no-data exception condition code, as will FETCH ABSOLUTE 17 if only 16 rows are in the set. FETCH <simple value specification> gives you the record specified by the simple value specification.
Positioned DELETE and UPDATE statements
You can perform delete and update operations on the row to which a cursor is currently pointing. The syntax of the DELETE statement looks like this:
DELETE FROM table-name WHERE CURRENT OF cursor-name ;
If the cursor doesn’t point to a row, the statement returns an error condition, and no deletion occurs.
The syntax of the UPDATE statement is as follows:
UPDATE table-name SET column-name = value [,column-name = value]... WHERE CURRENT OF cursor-name ;
The value you place into each specified column must be a value expression or the keyword DEFAULT. If an attempted positioned update operation returns an error, the update isn’t performed.