Single-Row Query Results--SELECT INTO Statements

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

Notes: Permissible syntax for expression1 through expressionN, the datasource, and the optional clauses is the same as for any SQL SELECT statement. For information about what is permissible in Oracle SQL, see the Oracle8i SQL Reference. There can be any number of SELECT-list and INTO-list items, as long as they match--one INTO-list item per SELECT-list item, with compatible types.

Examples of SELECT INTO Statements

The examples below use a subset of the standard EMP table:

CREATE TABLE EMP (
   EMPNO NUMBER(4),
   ENAME VARCHAR2(10),
   HIREDATE DATE );

The first example is a SELECT INTO statement with a single host expression in the INTO-list:

String empname;
#sql { SELECT ename INTO :enpname FROM emp WHERE empno=28959 };

The second example is a SELECT INTO statement with multiple host expressions in the INTO-list:

String empname;
Date hdate;
#sql { SELECT ename, hiredate INTO :empname, :hdate FROM emp 
       WHERE empno=28959 };

Examples with Host Expressions in SELECT-List

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 emp WHERE empno=28959 };
...

More realistically, you might want to perform an operation or concatenation on the data selected, as in the following examples (assume Java variables were previously declared and assigned, as necessary):

...
#sql { SELECT sal + :raise INTO :newsal FROM emp WHERE empno=28959 };
...

...
#sql { SELECT :(firstname + " ") || emp_last_name INTO :name FROM myemp 
       WHERE empno=28959 };
...

In the second example, presume MYEMP is a table much like the standard EMP table but with an EMP_LAST_NAME column instead of an ENAME column. In the SELECT statement, firstname is prepended to " " (a single space), 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.

---