# 9. SQL Procedure Language Guide

Chapter Contents | Prev | Next
Keyword Search:
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 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
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. 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.3. 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 signaled. 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.4. 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.5. 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.

Labelled statements (goto targets) and declarations can only occur within a compound statement.

See:

Create Procedure statement

### 9.7.6. 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.7. 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.8. 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.9. 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.10. 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.11. 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.12. 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.

Previous Exception Semantics | Chapter Contents | Next CREATE ASSEMBLY Syntax - External Libraries