RelationalDBDesign RelationalDBDesign

Composite Datatypes   «Prev  Next»
Lesson 4 The %Type attributes
Objective Use %Type and %RowType attributes to manipulate data.

Oracle %Type Attributes

PL/SQL objects (such as variables and constants) and database objects (such as columns and tables) include certain attributes that can be used to simplify variable and constant declarations.

%Type attribute

The %Type attribute provides the data type of a variable, constant, or column. Variable and constant declarations using the %Type attribute are treated like declarations that explicitly state the datatype. They can also be initialized.

Note that a NOT NULL column constraint does not apply to variables declared using %TYPE. Click the link below to hear more about using an %Type attribute.
Type Attributes
In the example below, you declare a variable v_prod_id of the same type as PRODUCT_ID, a field within the PRODUCT table:
v_prod_id product.product_id%TYPE;
Such a declaration has two advantages:
  1. The exact data type of PRODUCT_ID need not be known.
  2. If the Oracle definition of PRODUCT_ID is changed, the data type of v_prod_id changes accordingly at runtime.

In addition to the %ROWTYPE declaration, you can use the %TYPE declaration to inherit datatype information. If you use the %ROWTYPE declaration, the variable inherits the column and datatype information for all the columns in the cursor's result set. If you use the %TYPE declaration, the variable only inherits the definition of the column used to define it.
You can even base %TYPE definitions on cursors, as shown in the following example:
cursor rad_cursor is
select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
rad_val_radius rad_val.Radius%TYPE;

In the preceding listing, the rad_val variable inherits the datatypes of the result set of the rad_cursor cursor. The rad_val_radius variable inherits the datatype of the Radius column within the rad_val variable.
The advantage of datatype anchoring using %ROWTYPE and %TYPE definitions is that it makes the datatype definitions in your PL/SQL code independent of the underlying data structures. If the RADIUS_VALS Radius column is changed from a NUMBER(5) datatype to a NUMBER(4,2) datatype, you do not need to modify your PL/SQL code; the datatype assigned to the associated variables will be determined dynamically at runtime.

%ROWTYPE attribute

The %ROWTYPE attribute is useful if a record variable must be declared that has the same structure as a row within a table or view, or as a row fetched from a cursor. The row is represented as a record whose fields have the same names and datatypes as the columns within the table or view.

An %ROWTYPE declaration cannot include an initialization clause.
To declare a record that can store an entire row from the PRODUCT table, %ROWTYPE can be used as follows:
product_rec product%ROWTYPE;
The column values are stored within individual fields of the record. You can reference a specific field by using the following syntax:

You can assign a value to a field by using the following syntax:
product_rec.product_id := 11;

The next lesson concludes this module.

PL/SQL Composite Data Types

Click the link below to read about the correct use of composite datatypes within PL/SQL.
PL/SQL Composite Data Types