O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required