Selecting Multiple Rows
Go Up to Working with Data
Most queries specify search conditions that retrieve more than one row. For example, a query that asks to see all the employees in a company earning more than $60,000 per year can retrieve many employees.
Because host variables can only hold a single column value at a time, a query that returns multiple rows must build a temporary table in memory, called a results table, from which rows can then be extracted and processed, one at a time, in sequential order. SQL keeps track of the next row to process in the results table by establishing a pointer to it, called a cursor.
In dynamic SQL (DSQL), the process for creating a query and retrieving data is somewhat different. For more information about multi-row selection in DSQL, see Selecting Multiple Rows in DSQL.
To retrieve multiple rows into a results table, establish a cursor into the table, and process individual rows in the table, SQL provides the following sequence of statements:
DECLARE CURSOR
establishes a name for the cursor and specifies the query to perform.OPEN
executes the query, builds the results table, and positions the cursor at the start of the table.FETCH
retrieves a single row at a time from the results table into host variables for program processing.CLOSE
releases system resources when all rows are retrieved.
To select data from a table, a user must have
SELECT
privilege for a table, or a stored procedure invoked by the user’s application must have SELECT
privilege for it.Topics
- Declaring a Cursor
- Opening a Cursor
- Fetching Rows with a Cursor
- Closing the Cursor
- A Complete Cursor Example
- Selecting Rows with NULL Values
- Selecting Rows Through a View