SQL in a Nutshell, 3rd Edition

Name

DECLARE CURSOR Command

The DECLARE command is one of four commands used in cursor processing, along with FETCH, OPEN, and CLOSE. Cursors allow you to process queries one row at a time, rather than in a complete set. The DECLARE CURSOR command specifies the exact records to be retrieved and manipulated (one row at a time) from a specific table or view.

In other words, cursors are especially important for relational databases because databases are set-based, while most client-centric programming languages are row-based. This is important for two reasons. First, cursors allow programmers to program using methodologies supported by their favorite row-based programming languages. Second, cursors run counter to the default behavior of some relational database platforms, which operate on sets of records, and on those specific platforms cursor operations may be noticeably slower than standard set-based operations.

Platform	Command
MySQL	Supported, with limitations
Oracle	Supported, with limitations
PostgreSQL	Supported, with limitations
SQL Server	Supported, with limitations

SQL2003 Syntax

DECLARE cursor_name [ {SENSITIVE | INSENSITIVE | ASENSITIVE} ]
[[NO] SCROLL] CURSOR [{WITH | WITHOUT} HOLD]
    [{WITH | WITHOUT} RETURN]
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column[, ...]]}]

Keywords

DECLARE cursor_name

Gives the cursor a unique name in the context in which it is defined (for example, in the database or schema where it is created). No other cursors may share the same name.

SENSITIVE | INSENSITIVE ...