Developing VQL Stored Procedures¶

Virtual DataPort has a procedural language to create stored procedures that invoke VQL statements and use elements such as conditions and loops. These are called VQL procedures. This page explains how to create them.

The benefits of these procedures compared to Java stored procedures are:

• The SQL developers do not need to know Java to develop stored procedures.

• VQL procedures are easier to modify than Java procedures: you do not need to recompile a Java class, package it in a .jar file and import it again; you can modify the procedure from the Design Studio, the Administration Tool or any client application.

• It is easier for other users to review your procedure because they do not need to download the .jar file with the extension, open it and extract the Java class in another editor.

To open the wizard to create a VQL procedure, click the menu File > New… > Stored procedure > VQL stored procedure .

To create it programmatically (from the VQL Shell or another application), use the command CREATE VQL PROCEDURE.

CREATE [OR REPLACE] VQL PROCEDURE <name:identifier> (<variable:identifier> [IN/OUT/IN OUT:specification_type] <type:datatype> [, <variable:identifier> [IN/OUT/IN OUT] <type:datatype>]* )
AS (
    [ <localVariable:identifier> <type:identifier> [; <localVariable:identifier> <type:identifier>]* ]
)
BEGIN
    <command1>
    <command2>
        .
        .
        .
[EXCEPTION]
    <exception handling part>
END

This feature also supports the use of comments inside either where the local variables are declared or inside the part where procedure logic is defined (not before the input/output variables definition). There are multiple formats of comments that can be used inside VQL procedures:

