O'Reilly logo

SQL in a Nutshell, 3rd Edition 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

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

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