Assigning Values from the Database
Using Variables and Constants
4-20 Oracle Database 2 Day Developer's Guide
You can also assign values to variables within the body of a subprogram. You will edit
the function
calculate_score
by using the
running_total
variable inside the
body of the function to hold a value of an expression.
Example 43 Assigning variable values in the body of a function
In the
emp_eval
Body pane, modify function
calculate_score
by assigning to the
running_total
variable the value of an expression, 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
running_total NUMBER :=0; -- used in calculations
max_score CONSTANT scores.score%TYPE := 9; -- a constant limit check
max_weight CONSTANT performance_parts.weight%TYPE:= 1;
-- a constant limit check
BEGIN
running_total := max_score * max_weight;
RETURN running_total;
END calculate_score;
Compile and save
emp_eval
Body.
Assigning Values from the Database
The simplest possible assignment of a value is to use the assignment operator (:=) as
you did for the variable
running_total
in
However, the purpose of function
calculate_score
is to perform a calculation
based on values stored in database tables. To use existing database values in a
procedure, function, or package, you must assign these values to a variable by using a
SELECT INTO
statement. You can then use the variable in subsequent computations.
Example 44 Assigning to a variable a values from the database
In the
emp_eval
Body pane, modify function
calculate_score
by assigning the
table values to the variables
n_score
and
n_weight
, and then assigning their
product to the
running_total
variable, 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
running_total NUMBER := 0; -- used in calculations
max_score CONSTANT scores.score%TYPE := 9; -- a constant limit check
max_weight CONSTANT performance_parts.weight%TYPE:= 1;
-- a constant limit check
BEGIN
See Also:
Oracle Database PL/SQL Language Reference for information on
assigning values to variables
