www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

SQL Procedure Language Guide

General Principles
Scope of Declarations
Data Types
Handling Result Sets
Result Sets and Array Parameters
Exception Semantics
Virtuoso/PL Syntax
Create Procedure Statement Grant Execute Statement Stored Procedures as Views & Derived Tables Keyword and Optional Procedure Arguments if, while, for, foreach statements compound statement goto, return statements whenever statement call, assignment statements open, fetch, close, select ... into statements FOR Select Statement SET statement SET Triggers
Execute Stored Procedures via SELECT statement
Execute Stored Procedures In Background
CREATE ASSEMBLY Syntax - External Libraries
CREATE PROCEDURE Syntax - External hosted procedures
Asynchronous Execution and Multithreading in Virtuoso/PL
Performance Tips
Procedures and Transactions
Distributed Transaction & Two Phase Commit
Triggers
Character Escaping
Virtuoso/PL Scrollable Cursors
Virtuoso PL Modules
Handling Conditions In Virtuoso/PL Procedures
Procedure Language Debugger
Row Level Security

9.7. Virtuoso/PL Syntax

9.7.1. Create Procedure Statement

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.

See Also:

CREATE PROCEDURE Syntax - External hosted procedures

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);
}

9.7.2. Grant Execute Statement

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:

User Type
Figure: 9.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.


9.7.3. Stored Procedures as Views & Derived Tables

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.

See Also:

For more information about Store Procedures as Views & Derived Tables go to the SQL Reference Chapter


9.7.4. Keyword and Optional Procedure Arguments

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

9.7.5. if, while, for, foreach statements

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;
}

9.7.6. compound statement

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.

See:

Create Procedure statement


9.7.7. goto, return statements

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.

See:

Create Procedure statement


9.7.8. whenever statement

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;
}
Note:

This is about the same as select count (*) from CUSTOMER where C_NAME = ?;


9.7.9. call, assignment statements

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);
}

9.7.10. open, fetch, close, select ... into statements

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.
See:

the TPC C Bench Marking chapter for more examples.


9.7.11. FOR Select Statement

<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.

Examples
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.


9.7.12. SET statement

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:


9.7.13. SET Triggers

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.

See Also:

SET statement.