Skip to main content
Skip table of contents

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:

SQL
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:

SQL
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:

Parameter

Description

NameName of the parameter
Data typeDesign-time type of the input parameter
Parameter typeThe evaluation strategy of the parameter. Can be one of IN , OUT or INOUT. Defaults to IN
Default valueDefault value if the input parameter is not specified
Nullable

This parameter is optional. Pass NOT NULL  to reject NULL  values

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:

SQL
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

Procedure DefinitionSYSADMIN.ProcedureParams.Type
OUTOut
OUT with RESULT notationReturnValue
INOUTInOut
RETURNSResultSet

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

SQL
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:

SQL
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:

SQL
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:

SQL
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:

SQL
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):

SQL
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:

SQL
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:

SQL
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:

SQL
SELECT * FROM <schema_name>.<procedure_name>[( ... )]);;

Alternatively, you can use the following command:

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

SQL
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:

SQL
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:


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.