Direct field assignment with the assignment operator
SELECT INTO from an implicit cursor
FETCH INTO from an explicit cursor
These assignment methods are described in the sections that follow.
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;
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.
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.
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 rowtype. 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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.