Using the IF ... THEN ... ELSE Statement
Controlling Program Flow
Developing and Using Stored Procedures 4-23
keyword. The general form of the
IF...THEN...[ELSIF]...ELSE
statement
follows:
IF
condition_1
THEN
...;
ELSIF
condition_2
THEN -- optional
...;
ELSE -- optional
...;
END IF;
For example, the sample company could have a rule that an employee evaluation
should be done twice a year (December 31 and June 30) in the first ten years of
employment, but only once a year (December 31) subsequently. You could implement
this rule in an
eval_frequency
function that determines how many times in each
year an evaluation should be performed by using the
IF...THEN...ELSE
clause on
the value of the
hire_date
column.
The function
eval_frequency
uses the
employees.hire_date
value to determine
if evaluations should be performed once each year (over 10 years employment) or
twice each year.
Note also that you will create the function
eval_frequency
in the body of the
emp_
eval
package, but not declare it in the package specification. This means that
eval_
frequency
may be invoked only within the
emp_eval
package, by another
subprogram.
Example 46 Using the IF... THEN...ELSE Selection Control
In the
emp_eval
Body pane, add
eval_frequency
function immediately before the
END emp_eval;
statement, as shown by the following code. The control structures
are in bold font.
FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE)
RETURN PLS_INTEGER AS
hire_date employees.hire_date%TYPE; -- start of employment
today employees.hire_date%TYPE; -- today's date
eval_freq PLS_INTEGER; -- frequency of evaluations
BEGIN
SELECT SYSDATE INTO today FROM DUAL; -- set today's date
SELECT e.hire_date INTO hire_date -- determine when employee started
FROM employees e
WHERE employee_id = e.employee_id;
IF
((hire_date + (INTERVAL '120' MONTH)) < today)
THEN
eval_freq := 1;
ELSE
eval_freq := 2;
END IF
;
RETURN eval_freq;
END eval_frequency;
Compile and save
emp_eval
Body.
See Also:
Oracle Database PL/SQL Language Reference for more information
on
IF...THEN...ELSE
selection control
