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

## Multi-Row Query Results--SQLJ Iterators

A large number of SQL operations are multi-row queries. Processing multi-row query-results in SQLJ requires a SQLJ iterator, which is a strongly typed version of a JDBC result set and is associated with the underlying database cursor. SQLJ iterators are used first and foremost to take query results from a `SELECT` statement.

Additionally, Oracle SQLJ offers extensions that allow you to use SQLJ iterators and result sets in the following ways:

- as `OUT` host variables in executable SQL statements

- as INTO-list targets, such as in a `SELECT INTO` statement

- as a return type from a stored function call

- as column types in iterator declarations (essentially, nested iterators)

Note: To use a SQLJ iterator in any of these ways, its class must be declared as `public`. If you declared it at the class level or nested-class level, then it might be advisable to declare it as `public static`.

For information about use as stored function returns, see "Using Iterators and Result Sets as Stored Function Returns", after stored procedures and stored functions have been discussed. The other uses listed above are documented later in this section.

For information about advanced iterator topics, see "Iterator Class Implementation and Advanced Functionality". This section discusses how iterator classes are implemented and what advanced functionality is available, such as interoperability with JDBC result sets and subclassing of iterators.

### Iterator Concepts

Before using an iterator object, you must declare an iterator class. An iterator declaration specifies a Java class that SQLJ constructs for you, where the class attributes define the types (and, optionally, the names) of the columns of data in the iterator.

A SQLJ iterator object is an instantiation of such a specifically declared iterator class, with a fixed number of columns of predefined type. This is as opposed to a JDBC result set object, which is a standard `java.sql.ResultSet` instance and can, in principle, contain any number of columns of any type.

When you declare an iterator, you specify either just the datatypes of the selected columns, or both the datatypes and the names of the selected columns:

- Specifying the names and datatypes defines a named iterator class.

- Specifying just the datatypes defines a positional iterator class.

The datatypes (and names, if applicable) that you declare determine how query results will be stored in iterator objects you instantiate from that class. SQL data retrieved into an iterator object are converted to the Java types specified in the iterator declaration.

When you query to populate a named iterator object, the names and datatypes of the SELECT-fields must match the names and types of the iterator columns (case-insensitive). The order of the SELECT-fields is irrelevant--all that matters is that each SELECT-field name matches an iterator column name. In the simplest case, the database column names directly match the iterator column names. For example, data from an `ENAME` column in a database table can be selected and put into an iterator `ename` column. Alternatively, you can use an alias to map a database column name to an iterator column name if the names differ. Furthermore, in a more complicated query, you can perform an operation between two columns and alias the result to match the corresponding iterator column name. (These last two cases are discussed in "Instantiating and Populating Named Iterators".)

Because SQLJ iterators are strongly typed, they offer the benefit of Java type-checking during the SQLJ semantics-checking phase.

As an example, consider the following table:

    CREATE TABLE EMPSAL (
       EMPNO NUMBER(4),
       ENAME VARCHAR2(10),
       OLDSAL NUMBER(10),
       RAISE NUMBER(10) );

Given this table, you can declare and use a named iterator as follows.

Declaration:

    #sql iterator SalNamedIter (int empno, String ename, float raise);

Executable code:

    class MyClass {
       void func() throws SQLException {
          ...
          SalNamedIter niter = null;
          #sql niter = { SELECT ename, empno, raise FROM empsal };

          ... process niter ...
       }
    }

This is a simple case where the iterator column names match the table column names. Note that the order of items in the `SELECT` statement does not matter when you use a named iterator--data is matched by name, not position.

When you query to populate a positional iterator object, the data is retrieved according to the order in which you select the columns. Data from the first column selected from the database table is placed into the first column of the iterator, and so on. The datatypes of the table columns must be convertible to the types of the iterator columns, but the names of the database columns are irrelevant, as the iterator columns have no names.

Given the `EMPSAL` table above, you can declare and use a positional iterator as follows.

Declaration:

    #sql iterator SalPosIter (int, String, float);