• Single-line comments: begin the comment with two hyphens (--), a hash (#) or double slash (//). The rest of the line will be a comment.

• Multiline comments: begin the comment with a slash and two asterisks (/**) and end it with two asterisks and a slash (**/). This type of comments can span multiple lines.

The last mentioned comment format allows the definition of multi-line comments.

Data Types

These is the list of types of variables:

• Numeric: BIGINT, DECIMAL, DOUBLE PRECISION, FLOAT, INT, INTEGER, NUMBER, NUMERIC, REAL, SMALLINT

• Text: CHAR, NCHAR, NVARCHAR, VARCHAR

• Dates: DATE, TIMESTAMP, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND

• Other types: BOOL, ROWTYPE

With the specification type you can define an input or output variable. With an input variable you will need to assign a value when you try to execute the stored procedure. Otherwise, if the specification is OUT, that variable returns a value as a result of the execution.

Commands

These are the commands you can use in a VQL procedure (between the BEGIN and END keywords).

List of commands:

• variable_name := value With this instruction we can assign a value to a variable declared in the local variables declaration part. It is important to note that the value assigned to a variable must be of the same type as the variable and can be literals or expressions (arithmetic operations, concat of values…). You will also be able to assign to a local variable a special variable called SQL%ROWCOUNT. This will contain the number of rows affected by the execution of an INSERT, UPDATE or DELETE so it will only be useful to use after you define one of that statements in the body of your VQL Stored procedure.

  Assign value command example¶

  <variableVarchar> := <variable1> || <variable2>
  <variableNumeric> := <numeric1> + <numeric2>
  <variable> := function(<literal> | <variableAux>)
  

• IF/THEN/ELSE We can use an if structure to execute a list of commands when a condition is TRUE, or execute different commands if the condition evaluates to FALSE.

IF <condition> THEN
 <list of commands>
[ELSE]
  <list of commands>
END IF

• CASE WHEN The CASE statement has the functionality of an IF-THEN-ELSE statement. When one of the conditions is satisfied, the list of commands assigned is executed.

CASE [ identifier ]
 WHEN condition1 THEN <list of commands>
 WHEN condition2 THEN <list of commands>
[ELSE]
  <list of commands>
END CASE

• LOOP...END LOOP The LOOP statement has the functionality of executing several times a piece of code. We can use an optional EXIT WHEN statement to exit the loop when a condition is met. In the EXIT WHEN it is possible to use the NOTFOUND attribute in addition to conditions. This attribute will allow to exit a repeat loop when a cursor does not return any more rows of data. In that case the NOTFOUND attribute will be set to ‘true’ and the loop where the cursor values are returned will be exited.

LOOP
  <list of commands>
  [EXIT WHEN <condition>/EXIT WHEN <cursorName>%NOTFOUND]
END LOOP

• WHILE LOOP Another type of loop. Use this command when you are not sure how many times you will execute the loop body and the loop body may not execute even once.

WHILE <condition> LOOP
  <list of commands>
END LOOP

• FOR LOOP You can use the FOR LOOP statement when you want to execute a list of commands a specific number of times.

FOR numericVariable IN inferiorLimit .. superiorLimit LOOP
  <list of commands>
END LOOP

• INSERT INTO <viewName:identifier> (column1, column2...) VALUES (identifier1/literal1, identifier2/iteral2...). The INSERT statement is used to insert a single record or multiple records into a table.

• DELETE FROM <viewName:identifier> WHERE <condition>. The DELETE statement is used to delete a single record or multiple records from a table.

• UPDATE <viewName:identifier> SET (column1, column2...) = (valueColumn1, valueColumn2...) WHERE <condition>. The UPDATE statement is used to update existing records in a table.

• RETURN ROW (variable1, variable2...) VALUES (value1, value2...). Returns the values of the variables with OUT or IN OUT specification type defined in the header of the procedure. We can assign values in the form on another local variable or a literal value. With this instruction you will be able to see a result obtained as a result of executing the stored procedure.

• EXECUTE <DDL statement:literal>. Executes a DDL statement like CREATE OR REPLACE VIEW..., INSERT INTO <view> SELECT..., etc.

  To use this command, put the statement as a literal (i.e. put the statement surrounded with single quotes (') and escape them.

CREATE OR REPLACE VQL PROCEDURE procedureWithExecutionCommand
(oid IN INTEGER)
AS (
   varName VARCHAR;
)
BEGIN
   EXECUTE 'CREATE OR REPLACE VIEW v2 AS SELECT 1';
END;

Alternatively, you can parameterize the statement like in the example below, with the clause PARAMETERS (i.e. EXECUTE <literal> PARAMETERS ( <parameterizedValueName> ) VALUES ( <value> )).

CREATE OR REPLACE VQL PROCEDURE procedureWithExecutionCommand
(oid IN INTEGER)
AS (
   varName VARCHAR;
)
BEGIN
   EXECUTE 'CREATE OR REPLACE VIEW v2 AS SELECT @param' PARAMETERS (param) VALUES(oid);
END;

The VQL stored procedures also support cursors. A cursor processes a SELECT statement and stores in the cursor the results of that query. The steps for using a cursor in a stored procedure are as follows:

1. Define in the declaration area a variable of type cursor. The syntax for that is the following one: CURSOR <cursoName:literal> IS '<SELECT statement>'. You will have the possibility to create cursors without having to indicate the query as a literal. This would allow us to create cursors with parameterized queries. For example: CURSOR <cursorName:literal> IS 'SELECT * FROM table WHERE x = :param1'.

2. Once the cursor is defined, if we want to retrieve the results obtained as a result of the SELECT statement we will first need to open the cursor with the OPEN <cursorName>. If you created the cursor with the SELECT query using parameterized query you will need to indicate the value assigned to the parameterized value. With the example of the previous step in mind you would need to do something like: OPEN <cursorName> PARAMETERS (param1) VALUES (<literal>).

3. After the cursor is opened we can start retrieving the results obtained using the command FETCH <cursorName> INTO variables/list of variables. This instruction will allow you to retrieve values of a row inserting the values of the different columns in a list of variables.

4. Once you have finished using it you will have to close the cursor. To do that you will be able to use the CLOSE <cursorName> command.

In the VQL stored procedures you can declare your own exceptions. To do it, follow this steps:

1. Define in the declaration area a variable of type EXCEPTION.

2. Once this variable is defined, raise this exception in the body of the stored procedure with RAISE <variableName>

3. Define in the optional EXCEPTION area of the VQL stored procedure what commands will be executed when the exception is raised. The syntax for that is the following one:

   Syntax of the EXCEPTION section of the stored procedure¶

   EXCEPTION
      WHEN exception1 THEN
         <list of commands>
      [ WHEN OTHERS THEN ]
         <list of commands>
   

Example of stored procedure with handling of custom exceptions:

CREATE OR REPLACE VQL PROCEDURE procedureWithExceptionHandling
(param1 IN VARCHAR, param2 OUT VARCHAR)
AS (
   name VARCHAR;
   exception1 EXCEPTION;
)
BEGIN
    name := 'Test';
    IF name = 'Test' THEN
        RAISE exception1;
    END IF;

    EXCEPTION
        WHEN exception1 THEN
        name := param1;
        RETURN ROW (param2) VALUES (name);
END;

Transactions

VQL stored procedures can manage transactions.

These are the commands to manage transactions from a VQL procedure:

• BEGIN_TRANSACTION: starts a transaction.

• COMMIT: the current transaction finishes and all the changes made during the transaction are applied.

• ROLLBACK: the transaction finishes and the changes are rollbacked.

CREATE OR REPLACE VQL PROCEDURE procedureWithTransactionSupport
(param1 IN VARCHAR, param2 OUT VARCHAR)
AS (
   name VARCHAR;
)
BEGIN
    BEGIN_TRANSACTION;
    INSERT INTO idu_view_vql_sp(idtable, name) VALUES (5, 'Name example');
    ROLLBACK;

END;

The page Transactions in Virtual DataPort explains how transactions work.

Invoking the Procedure

To invoke a VQL stored procedure, you use the same syntax as with other procedures:

See Invoking Denodo Stored Procedures.

Examples

Example 1

CREATE OR REPLACE VQL PROCEDURE procedure_max_value
(oid IN INTEGER, valormax OUT INTEGER)
AS (
    a INTEGER;
    b INTEGER;
    c INTEGER;
    abcmax INTEGER;
)
BEGIN
    a:=oid;
    b:=(a/5);
    c:=(b*7);
    IF a > b THEN
      IF a > c THEN
         abcmax:=a;
      ELSE
         abcmax:=c;
      END IF;
    ELSE
      IF b > c THEN
       abcmax:=b;
      ELSE
       abcmax:=c;
      END IF;
    END IF;
    RETURN ROW (valormax) VALUES (abcmax);
END;

Stored procedure that returns the max value depending on the value passed by the user when the stored procedure is called.

Example 2

CREATE OR REPLACE VQL PROCEDURE procedureUsingCursors
(idInternet OUT INTEGER, ttime OUT TIMESTAMP, idtax OUT VARCHAR)
AS (
  CURSOR cursorData IS 'SELECT iinc_id, ttime, taxid FROM internet_inc WHERE specific_field1 > 1';
  rinternet cursorData%ROWTYPE;
)
BEGIN
    OPEN cursorData;
    LOOP
      FETCH cursorData INTO rinternet;
      RETURN ROW ( idInternet, ttime, idtax) VALUES (rinternet.iinc_id, rinternet.ttime, rinternet.taxid);
      EXIT WHEN cursorData%NOTFOUND;
    END LOOP;
    CLOSE cursorData;
END
;

This example shows how to use cursors. The procedure exits the LOOP statement when the cursor does not return any more rows of data.

Get debug details when executing a procedure

When executing VQL stored procedures and want to get step by step information about the status of the execution do it by enabling the logs related to this specific feature. To enable the logs set to INFO or DEBUG the logs related to VQL stored procedures depending on the level of details to obtain:

CALL logcontroller( 'com.denodo.vdb.engine.storedprocedure.CommandExecutorVisitorImpl', 'DEBUG');