Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
When only a single row of data is being returned from the database, SQLJ allows you to assign selected items directly to Java host expressions inside SQL syntax. This is done using the SELECT INTO
statement. The syntax is as follows:
#sql { SELECT expression1,..., expressionN INTO :host_exp1,..., :host_expN FROM datasource <optional clauses> };
Where:
expression1
through expressionN
are expressions specifying what is to be selected from the database. These can be any expressions that are valid for any SELECT
statement. This list of expressions is referred to as the SELECT-list.
In a simple case, these would be names of columns from a database table.
It is also legal to include a host expression in the SELECT-list (see the examples below).
host_exp1
through host_expN
are target host expressions, such as variables or array indexes. This list of host expressions is referred to as the INTO-list.
datasource
is the name of the database table, view, or snapshot from which you are selecting the data.
optional clauses
are any additional clauses that you want to include that are valid in a SELECT
statement, such as a WHERE
clause.
A SELECT INTO
statement must return one and only one row of data, otherwise an error will be generated at runtime.
The default is OUT
for a host expression in an INTO-list, but you can optionally state this explicitly:
#sql { SELECT column_name1, column_name2 INTO :OUT host_exp1, :OUT host_exp2 FROM table WHERE condition };
Trying to use an IN
or INOUT
token in the INTO-list will result in an error at translation time.
The examples below assume the following table definition:
CREATE TABLE EMP ( EMP_NUM NUMBER, EMP_NAME CHAR(30), HIRE_DATE DATE );
The first example is a SELECT INTO
statement with a single host expression in the INTO-list:
String name; #sql { SELECT emp_name INTO :name FROM emp WHERE emp_num=28959 };
The second example is a SELECT INTO
statement with multiple host expressions in the INTO-list:
String name; Date hiredate; #sql { SELECT emp_name, hire_date INTO :name, :hiredate FROM emp WHERE emp_num=28959 };
It is legal to use Java host expressions in the SELECT-list as well as in the INTO-list.
For example, you can select directly from one host expression into another (though this is of limited usefulness):
... #sql { SELECT :name1 INTO :name2 FROM emptable WHERE emp_num=28959 }; ...
More realistically, you may want to perform an operation or concatenation on the data that is selected, as in the following examples (assume Java variables were previously declared and assigned, as necessary):
... #sql { SELECT salary + :raise INTO :newsal FROM emptable WHERE emp_num=28959 }; ... ... #sql { SELECT :(firstname + " ") || emp_last_name INTO :name FROM emptable WHERE emp_num=28959 }; ...
In the second example, firstname
is concatenated to " " using a Java host expression and Java string concatenation (the +
operator). This result is then passed to the SQL engine which uses SQL string concatenation (the ||
operator) to append the last name.