Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle WHEN Clause      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 167 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?