# DECLARE CURSOR

## Syntax

{% tabs %}
{% tab title="Current" %}

```sql
DECLARE cursor_name CURSOR [(cursor_formal_parameter[,...])] FOR {select_statement | prepared_statement_name}

cursor_formal_parameter:
    [IN] name type [collate clause]
```

{% endtab %}

{% tab title="< 10.8" %}

```sql
DECLARE cursor_name CURSOR [(cursor_formal_parameter[,...])] FOR select_statement

cursor_formal_parameter:
    name type [collate clause]
```

{% endtab %}
{% endtabs %}

## Description

This statement declares a [cursor](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors). Multiple cursors may be declared in a [stored program](https://mariadb.com/docs/server/server-usage/stored-routines), but each cursor in a given block must have a unique name.

`select_statement` is not executed until the [OPEN](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors/open) statement is executed. It is important to remember this if the query produces an error, or calls functions which have side effects.

A `SELECT` associated to a cursor can use variables, but the query itself cannot be a variable, and cannot be dynamically composed. The `SELECT` statement cannot have an `INTO` clause.

{% hint style="info" %}
Starting with MariaDB 12.3, the query can also be a prepared statement name, which allows the query to be dynamically composed. The `SELECT` statement cannot have an `INTO` clause.
{% endhint %}

Cursors must be declared before [HANDLERs](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/declare-handler), but after local variables and [CONDITIONs](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/declare-condition).

> The `DECLARE CURSOR` statement provides a cursor to a specified `SELECT` statement.
>
> To declare a cursor variable type that can be connected with multiple queries at runtime (REF CURSOR), use `DECLARE TYPE... IS REF CURSOR` in Oracle mode. See [DECLARE TYPE](https://mariadb.com/docs/server/reference/sql-statements/declare-type#ref-cursor-types).

### **Dynamic Cursors**

Starting with MariaDB 12.3, a cursor can be declared for a prepared statement. This allows the use of Dynamic SQL within stored routines. The cursor is bound to a prepared statement name, which must be defined using the `PREPARE` statement before the cursor is opened.

```sql
CREATE OR REPLACE PROCEDURE p1(tab VARCHAR(64), min_id INTEGER)
BEGIN
  DECLARE v_id INT;
  DECLARE v_c1 VARCHAR(100);
  DECLARE no_data BOOL DEFAULT FALSE;
  
  -- 1. Declare cursor for the statement name 's1'
  DECLARE c1 CURSOR FOR s1;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_data = TRUE;

  -- 2. Prepare the statement dynamically
  PREPARE s1 FROM CONCAT('SELECT id, c1 FROM ', tab, ' WHERE id >= ?');
  
  -- 3. Open cursor and bind parameters
  OPEN c1 USING min_id;
  
  fetch_loop: LOOP
    FETCH c1 INTO v_id, v_c1;
    IF no_data THEN
      LEAVE fetch_loop;
    END IF;
    SELECT v_id, v_c1;
  END LOOP;
  
  CLOSE c1;
  DEALLOCATE PREPARE s1;
END;
$$

DELIMITER ;
```

### Parameters

Cursors can have parameters. This is a non-standard SQL extension. Cursor parameters can appear in any part of the `DECLARE CURSOR` `select_statement` where a stored procedure variable is allowed (select list, `WHERE`, `HAVING`, `LIMIT` , and so forth).

### IN

{% tabs %}
{% tab title="Current" %}
The `IN` qualifier is supported in the `cursor_formal_parameter` part of the syntax.
{% endtab %}

{% tab title="< 10.8" %}
The `IN` qualifier is **not** supported in the `cursor_formal_parameter` part of the syntax.
{% endtab %}
{% endtabs %}

See [Cursor Overview](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors/cursor-overview) for an example.

## See Also

* [Cursor Overview](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors/cursor-overview)
* [OPEN cursor\_name](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors/open)
* [FETCH cursor\_name](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors/fetch)
* [CLOSE cursor\_name](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors/close)
* [Cursors in Oracle mode](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors/declare-cursor.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.