<!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.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<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="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="CHECKPOINT.html" title="CHECKPOINT, SHUTDOWN Statement" />
<link rel="next" href="GRANT.html" title="GRANT, REVOKE Statement" />
<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>8. SQL Reference</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="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="spasviewsandtables" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="CHECKPOINT.html" title="CHECKPOINT, SHUTDOWN Statement">Prev</a> | <a class="link" href="GRANT.html" title="GRANT, REVOKE Statement">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="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div>
<a href="udt.html">User Defined Types</a>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div class="selected">
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
<div>
<a href="#proctabparams" title="Procedure Table Parameters">Procedure Table Parameters</a>
<a href="#proctabressets" title="Procedure Table Result Sets">Procedure Table Result Sets</a>
<a href="#proctabsecurity" title="Procedure Tables & Security">Procedure Tables & Security</a>
<a href="#proctabcost" title="Procedure Table Cost and Join Order">Procedure Table Cost and Join Order</a>
<a href="#proctablimits" title="Limitations">Limitations</a>
<a href="#procexamples" title="Procedure Table Examples">Procedure Table Examples</a>
</div>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="spasviewsandtables" />
<h2>8.22. Stored Procedures as Views & Derived Tables</h2>
<p>
Virtuoso allows using a stored procedure result set in place of a table. A view may also be
defined as a stored procedure. This provides smooth integration to external
procedural logic in queries.
</p>
<p>
When a procedure appears as a table, the procedure is called and its result set is inserted
into a temporary space. Processing continues from that point on as if the data came from a table.
</p>
<p>
Queries involving procedure views or derived tables are subject to normal join order selection.
For this purpose it is possible to associate a cost to a procedure used in a procedure view or derived table.
If the option (order) clause is given at the end of the select, joins are done left to right. If a procedure is in the leftmost
position in the from it will be called once for the query, if it is in the second position it will
be called once for every row of the leftmost table that passes selection criteria applicable
to it and so on.
</p>
<p>
Procedures used as tables can get parameters from the query. These parameters are expressed in the containing select's where clause
as column = expression, where column is a parameter name of the procedure table.
</p>
<p>
A procedure derived table is of the form:
</p>
<div>
<pre class="programlisting">
q_table_name '(' column_commalist ')' '(' column_def_commalist ')' alias
</pre>
</div>
<p>
The first column commalist is the parameter list. The second column_def_list is a
description of the result set, as in a CREATE TABLE statement. The correlation name
alias is required if the procedure occurs as a derived table, with no view definition.
</p>
<p>
A procedure view is declared as follows:
</p>
<div>
<pre class="programlisting">
CREATE procedure VIEW new_table_name
AS q_table_name '(' column_commalist ')' '(' column_def_commalist ')'
</pre>
</div>
<p>
The columns in the column definition list should correspond to the procedure's result
set columns. The columns are explicit in the view so as to be able to interpret the view
definition and to be able to compile procedures and queries using the view before the
procedure itself is defined. Thus the procedure need be defined only at time of execution,
not at time of definition.
</p>
<p>
The meta-data returned by ODBC catalog calls for a procedure view will show the columns
as they were declared, just like a regular view. Procedure views are never updatable.
</p>
<a name="proctabparams" />
<h3>8.22.1. Procedure Table Parameters</h3>
<p>
If there is a condition that is in the top level set of AND'ed conditions in the table
expression's WHERE clause and if it is an equality condition and if it references a
parameter of a procedure table and if the other side of the equality does not
reference the procedure table or a table to the right of it, then this condition is
considered a parameter. This means that the condition is not actually evaluated
but rather that the other side of the equality is evaluated before calling the procedure
and that the value returned is passed to the procedure as an input parameter in the
position indicated by the name in the parameter list of the view or derived table.
</p>
<p>
If the procedure table is referenced in an explicit join, as in outer or inner join syntax,
only equalities in the join condition (ON clause) are eligible to specify a parameter.
Equalities in the enclosing query's WHERE phrase will be considered tests on the results,
not parameters.
</p>
<p>
The parameter names in the procedure view's or derived table's parameter list do not
have to be names in the output columns, although this will often be the case. The
number of parameters in the parameter list in the view or derived table must match
that in the procedure definition but the names do not have to be the same. Only input
parameters are supported.
</p>
<p>
If a parameter is specified but no applicable predicate is found, a NULL value is passed.
</p>
<br />
<a name="proctabressets" />
<h3>8.22.2. Procedure Table Result Sets</h3>
<p>
The result set is declared in the derived table or view. This should match the result_names
in the procedure but the former will take precedence on the latter. If an actual result row is
shorter than the declared set, the missing columns will default to NULL.
</p>
<br />
<a name="proctabsecurity" />
<h3>8.22.3. Procedure Tables & Security</h3>
<p>
Accessing a procedure as a table requires execute privileges on the procedure.
Privileges declared on the view are not checked.
</p>
<br />
<a name="proctabcost" />
<h3>8.22.4. Procedure Table Cost and Join Order</h3>
<p>
The __cost declaration in a procedure definition can associate a cost
to a procedure. This declaration is then used for assessing different
join orders. Note that depending on the join order, some parameters
of a procedure table may or may not be available. It is of the form
__cost (n1, n2,...), where each n is a literal number.
The declaration can figure anywhere in the procedure's body as a regular statement.
At least one
number is required. The first number is the one-time cost of calling
the procedure. The second number is the number of result rows that
will be produced by the call, defaulting to 1. The numbers from third
onwards correspond to the parameters of the procedure from left to right. If a parameter
is NOT given or is NOT known because of join order, then the cost of
the single call and the result count will be multiplied by the number
corresponding to the parameter.
</p>
<p>
Consider the declaration __cost (10,
3, 5);. The procedure will take 10 units of time per call and produce
3 rows if the first parameter is known. Otherwise it will take 50
units and produce 15 rows. This is a rough way to specify the
selectivity of specifying a parameter versus not specifying it. One
may liken this to specifying or not specifying conditions of a table's
columns.
</p>
<p>
The unit of cost is an internal abstract unit. For purposes of scaling, selecting a single row from a table of 1000 on an exact match of an integer key is 3 units. The costs are shown by the explain function with a second argument of -5.
</p>
<br />
<a name="proctablimits" />
<h3>8.22.5. Limitations</h3>
<p>
There is no limitation to the number of rows in a procedure result set. The temporary
storage takes place in the database similarly to a sorted ORDER BY. Number of columns is
limited to the maximum number of columns in a real table. Total row size limit for tables and
ORDER BY intermediate results applies.
Blobs are allowed and do not count towards the row length limit.
</p>
<br />
<a name="procexamples" />
<h3>8.22.6. Procedure Table Examples</h3>
<div>
<pre class="programlisting">
create procedure n_range (in first integer, in last integer)
{
declare n, n2 integer;
n := first;
result_names (n, n2);
while (n < last){
result (n, 2 * n);
n := n + 1;
}
}
select n, n2 from n_range (first, last) (n int, n2 int) n
where first = 2 and last = 12;
</pre>
</div>
<p>
This returns a set of numbers from 2 to 11 and from 4 to 22.
</p>
<div>
<pre class="programlisting">
select a.n, b.n from n_range (first, last) (n int, n2 int) a,
n_range (f2, l2) (n int, n2 int) b
where first = 2 and last = 12 and f2 = a.n - 2 and l2 = a.n + 2;
</pre>
</div>
<p>
Here we join the second call to the procedure to the first, effectively passing
the output of the left call as parameters to the right call.
</p>
<div>
<pre class="programlisting">
create procedure view n_range as n_range (first, last) (n1 int, n2 decimal);
</pre>
</div>
<p>
This defines the procedure as a view, so that it can be referenced like a table.
</p>
<div>
<pre class="programlisting">
select * from n_range a, n_range b where a.first = 1 and a.last = 11
and b.last = a.n1 + 2 and b.first = a.n1 - 2;
</pre>
</div>
<p>
This is the previous join but now using the view.
</p>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="CHECKPOINT.html" title="CHECKPOINT, SHUTDOWN Statement">Previous</a>
<br />CHECKPOINT, SHUTDOWN Statement</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="GRANT.html" title="GRANT, REVOKE Statement">Next</a>
<br />GRANT, REVOKE Statement</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
>
878
