[Chapter 9] 9.3 Cursor-Based Records

9.3 Cursor-Based Records

A cursor-based record, or cursor record, is a record whose structure is drawn from the SELECT list of a cursor. (See Chapter 6, Database Interaction and Cursors , for more information on cursors.) Each field in the record corresponds to and has the same name as the column or aliased expression in the cursor's query. This relationship is illustrated by Figure 9.1 .

The same %ROWTYPE attribute used to declare table records is also used to declare a record for an explicitly declared cursor, as the following example illustrates:

DECLARE
   /* Define the cursor */
   CURSOR comp_summary_cur IS
      SELECT C.company_id, name, city
        FROM company C, sales S
       WHERE c.company_id = s.company_id;

   /* Create a record based on that cursor */
   comp_summary_rec comp_summary_cur%ROWTYPE;
BEGIN

The general format of the cursor %ROWTYPE declaration is:

<record_name> <cursor_name>%ROWTYPE;

where <record_name> is the name of the record and <cursor_name> is the name of the cursor upon which the record is based. This cursor must have been previously defined, in the same declaration section as the record, in an enclosing block, or in a package.

9.3.1 Choosing Columns for a Cursor Record

You could declare a cursor record with the same syntax as a table record, but you don't have to match a table's structure. A SELECT statement creates a "virtual table" with columns and expressions as the list of columns. A record based on that SELECT statement allows you to represent a row from this virtual table in exactly the same fashion as a true table record. The big difference is that I get to determine the fields in the record, as well as the names for those fields. Through the cursor you can, therefore, create special-purpose records tailored to a particular program and need.

The query for a cursor can contain all or only some of the columns from one or more tables. A cursor can also contain expressions or virtual columns in its select list. In addition, you can provide aliases for the columns and expressions in the select list of a cursor. These aliases effectively rename the fields in the cursor record.

In the following example I create a cursor against the rain forest history table for all records showing a greater than average loss of species in 1994. Then, for each record found, I execute the publicize_loss procedure to call attention to the problem and execute project_further_damage to come up with an analysis of future losses:

DECLARE
   /*
   || Create a cursor and rename the columns to give them a more
   || specific meaning for this particular cursor and block of code.
   */
   CURSOR high_losses_cur IS
      SELECT country_code  dying_country_cd,
             size_in_acres shrinking_plot,
             species_lost  above_avg_loss
        FROM rain_forest_history
       WHERE species_lost >
               (SELECT AVG (species_lost)
                  FROM rain_forest_history
                 WHERE TO_CHAR (analysis_date, 'YYYY') = '1994');

   /* Define the record for this cursor */
   high_losses_rec high_losses_cur%ROWTYPE;
BEGIN
   OPEN high_losses_cur;
   LOOP
      FETCH high_losses_cur INTO high_losses_rec;
      EXIT WHEN high_losses_cur%NOTFOUND;
      /*
      || Now when I reference one of the record's fields, I use the
      || name I gave that field in the cursor, not the original column
      || name from the table.
      */
      publicize_loss (high_losses_rec.dying_country_cd);
      project_further_damage (high_losses_rec.shrinking_plot);
   END LOOP;
   CLOSE high_losses_cur;
END;

9.3.2 Setting the Record's Column Names

The column aliases change the names of the fields in the record. In the above example, the customized column names are more descriptive of the matter at hand than the standard column names; the code becomes more readable as a result.

A cursor's query can also include calculated values or expressions; in those cases, you must provide an alias for that calculated value if you want to access it through a record. Otherwise, there is no way for PL/SQL to create a named field for that value in the record -- and that name is your handle to the data. Suppose, for example, I have a parameterized cursor and record defined as follows:

CURSOR comp_performance_cur (id_in IN NUMBER) IS
   SELECT name, SUM (order_amount)
     FROM company
    WHERE company_id = id_in;
comp_performance_rec comp_performance_cur%ROWTYPE;

I can refer to the company name with standard dot notation:

IF comp_performance_rec.name = 'ACME' THEN ...

But how can I refer to the sum of the order_amount values? I need to provide a name for this calculated column, as shown below:

CURSOR comp_performance_cur (id_in IN NUMBER)
IS
   SELECT name, SUM (order_amount) tot_sales
     FROM company
    WHERE company_id = id_in;
comp_performance_rec comp_performance_cur%ROWTYPE;

I can now refer to the sum of the order_amount values as follows:

IF comp_performance_rec.tot_sales > 10000 THEN ...

NOTE: Even though the same %ROWTYPE attribute is used in creating both table and cursor records and the declarations themselves look very similar, the record created from a table has a different record type from the record created from a cursor. Records of different types are restricted in how they can interact, a topic we will explore in the next section.