Dynamic cursor names

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’s cursor based on the given argument. However, it is convenient to refer to it as a function, and therefore we will do so.

Cursor names must be unique within a connection. This causes problems in recursive stored procedures because each invocation uses the same cursor name(s). 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, we must generate unique cursor names dynamically, and we must be able to use those names when we declare and use cursors. To enable us to generate unique names and use them as cursors, we use 2 functions:

The GET_UNIQUE_STRING function does what its name suggests: that is, it 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.

Below is an example of using 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 only be used in PREPARE statements and EXECDIRECT statements. It cannot be used in EXECUTE, FETCH, CLOSE, DROP, 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, this function will return a unique string that can be used as the cursor name in a PREPARE statement. See below for some examples of code that you could use inside a stored procedure.

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.