Using the %TYPE Attribute
Using Variables and Constants
4-18 Oracle Database 2 Day Developer's Guide
The
%TYPE
attribute supplies the data type of a table column or another variable.
This has the advantages of guaranteeing the correct data type assignment, and the
correct implementation of the function at runtime if the data type of the table
column changes.
The
%ROWTYPE
attribute supplies the definition of a row in a table to a
RECORD
variable. Columns in a table row and the corresponding fields in a
RECORD
have
the same names and data types. The advantages of using
%ROWTYPE
are the same
as for
%TYPE
. See
demonstration.
The following task shows how to use the
%TYPE
attribute in a function. You will edit
the function
calculate_score
to assign to variables
n_score
and
n_weight
the
data types that match the columns of the source tables. Note that the constants
max_
score
and
max_weight
will be used to check equivalence to table values, so they too
must match the table types.
To use the %TYPE attribute:
1.
In the
emp_eval
Body pane, modify function
calculate_score
by changing
the definition of the variables, as shown by the following code. New code is bold
font.
FUNCTION calculate_score(evaluation_id IN
scores.evaluation_id%TYPE
, performance_id IN
scores.performance_id%TYPE
)
RETURN NUMBER AS
n_score
scores.score%TYPE; -- from SCORES
n_weight
performance_parts.weight%TYPE; -- from PERFORMANCE_PARTS
max_score
CONSTANT scores.score%TYPE := 9; -- a constant limit check
max_weight
CONSTANT performance_parts.weight%TYPE := 1;
-- a constant limit check
BEGIN
RETURN NULL;
END calculate_score;
2.
In the
emp_eval
package specification, change the declaration of the function
calculate_score
.
FUNCTION calculate_score(evaluation_id IN
scores.evaluation_id%TYPE
, performance_id IN
scores.performance_id%TYPE
)
RETURN NUMBER;
3.
In the Connections navigation hierarchy, right-click the
emp_eval
package, and
select Compile. Alternatively, use the Ctrl+Shift+F9 keyboard shortcut.
The following message appears in the Messages-Log pane:
