CREATE PROCEDURE NAME (parameter , parameter...) [RETURNS data_type]
{ statement ... }
parameter: parameter_type name data_type opt_default
parameter_type: IN | OUT | INOUT
opt_default: | DEFAULT literal | := literal
The create procedure statement actually performs a "create or replace" type operation. The create procedure statement compiles and stores a Virtuoso/PL procedure. The procedure text is first parsed and compiled into Virtuoso virtual machine code and if the compilation is successful the text is stored into the SYS_PROCEDURES table. This table is read at startup. Stored procedures are thus always available for use and need be defined only once. New procedures created with the same name as existing procedures automatically replace their predecessor.
CREATE PROCEDURE FIBO (IN X INTEGER)
{
IF (X < 2)
RETURN X;
ELSE
RETURN (FIBO (X - 1) + FIBO (X - 2));
}
CREATE PROCEDURE CFIBO (IN X INTEGER)
{
DECLARE RES INTEGER;
RES := FIBO (X);
RESULT_NAMES (RES);
RESULT (RES);
}
GRANT EXECUTE ON proceudre_name TO grantee_commalist;
The grantee should have SQL rights in order execution of procedure to be granted to this user. The rights can be set from Conductor->System Admin->User Accounts->Account->Edit->User Type:
|
| Figure: 11.7.2.1. User Type |
Example
SQL>create procedure DB.DBA.SimplePrint (in txt varchar)
{
return sprintf('Output is %s', txt);
}
;
Done. -- 0 msec.
SQL>grant execute on DB.DBA.SimplePrint to "demo";
Done. -- 0 msec.
SQL>use demo;
Done. -- 0 msec.
SQL>select DB.DBA.SimplePrint('Virtuoso');
callret
VARCHAR
_______________________________________________________________________________
Output is Virtuoso
1 Rows. -- 0 msec.
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.
For more information about Store Procedures as Views & Derived Tables go to the SQL Reference Chapter
Normally arguments in a procedure call are bound to formal parameters from left to right, as is the default behavior in any programming language. If a default value is specified for a parameter in the procedure definition this parameter is optional and the default value will be assigned to it if the caller does not specify a value. A call may consist of zero or more positional arguments followed by zero or more keyword arguments. A positional argument is any scalar expression. A keyword argument is marked with the syntax:
NAME => scalar_exp
This notation specifies that the expression is to be bound to the parameter NAME in the procedure declaration. The names are matched case-insensitively in all case modes. After all leading positional arguments have been bound to the matching formal parameters in the procedure definition, each keyword argument is bound to the parameter of the same name. After this all unbound formal parameters are assigned to their default values. If a parameter with no default remains unbound an error is signalled. OUT and INOUT parameters are always required, regardless of the mode of calling.
An expression can be passed as INOUT or OUT, but in that case the output value assigned by the procedure is not accessible in the caller. The output value is only accessible if the actual parameter is a variable or parameter.
Arguments of procedures are always evaluated left to right.
create procedure kwd (in k1 int := 111, inout k2 int, in k3 int := 333)
{
result_names (k1, k2, k3);
result (k1, k2, k3);
}
kwd (1,1+1,3);
-- results 1,2,3
kwd ();
-- error because inout parameters are always required
kwd (k2=>1);
-- error because a constant is not a suitable value for an inout parameter.
kwd (k2=>1+2);
-- result 111, 2, 333
kwd (k3=>3, k1=>1,k2=>1+1);
-- result 1, 2, 3
kwd (1, k2=>1+1);
-- result 1, 2, 333
kwd (1);
-- error, k2 is required
kwd (badkey=>2, k2=>2+1);
-- error, badkey not a parameter of the function
create procedure kwd2 (in k1 int , in k2 int, in k3 int)
{
result_names (k1, k2, k3);
result (k1, k2, k3);
}
kwd2 (k1=>1, k2=>2, k3=>3);
-- result 1, 2, 3
kwd2 (1,2,3);
-- result 1, 2, 3
if_statement
: IF '(' search_condition ')' statement opt_else
opt_else
: /* empty */
| ELSE statement
while_statement
: WHILE '(' search_condition ')' statement
for_statement
: FOR '(' for_init_statement_list ';' for_opt_search_cond ';' for_inc_statement_list ')' statement
| FOREACH '(' data_type_ref identifier IN_L scalar_exp ')' DO statement
The IF statement executes the immediately following statement if the condition is true. If there is an else clause and the condition is false the statement immediately following the else keyword will be executed.
The while statement evaluates the search condition and executes the following statement if the condition is true. It does this as long as the condition is true. To exit from a loop, use goto. C-like break and continue statements are not available.
The for statement initiates the for_init_statement_list and executes the following statement until the search condition is true. After every execution of the statement it executes for_inc_statement_list. You can exit the loop with using goto syntax also.
The foreach statement executes the statement for each element from an array and sets a variable to the corresponding element of that array.
IF (A > B)
A := A + 1;
ELSE
B := B + 1;
WHILE (1 = 1) {
A := A + 1;
}
FOR (declare X any, X := 1; X <= 2 ; X := X + 1){
S := S + X;
}
FOR (declare X any, X := 1; X <= 2 ; ){
S := S + X;
X := X + 1;
}
FOR (declare X any, X := 1; ; X := X + 1){
if (X > 2)
goto exit_loop;
S := S + X;
}
exit_loop:
declare X integer;
X := 1;
FOR (; X <= 2 ; X := X + 1){
S := S + X;
}
ARR := vector (1,2);
FOREACH (int X in ARR) do {
S := S + X;
}
compound_statement
: '{' statement_list '}'
;
statement_list
: statement_in_cs
| statement_list statement_in_cs
;
statement_in_cs
: local_declaration ';'
| compound_statement
| routine_statement ';'
| control_statement
| label ':' statement
;
statement
: routine_statement ';'
| control_statement
| compound_statement
;
local_declaration
: cursor_def
| variable_declaration
| handler_declaration
;
variable_declaration
: DECLARE variable_list data_type
;
variable_list
: NAME
| variable_list ',' NAME
;
The compound statement is the main building block of procedures. Statements in a compound statement are executed left to right, unless the flow of control is changed with a goto statement. The compound statement allows declaring local variables and exception handlers. See 'Scope Rules' above for a description of the scope of declarations.
Labeled statements (goto targets) and declarations can only occur within a compound statement.
goto_statement : GOTO label ; label : NAME return_statement : RETURN scalar_exp | RETURN ;
The goto statement unconditionally transfers control to the label following it. The label can be anywhere within the same procedure. It is in principle possible to jump into a block (e.g. loop body) from outside.
The return statement causes the executing procedure to return. If a return value is specified the expression is evaluated and returned as the return value of the procedure. If no return value is specified the procedure returns an undefined value.
Returning from a procedure automatically frees any resources associated with the procedure. This includes values in local variables or call by value (IN) parameters and any cursors that may be open.
condition : NOT FOUND | SQLSTATE STRING ; handler_declaration : WHENEVER condition GOTO NAME ;
This declares that control should be transferred to a particular label in the procedure whenever a condition occurs within the lexical scope of the WHENEVER declaration. This is similar to the statement of the same name found in most embedded SQL implementations.
The scope of the declaration is all the lines lexically following the declaration. A previous declaration is replaced by a new declaration for the same <condition>.
CREATE PROCEDURE COUNT_CUSTOMERS (IN C_NAME VARCHAR)
{
DECLARE COUNT INTEGER;
COUNT := 0;
DECLARE C CURSOR FOR SELECT C_ID FROM CUSTOMER WHERE C_NAME = C_NAME;
WHENEVER SQLSTATE '4001' GOTO DEADLOCK;
WHENEVER NOT FOUND GOTO DONE;
OPEN C;
WHILE (1=1)
{
FETCH C INTO N;
COUNT := COUNT + 1;
}
DONE:
RETURN COUNT;
DEADLOCK:
RETURN -1;
}
This is about the same as select count (*) from CUSTOMER where C_NAME = ?;
function_call
: NAME '(' opt_scalar_exp_commalist ')'
| call '(' scalar_exp ')' '('opt_scalar_exp_commalist ')'
;
call_statement
: CALL NAME '(' opt_scalar_exp_commalist ')'
| function_call
;
assignment_statement
: lvalue EQUALS scalar_exp
| lvalue '=' scalar_exp
;
lvalue : NAME
The call statement calls a specified procedure with the given arguments. The procedure to call is resolved at run time, i.e. the latest definition prevails, even if it has been made after the calling procedure was defined. The CALL reserved word is optional and is supported for compatibility.
If the called procedure has reference parameters (OUT or INOUT) the matching actual parameter must be a variable or parameter.
There is a computed function call form of function_call. In this, the scalar expression in parentheses following the call keyword should evaluate to a string which then identifies the function to be called.
The assignment statement sets a value to a variable. The variable must be either a local variable declared with declare or a procedure argument declared in the procedure argument list. If the variable in question is a reference parameter the assignment takes effect in the actual parameter as will, i.e. the value of the argument variable in the caller is set.
CREATE PROCEDURE COMPUTED_CALL (IN Q INTEGER)
{
DECLARE FN VARCHAR;
FN := 'F';
--- CALL FUNCTION FF WITH ARGUMENT 11.
R := CALL (CONCATENATE (FN, 'F')) (11);
}
SELECT opt_all_distinct selection
INTO target_commalist
table_exp
with_opt_cursor_options_list
;
opt_all_distinct
: /* empty */
| ALL
| DISTINCT
;
with_opt_cursor_options_list
: /* empty */
| WITH opt_cursor_options_list
;
cursor_option
: EXCLUSIVE
;
cursor_options_commalist
: cursor_option
| cursor_options_commalist ',' cursor_option
;
opt_cursor_options_list
: /* empty */
| '(' cursor_options_commalist ')'
;
cursor_def : DECLARE NAME CURSOR FOR query_exp
| DECLARE NAME (DYNAMIC|KEYSET|STATIC) CURSOR FOR query_exp
open_statement
: OPEN cursor opt_cursor_options_list
;
fetch_statement : FETCH cursor INTO target_commalist
| FETCH cursor (FIRST|NEXT|PREVIOUS|LAST) INTO target_commalist
| FETCH cursor BOOKMARK scalar_exp INTO target_commalist
target_commalist
: variable
| target_commalist ',' variable
;
close_statement
: CLOSE cursor
;
The open, fetch and close statements manipulate cursors in Virtuoso/PL statements. Cursors are declared with the declare cursor statement. The select into statement is a shorthand for a cursor declaration, open, fetch and close.
A forward-only cursor declaration is a declaration only and executing one does not take time. The open statement effectively starts the search associated with the forward-only cursor.
The forward-only cursor options used with open and select into allow controlling how the cursor sets locks on selected rows and how many rows it fetches at a time. The EXCLUSIVE option should be used if intending to update or delete a row in the cursor's evaluation. This causes selected rows to be locked with exclusive (write) locks.
The statements:
{
DECLARE CR CURSOR FOR SELECT C_NAME FROM CUSTOMER WHERE C_ID = ID;
OPEN CR;
FETCH CR INTO NAME;
CLOSE CR;
}
and
SELECT C_NAME INTO NAME FROM CUSTOMER WHERE C_ID = ID;
have the same effect.
the TPC C Bench Marking chapter for more examples.
<for statement> ::=
FOR <query exp> DO statement
The FOR statement provides a compact notation for iterating over the result set of a cursor. The body is executed once for each row in the query expression's result set. The result columns produced by the query expression are accessible as variables of the same name inside the body. All result columns do therefore have to be named with the AS declaration if they are not simple columns, in which case the name defaults to the column's name.
The body can be exited in mid loop with a goto. The cursor of the FOR does not have to be specifically closed or opened. FOR statements can be freely nested. If a WHENEVER NOT FOUND declaration is in effect before the FOR it will be canceled by it, so that it is not in effect after the loop's body.
for select C_NAME, sum (O_VALUE) as value from CUSTOMER, ORDER group by C_NAME DO
{
result (C_NAME, value);
})
The equivalent code is
declare C_NAME, value any;
whenever not found goto done;
declare cr cursor for select ....;
open cr;
while (1) {
fetch cr into C_NAME, value;
whenever not found default;
...
}
done: ;
The cursor and end label names are generated to be unique by the FOR expansion.
Set_statement: SET option '=' scalar_exp | SET option OFF | SET option ON ; option: ISOLATION | LOCK_ESCALATION_PCT | TRIGGERS | PARAM_BATCH ;
The SET statement sets an option to a value. Options may control trigger invocation, transaction isolation and other settable parameters of the engine. A SET inside a procedure takes effect inside the procedure and invoked procedures, counting from time of execution. Control must pass through the SET statement for it to take effect, i.e. SET is not a declaration. The effect of a SET does typically not persist across procedure return.
A SET given at top level, i.e. directly executed and by a client as the statement of a SQLExecute sets an option at the connection level. This may only be reversed by another SET.
The option may be:
A value of OFF or 0 causes triggers not to be invoked even if there may be applicable triggers. This is mostly useful for controlling recursion of triggers or for debugging triggers.
The value of TRIGGERS is passed into called procedures but other options are not.
Note: This feature only applies to Virtuoso 7.0 and later.
A stored procedure may be declared vectored. This means that when called from a statement operating on multiple values, a single call of the procedure can take the whole batch of variable bindings the statement is operating on in a single invocation. This saves invocation and interpretation overhead, and, most importantly, allows running any SQL statements inside the procedure on multiple values at once, creating possibilities for parallelization and exploitation of locality. The vectored declaration consists of the VECTORED reserved word at the start of the procedure body.
Consider the example of a lookup table:
CREATE TABLE person
(
p_id INT PRIMARY KEY,
p_name VARCHAR
);
CREATE TABLE knows
( p1 INT REFERENCES PERSON,
p2 INT REFERENCES PERSON,
PRIMARY KEY ( p1, p2 )
);
CREATE PROCEDURE p_name
( IN code INT )
RETURNS VARCHAR
{
VECTORED;
RETURN
(
SELECT p_name
FROM person
WHERE p_id = id
) ;
}
SELECT p_name (p1)
FROM knows
WHERE p2 = 123;
This last statement is equivalent to:
SELECT p_name
FROM knows,
person
WHERE p_id = p1
AND p2 = 123;
For non-trivial transformations, hiding the logic inside a procedure makes sense, Running the procedure vectored makes it so that efficiency is not lost. For example, if person 123 knows 1000 people, there will not be 1000 random lookups in person for the names but rather a single, vectored, merge-style lookup, accessing the rows in order of ID, saving time if the IDs are nearby each other. Furthermore, if the lookup is in READ COMMITTED isolation, the multiple lookups can be scheduled on multiple threads.
The restrictions for vectored statement bodies also apply to function bodies that are declared vectored.
A vectored procedure can be called from a non-vectored procedure. In this case, the vectored procedure simply executes on a single set of values, as if it were not vectored.
A vectored procedure can call a non-vectored procedure. When this happens, the non-vectored procedure is called once for each set of eligible values, i.e., once for the first values of the arguments, once for the second values of the arguments, and so forth.
A vectored procedure, if called from vectored code, returns a return value for each set of arguments.
A vectored procedure can have IN and OUT parameters. These have the same semantics as in single-value execution. When calling a non-vectored procedure with an OUT or INOUT parameter, the argument in the vectored caller must be declared to be of a boxed data type. (See the section on vectoring and data types below.)
Note: This feature only applies to Virtuoso 7.0 and later.
FOR VECTORED
( {IN|OUT} <variable> <data_type> [ := <value>],
...
)
<compound_statement>
The FOR VECTORED statement allows executing a block of code on several sets of variable bindings at once. The benefit of this is that any database operations in such a block can be run on multiple sets of parameters at once, allowing exploitation of locality and, in some cases, running the operation on different bindings on different threads. Additionally, if vectored procedures are called from inside such a block, the call is made with multiple bindings for the parameters. The input variables of FOR VECTORED are initialized from an array of scalar values. The statements inside the body are then executed vectored, as if the operation were first made on all the first values of the vectors, then on the second values, and so forth. Operations combining values from different places in the vectors are not possible in the FOR VECTORED body, but, since vectored results can be seen as arrays after the return of FOR VECTORED, any aggregation or comparison between values in different positions of the same vector can be done after the FOR VECTORED, simply accessing different elements of the arrays produced.
The FOR VECTORED statement communicates with its environment through a list of input and output variables. The input variables are marked with the syntax:
IN <variable> <data_type> := <value>
The <value> must be an expression evaluating to an array. The data type must correspond to the element type of the array. When multiple input variables are specified, the arrays initializing each must be of equal length.
An output variable is marked with:
OUT <variable> := <value>
The variable must be declared in a context outside of the FOR VECTORED statement. The value of the variable will be an array where each value of the vectored expression <value> is represented as a separate value.
Variables declared outside of a FOR VECTORED statement are visible in the body of FOR VECTORED and they appear as a single value for all rows of the vectored section.
Consider the task of pair-wise adding the elements of two arrays:
CREATE PROCEDURE a_add
(
IN a1 INT ARRAY ,
IN a2 INT ARRAY
)
{
DECLARE res INT ARRAY ;
res := make_array (LENGTH (a1, ' any' );
FOR (i := 0; i < LENGTH (a1); i := i + 1)
res[i] := a1[i] + a2[i];
RETURN res;
}
This can be expressed as:
CREATE PROCEDURE a_add_v
(
IN a1 INT ARRAY ,
IN a2 INT ARRAY
)
{
DECLARE res INT ARRAY;
FOR VECTORED
( IN n1 INT := a1 ,
IN i2 INT := a2 ,
OUT res := r
)
{
DECLARE r INT ;
r := i1 + i2 ;
}
RETURN res;
}
The two procedures are identical in function. The second will make use of vector instructions in the host CPU, if available, and will incur less interpretation overhead, since the SQL run time will not need to run a loop. In practice, substantial benefit, up to an order of magnitude, can be had from vectored execution with database operations exhibiting significant locality. Bulk loads and bulk lookups are a typical example.
Note: This feature only applies to Virtuoso 7.0 and later.
The body of FOR VECTORED or a vectored procedure may contain arbitrary Virtuoso PL, except for LOOPs and backward GOTOs. Conditional expressions and statements are allowed, as well as any subqueries or DML statements. Looping over a cursor is not allowed, since this is a loop, but scalar subqueries and selecting-into-variables in SELECT ... INTO is allowed. Exception handlers are not allowed inside, but an exception handler outside of FOR VECTORED will catch errors signaled from inside FOR VECTORED. FOR VECTORED statements may not be nested and may not occur in the body of a vectored procedure. The handler, being itself not in vectored code, will not be able to see which specific value in a vectored section gave rise to the exception.
Note: This feature only applies to Virtuoso 7.0 and later.
Parameters in vectored procedures or FOR VECTORED blocks can be declared to be of the corresponding scalar data type. The vectoring is thus in most cases transparent; the variable will simply have multiple scalar values instead of one. The ANY type in a vectored code section is represented as an array of serialized values. Thus types that are represented as data structures in allocated memory (e.g., arrays, hash tables, XML elements, etc.) will not work efficiently with ANY vectored variables. In some cases (for example, with streams or dictionaries), assigning to a vectored ANY will lose the information.
Therefore, if dealing with vectors of complex data types in vectored code, the variable holding these must be declared as an ANY ARRAY. With this type, the representation will be an array of pointers to allocated memory, not an array of flat serialized values. The ANY ARRAY type must be used instead of the customary ANY in all cases involving complex values in vectored code. If dealing with vectors of simple scalars like strings or numbers, the ANY type is generally more efficient.
|
Previous
Exception Semantics |
Chapter Contents |
Next
Execute Stored Procedures via SELECT statement |
