|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.
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.
In the example below, you declare a variable v_prod_id of the same type as PRODUCT_ID, a field within the PRODUCT table:
Such a declaration has two advantages:
- The exact data type of PRODUCT_ID need not be known.
- 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;
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.
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:
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