Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## 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 statement 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.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index