Quick Search:
 
 The Oracle WHEN Clause      [Return To Index] Jump to:  

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;



Related Links:

Related Code Snippets:
  • INSERT WHEN - The insert when command can do a conditional insert. There are 2 modes for condi...
  • INSERT ALL WHEN - If you specify ALL, the default value, then the database evaluates each WHEN cla...
  • INSERT FIRST WHEN - The WHEN clause is evaluated in the order in which it appears in the statement...
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org