The resulting code much easier write and maintain
• 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 multiple distinct 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 two examples:
DECLARE
TYPE customer_sales_rectype IS RECORD
(customer_id customer.customer_id%TYPE,
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, customer_name, SUM (total_sales) INTO top_customer_rec
FROM cust_sales_roundup
WHERE sold_on < ADD_MONTHS (SYSDATE, −3)
GROUP BY customer_id, customer_name;• I can set all fields of a record to NULL with a direct assignment:
Records in PL/SQL | | | 333 |
---|
RETURN l_return;
END dept_for_name;Whenever possible, try to work with records at the aggregate level—the record as a whole, 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, though. Let’s take a look at how you would do that.
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.
334 | | |
|
---|