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)

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.

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.

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

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

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.

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.

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.

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.

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();
...

---