Virtual procedures in the Data Virtuality Server are similar to stored procedures in relational database management systems: if you need to write and execute the same query from time to time, you can save it as a virtual procedure and then just call it. You can use virtual procedures to execute queries and other SQL commands, define temporary tables, add data to temporary tables, iterate through result sets, use loops, and use conditional logic. Virtual procedures are similar to scripts, but, unlike them, they support parameters, which makes them more powerful.
Virtual procedures are defined using the Data Virtuality Server procedural language. In this section, we describe operations with virtual procedures, and detailed descriptions of different statements used in our procedural language can be found in a dedicated section.
Creating Virtual Procedures
There are some important things to keep in mind about virtual procedures: first, a virtual procedure can be created only in a virtual schema (such as
views). It can be named or anonymous; a named procedure can be called from within an SQL statement, but has to be defined beforehand, and an anonymous procedure is called at definition time.
Second, a virtual procedure can contain SQL and procedural SQL commands. Within the body of the procedure, any valid statement may be used. An important point to remember is that inside any procedure, single semicolon (;) should be used as a separator, and two procedures should be separated by a double semicolon (;;).
The ; separator used inside the procedure cannot be configured. The ;; separator, however, can be changed to something else as long as the new separator is different from ;.
Third, a virtual procedure can have zero or more input parameters. If it is expected to output a result, it must include either an
OUT parameter or a
RETURNS() clause (we recommend using the latter), and it has a
ResultSet return type.
And this is how a named virtual procedure is created:
CREATE [PRIVATE] [VIRTUAL] PROCEDURE <schema_name>.<procedure_name>([[<parameter type>] <name> <data type> [DEFAULT <value>][<nullable>], ...]) [RETURNS (<name> <data type> [<nullable>], ... )] AS BEGIN <procedure code>; END;;
PRIVATE keyword is reserved for internal use and should not be specified for user-defined procedures.
Parameters are what makes virtual procedures more powerful than simple scripts. They are added in parentheses after procedure name in the following order: <parameter type> <parameter name> <data type>. Here's a simple example procedure to show what parameters may look like:
CREATE PROCEDURE views.anonymize(IN plain_text STRING NOT NULL) RETURNS (anonymized STRING NOT NULL) AS BEGIN SELECT LEFT(plain_text, 1) || '***' || RIGHT(plain_text, 1); END;;
Virtual procedures can take zero or more
INOUT parameters and may also have any number of
OUT parameters and an optional
RETURN parameter. Again, if the procedure is expected to output a result, either
OUT parameter or
RETURNS() should be included. Each input includes the following information that is used during runtime processing:
|Name||Name of the parameter|
|Data type||Design-time type of the input parameter|
|Parameter type||The evaluation strategy of the parameter. Can be one of |
|Default value||Default value if the input parameter is not specified|
This parameter is optional. Pass
If no value is assigned to the
INOUT parameter in a procedure, it will keep the value it was assigned for input. Any OUT/RETURN parameter to which no value has been assigned will have the default
NULL value. The
RETURN output values are validated against the
NOT NULL metadata of the parameter.
Here is an example:
CREATE VIRTUAL PROCEDURE views.proc1(IN i INTEGER DEFAULT '1') RETURNS (i INTEGER) AS BEGIN SELECT i; END ;; SELECT * FROM views.proc1();; --returns 1 SELECT * FROM views.proc1(2);; --returns 2
Viewing Virtual Procedures
All created virtual procedures are stored in the
SYSADMIN.ProcDefinitions schema. If you need to view all existing virtual procedures, you can use the following command (we list all parameters a procedure may have, but you can indicate only the parameters you're interested in):
SELECT "id", "name", "definition", "creationDate", "lastModifiedDate", "state", "failureReason", "creator", "modifier" FROM "SYSADMIN.ProcDefinitions";;
A virtual procedure may have one of the two states:
READY (meaning that all is well, the procedure may be used) or
FAILED. To check just the state of a given procedure, you can use this command:
SELECT "state" FROM "SYSADMIN.ProcDefinitions" WHERE name = '<procedure_name>';;
Executing Virtual Procedures
A virtual procedure can be executed using the
EXECUTE command. If the procedure has defined input parameters, these can be specified in a list or using the name=value syntax. If the parameter name is ambiguous in the context of other columns or variables in the procedure, scope the name of the input parameter by the full procedure name.
This is how a virtual procedure can be executed using the
CALL <schema_name>.<procedure_name>[( ... )];;
If a virtual procedure call is expected to return a result set, it works just like any
SELECT, so you can use this in many places where you can use a
SELECT statement. In this case, the procedure can be executed using the following command:
SELECT * FROM <schema_name>.<procedure_name>[( ... )]);;
Alternatively, you can use the following command:
SELECT * FROM (CALL <schema_name>.<procedure_name>[( ... )]) AS x;;
Altering Virtual Procedures
The statement to alter an existing virtual procedure is very similar to the one used to create it: basically, all you need to do is replace
ALTER and define the procedure anew.
ALTER [VIRTUAL] PROCEDURE <schema_name>.<procedure_name>( ... ) [RETURNS ( ... )] AS BEGIN <procedure code>; END;;
Deleting Virtual Procedures
This can be done using this very simple command:
DROP [VIRTUAL] PROCEDURE <schema_name>.<procedure_name>;;
Pass Key Value Pairs to a Procedure Using Multi-Dimensional Arrays for an example on how to use use multi-dimensional arrays to pass key value pairs to a procedure
Procedural SQL in a Data Virtualization Environment - a series of posts in the Data Virtuality blog focusing on use cases:
- SQL Stored Procedures
- Real-World Use Cases and Benefits
- Use cases for Stored Procedures in Data Virtuality