RelationalDBDesign RelationalDBDesign

Composite Datatypes   «Prev 

Using the %Type attribute

The %Type attribute is particularly useful when declaring a variable that refers to a column within a database table.
Consider a typical software development environment where the column width or datatype could possibly change until the very last day of development. If you use the %TYPE or %ROWTYPE attributes within your stored procedures and functions for defining the variables, changes in column width or datatype will not affect the procedures and functions. This is because the variables that you defined will get their attributes at runtime only, and would never be affected by the database change.
This feature will save you a lot of time and will also help you build robust code in the face of ever-changing databases!

Declaring Collections with %TYPE

TYPE few_depts IS VARRAY(10) OF VARCHAR2(30);
TYPE many_depts IS VARRAY(100) OF VARCHAR2(64);
some_depts few_depts;
/* If the type of some_depts changes from few_depts to many_depts,
local_depts and global_depts will use the same type
when this block is recompiled */
local_depts some_depts%TYPE;
global_depts some_depts%TYPE;
You can declare collections as the formal parameters of subprograms. That way, you can pass collections to stored subprograms and from one subprogram to another.
%TYPE: Provides the data type of a previously declared user-defined record.