<!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=".html" title="" />
<link rel="next" href="SCOPEOFDECLARATION.html" title="Scope of Declarations" />
<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="GENERALPRINCIPLES" />
<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="sqlprocedures.html" title="SQL Procedure Language Guide">Prev</a> | <a class="link" href="SCOPEOFDECLARATION.html" title="Scope of Declarations">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 class="selected">
<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>
<a href="PLSCROLLCRSRS.html">Virtuoso/PL Scrollable Cursors</a>
</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="GENERALPRINCIPLES" />
<h2>9.1. General Principles</h2>
<p>
A stored procedure is a named piece of Virtuoso/PL code stored in the
SYS_PROCEDURES table. Stored procedures are created with the create
procedure statement and are used by executing a procedure call statement
through the regular SQL API.
</p>
<p>
A procedure takes zero or more arguments and optionally returns a value.
Procedure arguments may be input, output or input and output.
In this manner a procedure may modify a variable passed to it by its
caller. If the procedure is called from a call statement executed by
a client process, the client process gets back the procedure's return
value and the values of output parameters.
</p>
<p>
Procedures can be called with positional or keyword parameters. A call with positional
parameters will bind the first argument in the call to the first parameter in the
procedure parameter list and so on. A keyword parameter call allows specifying
named parameters, where the argument of a given name is bound to the parameter of
the same name in the procedure's parameter list. Procedure parameters may be
required or optional. The combination of optional parameters and the keyword call
notation make it convenient to have procedures with large numbers of parameters of
which only part are used at any one time.
</p>
<p>
Procedures have local variables and cursors that are not visible to
other procedures. Procedures can call each other without limitations,
including recursively.
</p>
<p>
In addition to returning a value and changing values of output parameters
a procedure may yield one or more result sets. The client can receive
rows in result sets just like rows returned by a select statement.
A procedure calling another procedure cannot receive a result set
produced by the called procedure, however. While parameters and return
values work equally well between procedures as between procedure and
client application, a result set always goes to the client, even if the
procedure has been called by another procedure. A procedure view is a
separate construct which allows a procedure to iterate over another procedure's
result set. See the Procedure Views section.
</p>
<p>
A procedure consists of statements and expressions similar to those of any
procedural language. In addition, procedures may contain SQL statements
operating on the procedure's arguments and local variables. Writing a
stored procedure is thus much like using embedded SQL in C, except that
a stored procedure is typically much faster.
</p>
<p>
The elements of the procedure are:
</p>
<ul>
<li>
<div class="formalpara">
<strong>Procedure Declaration</strong>
<p>
This is a create procedure statement that names the procedure and its
arguments.
</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>Variable Declaration</strong>
<p>
This declares a local variable for the procedure.
</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>Cursor Declaration</strong>
<p>
This declares a cursor, A cursor allows a procedure to iterate over the
rows produced by a select statement.
</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>Manipulative SQL statement</strong>
<p>
This can be a delete or update statement, either searched or positioned,
a cursor manipulation or other so called routine statement.
</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>Control statement</strong>
<p>
This is any control structure, loop, assignment or procedure call.
</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>Handler declaration</strong>
<p>
This specifies what to do in a specific exception situation. Exceptions
are error conditions produced by SQL statements (e.g. deadlock) or
'not found' situations.
</p>
</div>
</li>
</ul>
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="sqlprocedures.html" title="SQL Procedure Language Guide">Previous</a>
<br />Contents of SQL Procedure Language Guide</td>
<td align="center" width="34%">
<a href="sqlprocedures.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="SCOPEOFDECLARATION.html" title="Scope of Declarations">Next</a>
<br />Scope of Declarations</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
>
35
