O'Reilly logo

Java Programming with Oracle SQLJ by Jason Price

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required