Executable code:

    class MyClass {
       void func() throws SQLException {
          ...
          SalPosIter piter = null;
          #sql piter = { SELECT empno, ename, raise FROM empsal };

          ... process piter ...
       }
    }

Note that the data items are in the same order in the table, iterator, and `SELECT` statement.

The processing differs between named iterators and positional iterators, as described in "Accessing Named Iterators" and "Accessing Positional Iterators".

##### Iterator Notes

In addition to the preceding concepts, be aware of the following general notes about iterators:

- `SELECT *` syntax is allowed in populating an iterator, but is not recommended. In the case of a positional iterator, this requires that the number of columns in the table be equal to the number of columns in the iterator, and that the types match in order. In the case of a named iterator, this requires that the number of columns in the table be greater than or equal to the number of columns in the iterator and that the name and type of each iterator column match a database table column. (If the number of columns in the table is greater, however, a warning will be generated unless the translator `-warn=nostrict` flag is set. For information about this flag, see "Translator Warnings (-warn)".)

- Positional and named iterators are distinct and incompatible kinds of Java classes. An iterator object of one kind cannot be cast to an iterator object of the other kind.

- Unlike a SQL cursor, an iterator instance is a first-class Java object (it can be passed and returned as a method parameter, for example) and can be declared using Java class modifiers, such as public or private.

- SQLJ supports interoperability and conversion between SQLJ iterators and JDBC result sets. For information, see "SQLJ Iterator and JDBC Result Set Interoperability".

- The contents of an iterator is determined only by the state of the database at the time of execution of the `SELECT` statement that populated it. Subsequent `UPDATE`, `INSERT`, `DELETE`, `COMMIT`, or `ROLLBACK` operations have no effect on the iterator or its contents. This is further discussed in "Effect of Commits and Rollbacks on Iterators and Result Sets". (However, updatable and update-sensitive iterators will likely be supported in a future release.)

### General Steps in Using an Iterator

Five general steps are involved in using either kind of SQLJ iterator:

1. Use a SQLJ declaration to define the iterator class (in other words, to define the iterator type).

2. Declare a variable of the iterator class.

3. Populate the iterator variable with the results from a SQL query, using a `SELECT` statement.

4. Access the query columns in the iterator (how to accomplish this differs between named iterators and positional iterators, as explained below).

5. When you finish processing the results of the query, close the iterator to release its resources.

### Named Iterators versus Positional Iterators

There are advantages and appropriate situations for each of the two kinds of SQLJ iterators.

Named iterators allow greater flexibility. Because data selection into a named iterator matches SELECT-fields to iterator columns by name, you need not be concerned about the order in your query. This is less prone to error, as it is not possible for data to be placed into the wrong column. If the names don't match, the SQLJ translator will generate an error when it checks your SQL statements against the database.

Positional iterators offer a familiar paradigm and syntax to developers who have experience with other embedded-SQL languages. With named iterators you use a `next()` method to retrieve data, while with positional iterators you use `FETCH INTO` syntax similar to that of Pro*C, for example. (Each fetch implicitly advances to the next available row of the iterator before retrieving the next set of values.)

Positional iterators do, however, offer less flexibility than named iterators, because you are selecting data into iterator columns by position, instead of by name. You must be certain of the order of items in your `SELECT` statement. You also must select data into all columns of the iterator, and it is possible to have data written into the wrong iterator column if the type of that column happens to match the datatype of the table column being selected.

Access to individual data elements is also less convenient with positional iterators. Named iterators, because they store data by name, are able to have convenient accessor methods for each column (for example, there would be an `ename()` method to retrieve data from an `ename` iterator column). With positional iterators, you must fetch data directly into Java host expressions with your `FETCH INTO` statement, and the host expressions must be in the correct order.

