Virtual Procedures

Virtual procedures in the CData 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 CData 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 [OR REPLACE] [PRIVATE] [VIRTUAL] PROCEDURE <schema_name>.<procedure_name>([[<parameter type>] <name> <data type> [DEFAULT <value>][<nullable>][RESULT], ...]) [RETURNS (<name> <data type> [<nullable>], ... )] AS
BEGIN
    <procedure code>;
END;;

The PRIVATE keyword is reserved for internal use and should not be specified for user-defined procedures.

Procedure Parameters

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 IN/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:

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 INOUT/OUT/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

Procedure Out/Return Values

A procedure can have only one ReturnValue parameter. The ReturnValue parameter can be obtained via the following statement:

CREATE VIRTUAL PROCEDURE views.proc1(OUT i INTEGER RESULT) AS BEGIN
	i = 1;
END ;;         

BEGIN
	DECLARE INTEGER var;
	var = EXEC views.proc1();
	SELECT var;
END;; -- returns 1

The OUT, INOUT, and ReturnValue parameters can be obtained via the following statement:

CREATE VIRTUAL PROCEDURE views.proc_outs(OUT i INTEGER RESULT, OUT j INTEGER, INOUT k INTEGER) AS BEGIN
	i = 1;
	j = 2;
	k = 3;
END ;;         

SELECT * FROM (EXEC views.proc_outs()) x;; -- returns 1, 2, 3

Another way to get the OUT and INOUT parameter values is as follows:

CREATE VIRTUAL PROCEDURE views.proc_in_inout(OUT i INTEGER, INOUT j INTEGER) AS BEGIN
	i = 1;
	j = 2;
END ;;

BEGIN
	DECLARE INTEGER var1;
	DECLARE INTEGER var2;
	EXEC views.proc_in_inout(i => var1, j => var2);
	SELECT var1, var2;
END;; -- returns 1, 2

The ResultSet parameters can be obtained as follows:

CREATE VIRTUAL PROCEDURE views.proc1() RETURNS(i INTEGER, j INTEGER) AS BEGIN
	SELECT 1, 2
	UNION
	SELECT 3, 4;
END ;;         

EXEC views.proc1();;
/* returns:
1 2
3 4
*/

-- or
SELECT * FROM (EXEC views.proc1()) x;;
-- returns the same

The OUT, INOUT, and ReturnValue parameters should not be used together with the RETURNS notation. If they are still used together, the values could be obtained by executing the following statement:

CREATE VIRTUAL PROCEDURE views.proc_out_returns(OUT i INTEGER, INOUT j INTEGER) RETURNS(a INTEGER, b INTEGER, c INTEGER) AS BEGIN
	i = 1;
	j = 2;
	SELECT 4, 5, 6;
END ;;

EXEC views.proc_out_returns();;
-- returns RETURNS result set: 4 5 6
SELECT * FROM (EXEC views.proc_out_returns()) x;;
-- returns RETURNS result set: 4 5 6

BEGIN
	DECLARE INTEGER var1;
	DECLARE INTEGER var2;
	EXEC views.proc_in_inout(i => var1, j => var2);
	SELECT var1, var2;
END;; -- returns OUT values 1, 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 CALL or 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 command:

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 CREATE with 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>;;

See Also

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 CData Virtuality blog focusing on use cases:

• SQL Stored Procedures
• Real-World Use Cases and Benefits
• Use cases for Stored Procedures in CData Virtuality