# 9. SQL Procedure Language Guide

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
SQL Procedure Language Guide
General Principles
Scope of Declarations
Data Types
Handling Result Sets
Result Sets and Array Parameters
Exception Semantics
Virtuoso/PL Syntax
CREATE ASSEMBLY Syntax - External Libraries
CREATE PROCEDURE Syntax - External hosted procedures
Asynchronous Execution and Multithreading in Virtuoso/PL
Performance Tips
Procedures and Transactions
Distributed Transaction & Two Phase Commit
Triggers
Character Escaping
Virtuoso/PL Scrollable Cursors
Declaring a Scrollable Cursor Opening a Scrollable Cursor Fetching Data From a Scrollable Cursor Virtuoso/PL Scrollable Cursor Examples FORWARD-ONLY (traditional cursor statement) Example DYNAMIC (traditional cursor statement) Example KEYSET (traditional cursor statement) Example
Virtuoso PL Modules
Handling Conditions In Virtuoso/PL Procedures
Procedure Language Debugger
Row Level Security

## 9.16. Virtuoso/PL Scrollable Cursors

Virtuoso/PL supports scrollable cursors, providing functionality similar to the ODBC scrollable cursor support. Scrollable Cursor support extends the basic (forward-only) syntax of DECLARE CURSOR and FETCH to support the various fetch directions & cursor modes. The Virtuoso/PL scrollable cursors always operate with a rowset size equal to 1. The keyset size (where applicable) is as per the default.

Note:

If a Virtuoso/PL cursor is declared forward only it supports only FETCH .. NEXT scroll direction. The FETCH defaults its direction to NEXT (if omitted) so this is how the syntax extensions to DECLARE CURSOR & FETCH interoperate with the forward-only cursors syntax.

### 9.16.1. Declaring a Scrollable Cursor

Virtuoso/PL cursor types are specified at declaration time. Unlike the forward-only cursor declaration the scrollable cursor DECLARE CURSOR causes some actions (cursor statement preparation & cursor variable assignment). The cursor variable's value can not be copied, it should be passed only by reference in procedure calls. Scrollable cursors have an appropriate destructor, which will close the cursor when the cursor variable goes out of scope. Variables in the surrounding context are referenced similarly to the forward-only cursor.

Note:

Some types of statements do not allow other cursor types than static. For example SELECT DISTINCT will always result in a static cursor, ignoring the cursor declared type.

### 9.16.2. Opening a Scrollable Cursor

The OPEN on a scrollable cursor opens the cursor and sets it's position right before the first resultset row. So before taking the bookmark value at least one FETCH should be issued.

### 9.16.3. Fetching Data From a Scrollable Cursor

The FETCH on a scrollable cursor allows specification of a direction. If there is no more data in the specified fetch direction this causes the NOT FOUND exception to be raised, as with the forward-only cursors. In addition to that if the row on which the cursor is about to position has been deleted and the isolation level & cursor type allows detecting that, then the exception SQLSTATE 'HY109' (Row deleted) is raised.

Positioning on a bookmark is done the following way:

A bookmark value should be retrieved using the bookmark() function. The value returned by that function can be stored, copied and retrieved. This value can also survive a cursor close and reopen, even between transactions. How the cursor will behave if a bookmark from a cursor with different select statement or scroll type is used for positioning is undefined and should be avoided. On some occasions it may signal an error, on others it will position on a wrong or non-existing row. As a general rule bookmark values should be used only on the cursor from which they are generated.

The cursor should be in opened state. Now a FETCH .. BOOKMARK bm_value INTO ... can be issued with the bookmark variable.

Bookmarks can serve for persisting the cursor position in an VSP context. One can imagine a VSP page which on it's first go will execute a cursor and will show the first so-many rows. Then it can retrieve the bookmark value of the last displayed row, persist it somehow (for example as an HTTP session variable), then close the cursor and exit. On each subsequent hit it will open again the same cursor, position on the bookmark persisted and return the next, previous, first or last so-many rows.

### 9.16.4. Virtuoso/PL Scrollable Cursor Examples

Procedure using scrollable cursor to read the keys in batches of 20

    create procedure READ_KEYS_NEXT_20 (in mask varchar, inout bm any) returns integer
    {
      --- This procedure reads the next 20 table names based on a bookmark value.
      declare cr static cursor for
          select distinct KEY_TABLE
            from DB.DBA.SYS_KEYS
    	where
    	  KEY_IS_MAIN = 1 and
    	  KEY_MIGRATE_TO is NULL and
    	  KEY_TABLE like mask;
      declare table_name varchar;
      declare inx integer;

      inx := 1;

      -- no 'Row Deleted' (HY109) handling as the static cursors doesn't show the deleted rows.
      whenever not found goto done;
      open cr;

      -- positions on the bookmark or on the first if it is null
      -- and fetches the value into table_name
      if (bm is not null)
        {
          fetch cr bookmark bm into table_name;
          -- note that the value from fetch bookmark is omitted
          fetch cr next into table_name;
        }
      else
        fetch cr first into table_name;

      -- fetches the next 20 rows (or less)
      while (inx < 20)
        {
          result (table_name);
          inx := inx + 1;
          fetch cr next into table_name;
        }

      -- 20 rows were fetched - get the bookmark of the last row fetched
      bm := bookmark (cr);
      close cr;
      return;

    done:

      -- no more rows - set the bookmark to NULL
      close cr;
      bm := NULL;
    };

    create procedure READ_KEYS (in mask varchar)
    {
      -- the main function (mask is a mask to be applied over the select

      declare table_name varchar;
      declare bm any;

      -- it'll return a resultset with a single column
      result_names (table_name);

      -- sets the bookmark to empty
      bm := NULL;

      while (1)
        {
           READ_KEYS_NEXT_20 (mask, bm);

           -- that's the flag for no more rows
           if (bm is NULL)
             return;
        }
    };

    READ_KEYS ('%');

### 9.16.5. FORWARD-ONLY (traditional cursor statement) Example

Procedure using forward only cursor

    create procedure TEST_FW()
    {
      declare cr cursor for select KEY_ID from DB.DBA.SYS_KEYS;
      declare inx, data integer;
      inx := 0;

      whenever not found goto done;
      open cr;
      while (1)
        {
          fetch cr into data;
          inx := inx + 1;
        }
    done:
      close cr;
      result_names (data);
      result (inx);
    };
    TEST_FW();

### 9.16.6. DYNAMIC (traditional cursor statement) Example

Procedure using dynamic cursor

    create procedure TEST_DYNAMIC ()
    {
      declare cr dynamic cursor for select KEY_ID from DB.DBA.SYS_KEYS;
      declare inx, data integer;
      inx := 0;

      whenever not found goto done;
      open cr;
      while (1)
        {
          fetch cr into data;
          inx := inx + 1;
        }
    done:
      close cr;
      result_names (data);
      result (inx);
    };
    TEST_DYNAMIC ();

### 9.16.7. KEYSET (traditional cursor statement) Example

Procedure using keyset cursor

    create procedure TEST_KEYSET ()
    {
      declare cr keyset cursor for select KEY_ID from DB.DBA.SYS_KEYS;
      declare inx, data integer;
      inx := 0;

      whenever not found goto done;
      open cr;
      while (1)
        {
          fetch cr into data;
          inx := inx + 1;
        }
    done:
      close cr;
      result_names (data);
      result (inx);
    };
    TEST_KEYSET ();

Previous Character Escaping | Chapter Contents | Next Virtuoso PL Modules