Oracle case statement syntax

The Oracle case statement is one of the most commonly used statements. The following describes the syntax of the Oracle case statement and provides an example to help you.

When multiple condition branch statements are executed, the Oracle case statement is more concise and more efficient. There are two methods for Oracle case statements to process multi-condition branch statements. The first method is to use a single selector for equivalent comparison. The second method is to use multiple conditions for non-equivalent comparison.

1. Use a single selector for equivalent comparison
When you execute an Oracle case statement to execute multiple condition branches, if the condition selection operator is identical and the condition expression is the same condition selection, you can select a single selection operator for equivalent comparison. The syntax is as follows:

Case condition Selector
When condition value expression 1 then the operation to be executed 1;
When condition value expression 2 then the operation to be executed 2;
.......
Else
The operation that you want to perform.
End case;

Example:

 
 

  
  
Declare
  
  
V_count number;
  
  
Begi
  
  
Select count (*) into v_count from cip_temps;
  
  
Case v_count
  
  
When 1 then
  
  
Dbms_output.put_line ('when 1 operation ___ v_cont value: '| v_count );
  
  
When 5 then
  
  
Dbms_output.put_line ('when 5 Operation ___ v_count value: '| v_count );
  
  
When 10 then
  
  
Dbms_output.put_line ('when 10 Operator ____ v_count value: '| v_count );
  
  
Else
  
  
Dbms_output.put_line ('else operator ____ v_cout value: '| v_count );
  
  
End case;
  
  
End;
  
  
/
 
 

2. Compare multiple conditions in case
If you select multiple conditions for different comparisons, you must specify the comparison conditions in the when clause. The syntax is as follows:

Case
When condition value expression 1 then the operation to be executed 1;
When condition value expression 2 then the operation to be executed 2;
.......
Else
The operation that you want to perform.
End case;

Example:

 
 

  
  
Declare
  
  
V_count number;
  
  
Begin
  
  
Select count (*) into v_count from cip_temps;
  
  
Case
  
  
When v_count> 10 then
  
  
Dbms_output.put_line ('when 1 operation ___ v_cont value: '| v_count );
  
  
When v_count> 5 then
  
  
Dbms_output.put_line ('when 5 Operation ___ v_count value: '| v_count );
  
  
When v_count> 4 then
  
  
Dbms_output.put_line ('when 10 Operator ____ v_count value: '| v_count );
  
  
Else
  
  
Dbms_output.put_line ('else operator ____ v_cout value: '| v_count );
  
  
End case;
  
  
End;
 
 

Oracle sqlplus statement Editing Command

Oracle user syntax Modification

Oracle Default User Password Problems

Show you the Oracle explicit cursor

Use instances of Oracle stored procedures