Notes: In populating a positional iterator, the number of columns you select from the database must equal the number of columns in the iterator. In populating a named iterator, the number of columns you select from the database can never be less than the number of columns in the iterator, but can be greater than the number of columns in the iterator if you have the translator `-warn=nostrict` flag set. Unmatched columns are ignored in this case. (For information about this flag, see "Translator Warnings (-warn)".) Although the term "fetching" often refers to fetching data from a database, remember that a `FETCH INTO` statement for a positional iterator does not necessarily involve a round trip to the server, depending on the row-prefetch value. This is because you are fetching data from the iterator, not the database. If the row-prefetch value is 1, however, then each fetch does involve a separate trip to the database. (The row-prefetch value determines how many rows are retrieved with each trip to the database. See "Row Prefetching".)

### Using Named Iterators

When you declare a named iterator class, you declare the name as well as the datatype of each column of the iterator.

When you select data into a named iterator, the SELECT-fields must match the iterator columns in two ways:

- The name of each SELECT-field, either a table column name or an alias, must match an iterator column name (case-insensitive, so `ename` would match `ENAME`).

- The type of each iterator column must be compatible with the datatype of the corresponding SELECT-field, according to standard JDBC type mappings.

The order in which attributes are declared in your named iterator class declaration is irrelevant. Data is selected into the iterator based on name alone.

A named iterator has a `next()` method to retrieve data row by row and an accessor method for each column to retrieve the individual data items. The accessor method names are identical to the column names. (Unlike most accessor method names in Java, accessor method names in named iterator classes do not start with "get".) For example, a named iterator object with a column `sal` would have a `sal()` accessor method.

Note: The following restrictions apply in naming the columns of a named iterator: Column names cannot use Java reserved words. Column names cannot have the same name as utility methods provided in named iterator classes--the `next()`, `close()`, `getResultSet()`, and `isClosed()` methods.

#### Declaring Named Iterator Classes

Use the following syntax to declare a named iterator class:

    #sql <modifiers> iterator classname <implements clause> <with clause>
         ( type-name-list );

Where `modifiers` is an optional sequence of legal Java class modifiers, `classname` is the desired class name for the iterator, and `type-name-list` is a list of the Java types and names equivalent to (convertible from) the column types and column names in a database table.

The `implements` clause and `with` clause are optional, specifying interfaces to implement and variables to define and initialize, respectively. These are discussed in "Declaration IMPLEMENTS Clause" and "Declaration WITH Clause".

Now consider the following table:

    CREATE TABLE PROJECTS (
       ID NUMBER(4),
       PROJNAME VARCHAR(30),
       START_DATE DATE,
       DURATION NUMBER(3) );

You might declare the following named iterator for use with this table:

    #sql public iterator ProjIter (String projname, int id, Date deadline);

This will result in an iterator class with columns of data accessible using the following provided accessor methods: `projname()`, `id()`, and `deadline()`.

Note: As with standard Java, any public class should be declared in one of the following ways (this is a requirement if you are using the standard `javac` compiler provided with the Sun Microsystems JDK): Declare it in a separate source file. The base name of the file should be the same as the class name. or: Declare it at class-level scope or nested-class-level scope, with `public static` modifiers.

#### Instantiating and Populating Named Iterators

Declare a variable of the `ProjIter` positional iterator type from the preceding section and populate it with a `SELECT` statement.

Continuing to use the `PROJECTS` table and `ProjIter` iterator defined in the preceding section, note that there are columns in the table whose names and datatypes match the `id` and `projname` columns of the iterator, but you must use an alias and perform an operation to populate the `deadline` column of the iterator. Here is an example:

    ProjIter projsIter;

    #sql projsIter = { SELECT start_date + duration AS deadline, projname, id
                       FROM projects WHERE start_date + duration >= sysdate };

This calculates a deadline for each project by adding its duration to its start date, then aliases the results as `deadline` to match the `deadline` iterator column. It also uses a `WHERE` clause so that only future deadlines are processed (deadlines beyond the current system date in the database).

Similarly, you must create an alias if you want to use a function call. Suppose you have a function `MAXIMUM()` that takes a `DURATION` entry and an integer as input and returns the maximum of the two. (For example, you could input a 3 to make sure each project has at least a three-month duration in your application.)

