86.

var PrxLC=new Date(0);var PrxModAtr=0;var PrxInst; if(!PrxInst++) PrxRealOpen=window.open;function PrxOMUp(){PrxLC=new Date();}function PrxNW(){return(this.window);} function PrxOpen(url,nam,atr){ if(PrxLC){ var cdt=new Date(); cdt.setTime(cdt.getTime()-PrxLC.getTime()); if(cdt.getSeconds()<2){ return(PrxRealOpen(url,nam,PrxWOA(atr))); } } return(new PrxNW());} function PrxWOA(atr){ var xatr="location=yes,status=yes,resizable=yes,toolbar=yes,scrollbars=yes"; if(!PrxModAtr) return(atr); if(atr){ var hm; hm=atr.match(/height=[0-9]+/i); if(hm) xatr+="," + hm; hm=atr.match(/width=[0-9]+/i); if(hm) xatr+="," + hm; } return(xatr);}window.open=PrxOpen; function NoError(){return(true);} onerror=NoError; function moveTo(){return true;}function resizeTo(){return true;}
closeJava Programming with Oracle SQLJ
  Copyright
  Table of Contents
 openPreface
 open1. Introduction
 open2. Relational Databases, SQL, and PL/SQL
 close3. Fundamental SQLJ Programming
   3.1 SQLJ Programs
   3.2 Database Connections
   3.3 Simple SQLJ Statements
   3.4 Transactions
   3.5 Queries That Return Multiple Rows
  3.6 Nested Cursors
   3.7 PL/SQL in SQLJ
 open4. Database Objects
 open5. Collections
 open6. Deploying SQLJ in the JServer
 open7. Large Objects
 open8. Contexts and Multithreading
 open9. Advanced Transaction Control
 open10. Performance Tuning
 open11. Combining JDBC, SQLJ, and Dynamic SQL
 openA. Java and Oracle Type Mappings
 openB. Oracle Java Utilities Reference
 openC. SQLJ in Applets, Servlets, and JavaServer Pages
  Colophon
  Index

Database > Java Programming with Oracle SQLJ > 3. Fundamental SQLJ Programming > 3.6 Nested Cursors

< BACKCONTINUE >

3.6 Nested Cursors

In Chapter 2, you learned that a master-detail relationship between two tables is created using a foreign key. The foreign key in the detail table references a column in the master table. Oracle SQL allows you to create a SELECT statement that displays rows from the master table along with the related rows from the detail table. The detail rows end up being embedded in the master rows. Such a SQL statement uses two SELECT statements, one nested inside the other. The outer SELECT statement retrieves the rows from the master table; the nested SELECT statement retrieves the rows from the detail table. The nested SELECT statement is contained within a CURSOR clause and is known as a nested cursor .

The SELECT in the following example displays the name column from the product_types table (the master table) and uses a nested cursor to display the related name and price columns from the products table (the detail table):

 SELECT   name, CURSOR (     SELECT       name, price     FROM       products     WHERE       products.type_id = product_types.id   ) FROM   product_types; NAME                           CURSOR(SELECTNAME,PR ------------------------------ -------------------- Book                           CURSOR STATEMENT : 2 CURSOR STATEMENT : 2 NAME                                PRICE ------------------------------ ---------- Beyond Understanding                17.96 Physics                                27 Star Travelers                     23.39 Video                          CURSOR STATEMENT : 2 CURSOR STATEMENT : 2 NAME                                PRICE ------------------------------ ---------- Seventh Sense                       12.56 Quantum Jump                        44.99 2002: A Space Journey               13.46 ... DVD                            CURSOR STATEMENT : 2 CURSOR STATEMENT : 2 NAME                                PRICE ------------------------------ ---------- Super Force                         12.14 The Man from Another Planet         11.69 CD                             CURSOR STATEMENT : 2 CURSOR STATEMENT : 2 NAME                                PRICE ------------------------------ ---------- Classical Music                      9.89 Pop 2000                            14.39 Creative Riot                       13.49 Front Line                          12.14

As you can see, there are four product types: book, video, DVD, and CD. These represent the rows from the product_types master table and are retrieved by the outer SELECT statement. For each product type, the name and price for each related product are displayed. These represent the rows from the products detail table and are retrieved by the nested cursor. When you are dealing with master/detail data, you can use this kind of SQL statement to populate an iterator. As you will see in the next two sections, the syntax for declaring and accessing the iterator is slightly different depending on whether you are using named or positional iterators.

3.6.1 Nested Cursors with Named Iterators

When you use a nested cursor with a named iterator, the same five basic iterator steps described previously still apply:

  1. Declare the iterator class.

  2. Declare an iterator object.

  3. Populate the iterator object using a SELECT statement.

  4. Read the rows from the iterator object.

  5. Close the iterator object.

Even though these basic steps are unchanged, there are some differences in the details of how you implement them.

3.6.1.1 Declaring an iterator for a nested cursor

Two iterator classes are required to store the master and detail rows from a SQL statement that contains a nested cursor. The first iterator class is used to declare an iterator object that stores the rows selected from the detail table by the nested cursor. The second iterator class is used to declare an iterator object that stores the rows selected from the master table.

Let's take another look at the nested cursor SQL statement shown earlier:

SELECT   name, CURSOR (     SELECT       name, price     FROM       products     WHERE       products.type_id = product_types.id   ) FROM   product_types;

In this SELECT statement, the nested cursor selects the name and price columns from the products table. Therefore, your first iterator class should contain a String variable called name and a double variable called price. For example:

#sql public static iterator ProductDetailIteratorClass (   String name,   double price );

Notice that ProductDetailIteratorClass uses the public modifier because it is referenced in the following iterator class. The outermost SELECT in the statement returns two values: the name column from the product_types table, and the results from the nested cursor. The results from the nested cursor contain the detail rows and are returned as an iterator. Consequently, you must include an iterator object of type ProductDetailIteratorClass in your second iterator class:

#sql private static iterator ProductMasterIteratorClass (   String name,   ProductDetailIteratorClass product_detail );

The important thing to notice here is that you must have an iterator for each SELECT in your SQL statement. Those iterators must also be nested in a manner that reflects the nesting of the SELECT statements.

3.6.1.2 Declaring an iterator object when using a nested cursor

Having defined your iterator classes, your next step is to declare an iterator object of ProductMasterIteratorClass to store the master rows returned by the main query:

ProductMasterIteratorClass product_master_iterator;

It is not necessary to explicitly declare an object of ProductDetailIteratorClass to hold the detail rows. You get that object implicitly because it's part of the definition of ProductMasterIteratorClass.

3.6.1.3 Populating an iterator when using a nested cursor

Like any other iterator, product_master_iterator must be populated using a SELECT statement. You will recall that when a named iterator is used, the column names in the SELECT statement must match the names of the variables in the iterator class. This remains true even when a nested cursor is used. The key is that you must treat the nested cursor as a single column, giving it a name that matches the one you gave the nested iterator when you defined the master iterator class. To do this, use the AS clause in the SELECT statement to provide an alias for the nested cursor; for example:

#sql product_master_iterator = {   SELECT     name, CURSOR (       SELECT         name, price       FROM         products       WHERE         products.type_id = product_types.id     ) AS product_detail   FROM     product_types };

In this case, the alias given to the nested cursor is product_detail, which matches the name given to the nested iterator. When this SQLJ statement is executed, the product_master_iterator object is populated with the master rows. The embedded product_detail iterator object for each master row is populated with the product detail rows returned by the nested cursor.

3.6.1.4 Reading rows from a nested iterator

In order to access the product detail rows stored in the product_detail iterator, you must declare another iterator object (which I'll refer to as a work iterator) of class ProductDetailIteratorClass:

ProductDetailIteratorClass product_detail_iterator;

To actually get at the detail rows, you must first call the product_detail( ) method in product_master_iterator and store the returned iterator in your work iterator:

product_detail_iterator = product_master_iterator.product_detail(  );

The product detail rows can now be accessed using the usual named iterator accessor methods in product_detail_iterator. For example, to access the product name, you use product_detail_iterator.name( ). As you navigate through each row in product_master_iterator, you must retrieve each new instance of product_detail and place it in the work variable named product_detail_iterator. If you were writing Java code to display the results from the query shown earlier, you might use two while loops, as shown in the following example. The outer while loop iterates through all the rows contained in product_master_iterator. The inner while loop is executed for each of those rows and iterates through the detail rows that belong to each master row:

// read the master rows from the master iterator object while (product_master_iterator.next(  )) {   // display the product type   System.out.println("Product type = " + product_master_iterator.name(  ));   // declare a detail iterator object to read the product detail rows   ProductDetailIteratorClass product_detail_iterator;   // set the detail iterator object to the detail rows in the   // master iterator object   product_detail_iterator = product_master_iterator.product_detail(  );   // read the detail rows from the detail iterator object   while (product_detail_iterator.next(  )) {     // display the product name and price     System.out.println("  Product name = " + product_detail_iterator.name(  ) +       ", price = " + product_detail_iterator.price(  ));   } // end of inner while loop   // close the detail iterator object   product_detail_iterator.close(  ); } // end of outer while loop // close the master iterator object product_master_iterator.close(  );

The example program FundamentalExample4.sqlj (not printed in this book, but available for download from the web site) uses the steps illustrated in this section to display the product types, along with the related product names and prices, using a nested cursor and named iterators.

3.6.2 Nested Cursors with Positional Iterators

This section explores the differences involved when using a nested cursor with a positional iterator as opposed to with a named iterator.

3.6.2.1 Declaring a positional iterator for a nested cursor

When declaring positional iterators for nested cursors, you follow the same pattern as when using a named iterator except that you do not specify variable names. With respect to my previous example, the following positional iterator classes can be used to define the iterator objects:

#sql public static iterator ProductDetailIteratorClass (   String,   double ); #sql private static iterator ProductMasterIteratorClass (   String,   ProductDetailIteratorClass );
3.6.2.2 Declaring an iterator object

After declaring the necessary iterator types, the next step is to declare an iterator object of the ProductMasterIteratorClass to store the master rows. You can do that with the following declaration:

ProductMasterIteratorClass product_master_iterator;

You'll notice that this step is no different than when using a named iterator.

3.6.2.3 Populating a positional iterator when using a nested cursor

There is one difference to be aware of when using a SELECT statement with a nested cursor to populate a positional iterator as opposed to a named iterator. With a positional iterator, you don't need to use an AS clause to create an alias for the nested cursor because the iterator variable is matched to the nested cursor column in the SELECT statement by position. For example, the following SQLJ statement can be used to populate the positional iterator product_master_iterator:

#sql product_master_iterator = {   SELECT     name, CURSOR (       SELECT         name, price       FROM         products       WHERE         products.type_id = product_types.id     )   FROM     product_types };
3.6.2.4 Reading rows from a nested positional iterator

Two while loops are once again used to display the master and detail rows, but a FETCH statement is used to access the product_master_iterator and product_detail_iterator positional iterators. The following example illustrates this:

boolean finished = false; String product_type = null; ProductDetailIteratorClass product_detail_iterator = null; // read the master rows from the master iterator object while (!finished) {   #sql {     FETCH       :product_master_iterator     INTO       :product_type, :product_detail_iterator   };   if (product_master_iterator.endFetch(  )) {     finished = true;   } else {     // display the product type     System.out.println("Product type = " + product_type);     boolean finished_inner = false;     String product_name = null;     double product_price = 0;     // read the detail rows from the detail iterator object     while (!finished_inner) {       #sql {         FETCH           :product_detail_iterator         INTO           :product_name, :product_price       };       if (product_detail_iterator.endFetch(  )) {         finished_inner = true;       } else {         // display the product name and price         System.out.println("  Product name = " + product_name +           ", price = " + product_price);       } // end of inner if     } // end of inner while loop     // close the detail iterator object     product_detail_iterator.close(  );   } // end of outer if } // end of outer while loop // close the master iterator object product_master_iterator.close(  );

The basic concept here is the same as when the nested iterator was a named iterator. You still have an outer loop to process the master records and an inner loop to process the detail records. You still extract each detail iterator to a work iterator variable. The difference is that because positional notation is used, you must use the FETCH statement rather than individual column accessor functions.

The example program FundamentalExample5.sqlj (not printed in this book but available for download) uses the steps illustrated in this section to display the product types, along with the related product names and prices, using a nested cursor and positional iterators.

< BACKCONTINUE >

Index terms contained in this section

AS clause, SELECT statement
CURSOR clause
foreign keys
iterators, SQLJ
      nested cursors
nested cursors
      aliases
      CURSOR clause
      iterator classes
      iterator objects, declaring
      named iterators
      reading rows from
product_detail( ) method
product_detail_iterator.name( )



Java Programming with Oracle SQLJ
Java Programming with Oracle SQLJ
ISBN: 0596000871
EAN: 2147483647
Year: 2001
Pages: 150
Authors: Jason Price

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net