Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide)
ISBN: 0596514468
EAN: 2147483647
EAN: 2147483647
Year: 2004
Pages: 234
Pages: 234
Authors: Steven Feuerstein, Bill Pribyl
| Chapter 9 Records in PL/SQL |
As you may have noticed in earlier examples, you can include a record as a field within another record. This is called a nested record. The record that contains the nested record as a field is called the enclosing record.
You may wonder when and why you might ever use such an elaborate and seemingly convoluted structure. After considering the following example, however, you will realize that the data in your applications is full of structures which are best represented as nested records. In many ways, nested records serve to represent normalized data structures within fixed PL/SQL memory storage.
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 which 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 */ cell_phone# phone_rectype /* Nested record */ ); auth_rep_info_rec contact_set_rectype; BEGIN
Without nested records, my contact_set_rectype would look like this:
DECLARE TYPE phone_rectype IS RECORD (day_intl_prefix VARCHAR2(2), day_area_code VARCHAR2(3), day_exchange VARCHAR2(3), day_phn_number VARCHAR2(4), day_extension VARCHAR2(4), eve_intl_prefix VARCHAR2(2), eve_area_code VARCHAR2(3), eve_exchange VARCHAR2(3), eve_phn_number VARCHAR2(4), eve_extension VARCHAR2(4), fax_intl_prefix VARCHAR2(2), fax_area_code VARCHAR2(3), fax_exchange VARCHAR2(3), fax_phn_number VARCHAR2(4), fax_extension VARCHAR2(4), cell_intl_prefix VARCHAR2(2), cell_area_code VARCHAR2(3), cell_exchange VARCHAR2(3), cell_phn_number VARCHAR2(4), cell_extension VARCHAR2(4) ); auth_rep_info_rec contact_set_rectype; BEGIN
The structure of this unnested record is very cumbersome for two reasons: it is difficult to read and it is hard to maintain.
Unnested records are difficult to read. Even though I used prefixes for each of the phone numbers so that I know which parts belong to which number, it still takes a conscious and belabored effort to sort through the fields and make the connections. There are just too many fields and too much repetition for the mind to focus and take it all in. With the nested record, I shift the complexity of each individual phone number down a level into its own record type. This way the contact_set_rectype is self-explanatory: it's made up of four different phone numbers.
Unnested records are hard to maintain. Today, my phone number might have only five components. Why not just lay them all out there in the record? Because down the road my manager tells me that a phone number needs to also store a PIN (Personal Identification Number) for calling cards. As a result, I have denormalized my data structure within the unnested record (with all the same downside denormalization has in the RDBMS). I now need to add four different fields to this record, all with the same datatypes and the right naming conventions. The more coding I do, the greater the chance that I will introduce errors into that code.
With nested records, on the other hand, I could simply add a single field to the phone_rectype record type. I do not have to change the contact_set_rectype record. The contact_set_rectype automatically has that extra field available to it in each of its nested records. The resulting nested record structure allows me to easily keep all phone numbers in one place and access them instantly.
Although I still use the dot notation to refer to a field with nested records, now I might have to refer to a field which is nested several layers deep inside the structure. To do this I must include an extra dot for each nested record structure. Suppose that I have a record with two levels of nested records:
DECLARE TYPE level3_rectype IS RECORD (textline VARCHAR2(2000)); TYPE level2_rectype IS RECORD (paragraph level3_rectype); TYPE level1_rectype IS RECORD (story level2_rectype); level1_rec level1_rectype;
Then the fully qualified name for the textline variable is:
level1_rec.story.paragraph.textline
Assignments with nested records follow the same rules as those for "flat" records. The following statement, for example, 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;
I can also perform an aggregate assignment of one nested record to another. To set the fax number equal to the home phone number, I simply execute the following:
auth_rep_info_rec.fax_phone# := auth_rep_info_rec.home_phone#;
This is possible because both the fax_phone# and the home_phone# records have the same type, namely the phone_rectype. You can even perform this kind of assignment between records which are nested within different enclosing records, as the following example illustrates:
DECLARE /* Using the phone_rectype defined earlier in chapter... */ TYPE contact_set_rectype IS RECORD (day_phone# phone_rectype, eve_phone# phone_rectype, fax_phone# phone_rectype, cell_phone# phone_rectype ); TYPE emerg_contacts_rectype IS RECORD (emerg1_phone# phone_rectype, emerg2_phone# phone_rectype ); auth_rep_info_rec contact_set_rectype; in_an_emergency_rec emerg_contacts_rectype; BEGIN in_an_emergency_rec.emerg1_phone# := auth_rep_info_rec.day_phone#; END;
The emerg1_phone# and the day_phone# have the same record type: phone_rectype. So even though the enclosing records auth_rep_info_rec and in_an_emergency_rec have different record types, the nested record assignment works. A direct aggregate assignment between the two enclosing records as shown below would fail to compile:
in_an_emergency_rec := auth_rep_info_rec; -- This would fail!
At the beginning of this chapter I mentioned that nested records let you carry over the normalized data structures of your database to the memory structures in your programs. Using nested records I was able to avoid hardcoding the internal structure of a phone number multiple times in my contact set record. Oddly enough, you can also use nested records to take your normalized data from the database and denormalize that data inside your program so it is easier to manipulate.
Well-designed relational data structures are usually normalized, which means (roughly) that you eliminate redundant information and repeating groups. Consider a company entity. It might have four different kinds of addresses: receiving, shipping, accounts payable, and customer relations. Each address is made up of seven attributes or columns: address lines one through four, city, state, and zip code. Rather than burden the company table with 28 (7 4) columns for all four addresses, you would create a separate table of addresses with a foreign key back to the company table. By creating a separate table for company addresses, you also can easily add another kind of address for a company at a later date.
While normalized data and foreign keys make sense enough inside a database, these concepts can be very confusing to users and actually present barriers to efficient and straightforward manipulation of the data within your programs. There is a simple reason for this seeming contradiction: real objects in the world are not normalized. Sure, in the most general sense a company can have four or more addresses. But in the real world, a particular company has only one or two addresses. And those addresses are not separate in any way from the company. They are a part of the definition of that company. Therefore, when data is in the database it is normalized, but whenever it is presented to or entered by the user it needs to be denormalized back to its natural state. Our programs, whether they are screens or reports or batch processes, must translate between these two perspectives on the data.
Nested records provide a highly structured and efficient mechanism to perform this translation. Consider the situation of the company and its multiple addresses. Suppose I have a set of procedures and functions which are used to fetch company information from the database and then manipulate that information. Ideally, I would like to be able to pass to these modules a single record which contains all that company's information. This would be difficult to do if I were to keep my program data normalized like its database counterpart, stored separately, and linked via foreign keys. So how about if I create a nested record?
The following programmer-defined record for the company table contains separate address records for each of the four different addresses:
DECLARE /* First create address record structure and record. */ TYPE comp_address_rectype IS RECORD (addr1 company.addr1%TYPE, addr2 company.addr2%TYPE, addr3 company.addr1%TYPE, addr4 company.addr2%TYPE, city company.city%TYPE, state company.state_abbrev%TYPE, zipcode company.zipcode%TYPE); /* || Now use the address record to declare records as fields in the || master company record type. */ TYPE company_rectype IS RECORD (comp_id company.company_id%TYPE, comp_name company.name%TYPE, recv_address_rec comp_address_rectype, ship_address_rec comp_address_rectype, ap_address_rec comp_address_rectype, cr_address_rec comp_address_rectype); company_rec company_rectype; BEGIN
Following the TYPE statement for the company_rectype record, I created a record called company_rec that contains enough storage space for all four company addresses.
Notice that the address record does not contain the company ID key. I don't have to maintain that relation within the nested address record because the address is always only a component of the overall company record structure, which does have a company ID in the comp_id field. This denormalization makes perfect sense within my program because it now gives me a single identifier, the company_rec, through which I can reference the entire company.
I can now execute procedures and pass this company as a parameter without also having to also tell the procedure to pull along the different addresses as follows:
PROCEDURE display_company (company_rec_in IN company_rectype)
If I maintain separate records for the addresses, thereby paralleling the structures in the database, then each address record would also contain the company ID number to tie it back to the company. Every time I want to work with the company, particularly when I want to pass that company as a parameter through to a procedure, I would also need to list the addresses, as follows:
PROCEDURE display_company (company_rec_in IN company_rectype, recv_address_in IN comp_address_rectype, ship_address_in IN comp_address_rectype, ap_address_in IN comp_address_rectype, cr_address_in IN comp_address_rectype)
What if the company I am currently working with doesn't have an accounts payable address or a customer relations address? Too bad. I have listed these records explicitly in the parameter list and still need to declare the superfluous address records and pass them along in the parameter list. Oddly enough, by denormalizing the data into a single record, I can hide the underlying level of detail and thereby avoid denormalizing the data structure in my parameter list!
In the database you normalize your data structures and therefore your data. In your programs you can always maintain this normalization through the use of records, particularly nested records. It may often make sense, however, to denormalize the data in your programs. This makes it easy to manipulate that information and, particularly, present and validate data entered by the users from their naturally denormalized perspective.
| 9.6 Record Types and Record Compatibility | 10. PL/SQL Tables |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.