Cursors
Tags: opengauss
Published: 2023-03-07 04:27:24

To process SQL statements, the stored procedure process assigns a memory segment to store context association. Cursors are handles or pointers pointing to context regions. With cursors, stored procedures can control alterations in context regions.

**Syntax**

**·** Define a cursor.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qdkkc667pqhifiu682ix.png)

**·** Retrieve data by using a created cursor.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1ggj80ccjiyoo9qhwipt.png)

The direction clause specifies optional parameters.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/auie1joffl1hjmz0zfil.png)

**·** Reposition a cursor without retrieving any data.

The MOVE statement works exactly like the FETCH statement, except that it only repositions the cursor and does not return rows.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8y2k863zhk779ticjmac.png)

The direction clause specifies optional parameters.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wypkbiocr38b3sqsit06.png)

**·** Close a cursor and release all resources associated with the cursor.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ybikgjq5ib1dktmyce89.png)

**Parameter Description**

**· cursor_name**

Specifies the name of the cursor to be created or closed.

**· BINARY**

Specifies that data retrieved by a cursor will be returned in binary format, not in text format.

**· NO SCROLL**

Specifies how a cursor retrieves rows.
**· NO SCROLL**: specifies that the cursor cannot be used to retrieve rows in a nonsequential fashion.
**·** Unspecified: Based on the query's execution plan, the system automatically determines whether the cursor can be used to retrieve rows in a nonsequential fashion.

**· WITH HOLD | WITHOUT HOLD**

Specifies whether a cursor can be used after the transaction that created it ends.

**· WITH HOLD**: The cursor can be used after the transaction that created it ends.
**· WITHOUT HOLD**: The cursor cannot be used outside of the transaction that created it.
· If neither **WITH HOLD** nor **WITHOUT HOLD** is specified, the default is WITHOUT HOLD.
· Cross-node transactions (for example, DDL-contained transactions created in openGauss with multiple DBnode) do not support **WITH HOLD**.

**· query**

Uses the **SELECT** or **VALUES** clause to specify the rows to be returned by a cursor.

Value range: **SELECT** or **VALUES** clause

**· direction_clause**

Defines the fetch direction.

Value range:

**· NEXT** (default value)

Fetches the next row.

**·** PRIOR

Fetches the prior row.

**·** FIRST

Fetches the first row of the query (same as **ABSOLUTE 1**).

**·** LAST

Fetches the last row of the query (same as **ABSOLUTE – 1**).

**·** ABSOLUTE count

Fetches the _count_th row of the query.

ABSOLUTE fetches are not any faster than navigating to the desired row with a relative move, because the underlying implementation must traverse all the intermediate rows anyway.

**·** Value range: a possibly-signed integer

**·** If **count** is positive, the _count_th row of the query will be fetched.
**·** If **count** is negative, the _abs(count)_th row from the end of the query result will be fetched.
**·** If **count** is set to 0, the cursor is positioned before the first row.

**·** RELATIVE count

Fetches the _count_th succeeding row or the _count_th prior row.

Value range: a possibly-signed integer

**·** If **count** is positive, the _count_th succeeding rows will be fetched.
**·** If **count** is negative, the _abs(count)_th prior rows will be fetched.
**·** If the current row contains no data, **RELATIVE** 0 returns **NULL**.

**·** count

Fetches the next count rows (same as **FORWARD** count).

**·** ALL

Fetches all remaining rows (same as **FORWARD ALL**).

**·** FORWARD

Fetches the next row (same as **NEXT**).

**·** FORWARD count

Fetches the count succeeding rows or count prior rows.

**·** FORWARD ALL

Fetches all remaining rows.

**·** BACKWARD

Fetches the prior row (same as **PRIOR**).

**·** BACKWARD count

Fetches the prior count rows (scanning backwards).

Value range: a possibly-signed integer

If **count** is positive, the prior count rows will be fetched.
If **count** is a negative, the succeeding abs (count) rows will be fetched.
**BACKWARD 0** re-fetches the current row, if any.

**·** BACKWARD ALL

Fetches all prior rows (scanning backwards).

**· { FROM | IN } cursor_name**

Specifies the cursor name using the keyword **FROM** or **IN**.

Value range: an existing cursor name

**· ALL**

Closes all open cursors.

**Examples**

Assume that the customer_t1 table exists and contains the following data:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b23a7gd7v4aebozrsrfr.png)

**·** Read a table using a cursor.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vpkb97opomnw3e66yj2v.png)

**·** Read the **VALUES** clause using a cursor.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i3yyfb9qh3g6b1137dmp.png)

**·** Use the **WITH HOLD** cursor.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mmie5zxu1d88xhdfy0rr.png)

**·** Use the MOVE statement.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bn5saowfg0q1zgvq0kwd.png)