Assignment Statements (SET)

SQLJ allows you to assign a value to a Java host expression inside a SQL operation. This is known as an assignment statement and is accomplished using the following syntax:

#sql { SET :host_exp = expression };

The host_exp is the target host expression, such as a variable or array index. The expression could be a number, host expression, arithmetic expression, function call, or other construct that yields a valid result into the target host expression.

The default is OUT for a target host expression in an assignment statement, but you can optionally state this explicitly:

#sql { SET :OUT host_exp = expression };

Trying to use an IN or INOUT token in an assignment statement will result in an error at translation time.

The preceding statements are functionally equivalent to the following:

#sql { BEGIN :OUT host_exp := expression; END; };

Here is a simple example of an assignment statement:

#sql { SET :x = foo1() + foo2() };

This assigns to x the sum of the return values of foo1() and foo2() and assumes that the type of x is compatible with the type of the sum of the outputs of these functions.

Consider the following additional examples:

int i2;
java.sql.Date dat;
...
#sql { SET :i2 = TO_NUMBER(substr('750 etc.', 1, 3)) +
        TO_NUMBER(substr('250 etc.', 1, 3)) };
...
#sql { SET :dat = sysdate };
...

The first statement will assign to i2 the value 1000 (750 + 250). (The substr() calls take the first three characters of the strings, or '750' and '250'. The TO_NUMBER() calls convert the strings to the numbers 750 and 250.)

The second statement will read the database system date and assign it to dat.

An assignment statement is especially useful when you are performing operations on return variables from functions stored in the database. You do not need an assignment statement to simply assign a function result to a variable, because you can accomplish this using normal function call syntax as explained in "Stored Procedure and Function Calls". You also do not need an assignment statement to manipulate output from Java functions, because you can accomplish that in a normal Java statement. So you can presume that foo1() and foo2() above are stored functions in the database, not Java functions.

---