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.
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.
Normally, joins involving procedures 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.
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.
A procedure derived table is of the form:
q_table_name '(' column_commalist ')' '(' column_def_commalist ')' alias
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.
A procedure view is declared as follows:
CREATE procedure VIEW new_table_name AS q_table_name '(' column_commalist ')' '(' column_def_commalist ')'
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.
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.
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.
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.
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.
If a parameter is specified but no applicable predicate is found, a NULL value is passed.
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.
Accessing a procedure as a table requires execute privileges on the procedure. Privileges declared on the view are not checked.
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.
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;
This returns a set of numbers from 2 to 11 and from 4 to 22.
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;
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.
create procedure view n_range as n_range (first, last) (n1 int, n2 decimal);
This defines the procedure as a view, so that it can be referenced like a table.
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;
This is the previous join but now using the view.
Previous
CHECKPOINT, SHUTDOWN Statement |
Chapter Contents |
Next
GRANT, REVOKE Statement |