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

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

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