PL/SQL Collections and Records

PL/SQL Collections and Records:

Difference between Nested Tables and Associative Arrays.

Nested tables can be stored in a database column, but associative arrays cannot. Nested tables are appropriate for important data relationships that must be stored persistently.

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end. Nested table data is stored out-of-line in a store table, a system-generated database table associated with the nested table. 

Varrays are dense, so you cannot delete their individual elements.

Example: Declaring Nested Tables, Varrays, and Associative Arrays

DECLARE

   TYPE nested_type IS TABLE OF VARCHAR2(20);

   TYPE varray_type IS VARRAY(50) OF INTEGER;

   TYPE associative_array_type IS TABLE OF NUMBER

      INDEXED BY BINARY_INTEGER;

   v1 nested_type;

   v2 varray_type;

   v3 associative_array_type;

Arrays have a fixed upper bound, but nested tables are unbounded (see Figure 5-1). So, the size of a nested table can increase dynamically.

Example: Constructor for a Nested Table

In the following example, you pass multiple elements to the constructor CourseList(), which returns a nested table containing those elements:

DECLARE

   TYPE CourseList IS TABLE OF VARCHAR2(16);

   my_courses CourseList;

BEGIN

   my_courses :=

      CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100');  --Initialization of Nested Tabe.

END;

Example: Empty Varray Constructor

If you call a constructor without arguments, you get an empty but non-null collection:

DECLARE

   TYPE Clientele IS VARRAY(100) OF Customer;

   vips Clientele := Clientele(); -- initialize empty varray 

BEGIN

   IF vips IS NOT NULL THEN  -- condition yields TRUE

      ...

   END IF;

END;

In this case, you can call the collection's EXTEND method to add elements later.

Decreasing the Size of a Collection (TRIM Method)

This procedure has two forms:

• TRIM removes one element from the end of a collection.
• TRIM(n) removes n elements from the end of a collection.

For example, this statement removes the last three elements from nested table courses:

courses.TRIM(3);

Example: Possible Exceptions for Collection Assignments

Assigning a value to a collection element can cause various exceptions:

• If the subscript is null or is not convertible to the right datatype, PL/SQL raises the predefined exception VALUE_ERROR. Usually, the subscript must be an integer. Associative arrays can also be declared to have VARCHAR2 subscripts.
• If the subscript refers to an uninitialized element, PL/SQL raises SUBSCRIPT_BEYOND_COUNT.
• If the collection is atomically null, PL/SQL raises COLLECTION_IS_NULL.

·         DECLARE

·            TYPE WordList IS TABLE OF VARCHAR2(5);

·            words WordList;

·         BEGIN

·            /* Assume execution continues despite the raised exceptions. */

·         -- Raises COLLECTION_IS_NULL. We haven't used a constructor yet.

·         -- This exception applies to varrays and nested tables, but not

·         -- associative arrays which don't need a constructor.

·            words(1) := 10;

·         -- After using a constructor, we can assign values to the elements.

·            words := WordList(10,20,30);

·         -- Any expression that returns a VARCHAR2(5) is OK.

·            words(1) := 'yes';

·            words(2) := words(1) || 'no';

·         -- Raises VALUE_ERROR because the assigned value is too long.

·            words(3) := 'longer than 5 characters';

·         -- Raises VALUE_ERROR because the subscript of a nested table must

·         -- be an integer.

·            words('B') := 'dunno';

·         -- Raises SUBSCRIPT_BEYOND_COUNT because we only made 3 elements

·         -- in the constructor. To add new ones, we must call the EXTEND

·         -- method first.

·            words(4) := 'maybe';

END;

SQL> CREATE TABLE department (

  2  name     VARCHAR2(20),

  3  director VARCHAR2(20),

  4  office   VARCHAR2(20),

  5  courses  CourseList) 

  6  NESTED TABLE courses STORE AS courses_tab;

Each item in column COURSES is a nested table that will store the courses offered by a given department. The NESTED TABLEclause is required whenever a database table has a nested table column. The clause identifies the nested table and names a system-generated store table, in which Oracle stores the nested table data.

