Using Cursors

NuoDB SQL supports the definition of cursor variables inside stored procedures only. Cursor variables in NuoDB SQL are bound to a query definition. Using cursors allows fetching a query result set, one row at a time. The benefit of this is being able to process quickly the first several rows of a result set that could contain a large number of rows. In this release of NuoDB, the cursor is as sensitive as possible to insert, update, or delete operations made to the underlying rows of the result set by other database sessions.

A cursor must be declared using the DECLARE statement. This will bind the cursor to a query, but will not actually execute the query. Then before you can fetch any rows from the cursor, you must OPEN it. From there, you can process one row at a time using FETCH. This will fetch the next row of the result set from the server and will be as sensitive as possible to updates. As you loop through the cursor result set, each FETCH statement positions the cursor to the next row. With some restrictions, you can use the cursor to delete or update data based on its position, using DELETE FROM table_name WHERE CURRENT OF cursor_name or UPDATE table_name SET…​ WHERE CURRENT OF cursor_name.