Term: WHEN

Definition:
The Oracle WHEN keyword is used in two ways. One use is in the CASE statement to pick among given values. The other use of WHEN is in a conditional INSERT statement.

Using WHEN in the CASE statement
The CASE statement operates like a series of IF statements, only using the key word WHEN.

Example Usage:

An example of WHEN used in a CASE statement that reports a temperature range:

CASE

WHEN n_temperature = 10 THEN v_status := 'very low';

WHEN n_temperature < 20 THEN v_status := 'low';

WHEN n_temperature = 50 THEN v_status := 'medium';

WHEN n_temperature > 80 THEN v_status := 'high';

ELSE v_status := 'very high';

END CASE;

Using WHEN in a conditional INSERT statement
There are two modes for conditional insert: ALL and FIRST.

If you specify ALL, the default value, then the database evaluates each WHEN clause regardless of the results of the evaluation of any other WHEN clause. For each WHEN clause whose condition evaluates to true, the database executes the corresponding INTO clause list.

If you specify FIRST, then the database evaluates each WHEN clause in the order in which it appears in the statement. For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row.

Example Usage:

INSERT

WHEN MOD(deptno,2)=0 THEN

INTO even_employees (empno, ename)

VALUES (empno, ename)

WHEN MOD(deptno,2)=1 THEN

INTO uneven_employees (empno, ename)

VALUES (empno, ename)

ELSE

INTO unknow_employees (empno, ename)

VALUES (empno, ename)

SELECT empno, ename, deptno FROM emp;