<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="9. SQL Procedure Language Guide" />
<meta name="dc.subject" content="9. SQL Procedure Language Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlprocedures.html" title="Chapter Contents" />
<link rel="prev" href="charescaping.html" title="Character Escaping" />
<link rel="next" href="plmodules.html" title="Virtuoso PL Modules" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>9. SQL Procedure Language Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="PLSCROLLCRSRS" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>9. SQL Procedure Language Guide</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlprocedures.html">Chapter Contents</a> | <a class="link" href="charescaping.html" title="Character Escaping">Prev</a> | <a class="link" href="plmodules.html" title="Virtuoso PL Modules">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlprocedures.html">SQL Procedure Language Guide</a>
</div>
<br />
<div>
<a href="GENERALPRINCIPLES.html">General Principles</a>
</div>
<div>
<a href="SCOPEOFDECLARATION.html">Scope of Declarations</a>
</div>
<div>
<a href="sqlplDATATYPES.html">Data Types</a>
</div>
<div>
<a href="RESULTSETS.html">Handling Result Sets</a>
</div>
<div>
<a href="ARRAYS.html">Result Sets and Array Parameters</a>
</div>
<div>
<a href="EXCEPTIONS.html">Exception Semantics</a>
</div>
<div>
<a href="PLREF.html">Virtuoso/PL Syntax</a>
</div>
<div>
<a href="EXECSQLPROCSELECT.html">Execute Stored Procedures via SELECT statement</a>
</div>
<div>
<a href="EXECSQLPROCFORK.html">Execute Stored Procedures In Background</a>
</div>
<div>
<a href="createassembly.html">CREATE ASSEMBLY Syntax - External Libraries</a>
</div>
<div>
<a href="createexthostproc.html">CREATE PROCEDURE Syntax - External hosted procedures</a>
</div>
<div>
<a href="ASYNCEXECMULTITHREAD.html">Asynchronous Execution and Multithreading in Virtuoso/PL</a>
</div>
<div>
<a href="PERFTIPS.html">Performance Tips</a>
</div>
<div>
<a href="PROCEDURES_TRANSACTIONS.html">Procedures and Transactions</a>
</div>
<div>
<a href="twopcimplementation.html">Distributed Transaction & Two Phase Commit</a>
</div>
<div>
<a href="TRIGGERS.html">Triggers</a>
</div>
<div>
<a href="charescaping.html">Character Escaping</a>
</div>
<div class="selected">
<a href="PLSCROLLCRSRS.html">Virtuoso/PL Scrollable Cursors</a>
<div>
<a href="#PLSCROLLDECLARE" title="Declaring a Scrollable Cursor">Declaring a Scrollable Cursor</a>
<a href="#PLSCROLLOPEN" title="Opening a Scrollable Cursor">Opening a Scrollable Cursor</a>
<a href="#PLSCROLLFETCH" title="Fetching Data From a Scrollable Cursor">Fetching Data From a Scrollable Cursor</a>
<a href="#PLSCROLLEXAMPLES" title="Virtuoso/PL Scrollable Cursor Examples">Virtuoso/PL Scrollable Cursor Examples</a>
<a href="#PLFORWARDONLYCR" title="FORWARD-ONLY (traditional cursor statement) Example">FORWARD-ONLY (traditional cursor statement) Example</a>
<a href="#PLDYNAMICCR" title="DYNAMIC (traditional cursor statement) Example">DYNAMIC (traditional cursor statement) Example</a>
<a href="#PLKEYSETCR" title="KEYSET (traditional cursor statement) Example">KEYSET (traditional cursor statement) Example</a>
</div>
</div>
<div>
<a href="plmodules.html">Virtuoso PL Modules</a>
</div>
<div>
<a href="handlingplcondit.html">Handling Conditions In Virtuoso/PL Procedures</a>
</div>
<div>
<a href="pldebugger.html">Procedure Language Debugger</a>
</div>
<div>
<a href="rowlevelsecurity.html">Row Level Security</a>
</div>
<br />
</div>
<div id="text">
<a name="PLSCROLLCRSRS" />
<h2>9.18. Virtuoso/PL Scrollable Cursors</h2>
<p>
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.
</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>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.
</p>
</div>
<a name="PLSCROLLDECLARE" />
<h3>9.18.1. Declaring a Scrollable Cursor</h3>
<p>
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.
</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>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.
</p>
</div>
<br />
<a name="PLSCROLLOPEN" />
<h3>9.18.2. Opening a Scrollable Cursor</h3>
<p>
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.
</p>
<br />
<a name="PLSCROLLFETCH" />
<h3>9.18.3. Fetching Data From a Scrollable Cursor</h3>
<p>
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.
</p>
<p>
Positioning on a bookmark is done the following way:
</p>
<p>
A bookmark value should be retrieved using the <a href="fn_bookmark.html">bookmark() function</a>.
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.
</p>
<p>
The cursor should be in opened state. Now a FETCH .. BOOKMARK bm_value INTO ... can be issued with
the bookmark variable.
</p>
<p>
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.
</p>
<br />
<a name="PLSCROLLEXAMPLES" />
<h3>9.18.4. Virtuoso/PL Scrollable Cursor Examples</h3>
<a name="" />
<div class="example">
<div class="exampletitle">Procedure using scrollable cursor to read the keys in batches of 20</div>
<div>
<pre class="programlisting">
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 ('%');
</pre>
</div>
</div>
<br />
<a name="PLFORWARDONLYCR" />
<h3>9.18.5. FORWARD-ONLY (traditional cursor statement) Example</h3>
<a name="" />
<div class="example">
<div class="exampletitle">Procedure using forward only cursor</div>
<div>
<pre class="programlisting">
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();
</pre>
</div>
</div>
<br />
<a name="PLDYNAMICCR" />
<h3>9.18.6. DYNAMIC (traditional cursor statement) Example</h3>
<a name="" />
<div class="example">
<div class="exampletitle">Procedure using dynamic cursor</div>
<div>
<pre class="programlisting">
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 ();
</pre>
</div>
</div>
<br />
<a name="PLKEYSETCR" />
<h3>9.18.7. KEYSET (traditional cursor statement) Example</h3>
<a name="" />
<div class="example">
<div class="exampletitle">Procedure using keyset cursor</div>
<div>
<pre class="programlisting">
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 ();
</pre>
</div>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="charescaping.html" title="Character Escaping">Previous</a>
<br />Character Escaping</td>
<td align="center" width="34%">
<a href="sqlprocedures.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="plmodules.html" title="Virtuoso PL Modules">Next</a>
<br />Virtuoso PL Modules</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
40
