Editing an Existing Function
Using Variables and Constants
Developing and Using Stored Procedures 4-17
To declare variables and constants:
1.
In the Connections navigation hierarchy, click the plus sign (+) beside Packages to
expand the group.
2.
Click the 'plus' beside
emp_eval
to expand the package.
3.
Right-click EMP_EVAL Body.
4.
Select Edit.
emp_eval
Body pane appears.
5.
In the
emp_eval
Body pane, modify function
calculate_score
by adding
variables and constants, as shown by the following code. New code is bold font.
FUNCTION calculate_score(evaluation_id IN NUMBER
, performance_id IN NUMBER)
RETURN NUMBER AS
n_score NUMBER(1,0); -- a variable
n_weight NUMBER; -- a variable
max_score CONSTANT NUMBER(1,0) := 9; -- a constant limit check
max_weight CONSTANT NUMBER(8,8) := 1; -- a constant limit check
BEGIN
RETURN NULL;
END calculate_score;
6.
Use the key combination 'CTRL'+'S' to save the updated package body.
The following message appears in the Messages-Log pane:
EMP_EVAL Body Compiled
Declaring Variables with Structure Identical to Database Columns
calculate_score
by
adding two variables,
n_score
and
n_weight
. These variables will represent values
from tables in the database:
n_score
is stored in the
scores
table, and
n_weight
is
stored in the
performance_parts
table. The data types you used for these variables
match the column data type definitions in the tables.
Over time, applications evolve and the column definitions may change; this may
invalidate the
calculate_score
function. For easier code maintenance, you should
use special qualifiers that declare variables with data types that match the definitions
of the appropriate columns and rows. These qualifiers are
%TYPE
and
%ROWTYPE
.
See Also:
Oracle Database PL/SQL Language Reference for information on
assigning values to variables
