Field-level operations

When you need to access a field within a record (to either read or change its value), you must usedot notation, just as you would when identifying a column from a specific database table. The syntax for such a reference is:

[schema_name.][package_name.]record_name.field_name

You need to provide a package name only if the record is defined in the specification of a package that is different from the one you are working on at that moment. You need to provide a schema name only if the package is owned by a schema different from that in which you are compiling your code.

Once you have used dot notation to identify a particular field, all the normal rules in PL/SQL apply as to how you can reference and change the value of that field. Let's take a look at some examples.

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):

BEGIN top_customer_rec.total_sales := 0; report_rec.output_generated := check_report_status (report_rec.report_id);END;

In the next example I create a record based on the rain_forest_history table, populate it with values, and theninsert 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 := ADD_MONTHS (TRUNC (SYSDATE), -3); 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 (country_code, analysis_date, size_in_acres, species_lost) VALUES (rain_forest_rec.country_code, rain_forest_rec.analysis_date, rain_forest_rec.size_in_acres, rain_forest_rec.species_lost); ...END;

Notice that because the analysis_date field is of type DATE, I can assign any valid DATE expression to that field. The same goes for the other fields, and this is even true for more complex structures.

Suppose that I have created a nested record structure; that is, one of the fields in my "outer" record is actually another record. In the following example I declare a record type for all the elements of a phone number (phone_rectype), and then declare a record type that collects all the phone numbers for a person together in a single structure (contact_set_rectype).

DECLARE TYPE phone_rectype IS RECORD (intl_prefix VARCHAR2(2), area_code VARCHAR2(3), exchange VARCHAR2(3), phn_number VARCHAR2(4), extension VARCHAR2(4) ); TYPE contact_set_rectype IS RECORD (day_phone# phone_rectype, /* Nested record */ eve_phone# phone_rectype, /* Nested record */ fax_phone# phone_rectype, /* Nested record */ home_phone# phone_rectype, /* Nested record */ cell_phone# phone_rectype /* Nested record */ ); auth_rep_info_rec contact_set_rectype;BEGIN

Although I still use the dot notation to refer to a field with nested records, now I might have to refer to a field that is nested several layers deep inside the structure. To do this I must include an extra dot for each nested record structure, as shown in the following assignment, which sets the fax phone number's area code to the home phone number's area code:

auth_rep_info_rec.fax_phone#.area_code := auth_rep_info_rec.home_phone#.area_code;

Finally, here is an example demonstratingreferences topackaged records (and package-based record TYPEs). Suppose I want to plan out my summer reading (for all those days I will be lounging about in the sand outside my Caribbean hideaway). I create a package specification as follows:

CREATE OR REPLACE PACKAGE summerIS TYPE reading_list_rt IS RECORD ( favorite_author VARCHAR2 (100), title VARCHAR2 (100), finish_by DATE); must_read reading_list_rt; wifes_favorite reading_list_rt;END summer; CREATE OR REPLACE PACKAGE BODY summerISBEGIN -- Initialization section must_read.favorite_author := 'Tepper, Sheri S.'; must_read.title := 'Gate to Women''s Country';END summer;

With this package compiled in the database, I can then construct my reading list as follows:

DECLARE first_book summer.reading_list_rt; second_book summer.reading_list_rt;BEGIN summer.must_read.finish_by := TO_DATE ('01-AUG-2002', 'DD-MON-YYYY'); first_book := summer.must_read; second_book.favorite_author := 'Morris, Benny'; second_book.title := 'Righteous Victims'; second_book.finish_by := TO_DATE ('01-SEP-2002', 'DD-MON-YYYY');END;

I declare two local book records. I then assign a "finish by" date to the packaged must-read book (notice the package.record.field syntax) and assign that packaged record to my first book of the summer record. I then assign values to individual fields for the second book of the summer.

By the way, when you work with the UTL_FILE built-in package for file I/O in PL/SQL, you follow these same rules. the UTL_FILE.FILE_TYPE datatype is actually a record TYPE definition. So when you declare a file handle, you are really declaring a record from a package-based TYPE: