SQL in a Nutshell, 3rd Edition

Name

OPEN Statement

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 as a complete set. The OPEN statement opens a pre-existing server cursor created with the DECLARE CURSOR statement.

Cursors are especially important in relational databases because databases are set-based, while most client-centric programming languages are row-based. 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
MySQL	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 generated by a SELECT statement, except that you can operate against each individual row within the result set.

The following generic SQL example opens a cursor and fetches the first and last names of all of the authors from the authors table:

DECLARE employee_cursor CURSOR FOR SELECT au_lname, au_fname FROM pubs.dbo.authors WHERE lname LIKE 'K%' OPEN employee_cursor FETCH NEXT FROM employee_cursor BEGIN FETCH NEXT FROM ...