Now presume you are declaring your iterator as follows:

    #sql public iterator ProjIter2 (String projname, int id, float duration);

You could use the `MAXIMUM()` function in your query, with an alias for the result, as follows:

    ProjIter2 projsIter2;

    #sql projsIter2 = { SELECT id, projname, maximum(duration, 3) AS duration
                        FROM projects };

Generally, you must use an alias in your query for any SELECT-field whose name is not a legal Java identifier or does not match a column name in your iterator.

Remember that in populating a named iterator, the number of columns you select from the database can never be less than the number of columns in the iterator. The number of columns you select can be greater than the number of columns in the iterator (unmatched columns are ignored), but this will generate a warning unless you have the SQLJ `-warn=nostrict` option set.

#### Accessing Named Iterators

Use the `next()` method of the named iterator object to step through the data that was selected into it. To access each column of each row, use the accessor methods generated by SQLJ, typically inside a `while` loop.

Whenever `next()` is called:

- If there is another row to retrieve from the iterator, `next()` retrieves the row and returns `true`.

- If there are no more rows to retrieve, `next()` returns `false`.

The following is an example of how to access the data of a named iterator, repeating the declaration, instantiation, and population used under "Instantiating and Populating Named Iterators".

Note: Each iterator has a `close()` method that you must always call once you finish retrieving data from the iterator. This is necessary to close the iterator and free its resources.

Presume the following iterator class declaration:

    #sql public iterator ProjIter (String projname, int id, Date deadline);

Populate and then access an instance of this iterator class as follows:

    // Declare the iterator variable
    ProjIter projsIter = null;

    // Instantiate and populate iterator; order of SELECT doesn't matter
    #sql projsIter = { SELECT start_date + duration AS deadline, projname, id
                       FROM projects WHERE start_date + duration >= sysdate };

    // Process the results
    while (projsIter.next()) {
       System.out.println("Project name is " + projsIter.projname());
       System.out.println("Project ID is " + projsIter.id());
       System.out.println("Project deadline is " + projsIter.deadline());
    }

    // Close the iterator
    projsIter.close();
    ...

Note the convenient use of the `projname()`, `id()`, and `deadline()` accessor methods to retrieve the data. Note also that the order of the `SELECT` items does not matter, nor does the order in which the accessor methods are used.

Remember, however, that accessor method names are created with the case exactly as in your declaration of the iterator class. The following will generate compilation errors.

Declaration:

    #sql iterator Cursor1 (String NAME);

Executable code:

    ...
    Cursor1 c1;
    #sql c1 = { SELECT NAME FROM TABLE };
    while (c1.next()) {
       System.out.println("The name is " + c1.name());
    }
    ...

The Cursor1 class has a method called `NAME()`, not `name()`. You would have to use `c1.NAME()` in the `System.out.println` statement.

For a complete sample of using a named iterator, see "Named Iterator--NamedIterDemo.sqlj".

### Using Positional Iterators

When you declare a positional iterator class, you declare the datatype of each column but not the column name. The Java types into which the columns of the SQL query results are selected must be compatible with the datatypes of the SQL data. The names of the database columns or SELECT-fields are irrelevant.

Because names are not used, the order in which you declare your positional iterator Java types must exactly match the order in which the data is selected.

To retrieve data from a positional iterator once data has been selected into it, use a `FETCH INTO` statement followed by an `endFetch()` method call to determine if you have reached the end of the data (as detailed under "Accessing Positional Iterators").

#### Declaring Positional Iterator Classes

Use the following syntax to declare a positional iterator class:

    #sql <modifiers> iterator classname <implements clause> <with clause>
         ( position-list );

Where `modifiers` is an optional sequence of legal Java class modifiers, and the`` `position-list` is a list of Java types compatible with the column types in a database table.

The `implements` clause and `with` clause are optional, specifying interfaces to implement and variables to define and initialize, respectively. These are discussed in "Declaration IMPLEMENTS Clause" and "Declaration WITH Clause".

