Selecting Multiple Rows

From InterBase

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.

Important:
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:

  1. DECLARE CURSOR establishes a name for the cursor and specifies the query to perform.
  2. OPEN executes the query, builds the results table, and positions the cursor at the start of the table.
  3. FETCH retrieves a single row at a time from the results table into host variables for program processing.
  4. CLOSE releases system resources when all rows are retrieved.
Important:
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

Advance To: