-
Language:
English
-
Language:
English
Red Hat Training
A Red Hat training course is available for Red Hat JBoss Data Virtualization
3.9. Procedural Language
3.9.1. Procedural Language
3.9.2. Command Statement
command [(WITH|WITHOUT) RETURN];
Example 3.8. Example Command Statements
SELECT * FROM MySchema.MyTable WHERE ColA > 100 WITHOUT RETURN; INSERT INTO MySchema.MyTable (ColA,ColB) VALUES (50, 'hi');
var = EXEC proc...
. To access OUT or IN/OUT values named parameter syntax must be used. For example, EXEC proc(in_param=>'1', out_param=>var)
will assign the value of the out parameter to the variable var. It is expected that the data type of parameter will be implicitly convertible to the data type of the variable.
3.9.3. Dynamic SQL
EXECUTE IMMEDIATE <expression> [AS <variable> <type> [, <variable> <type>]* [INTO <variable>]] [USING <variable>=<expression> [,<variable>=<expression>]*] [UPDATE <literal>]
Syntax Rules:
- The "AS" clause is used to define the projected symbols names and types returned by the executed SQL string. The "AS" clause symbols will be matched positionally with the symbols returned by the executed SQL string. Non-convertible types or too few columns returned by the executed SQL string will result in an error.
- The "INTO" clause will project the dynamic SQL into the specified temp table. With the "INTO" clause specified, the dynamic command will actually execute a statement that behaves like an INSERT with a QUERY EXPRESSION. If the dynamic SQL command creates a temporary table with the "INTO" clause, then the "AS" clause is required to define the table's metadata.
- The "USING" clause allows the dynamic SQL string to contain variable references that are bound at runtime to specified values. This allows for some independence of the SQL string from the surrounding procedure variable names and input names. In the dynamic command "USING" clause, each variable is specified by short name only. However in the dynamic SQL the "USING" variable must be fully qualified to "DVAR.". The "USING" clause is only for values that will be used in the dynamic SQL as legal expressions. It is not possible to use the "USING" clause to replace table names, keywords, etc. This makes using symbols equivalent in power to normal bind (?) expressions in prepared statements. The "USING" clause helps reduce the amount of string manipulation needed. If a reference is made to a USING symbol in the SQL string that is not bound to a value in the "USING" clause, an exception will occur.
- The "UPDATE" clause is used to specify the updating model count. Accepted values are (0,1,*). 0 is the default value if the clause is not specified. See Section 6.3, “Updating Model Count”.
Example 3.9. Example Dynamic SQL
... /* Typically complex criteria would be formed based upon inputs to the procedure. In this simple example the criteria is references the using clause to isolate the SQL string from referencing a value from the procedure directly */ DECLARE string criteria = 'Customer.Accounts.Last = DVARS.LastName'; /* Now we create the desired SQL string */ DECLARE string sql_string = 'SELECT ID, First || '' '' || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || criteria; /* The execution of the SQL string will create the #temp table with the columns (ID, Name, Birthdate). Note that we also have the USING clause to bind a value to LastName, which is referenced in the criteria. */ EXECUTE IMMEDIATE sql_string AS ID integer, Name string, Birthdate date INTO #temp USING LastName='some name'; /* The temp table can now be used with the values from the Dynamic SQL */ loop on (SELCT ID from #temp) as myCursor ...
Example 3.10. Example Dynamic SQL with USING clause and dynamically built criteria string
... DECLARE string crit = null; IF (AccountAccess.GetAccounts.ID IS NOT NULL) crit = '(Customer.Accounts.ID = DVARS.ID)'; ELSE IF (AccountAccess.GetAccounts.LastName IS NOT NULL) BEGIN IF (AccountAccess.GetAccounts.LastName == '%') ERROR "Last name cannot be %"; ELSE IF (LOCATE('%', AccountAccess.GetAccounts.LastName) < 0) crit = '(Customer.Accounts.Last = DVARS.LastName)'; ELSE crit = '(Customer.Accounts.Last LIKE DVARS.LastName)'; IF (AccountAccess.GetAccounts.bday IS NOT NULL) crit = '(' || crit || ' and (Customer.Accounts.Birthdate = DVARS.BirthDay))'; END ELSE ERROR "ID or LastName must be specified."; EXECUTE IMMEDIATE 'SELECT ID, First || '' '' || Last AS Name, Birthdate FROM Customer.Accounts WHERE ' || crit USING ID=AccountAccess.GetAccounts.ID, LastName=AccountAccess.GetAccounts.LastName, BirthDay=AccountAccess.GetAccounts.Bday; ...
3.9.4. Dynamic SQL Limitations
- The use of dynamic SQL command results in an assignment statement requires the use of a temp table.
Example 3.11. Example Assignment
EXECUTE IMMEDIATE <expression> AS x string INTO #temp; DECLARE string VARIABLES.RESULT = (SELECT x FROM #temp);
- The construction of appropriate criteria will be cumbersome if parts of the criteria are not present. For example if "criteria" were already NULL, then the following example results in "criteria" remaining NULL.
Example 3.12. Example Dangerous NULL handling
... criteria = '(' || criteria || ' and (Customer.Accounts.Birthdate = DVARS.BirthDay))';
The preferred approach is for the user to ensure the criteria is not NULL prior its usage. If this is not possible, a good approach is to specify a default as shown in the following example.Example 3.13. Example NULL handling
... criteria = '(' || nvl(criteria, '(1 = 1)') || ' and (Customer.Accounts.Birthdate = DVARS.BirthDay))';
- If the dynamic SQL is an UPDATE, DELETE, or INSERT command, and the user needs to specify the "AS" clause (which would be the case if the number of rows effected needs to be retrieved). The user will still need to provide a name and type for the return column if the into clause is specified.
Example 3.14. Example with AS and INTO clauses
/* This name does not need to match the expected update command symbol "count". */ EXECUTE IMMEDIATE <expression> AS x integer INTO #temp;
- Unless used in other parts of the procedure, tables in the dynamic command will not be seen as sources in Teiid Designer.
- When using the "AS" clause only the type information will be available to Teiid Designer. Result set columns generated from the "AS" clause then will have a default set of properties for length, precision, etc.
3.9.5. Declaration Statement
DECLARE <type> [VARIABLES.]<name> [= <expression>];
Example Syntax
declare integer x; declare string VARIABLES.myvar = 'value';
Syntax Rules:
- You cannot redeclare a variable with a duplicate name in a sub-block
- The VARIABLES group is always implied even if it is not specified.
- The assignment value follows the same rules as for an Assignment Statement.
- In addition to the standard types, you may specify EXCEPTION if declaring an exception variable.
3.9.6. Assignment Statement
<variable reference> = <expression>;
Example Syntax
myString = 'Thank you'; VARIABLES.x = (SELECT Column1 FROM MySchema.MyTable);
Special Variables
- The
VARIABLES.ROWCOUNT
integer variable will contain the numbers of rows affected by the last INSERT/UPDATE/DELETE command statement executed. Inserts that are processed by dynamic SQL with an INTO clause will also update the ROWCOUNT.Example 3.15. Sample Usage
... UPDATE FOO SET X = 1 WHERE Y = 2; DECLARE INTEGER UPDATED = VARIABLES.ROWCOUNT; ...
3.9.7. Compound Statement
[label :] BEGIN [[NOT] ATOMIC] statement* [EXCEPTION ex statement* ] END
Note
Syntax Rules
- If NOT ATOMIC or no ATOMIC clause is specified, the block will be executed non-atomically.
- If the ATOMIC clause is specified, the block must execute atomically. If a transaction is already associated with the thread, no additional action will be taken - savepoints and/or sub-transactions are not currently used. Otherwise a transaction will be associated with the execution of the block.
- The label must not be the same as any other label used in statements containing this one.
3.9.8. Exception Handling
Note
RuntimeException
will not be caught.
Variable
|
Type
|
Description
|
---|---|---|
STATE
|
string
|
The SQL State
|
ERRORCODE
|
integer
|
The error or vendor code. In the case of an internal exception, this will be the integer suffix of the TEIIDxxxx code
|
TEIIDCODE
|
string
|
The full event code. Typically TEIIDxxxx.
|
EXCEPTION
|
object
|
The exception being caught, will be an instance of
TeiidSQLException
|
CHAIN
|
object
|
The chained exception or cause of the current exception
|
Note
Example 3.16. Example Exception Group Handling
BEGIN DECLARE EXCEPTION e = SQLEXCEPTION 'this is bad' SQLSTATE 'xxxxx'; RAISE variables.e; EXCEPTION e IF (e.state = 'xxxxx') //in this trivial example, we'll always hit this branch and log the exception RAISE SQLWARNING e.exception; ELSE RAISE e.exception; END
3.9.9. If Statement
IF (criteria) block [ELSE block] END
Example 3.17. Example If Statement
IF ( var1 = 'North America') BEGIN ...statement... END ELSE BEGIN ...statement... END
Note
3.9.10. Loop Statement
[label :] LOOP ON <select statement> AS <cursorname> block
Syntax Rules
- The label must not be the same as any other label used in statements containing this one.
3.9.11. While Statement
[label :] WHILE <criteria> block
Syntax Rules
- The label must not be the same as any other label used in statements containing this one.
3.9.12. Continue Statement
CONTINUE [label];
Syntax Rules
- If the label is specified, it must exist on a containing LOOP or WHILE statement.
- If no label is specified, the statement will affect the closest containing LOOP or WHILE statement.
3.9.13. Break Statement
BREAK [label];
Syntax Rules
- If the label is specified, it must exist on a containing LOOP or WHILE statement.
- If no label is specified, the statement will affect the closest containing LOOP or WHILE statement.
3.9.14. Leave Statement
LEAVE label;
Syntax Rules
- The label must exist on a containing compound statement, LOOP, or WHILE statement.
3.9.15. Return Statement
RETURN [expression];
Syntax Rules
- If an expression is specified, the procedure must have a return parameter and the value must be implicitly convertible to the expected type.
- Even if the procedure has a return value, it is not required to specify a return value in a RETURN statement.
3.9.16. Error Statement
ERROR message;
Example 3.18. Example Error Statement
ERROR 'Invalid input value: ' || nvl(Acct.GetBalance.AcctID, 'null');
RAISE SQLEXCEPTION message;
3.9.17. Raise Statement
RAISE [SQLWARNING] exception;
Syntax Rules
- If SQLWARNING is specified, the exception will be sent to the client as a warning and the procedure will continue to execute.
- A null warning will be ignored. A null non-warning exception will still cause an exception to be raised.
Example 3.19. Example Raise Statement
RAISE SQLWARNING SQLEXCEPTION 'invalid' SQLSTATE '05000';
3.9.18. Exception Expression
SQLEXCEPTION message [SQLSTATE state [, code]] CHAIN exception
Syntax Rules
- Any of the values may be null;
- message and state are string expressions specifying the exception message and SQL state respectively. JBoss Data Virtualization does not yet fully comply with the ANSI SQL specification on SQL state usage, but you are allowed to set any SQL state you choose.
- code is an integer expression specifying the vendor code
- exception must be a variable reference to an exception or an exception expression and will be chained to the resulting exception as its parent.