BEGIN
MySchema.GetBalance.RetVal = UPPER(MySchema.GetBalance.AcctID);
SELECT Balance FROM MySchema.Accts WHERE MySchema.Accts.AccountID = MySchema.GetBalance.AcctID;
END
Virtual procedures
Virtual procedures are defined using the Teiid procedural language. For more information, see Procedure language.
A virtual procedure has zero or more INPUT, INOUT, or OUT parameters, an optional RETURN parameter, and an optional result set. Virtual procedures can execute queries and other SQL commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.
For more information, see Create procedure/function in DDL metadata for schema objects.
Note that the optional result parameter is always considered the first parameter.
Within the body of the procedure, you can use any valid statement. For more information avbout procedure language statements, see Procedure language.
There is no explicit cursoring or value returning statement. Instead, the last unnamed command statement executed in the procedure that returns a result set will be returned as the result. The output of that statement must match the expected result set and parameters of the procedure.
Virtual procedures can take zero or more IN
or INOUT
parameters, and can have any number of OUT
parameters and an optional RETURN
parameter.
Each input has the following information that is used during runtime processing:
- Name
-
The name of the input parameter.
- Datatype
-
The design-time type of the input parameter.
- Default value
-
The default value if the input parameter is not specified.
- Nullable
-
NO_NULLS
,NULLABLE
,NULLABLE_UNKNOWN
; parameter is optional if nullable, and is not required to be listed when using named parameter syntax.
You reference a parameter in a virtual procedure by using its fully-qualified name (or less if unambiguous). For example, MySchema.MyProc.Param1
.
GetBalance
procedureIf an INOUT
parameter is not assigned any value in a procedure, it will retain the value it was assigned for input.
Any OUT/RETURN
parameter that is not assigned a value will retain the default NULL value.
The INOUT/OUT/RETURN
output values are validated against the NOT NULL
metadata of the parameter.
The following example represents a loop that walks through a cursored table and uses CONTINUE
and BREAK
.
BEGIN
DECLARE double total;
DECLARE integer transactions;
LOOP ON (SELECT amt, type FROM CashTxnTable) AS txncursor
BEGIN
IF(txncursor.type <> 'Sale')
BEGIN
CONTINUE;
END ELSE
BEGIN
total = (total + txncursor.amt);
transactions = (transactions + 1);
IF(transactions = 100)
BEGIN
BREAK;
END
END
END
SELECT total, (total / transactions) AS avg_transaction;
END
The following example uses conditional logic to determine which of two SELECT statements to execute.
BEGIN
DECLARE string VARIABLES.SORTDIRECTION;
VARIABLES.SORTDIRECTION = PartsVirtual.OrderedQtyProc.SORTMODE;
IF ( ucase(VARIABLES.SORTDIRECTION) = 'ASC' )
BEGIN
SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID;
END ELSE
BEGIN
SELECT * FROM PartsVirtual.SupplierInfo WHERE QUANTITY > PartsVirtual.OrderedQtyProc.QTYIN ORDER BY PartsVirtual.SupplierInfo.PART_ID DESC;
END
END
You execute procedures using the SQL EXECUTE
command. For more information, see Execute command in DML commands.
If the procedure has defined inputs, you specify those in a sequential list, or using name=value syntax. You must use the name of the input parameter, scoped by the full procedure name if the parameter name is ambiguous in the context of other columns or variables in the procedure.
A virtual procedure call returns a result set like any SELECT
, so you can use this in many places you can use a SELECT
.
Typically you’ll use the following syntax:
SELECT * FROM (EXEC ...) AS x
A virtual procedure can return only one result set. If you need to pass in a result set, or pass out multiple result sets, then consider using global temporary tables instead.