6.7 Column Aliases in Cursors

The SELECT statement of the cursor includes the list of columns that are returned by that cursor. Just as with any SELECT statement, this column list may contain either actual column names or column expressions, which are also referred to as calculated or virtual columns.

A column alias is an alternative name you provide to a column or column expression in a query. You may have used column aliases in SQL*Plus in order to improve the readability of ad hoc report output. In that situation, such aliases are completely optional. In an explicit cursor, on the other hand, column aliases are required for calculated columns when:

• You FETCH into a record declared with a %ROWTYPE declaration against that cursor.

• You want to reference the calculated column in your program.

Consider the following query. For all companies with sales activity during 1994, the SELECT statement retrieves the company name and the total amount invoiced to that company (assume that the default date format mask for this instance is `DD-MON-YYYY'):

SELECT company_name, SUM (inv_amt)
  FROM company C, invoice I
 WHERE C.company_id = I.company_id
   AND I.invoice_date BETWEEN '01-JAN-1994' AND '31-DEC-1994';

If you run this SQL statement in SQL*Plus, the output will look something like this:

COMPANY_NAME                         SUM (INV_AMT)
------------------------                         --------------------------
ACME TURBO INC.                      1000
WASHINGTON HAIR CO.                  25.20

SUM (INV_AMT) does not make a particularly attractive column header for a report, but it works well enough for a quick dip into the data as an ad hoc query. Let's now use this same query in an explicit cursor and add a column alias:

DECLARE
   CURSOR comp_cur IS
      SELECT company_name, SUM (inv_amt) total_sales
        FROM company C, invoice I
       WHERE C.company_id = I.company_id
         AND I.invoice_date BETWEEN '01-JAN-1994' AND '31-DEC-1994';
   comp_rec comp_cur%ROWTYPE;
BEGIN
   OPEN comp_cur;
   FETCH comp_cur INTO comp_rec;
   ...
END;

With the alias in place, I can get at that information just as I would any other column in the query:

IF comp_rec.total_sales > 5000
THEN
   DBMS_OUTPUT.PUT_LINE
      (' You have exceeded your credit limit of $5000 by ' ||
       TO_CHAR (5000-company_rec.total_sales, '$9999'));
END IF;

If you fetch a row into a record declared with %ROWTYPE, the only way to access the column or column expression value is to do so by the column name -- after all, the record obtains its structure from the cursor itself.