When you work at the record level, you avoid any references to individual fields in the record. Here are the record-level operations currently supported by PL/SQL:
You can copy the contents of one record to another (as long as they are compatible in structure, i.e., have the same number of fields and the same or convertible datatypes).
You can assign a value of NULL to a record with a simple assignment.
You can define and pass the record as an argument in a parameter list.
You can RETURN a record back through the interface of a function.
Several record-level operations are not yet supported:
You cannot use the IS NULL syntax to see if all fields in the record have NULL values. Instead, you must apply the IS NULL operator to each field individually.
You cannot compare two recordsfor example, you cannot ask if the records (the values of their fields) are the same or different, or if one record is greater than or less than another. Unfortunately, to answer these kinds of questions, you must compare each field individually. We cover this topic and provide a utility that generates such comparison code in the later section Section 11.1.5.
Prior to Oracle9i Release 2, you cannot insert into a database table with a record. Instead, you must pass each individual field of the record for the appropriate column. For more information on record-based DML, see Chapter 13.
You can perform record-level operations on any records with compatible structures. In other words, the records must have the same number of fields and the same or convertible datatypes, but they don't have to be the same type. Suppose that I have created the following table:
CREATE TABLE cust_sales_roundup (customer_id NUMBER (5), customer_name VARCHAR2 (100), total_sales NUMBER (15,2));Then the three records defined as follows all have compatible structures, and I can "mix-and-match" the data in these records as shown:
DECLARE cust_sales_roundup_rec cust_sales_roundup%ROWTYPE; CURSOR cust_sales_cur IS SELECT * FROM cust_sales_roundup; cust_sales_rec cust_sales_cur%ROWTYPE; TYPE customer_sales_rectype IS RECORD (customer_id NUMBER(5), customer_name customer.name%TYPE, total_sales NUMBER(15,2)); prefererred_cust_rec customer_sales_rectype;BEGIN -- Assign one record to another. cust_sales_roundup_rec:= cust_sales_rec; prefererred_cust_rec:= cust_sales_rec;END;Let's look at some other examples of record-level operations.
In this example, I'll assign a default value to a record. You can initialize a record at the time of declaration by assigning it another, compatible record. In the following program, I assign an IN argument record to a local variable. I might do this so that I can modify the values of fields in the record:
PROCEDURE compare_companies (prev_company_rec IN company%ROWTYPE) IS curr_company_rec company%ROWTYPE:= prev_company_rec; BEGIN...END;In this next initialization example, I create a new record type and record. I then create a second record type using the first record type as its single column. Finally, I initialize this new record with the previously defined record:
|
|
|
I can also perform assignments within the execution section, as you might expect.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 in the previous record. I could also have accomplished this with individual direct assignments from the previous to current records. This would have required four separate assignments and lots of typing; whenever possible, use record-level operations to save time and make your code less vulnerable to change.
I can move data directly from a row in a table to a record in a program by fetching directly into a record. Here are several examples:
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 (total_sales) tot_sales FROM cust_sales_roundup WHERE sold_on < ADD_MONTHS (SYSDATE, -3) GROUP BY customer_id, name; 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;In this next block, I declare a programmer-defined TYPE that matches the data retrieved by the implicit cursor. Then I SELECT directly into a record based on that type.
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 (total_sales) INTO top_customer_rec FROM cust_sales_roundup WHERE sold_on < ADD_MONTHS (SYSDATE, -3) GROUP BY customer_id, name;I can declare a function that returns a record, and also demonstrate the ability to "null out" a record with a direct assignment.
CREATE OR REPLACE FUNCTION best_seller ( week_in IN PLS_INTEGER, year_in IN PLS_INTEGER) RETURN books%ROWTYPE IS return_value books%ROWTYPE; BEGIN SELECT * INTO return_value FROM books B WHERE week_number = week_in AND year = year_in AND sales = (SELECT MAX (sales) FROM book_sales BS WHERE BS.isbn = B.isbn AND week_number = week_in AND year = year_in); RETURN return_value; EXCEPTION WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN -- Make sure to return NULL. return_value:= NULL; RETURN return_value;END best_seller;Whenever possible, try to work with records at the aggregate level: the record as a whole, and not individual fields. The resulting code is much easier to write and maintain. There are, of course, many situations in which you need to manipulate individual fields of a record. Let's take a look at how you would do that.
