19.7 Example: PL/SQL-to-Server Integration

To provide an(other) demonstration of how collections can ease the burden of transferring data between server and PL/SQL application program, let's look at a new example. The main entity in this example is the "apartment complex." We use a nested table of objects in order to hold the list of apartments for each apartment complex.

Each apartment is described by the following attributes:

CREATE TYPE Apartment_t AS OBJECT (
   unit_no NUMBER,
   square_feet NUMBER,
   bedrooms NUMBER,
   bathrooms NUMBER,
   rent_in_dollars NUMBER
);

And we can now define the nested table type which will hold a list of these apartment objects:

CREATE TYPE Apartment_tab_t AS TABLE OF Apartment_t;

Using this type as the type of a column, here is the definition of our database table:

CREATE TABLE apartment_complexes
  (name VARCHAR2(75),
   landlord_name VARCHAR2(45),
   apartments Apartment_tab_t)
NESTED TABLE apartments STORE AS apartments_store_tab;

If you're curious, the INSERT statements to populate such a table look like the following (note the use of nested constructors to create the collection of objects):

INSERT INTO apartment_complexes VALUES
   ('RIVER OAKS FOUR', 'MR. JOHNSON',
     Apartment_tab_t(
        Apartment_t(1, 780, 2, 1, 975),
        Apartment_t(2, 1200, 3, 2, 1590),
        Apartment_t(3, 690, 1, 1.5, 800),
        Apartment_t(4, 690, 1, 2, 450),
        Apartment_t(5, 870, 2, 2, 990)
     )
   );
INSERT INTO apartment_complexes VALUES
   ('GALLERIA PLACE', 'MS. DODENHOFF',
     Apartment_tab_t(
        Apartment_t(101, 1000, 3, 2, 1295),
        Apartment_t(102, 800, 2, 1, 995),
        Apartment_t(103, 800, 2, 1, 995),
        Apartment_t(201, 920, 3, 1.5, 1195),
        Apartment_t(202, 920, 3, 1.5, 1195),
        Apartment_t(205, 1000, 3, 2, 1295)
     )
  );

Now, at last, we can show off some wonderful features of storing collections in the database.

Imagine that we are the new managers of the River Oaks Four apartments (hardly large enough to qualify as a complex) and we want to demolish any unit that rents for less than $500, and raise the rent on everything else by 15%.

DECLARE
   /* Declare the cursor that will retrieve the collection of
   || apartment objects. Since we know we're going to update the
   || record, we can lock it using FOR UPDATE.
   */
   CURSOR aptcur IS
      SELECT apartments
        FROM apartment_complexes
       WHERE name = 'RIVER OAKS FOUR'
         FOR UPDATE OF apartments;

   /* Need a local variable to hold the collection of fetched
   || apartment objects.
   */
   l_apartments apartment_tab_t;
   which INTEGER;
BEGIN
   /* A single fetch is all we need! */
   OPEN aptcur;
   FETCH aptcur INTO l_apartments;
   CLOSE aptcur;

   /* Iterate over the apartment objects in the collection and
   || delete any elements of the nested table which meet the
   || criteria
   */
   which := l_apartments.FIRST;
   LOOP
      EXIT WHEN which IS NULL;
      IF l_apartments(which).rent_in_dollars < 500
      THEN
         l_apartments.DELETE(which);
      END IF;
      which := l_apartments.NEXT(which);
   END LOOP;

   /* Now iterate over the remaining apartments and raise the
   || rent. Notice that this code will skip any deleted
   || elements.
   */
   which := l_apartments.FIRST;
   LOOP
      EXIT WHEN which IS NULL;
      l_apartments(which).rent_in_dollars :=
         l_apartments(which).rent_in_dollars * 1.15;
      which := l_apartments.NEXT(which);

   END LOOP;

   /* Finally, ship the entire apartment collection back to the
   || server -- in a single statement!
   */
   UPDATE apartment_complexes
      SET apartments = l_apartments
    WHERE name = 'RIVER OAKS FOUR';

END;

To me, one of the most significant aspects of this example is the single-statement fetch (and store). This PL/SQL fragment emulates the creating of a "client-side cache" of data, which is an essential concept in many object-oriented and client-server architectures. Using this kind of approach with collections can reduce network traffic and improve the quality of your code.