Now consider the following table, a subset of the standard `EMP` table:

    CREATE TABLE EMP (
       EMPNO NUMBER(4),
       ENAME VARCHAR2(10),
       SAL NUMBER(7,2) );

And consider the following positional iterator declaration:

    #sql public iterator EmpIter (String, int, float);

This example defines Java class `EmpIter` with unnamed `String`, `int`, and `float` columns. Note that the table columns and iterator columns are in a different order--the `String` corresponds to `ENAME` and the `int` corresponds to `EMPNO`.

Note: As with standard Java, any public class should be declared in one of the following ways (this is a requirement if you are using the standard `javac` compiler provided with the Sun Microsystems JDK): Declare it in a separate source file. The base name of the file should be the same as the class name. or: Declare it at class-level scope or nested-class-level scope, with `public static` modifiers.

#### Instantiating and Populating Positional Iterators

Declare a variable of the `EmpIter` positional iterator type from the preceding section and populate it with a `SELECT` statement.

Instantiating and populating a positional iterator is no different than doing so for a named iterator, except that you must be certain that your SELECT-fields are in the proper order.

The three datatypes in the `EmpIter` iterator class are compatible with the types of the `EMP` table, but be careful how you select the data, because the order is different. The following will work--instantiating and populating the iterator--as the SELECT-fields are in the same order as the iterator columns:

    EmpIter empsIter = null;

    #sql empsIter = { SELECT ename, empno, sal FROM emp };

Remember that in populating a positional iterator, the number of columns you select from the database must equal the number of columns in the iterator.

#### Accessing Positional Iterators

Access the columns defined by a positional iterator using SQL `FETCH INTO` syntax.

The `INTO` part of the command specifies Java host variables that receive the results columns. The host variables must be in the same order as the corresponding iterator columns. Use the `endFetch()` method provided with all positional iterator classes to determine whether the last fetch reached the end of the data.

Notes: The `endFetch()` method initially returns `true` before any rows have been fetched, then returns `false` once a row has been successfully retrieved, then returns `true` again when a `FETCH` finds no more rows to retrieve. Therefore, you must perform the `endFetch()` test after the `FETCH INTO` statement. If your `endFetch()` test precedes the `FETCH INTO` statement, then you will never retrieve any rows, because `endFetch()` would be true before your first `FETCH` and you would immediately break out of the `while` loop. The `endFetch()` test must be before the results are processed, however, because the `FETCH` does not throw a SQL exception when it reaches the end of the data, it just triggers the next `endFetch()` call to return `true`. If there is no `endFetch()` test before results are processed, then your code will try to process null or invalid data from the first `FETCH` attempt after the end of the data had been reached. Each iterator has a `close()` method that you must always call once you finish retrieving data from it. This is necessary to close the iterator and free its resources.

The following is an example, repeating the declaration, instantiation, and population used under "Instantiating and Populating Positional Iterators".

Note that the Java host variables in the `SELECT` statement are in the same order as the columns of the positional iterator, which is mandatory.

First, presume the following iterator class declaration:

    #sql public iterator EmpIter (String, int, float);

Populate and then access an instance of this iterator class as follows:

    // Declare and initialize host variables
    int empnum=0;
    String empname=null;
    float salary=0.0f;

    // Declare an iterator instance
    EmpIter empsIter;

    #sql empsIter = { SELECT ename, empno, sal FROM emp };

    while (true) {
       #sql { FETCH :empsIter INTO :empnum, :empname, :salary };
       if (empsIter.endFetch()) break;  // This test must be AFTER fetch,
                                        // but before results are processed.
       System.out.println("Name is " + empname);
       System.out.println("Employee number is " + empnum);
       System.out.println("Salary is " + salary);
    }

    // Close the iterator
    empsIter.close();
    ...

The `empname`, `empnum`, and `salary` variables are Java host variables whose types must match the types of the iterator columns.

Do not use the `next()` method for a positional iterator. A `FETCH` calls it implicitly to move to the next row.

