Using the CASE ... WHEN Statement
Controlling Program Flow
4-24 Oracle Database 2 Day Developer's Guide
Using CASE...WHEN Selection Control
The
CASE...WHEN
construct is a good alternative to nested
IF...THEN
statements if
the variable that determines the course of action has several possible values. The
CASE
evaluates a condition, and performs a different action for each possible value.
Whenever possible, use the
CASE...WHEN
statement instead of
IF...THEN
, both for
readability and efficiency. The general form of the
CASE...WHEN
construct follows:
CASE
condition
WHEN
value_1
THEN
expression_1
;
WHEN
value_2
THEN
expression_2
;
...
ELSE
expression_default
;
END CASE;
Suppose that in the
make_evaluation
hr
user if a long-time
employee who holds one of a select positions should be considered for a salary raise.
Depending on the value of
employees.job_id
, the program logic should notify the
user of the suggested salary raise.
Note that you will use the
DBMS_OUTPUT.PUT_LINE
procedure, described in Oracle
Database PL/SQL Packages and Types Reference.
Example 47 Using CASE...WHEN Conditional Control
In the
emp_eval
Body pane, edit
eval_frequency
function to add a
job_id
variable and a
CASE
statement that is based on the value of the
job_id
, as shown by
the following code. New code is 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
job_id employees.job_id%TYPE; -- category of the job
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;
/* Suggesting salary increase based on position */
SELECT e.job_id INTO job_id FROM employees e
WHERE employee_id = e.employee_id;
CASE job_id
WHEN 'PU_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 8% salary increase for employee number ' || employee_id);
WHEN 'SH_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 7% salary increase for employee number ' || employee_id);
WHEN 'ST_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 6% salary increase for employee number ' || employee_id);
WHEN 'HR_REP' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 5% salary increase for employee number ' || employee_id);
WHEN 'PR_REP' THEN DBMS_OUTPUT.PUT_LINE(
'Consider 5% salary increase for employee number ' || employee_id);
WHEN 'MK_REP' THEN DBMS_OUTPUT.PUT_LINE(
