PL/SQL Collection Concepts

Collections :

                    A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

RECORD 

A record is a group of related data items stored in fields, each with its own name and datatype. Suppose you have various data about an employee such as name, salary, and hire date. These items are logically related but dissimilar in type. A record containing a field for each item lets you treat the data as a logical unit. Thus, records make it easier to organize and represent information.

                   The attribute %ROWTYPE lets you declare a record that represents a row in a database table. However, you cannot specify the datatypes of fields in the record or declare fields of your own. The datatype RECORD lifts those restrictions and lets you define your own records

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;