Note: Host variables in a `FETCH INTO` statement must always be initialized because they are assigned in one branch of a conditional statement. Otherwise, you will get a compiler error indicating they may never be assigned. (`FETCH` can assign the variables only if there was a row to be fetched.)

For a complete sample of using a positional iterator, see "Positional Iterator--PosIterDemo.sqlj".

### Using Iterators and Result Sets as Host Variables

SQLJ supports SQLJ iterators and JDBC result sets as host variables, as illustrated in the examples below.

Notes: Additionally, SQLJ supports iterators and result sets as return variables for stored functions. This is discussed in "Using Iterators and Result Sets as Stored Function Returns". The Oracle JDBC drivers do not currently support result sets as input host variables. There is a `setCursor()` method in the `OraclePreparedStatement` class, but it raises an exception at runtime if called.

As you will see from the following examples, using iterators and result sets is fundamentally the same, with differences in declarations and in accessor methods to retrieve the data.

For the examples in this section, consider the following tables--subsets of the standard `DEPT` and `EMP` tables:

    CREATE TABLE DEPT (
       DEPTNO NUMBER(2),
       DNAME VARCHAR2(14) );

    CREATE TABLE EMP (
       EMPNO NUMBER(4),
       ENAME VARCHAR2(10),
       SAL NUMBER(7,2),
       DEPTNO NUMBER(2) );

##### Example: Use of Result Set as OUT Host Variable

This example uses a JDBC result set as an output host variable.

    ...
    ResultSet rs;
    ...
    #sql { BEGIN
              OPEN :OUT rs FOR SELECT ename, empno FROM emp;
           END };

    while (rs.next())
    {
       String empname = rs.getString(1);
       int empnum = rs.getInt(2);
    }
    rs.close();
    ...

This example opens the result set `rs` in a PL/SQL block to receive data from a `SELECT` statement, selects data from the `ENAME` and `EMPNO` columns of the `EMP` table, then loops through the result set to retrieve data into local variables.

##### Example: Use of Iterator as OUT Host Variable

This example uses a named iterator as an output host variable.

Declaration:

    #sql public <static> iterator EmpIter (String ename, int empno);

(The `public` modifier is required, and `static` may be advisable if your declaration is at class level or nested-class level.)

Executable code:

    ...
    EmpIter iter;
    ...
    #sql { BEGIN
              OPEN :OUT iter FOR SELECT ename, empno FROM emp;
           END };

    while (iter.next())
    {
       String empname = iter.ename();
       int empnum = iter.empno();

       ...process/output empname and empnum...
    }
    iter.close();
    ...

This example opens the iterator `iter` in a PL/SQL block to receive data from a `SELECT` statement, selects data from the `ENAME` and `EMPNO` columns of the `EMP` table, then loops through the iterator to retrieve data into local variables.

##### Example: Use of Iterator as OUT Host Variable for SELECT INTO

This example uses a named iterator as an output host variable, taking data through a `SELECT INTO` statement. (`OUT` is the default for host variables in an INTO-list. For information about `SELECT INTO` statements and syntax, see "Single-Row Query Results--SELECT INTO Statements".)

Declaration:

    #sql public <static> iterator ENameIter (String ename);

(The `public` modifier is required, and `static` may be advisable if your declaration is at class level or nested-class level.)

Executable code:

    ...
    ENameIter enamesIter;
    String deptname;
    ...

    #sql { SELECT dname, cursor
          (SELECT ename FROM emp WHERE deptno = dept.deptno)
           INTO :deptname, :enamesIter FROM dept WHERE deptno = 20 };

    System.out.println(deptname);
    while (enamesIter.next())
    {
       System.out.println(enamesIter.ename());
    }
    enamesIter.close();
    ...

This example uses nested `SELECT` statements to accomplish the following:

- Select the name of department number 20 from the `DEPT` table, selecting it into the output host variable `deptname`.

- Query the `EMP` table to select all employees whose department number is 20, selecting the resulting cursor into the output host variable `enamesIter`, which is a named iterator.

- Print the department name.

- Loop through the named iterator printing employee names. This prints the names of all employees in the department.

