SQL in a Nutshell, 2nd Edition

Name

OPEN Statement

Synopsis

The OPEN statement is one of four commands used in cursor processing, along with DECLARE, FETCH, and CLOSE. Cursors allow you to process queries one row at a time, rather than in a complete set. The OPEN statement opens a pre-existing server cursor created with the DECLARE CURSOR statement.

Cursors are especially important on relational databases because databases are set-based, while most client-centric programming languages are row-based. So cursors allow programmers and databases to perform operations a single row at a time, while the default behavior of a relational database is to operate on a whole set of records.

Platform	Command
DB2	Supported, with variations
MySQL	Not supported
Oracle	Supported
PostgreSQL	Not supported
SQL Server	Supported

SQL2003 Syntax

OPEN cursor_name

Keywords

OPEN cursor_name

Identifies and opens the previously defined cursor created with the DECLARE CURSOR command.

Rules at a Glance

At the highest level, a cursor must be:

1. Created using DECLARE

2. Opened using OPEN

3. Operated against using FETCH

4. Dismissed using CLOSE

By following these steps, you create a result set similar to that of a SELECT statement, except that you can operate against each individual row within the result set. For example, assume, on a DB2 database, that you’ve created a cursor called employee_cursor, containing three columns:

DECLARE CURSOR employee_cursor FOR
SELECT lname, fname, emp_id
FROM employee
WHERE hire_date >= 'FEB-14-2004';

Having created the ...