 |
|
PL/SQL
%TYPE And %ROWTYPE
Oracle Tips by
Burleson
|
PL/SQL allows variables to be declared dynamically, depending on
the type of a referenced column or variable. Consider this code:
vFirstName STUDENTS.first_name%TYPE;
Students_rec Students_cur%ROWTYPE;
The vFirstName variable will have the same datatype and size
as the column first_name in the STUDENTS table. If the
length of the first_name column is increased, the length of
vFirstName will also be increased. The use of %TYPE allows
you to develop a variable that is dependent on the structure of your
data; if the structure of the data changes, your variable will change
as well.
Using %TYPE references to variables is an excellent idea for
making maintenance of your code simpler; however, this is not a
complete solution. Imagine that you have a variable referencing a
column of type integer ; if this changes to type number
or one of the other subtypes of type number, %TYPE has
done its job.
Now imagine that your variable is a %TYPE reference to a
number column, perhaps a unique sequence number for records. If a
business rule changes, you may suddenly find yourself with a sequence
that includes characters and a block of code that calls a numeric
function such as min() or max(). In this instance, your
code will have to change.
Admittedly, this is a fairly radical change in the data model that
your code is based upon, but it has been known to occur. Using
%TYPE is a good idea (fields changing in size is quite common,
especially during system development), but don’t expect %TYPE
to solve all your future maintenance woes.
%ROWTYPE is very similar to %TYPE, except the it
creates a variable of record type. %ROWTYPE is typically used
with cursors, but can duplicate the structure of a table or record as
well.
This is an
excerpt from the book "High Performance Oracle Database
Automation" by Jonathan Ingram and Donald K. Burleson, Series
Editor. |
