Chapter 5. Understanding and Using Cursors and Result Sets

In this chapter, you will learn

  • What a cursor is

  • How to use cursors inside SQL procedures

  • How to perform positioned deletes and positioned updates

  • How cursors affect locking

  • About cursor behavior on commit and rollback

  • How to use nested save points

  • How cursors are used to return result sets to applications.

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."

    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205 © 2008-2017.
    If you may any questions please contact us: