Java Programming with Oracle SQLJ

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