Using dynamic cursor names

For the full syntax of the CREATE PROCEDURE statement, see CREATE PROCEDURE.

The CURSORNAME() function allows you to dynamically generate a cursor name rather than hard-coding the cursor name.

Note Strictly speaking, CURSORNAME() is not a function, despite the syntactic similarity. CURSORNAME(arg) does not actually return anything; instead it sets the name of the current statement cursor based on the given argument. However, it is convenient to refer to it as a function.

Cursor names must be unique within a connection. This causes problems in recursive stored procedures because each invocation uses the same cursor names. When the recursive procedure calls itself, the second invocation will find that the first invocation has already created a cursor with the same name as the second invocation wants to use.

To get around this problem, you must generate unique cursor names dynamically, and you must be able to use those names when you declare and use cursors. To enable you to generate unique names and use them as cursors, you can use 2 functions:

The GET_UNIQUE_STRING function generates a unique string. The CURSORNAME function (actually a pseudo-function) allows you to use a dynamically generated string as part of a cursor name.

Note that GET_UNIQUE_STRING returns a different output each time it is called, even if the input is the same. CURSORNAME, on the other hand, returns the same output each time if the input is the same each time.

The following example uses GET_UNIQUE_STRING and CURSORNAME to dynamically generate a cursor name. The dynamically generated cursorname is assigned to the placeholder cname, which is then used in each statement after the PREPARE.

DECLARE autoname VARCHAR;
autoname := GET_UNIQUE_STRING('CUR_');
EXEC SQL PREPARE cname CURSORNAME(autoname) SELECT * FROM TABLES;
EXEC SQL EXECUTE cname USING(...) INTO(...);
EXEC SQL FETCH cname;
EXEC SQL CLOSE cname;
EXEC SQL DROP cname;

CURSORNAME() can be used in only PREPARE and EXECDIRECT statements. It cannot be used in EXECUTE, FETCH, CLOSE, or DROP statements, and so on.

By using the CURSORNAME() feature and the GET_UNIQUE_STRING() function, you can generate unique cursor names in recursive stored procedures. If the procedure calls itself, then each time that this function is called within the stored procedure, the function returns a unique string that can be used as the cursor name in a PREPARE statement.

Note that each call to CURSORNAME(autoname) returns the same value — that is, the same cursor name, as long as the input (autoname) does not change.