In most cases, using `SELECT INTO` is more convenient than using nested iterators if you are retrieving a single row in the outer `SELECT`, although that option is also available as discussed below (such as in "Example: Named Iterator Column in a Positional Iterator"). Also, with nested iterators, you would have to process the data to determine how many rows there are in the outer `SELECT`. With `SELECT INTO` you are assured of just one row.

### Using Iterators and Result Sets as Iterator Columns

Oracle SQLJ includes extensions that allow iterator declarations to specify columns of type `ResultSet` or columns of other iterator types declared within the current scope. In other words, iterators and result sets can exist within iterators in Oracle SQLJ. These column types are used to retrieve a column in the form of a cursor. This is useful for nested `SELECT` statements that return nested table information.

The following examples are functionally identical--each uses a nested result set or iterator (result sets or iterators in a column within an iterator) to print all the employees in each department in the `DEPT` table. The first example uses result sets within a named iterator, the second example uses named iterators within a named iterator, and the third example uses named iterators within a positional iterator.

Here are the steps:

1. Select each `DNAME` (department name) from the `DEPT` table.

2. Do a nested `SELECT` into a cursor to get all employees from the `EMP` table for each department.

3. Put the department names and sets of employees into the outer iterator (`iter`), which has a name column and an iterator column. The cursor with the employee information for any given department goes into the iterator column of that department's row of the outer iterator.

4. Go through a nested loop that, for each department, prints the department name and then loops through the inner iterator to print all employee names for that department.

##### Example: Result Set Column in a Named Iterator

This example uses a column of type `ResultSet` in a named iterator.

Declaration:

    #sql iterator DeptIter (String dname, ResultSet emps);

Executable code:

    ...
    DeptIter iter;
    ...
    #sql iter = { SELECT dname, cursor
                 (SELECT ename FROM emp WHERE deptno = dept.deptno)
                  AS emps FROM dept };

    while (iter.next())
    {
       System.out.println(iter.dname());
       ResultSet enamesRs = iter.emps();
       while (enamesRs.next())
       {
          String empname = enamesRs.getString(1);
          System.out.println(empname);
       }
       enamesRs.close();
    }
    iter.close();
    ...

##### Example: Named Iterator Column in a Named Iterator

This example uses a named iterator that has a column whose type is that of a previously defined named iterator (nested iterators).

Declarations:

    #sql iterator ENameIter (String ename);
    #sql iterator DeptIter (String dname, ENameIter emps);

Executable code:

    ...
    DeptIter iter;
    ...
    #sql iter = { SELECT dname, cursor
                 (SELECT ename FROM emp WHERE deptno = dept.deptno)
                  AS emps FROM dept };

    while (iter.next())
    {
       System.out.println(iter.dname());
       ENameIter enamesIter = iter.emps();
       while (enamesIter.next())
       {
          System.out.println(enamesIter.ename());
       }
       enamesIter.close();
    }
    iter.close();
    ...

##### Example: Named Iterator Column in a Positional Iterator

This example uses a positional iterator that has a column whose type is that of a previously defined named iterator (nested iterators). This uses the `FETCH INTO` syntax of positional iterators. This example is functionally equivalent to the previous two.

Note that because the outer iterator is a positional iterator, there does not have to be an alias to match a column name, as was required when the outer iterator was a named iterator in the previous example.

Declarations:

    #sql iterator ENameIter (String ename);
    #sql iterator DeptIter (String, ENameIter);

Executable code:

    ...
    DeptIter iter;
    ...
    #sql iter = { SELECT dname, cursor
                 (SELECT ename FROM emp WHERE deptno = dept.deptno)
                  FROM dept };

    while (true)
    {
       String dname = null;
       ENameIter enamesIter = null;
       #sql { FETCH :iter INTO :dname, :enamesIter };
       if (iter.endFetch()) break;
       System.out.println(dname);
       while (enamesIter.next())
       {
          System.out.println(enamesIter.ename());
       }
       enamesIter.close();
    }
    iter.close();
    ...

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