How can you check to see if two records are equal (i.e., that each corresponding field contains the same value)? It would be wonderful if PL/SQL would allow you to perform a direct comparison, as in:
DECLARE first_book summer.reading_list_rt:= summer.must_read; second_book summer.reading_list_rt:= summer.wifes_favorite;BEGIN IF first_book = second_book THEN lots_to_talk_about; END IF;END;Unfortunately, you cannot do that. Instead, to test for record equality, you must write code that compares each field individually. If a record doesn't have many fields, this isn't too cumbersome. For the reading list record, you would write something like this:
DECLARE first_book summer.reading_list_rt:= summer.must_read; second_book summer.reading_list_rt:= summer.wifes_favorite;BEGIN IF first_book.favorite_author = second_book.favorite_author AND first_book.title = second_book.title AND first_book.finish_by = second_book.finish_by THEN lots_to_talk_about; END IF;END;There is one complication to keep in mind. If your requirements indicate that two NULL records are equal (equally NULL), you will have to modify each comparison to something like this:
(first_book.favorite_author = second_book.favorite_author OR(first_book.favorite_author IS NULL AND second_book.favorite_author IS NULL)Any way you look at it, this is pretty tedious coding. Wouldn't it be great if we could generate code to do this for us? In fact, it's not all that difficult to do precisely thatat least if the records you want to compare are defined with %ROWTYPE against a table or view. In this case, you can obtain the names of all fields from the ALL_TAB_COLUMNS data dictionary view and then format the appropriate code out to the screen or to a file.
Better yet, you don't have to figure all that out yourself. Instead, you can download and run the "records equal" generator designed by Dan Spencer; you will find his package on the O'Reilly web site in the gen_record_comparison.pkg file.
11.2 Collections in PL/SQL
A collection is a datatype that offers a way to store singly dimensioned arrays in PL/SQL. Great. So what does that mean? You will use collections to create lists of related information, either in your PL/SQL program or in the column of a database table. Here are some of the ways we've found collections handy:
Emulate bidirectional or random-accesscursors. PL/SQL only allows you to fetch forward through a cursor's result set. But if I load the result set of a cursor into a collection, I can move back and forth through that set, and can instantly (and repetitively) access any particular row in the set.
Improve performance of lookups by storing lists of subordinate information directly in the column of a table (as a nested table or VARRAY), rather than normalizing that data into a separate relational table. (Nested tables, VARRAYs, and associative arrays are collection types described in the upcoming section Section 11.2.2.)
Keep track of data elements selected in a program for special processing.
Cache database information that is static and frequently queried to speed up performance of those queries.
In the following sections we'll show how to create and use collection types both in the database and in PL/SQL programs, and show the syntax for creating collection types. We'll present the three different initialization techniques with additional examples, and review the built-in "methods," such as NEXT, DELETE, and TRIM, for managing collection content. Although we can't cover every aspect of SQL's use of collections (for example, as columns in tables), the examples will give you a sense of how importantand usefulthese new devices can be, despite their complexity.