Example: Inserting a Nested Table into a Database Table

Now, you can populate the database table. The table constructor provides values that all go into the single column COURSES:

BEGIN

   INSERT INTO department

      VALUES('English', 'Lynn Saunders', 'Breakstone Hall 205',

         CourseList('Expository Writing',

                    'Film and Literature',

                    'Modern Science Fiction',

                    'Discursive Writing',

                    'Modern English Grammar',

                    'Introduction to Shakespeare',

                    'Modern Drama',

                    'The Short Story',

                    'The American Novel'));

END;

DECLARE

   new_projects ProjectList :=

      ProjectList(Project(1, 'Issue New Employee Badges', 13500),

                  Project(2, 'Develop New Patrol Plan', 1250),

                  Project(3, 'Inspect Emergency Exits', 1900),

                  Project(4, 'Upgrade Alarm System', 3350),

                  Project(5, 'Analyze Local Crime Stats', 825));

BEGIN

   UPDATE department

      SET projects = new_projects WHERE dept_id = 60;

END;

In the next example, you retrieve all the projects for the Accounting Department into a local varray:

DECLARE

   my_projects ProjectList;

BEGIN

   SELECT projects INTO my_projects FROM department

      WHERE dept_id = 30;

END;

ORACLE RECORD TYPE.

In SQL*Plus, suppose you define object type Passenger, as follows:

SQL> CREATE TYPE Passenger AS OBJECT(
  2  flight_no NUMBER(3), 
  3  name      VARCHAR2(20), 
  4  seat      CHAR(5));

Next, you define VARRAY type PassengerList, which stores Passenger objects:

SQL> CREATE TYPE PassengerList AS VARRAY(300) OF Passenger;

Finally, you create relational table flights, which has a column of type PassengerList, as follows:

SQL> CREATE TABLE flights (
  2  flight_no  NUMBER(3),
  3  gate       CHAR(5),
  4  departure  CHAR(15),
  5  arrival    CHAR(15),
  6  passengers PassengerList);

Each item in column passengers is a varray that will store the passenger list for a given flight. Now, you can populate database table flights, as follows:

BEGIN
   INSERT INTO flights
      VALUES(109, '80', 'DFW 6:35PM', 'HOU 7:40PM',
         PassengerList(Passenger(109, 'Paula Trusdale', '13C'),
                       Passenger(109, 'Louis Jemenez', '22F'),
                       Passenger(109, 'Joseph Braun', '11B'), ...));
   INSERT INTO flights
      VALUES(114, '12B', 'SFO 9:45AM', 'LAX 12:10PM',
         PassengerList(Passenger(114, 'Earl Benton', '23A'),
                       Passenger(114, 'Alma Breckenridge', '10E'),
                       Passenger(114, 'Mary Rizutto', '11C'), ...));
   INSERT INTO flights
      VALUES(27, '34', 'JFK 7:05AM', 'MIA 9:55AM',
         PassengerList(Passenger(27, 'Raymond Kiley', '34D'),
                       Passenger(27, 'Beth Steinberg', '3A'),
                       Passenger(27, 'Jean Lafevre', '19C'), ...));
END;

In the example below, you fetch rows from database table flights into record flight_info. That way, you can treat all the information about a flight, including its passenger list, as a logical unit.

DECLARE
   TYPE FlightRec IS RECORD (
      flight_no  NUMBER(3),
      gate       CHAR(5), 
      departure  CHAR(15),
      arrival    CHAR(15),
      passengers PassengerList);
   flight_info FlightRec;
   CURSOR c1 IS SELECT * FROM flights;
   seat_not_available EXCEPTION;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO flight_info;
      EXIT WHEN c1%NOTFOUND;
      FOR i IN 1..flight_info.passengers.LAST LOOP
         IF flight_info.passengers(i).seat = 'NA' THEN
            dbms_output.put_line(flight_info.passengers(i).name);
            RAISE seat_not_available;
         END IF;
         ...
      END LOOP;
   END LOOP;
   CLOSE c1;
EXCEPTION
   WHEN seat_not_available THEN
      ...
END;

'via Blog this'