---
layout: default
title: Cursor - Reference Manual - csvq
category: reference
---

# Cursor

A cursor is a pointer to a record in a view created by a select query.

* [Usage Flow](#usage_flow)
* [Cursor Operation](#operation)
* [Cursor Status](#status)


## Usage Flow
{: #usage_flow}

1. [Declare](#declare) a cursor. 
2. [Open](#open) the cursor to create the view. The pointer is set to before the first record.
3. [Fetch](#fetch) the data of the referring record into variables.
   
   You can use the [While In Statement]({{ '/reference/control-flow.html#while_in_loop' | relative_url }}) to fetch all records in loop.
   
4. [Close](#close) the cursor to discard the view.
5. [Dispose](#dispose) the cursor to discard the cursor definition as necessary.

Cursors are not affected by transactions and does not detect any update operations.
The view refered by a cursor is retrieved when the cursor is opened, and it will be held until the cursor is closed.
If you update any records in the tables that refered in any cursors, you may need to close and reopen the cursors.


## Cursor Operation
{: #operation}

### Declare Cursor
{: #declare}

```sql
DECLARE cursor_name CURSOR FOR select_query;
DECLARE cursor_name CURSOR FOR statement_name;
```

_cursor_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

_select_query_
: [Select Query]({{ '/reference/select-query.html' | relative_url }})

_statement_name_
: [Prepared Statement]({{ '/reference/prepared-statement.html' | relative_url }})

### Open Cursor
{: #open}

```sql
OPEN cursor_name;
OPEN cursor_name USING replace_value [, replace_value ...];
```

_cursor_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

_replace_value_
: [replace value]({{ '/reference/prepared-statement.html#execute' | relative_url }}) for [Prepared Statement]({{ '/reference/prepared-statement.html' | relative_url }})

### Close Cursor
{: #close}

```sql
CLOSE cursor_name;
```

_cursor_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

### Dispose Cursor
{: #dispose}

```sql
DISPOSE CURSOR cursor_name;
```

_cursor_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

### Fetch Cursor
{: #fetch}

```sql
FETCH [position] cursor_name INTO variable [, variable ...];

position
  : {NEXT|PRIOR|FIRST|LAST|ABSOLUTE number|RELATIVE number}
```

_cursor_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

_variable_
: [Variable]({{ '/reference/variable.html' | relative_url }})

_number_
: [integer]({{ '/reference/value.html#integer' | relative_url }})

#### Position

A Position keyword in a _fetch cursor statement_ specifies a record to set the pointer.
If specified record does not exist, the _fetch cursor statement_ is set nulls to the variables.

If any position keyword is not specified, then the NEXT keyword is used to fetch.

NEXT
: The pointer is set to the next record and return the record.

PRIOR
: The pointer is set to the previous record and return the record.

FIRST
: The pointer is set to the first record and return the record.

LAST
: The pointer is set to the last record and return the record.

ABSOLUTE number
: The pointer is set to the _number_-th record from the first record and return the record.
  _"ABSOLUTE 0"_ represents the first record.

RELATIVE number
: The pointer is set to the _number_-th record from the current record and return the record.
  _"RELATIVE 0"_ represents the current record.

## Cursor Status
{: #status}

You can get some cursor status by using following expressions.

### Cursor Is Open
{: #cursor_is_open}

```sql
CURSOR cursor_name IS [NOT] OPEN
```

_cursor_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

_return_
: [ternary]({{ '/reference/statement.html#parsing#ternary' | relative_url }})

Return a ternary value indicating whether the cursor is open.

### Cursor Is In Range
{: #cursor_is_in_range}

```sql
CURSOR cursor_name IS [NOT] IN RANGE
```

_cursor_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

_return_
: [ternary]({{ '/reference/statement.html#parsing#ternary' | relative_url }})

Return a ternary value indicating whether the cursor pointer is set to any existing record.
If the cursor is closed, then an error is occurred.
Before the first fetch, return UNKNOWN. 

### Cursor Count
{: #cursor_count}

```sql
CURSOR cursor_name COUNT
```

_cursor_name_
: [identifier]({{ '/reference/statement.html#parsing' | relative_url }})

_return_
: [integer]({{ '/reference/statement.html#parsing#integer' | relative_url }})

Return the number of rows in the view that the cursor is referring. 