Assigning Values to and from Records

You can modify the values in a record in the following ways:

·          

     Direct field assignment with the assignment operator

·         SELECT INTO from an implicit cursor

·         FETCH INTO from an explicit cursor

·         Aggregate assignment

These assignment methods are described in the sections that follow.

 Direct Field Assignment

The assignment operator ( := ) changes the value of a particular field. In the first assignment, total_sales is zeroed out. In the second assignment, a function is called to return a value for the Boolean flag output_generated (it is set to either TRUE or FALSE):

top_customer_rec.total_sales:= 0;

report_rec.output_generated:=

   check_report_status (report_rec.report_id);

In the next example I create a record based on the rain_forest_history table, populate it with values, and then insert a record into that same table: 

DECLARE

   rain_forest_rec rain_forest_history%ROWTYPE;

BEGIN

   /* Set values for the record */

   rain_forest_rec.country_code  := 1005;

   rain_forest_rec.analysis_date := SYSDATE;

   rain_forest_rec.size_in_acres := 32;

   rain_forest_rec.species_lost  := 425;

   /* Insert a row in the table using the record values */

   INSERT INTO rain_forest_history VALUES

      (rain_forest_rec.country_code,

       rain_forest_rec.analysis_date,

       rain_forest_rec.size_in_acres,

       rain_forest_rec.species_lost);

   ...

END;

 

SELECT INTO from an Implicit Cursor

Uses the implicit cursors SELECT INTO to fill the values in a record. You can SELECT INTO either the record as a whole or the individual fields in the record: 

DECLARE

   TYPE customer_sales_rectype IS RECORD

      (customer_id   NUMBER (5),

       customer_name customer.name%TYPE,

       total_sales   NUMBER (15,2)

       );

   top_customer_rec  customer_sales_rectype;

BEGIN

   /* Move values directly into the record: */

SELECT customer_id, name, SUM (sales)

     INTO top_customer_rec

     FROM customer

    WHERE sold_on BETWEEN < ADD_MONTHS (SYSDATE, -3);

   /* or list the individual fields: */

   SELECT customer_id, name, SUM (sales)

INTO top_customer_rec.customer_id, top_customer_rec.customer_name,

     top_customer_rec.total_sales

     FROM customer

    WHERE sold_on BETWEEN < ADD_MONTHS (SYSDATE, -3);

If you SELECT INTO a record, you must be sure that the structure of the select list (columns or expressions) matches that of the record.

The INTO clause of an implicit query is the only part of a SQL DML statement in which a PL/SQL record (as an aggregate and not its component fields) can be referenced.

 

FETCH INTO from an Explicit Cursor

Use an explicit cursor to FETCH values INTO a record. You can FETCH INTO the record as a whole or into the individual fields in the record:

 DECLARE

   /*

   || Declare a cursor and then define a record based on that cursor

   || with the %ROWTYPE attribute.

   */

   CURSOR cust_sales_cur IS

       SELECT customer_id, name, SUM(sales) tot_sales

       FROM customer

       WHERE sold_on BETWEEN < ADD_MONTHS (SYSDATE, -3);

   cust_sales_rec cust_sales_cur%ROWTYPE;

BEGIN

/* Move values directly into record by fetching from cursor */

   OPEN cust_sales_cur;

   FETCH cust_sales_cur INTO cust_sales_rec;

   /* or fetch values from the select list into individual fields. */

   OPEN cust_sales_cur;

   FETCH cust_sales_cur

      INTO cust_sales_rec.customer_id,

           cust_sales_rec.customer_name,

           cust_sales_rec.total_sales;

If you FETCH INTO the record without specifying the fields, you must be sure that the structure of the cursor's select list (columns or expressions) matches that of the record.

Aggregate Assignment

In this last and most powerful approach to record assignments, I change all the values of the record once, through an aggregate assignment or assignment of the group. When you SELECT INTO the entire record without listing its individual fields, you perform a type of aggregate assignment. But you can also change the values of every field in a record simultaneously with the assignment operator ( := ). In the following example I declare two different rain_forest_history records and then set the current history information to the previous history record: 

DECLARE

   prev_rain_forest_rec rain_forest_history%ROWTYPE;

   curr_rain_forest_rec rain_forest_history%ROWTYPE;

BEGIN

   ... Initialize previous year rain forest data...

   -- Transfer data from previous to current records.

curr_rain_forest_rec:= prev_rain_forest_rec;

The result of this aggregate assignment is that the value of each field in the current record is set to the value of the corresponding field record in the previous record. I could also have accomplished this with individual direct assignments from the previous to current records. This would require four separate assignments and lots of typing: 

curr_rain_forest_rec.country_code:= prev_rain_forest_rec.country_code;

curr_rain_forest_rec.analysis_date:= prev_rain_forest_rec.analysis_date;

curr_rain_forest_rec.size_in_acres:= prev_rain_forest_rec.size_in_acres;

curr_rain_forest_rec.species_lost:= prev_rain_forest_rec.species_lost;

Which of these two types of assignments would you rather code? Which would you rather have to maintain?

I was able to perform this aggregate assignment because both of the records were based on the same row type. If the records are not compatible in this way, your assignment will not compile.

The next section on record types and record compatibility explores the restrictions on use of aggregate assignments and other record operations.