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

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. You also have the possibility to apply arithmetic operations to calculate a value to a variable.

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

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>
   

Invoking the Procedure

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

SELECT * FROM new_procedure() WHERE <IN parameter #1> = <value>;

CALL new_procedure( <values of IN parameters> );

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.