JBoss.orgCommunity Documentation

Chapter 5. Procedures

5.1. Procedure Language
5.1.1. Command Statement
5.1.2. Dynamic SQL Command
5.1.3. Declaration Statement
5.1.4. Assignment Statement
5.1.5. If Statement
5.1.6. Loop Statement
5.1.7. While Statement
5.1.8. Continue Statement
5.1.9. Break Statement
5.1.10. Error Statement
5.2. Virtual Procedures
5.2.1. Virtual Procedure Definition
5.2.2. Procedure Input Parameters
5.2.3. Examle Virtual Procedures
5.2.4. Executing Virtual Procedures
5.3. Update Procedures
5.3.1. Update Procedure Definition
5.3.2. Special Variables
5.3.3. Update Procedure Command Criteria
5.3.4. Update Procedure Processing

Teiid supports a procedural language for defining virtual procedures . These are similar to stored procedures in relational database management systems. You can use this language to define the transformation logic for decomposing INSERT, UPDATE, and DELETE commands against virtual tables; these are known as update procedures .

Dynamic SQL allows for the execution of an arbitrary SQL command in a virtual procedure. Dynamic SQL is useful in situations where the exact command form is not known prior to execution.


EXECUTE STRING <expression> [AS <variable> <type> [, <variable> <type>]* [INTO <variable>]] 
[USING <variable>=<expression> [,<variable>=<expression>]*] [UPDATE <literal>]        

Syntax Rules:

Here is an example showing a more complex approach to building criteria for the dynamic SQL string. In short, the virtual procedure AccountAccess.GetAccounts has inputs ID, LastName, and bday. If a value is specified for ID it will be the only value used in the dynamic SQL criteria. Otherwise if a value is specified for LastName the procedure will detect if the value is a search string. If bday is specified in addition to LastName, it will be used to form compound criteria with LastName.

Known Limitations and Work-Arounds

Virtual procedures are defined using the Teiid procedural language. A virtual procedure has zero or more input parameters, and a result set return type. Virtual procedures support the ability to execute queries and other SQL commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.



The CREATE VIRTUAL PROCEDURE line demarcates the beginning of the procedure. The BEGIN and END keywords are used to denote block boundaries. Within the body of the procedure, any valid statement may be used.

The last command statement executed in the procedure will be return as the result. The output of that statement must match the expected result set and parameters of the procedure.

You execute procedures using the SQL EXECUTE command. 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 elements or variables in the procedure.

A virtual procedure call will return a result set just like any SELECT, so you can use this in many places you can use a SELECT. However, within a virtual procedure itself you cannot always use an EXEC directly. Instead, you use the following syntax:


The following are some examples of how you can use the results of a virtual procedure call within a virtual procedure definition:

  • LOOP instruction - you can walk through the results and do work on a row-by-row basis

  • Assignment instruction - you can run a command and set the first column / first row value returned to a variable

  • SELECT * INTO #temp FROM (EXEC ...) AS x - you can select the results from a virtual procedure into a temp table, which you can then query against as if it were a physical table.

Virtual tables are abstractions above physical sources. They typically union or join information from multiple tables, often from multiple data sources or other views. Teiid can perform update operations against virtual tables. Update commands - INSERT, UPDATE, or DELETE - against a virtual table require logic to define how the tables and views integrated by the virtual table are affected by each type of command. This transformation logic is invoked when an update command is issued against a virtual table. Update procedures define the logic for how a user's update command against a virtual table should be decomposed into the individual commands to be executed against the underlying physical sources. Similar to virtual procedures , update procedures have the ability to execute queries or other commands, define temporary tables, add data to temporary tables, walk through result sets, use loops, and use conditional logic.

You can use a number of special variables when defining your update procedure.

You can use a number of special SQL clauses when defining UPDATE or DELETE procedures. These make it easier to do variable substitutions in WHERE clauses or to check on the change state of variables without using a lot of conditional logic.

You can use the TRANSLATE CRITERIA clause to convert the criteria from the user application’s SQL command into the form required to interact with the target source or view tables. The TRANSLATE CRITERIA statement uses the SELECT transformation to infer the element mapping. This clause evaluates to a translated criteria that is evaluated in the context of a command.


TRANSLATE [criteria operator] CRITERIA [ON (element list)] [WITH (mapping list)]

You can use these mappings either to replace the default mappings generated from the SELECT transformation or to specify a reverse expression when a virtual element is defined by an expression.

Some samples of the HAS TRANSLATE clause:

TRANSLATE CRITERIATranslates any user criteria using the default mappings.
TRANSLATE CRITERIA WITH (element1 = 'A', element2 = INPUT.element2 + 2)Translates any criteria with some additional mappings: element1 is always mapped to 'A' and element2 is mapped to the incoming element2 value + 2.
TRANSLATE = CRITERIA ON (element1)Translates only criteria that have = comparison operator and involve element1.

The TRANSLATE CRITERIA, ON clause always refers to virtual elements. The WITH clause always has items with form <elem> = <expression>, where the <elem> is a virtual element and the <expression> specifies what that virtual element should be replaced with when TRANSLATE CRITERIA translates the virtual criteria (from UPDATE or DELETE) into a physical criteria in the command. By default, a mapping is created based on the SELECT clause of the SELECT transformation (virtual column gets mapped to expression in SELECT clause at same position).