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

DECLARE CURSOR

Synopsis

The DECLARE CURSOR command enables the retrieval and manipulation of records from a table one row at a time. This provides row-by-row processing, rather than the traditional set processing offered by SQL. To use this procedure properly, you should:

  1. DECLARE the cursor

  2. OPEN the cursor

  3. FETCH rows from the cursor

  4. When finished, CLOSE the cursor

MySQL does not support server-side cursors in the ANSI SQL style, but does support extensive C-programming extensions that provide the same functionality.

Vendor

Command

SQL Server

Supported

MySQL

Not supported

Oracle

Supported

PostgreSQL

Supported

SQL99 Syntax and Description

The DECLARE CURSOR command works by specifying a SELECT statement. Each row returned by the SELECT statement may be individually retrieved and manipulated. The DECLARE CURSOR command also defines the characteristics of a server-side cursor. The characteristics might include how the cursor scrolls and the SELECT statement used to retrieve a result set.

Microsoft SQL Server allows the INSENSITIVE and SCROLL options to be identified. The INSENSITIVE keyword specifies that the cursor build a temporary copy of the result set used by the cursor. All requests to the cursor are answered from the temporary table, not the base table. The cursor does not allow modifications. Subsequent fetches by the cursor do not reflect any changes made by the cursor. The SCROLL keyword enables all FETCH options for the cursor (FIRST, LAST, PRIOR, NEXT, RELATIVE ...

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