In this chapter, you will learn
In previous chapters, you have seen how SQL procedures can manipulate table data by directly executing INSERT, UPDATE, and DELETE statements. Using SQL in this manner, however, means that operations must be applied to the entire set of data defined by the WHERE clause of the statement. In SQL procedures, it is possible to define a set of data rows (which will be called a result set from here on) and then 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 another program for further processing.
To take advantage of these features, you need to know about cursors. A cursor can be viewed as a pointer to one row in a set of rows. The cursor can reference only one row at any given time, but can move to other rows of the result set as needed. For example, consider a cursor that is defined with this SELECT statement:
SELECT * FROM employees WHERE sex='M'
The result set will contain a list of male employees. The cursor will be positioned just before the first row of the result set initially. To position the cursor onto the first row, you execute a FETCH operation. Once the cursor is positioned onto a row, the data can be processed. To retrieve the next row, FETCH can be executed again. The FETCH operation can be repeated until all rows of the result set have been exhausted.
This chapter covers only returning result sets from SQL procedures. Chapter 8, "Nested SQL Procedures," tells you how to receive result sets from another SQL procedure. Examples of receiving result sets in Java or C client programs will be presented in Appendix H, "Sample Application Code."