Oracle8i SQLJ Developer's Guide and Reference Release 8.1.5 A64684-01 |
|
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:
OUT
host variables in executable SQL statements
SELECT INTO
statement
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.
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 an instantiation of the generic java.sql.ResultSet
class 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:
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 that are retrieved into an iterator object are converted to the Java types that are 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 EMP
column in a database table can be selected and put into an iterator emp
column. Alternatively, you can use an alias to map a database column name to an iterator column name. 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.
Because SQLJ iterators are strongly typed, they offer the benefit of Java type-checking during the SQLJ semantics-checking phase.
An an example, assume the following table:
CREATE TABLE EMPSAL ( ID NUMBER(5), NAME VARCHAR(30), OLDSAL NUMBER(10), RAISE NUMBER(10) );
Given this table, you can declare and use a named iterator as follows.
Declaration:
#sql iterator SalNamedIter (int id, String empname, float newsalary);
Executable code:
class MyClass { void func() throws SQLException { ... SalNamedIter niter = null; #sql niter = { SELECT name AS empname, id, oldsal + raise AS newsalary FROM empsal }; ... } }
The id
columns match directly, an alias is used to map the database name
column to the iterator empname
column, and the newsalary
target for the oldsal + raise
operation matches the newsalary
column of the iterator. The order of items in the SELECT
statement does not matter.
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 id, name, oldsal + raise FROM empsal }; ... } }
Note that the data items are in the same order in the table, iterator, and SELECT
statement.
Notes:
|
Five general steps are involved in using either kind of SQLJ iterator:
SELECT
statement.
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 row of the iterator upon completion.)
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 emp()
method to retrieve data from an emp
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.
Note:
|
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:
emp
would match EMP
).
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 name is identical to the column name. (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.
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".
For example, you might declare the following named iterator for use with company projects:
#sql public iterator ProjIter (String projname, int id, Date deadline);
This will result in an iterator class with columns of data that are accessible using the following provided accessor methods: projname()
, id()
, and deadline()
.
Declare a named iterator variable and then instantiate and populate it using a SELECT
statement. For example, declare an instance of the above ProjIter
named iterator:
ProjIter projs;
Now presume you want to populate this iterator with data from a database table defined as follows:
CREATE TABLE PROJECTS ( ID NUMBER(4), NAME VARCHAR(30), START_DATE DATE, DURATION NUMBER(3) );
There are columns in this table whose names and datatypes match the id
and name
columns of the iterator, but you must use an alias (and perform an operation as appropriate) to populate the deadline
column of the iterator. Here is an example:
#sql projs = { SELECT start_date + duration AS deadline, name, 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 iterator column deadline
. 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 database table Emp
with a column comm
and a function maximum()
that takes a comm
entry and an integer as input and returns the maximum of the two. (For example, you could input a 0 to avoid negative numbers in your iterator.) Additionally, suppose you declare an iterator class with a corresponding maxComm
column.
Now consider the following SELECT
syntax:
SELECT maximum(comm, 0) FROM Emp
This is valid SELECT
syntax, but you cannot use it to populate a named iterator because "maximum(comm, 0)" is not an iterator column name (and cannot possibly be, because that is not a valid Java identifier). You can, however, work around this problem by using an alias, as follows:
#sql emps = { SELECT maximum(comm, 0) AS maxComm FROM Emp };
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.
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 that are generated by SQLJ, typically inside a while
loop.
Whenever next()
is called:
next()
retrieves the row and returns true
.
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 |
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 projs = null; // Instantiate and populate iterator; order of SELECT doesn't matter #sql projs = { SELECT start_date + duration as deadline, projname, id FROM projects WHERE start_date + duration >= sysdate }; // Process the results while (projs.next()) { System.out.println("Project name is " + projs.projname()); System.out.println("Project ID is " + projs.id()); System.out.println("Project deadline is " + projs.deadline()); } // Close the iterator projs.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".
When you declare a positional iterator class, you only declare the datatype of each column; you do not define column names. 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").
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 the 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".
For example, consider a database table defined as follows:
#sql public iterator EmpIter (int, String, float);
This defines Java class EmpIter
with unnamed integer, string, and float columns.
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.
Declare a variable of the EmpIter
positional iterator class:
EmpIter emps = null;
Now presume that you want to populate this iterator with data from a database table defined as follows:
CREATE TABLE EMPLOYEES ( EMPNUM NUMBER(5), EMPSAL NUMBER(8,2), EMPNAME VARCHAR2(30) );
These three datatypes are compatible with the types of the EmpIter
positional iterator columns, but be careful about 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:
// Populate iterator emps #sql emps = { SELECT empnum, empname, empsal FROM employees };
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.
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 that is provided with all positional iterator classes to determine when you have 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 (int, String, float); ...
Populate and then access an instance of this iterator class as follows:
// Declare and initialize host variables int id=0; String name=null; float salary=0.0f; // Declare an iterator instance EmpIter emps; #sql emps = { SELECT empnum, empname, empsal FROM employees }; while (true) { #sql { FETCH :emps INTO :id, :name, :salary }; if (emps.endFetch()) break; // This test must be AFTER fetch, // but before results are processed. System.out.println("Name is " + name); System.out.println("Employee number is " + id); System.out.println("Salary is " + salary); ... } emps.close(); ...
The id
, name
, and salary
variables are Java host variables whose types must match the types of the iterator columns.
Explicitly using the next()
method is not necessary for a positional iterator, because FETCH
calls it implicitly to move to the next row.
For a complete sample of using a positional iterator, see "Positional Iterator--PosIterDemo.sqlj".
SQLJ supports SQLJ iterators and JDBC result sets as host variables, as illustrated in the examples below.
Notes:
|
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.
This example uses a named iterator as an output host variable.
Declaration:
#sql iterator EmpIter (String ename, int empno);
Executable code:
... EmpIter iter; ... #sql { BEGIN OPEN :OUT iter FOR SELECT ename, empno FROM emp; END; }; while (iter.next()) { String name = iter.ename(); int empno = iter.empno(); } iter.close(); ...
This example opens 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.
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 name = rs.getString(1); int empno = rs.getInt(2); } rs.close(); ...
This example opens 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.
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 iterator DNameIter (String dname);
Executable code:
... DNameIter dnameIter; String ename; ... #sql { SELECT ename, cursor (SELECT dname FROM dept WHERE deptno = emp.deptno) INTO :ename, :dnameIter FROM emp WHERE empno = 7788 }; System.out.println(ename); while (dnameIter.next()) { System.out.println(dnameIter.dname()); } dnameIter.close(); ...
This example uses nested SELECT
statements to accomplish the following:
emp
table, selecting it into the output host variable ename
.
dept
table into a cursor wherever the department number matches one of employee 7788's department numbers, selecting that cursor into the output host variable dnameIter
, which is a named iterator.
Oracle SQLJ includes extensions that allow iterator declarations to specify columns of type ResultSet
or columns of other iterator types that were 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, but the first example uses named iterators within a named iterator, the second example uses result sets within a named iterator, and the third example uses named iterators within a positional 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 DNameIter (String dname); #sql iterator NameDeptIter2 (String ename, DNameIter depts);
Executable code:
... NameDeptIter2 iter; ... #sql iter = { SELECT ename, cursor (SELECT dname FROM dept WHERE deptno = emp.deptno) AS depts FROM emp }; while (iter.next()) { System.out.println(iter.ename()); DNameIter deptsIter = iter.depts(); while (deptsIter.next()) { System.out.println(deptsIter.dname()); } deptsIter.close(); } iter.close(); ...
This example uses a nested iterator (iterators in a column within another iterator) to print all the departments of each employee in the emp
table, as follows:
ename
(employee name) from the emp
table.
SELECT
into a cursor to get all the departments from the dept
table for each employee (each department that the employee belongs to).
iter
), which has a name column and an iterator column. The cursor with the department information for any given employee goes into the iterator column of that employee's row of the outer iterator.
This example uses a column of type ResultSet
in a named iterator. This example is essentially equivalent to the previous example, except it uses result sets inside an iterator instead of nested iterators.
Declaration:
#sql iterator NameDeptIter1 (String ename, ResultSet depts);
Executable code:
... NameDeptIter1 iter; ... #sql iter = { SELECT ename, cursor (SELECT dname FROM dept WHERE deptno = emp.deptno) AS depts FROM emp }; while (iter.next()) { System.out.println(iter.ename()); ResultSet deptsRs = iter.depts(); while (deptsRs.next()) { String deptName = deptsRs.getString(1); System.out.println(deptName); } deptsRs.close(); } iter.close(); ...
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 there was when the outer iterator was a named iterator in the earlier example.
Declarations:
#sql iterator DNameIter (String dname); #sql iterator NameDeptIter3 (String, DNameIter);
Executable code:
... NameDeptIter3 iter; ... #sql iter = { SELECT ename, cursor (SELECT dname FROM dept WHERE deptno = emp.deptno) FROM emp }; while (true) { String ename = null; DNameIter deptsIter = null; #sql { FETCH :iter INTO :ename, :deptsIter }; if (iter.endFetch()) break; System.out.println(ename); while (deptsIter.next()) { System.out.println(deptsIter.dname()); } deptsIter.close(); } iter.close(); ...