Oracle database case When control statement use example

A simple case expression that uses an expression to determine the return value.

Grammar:

The code is as follows	Copy Code
Case Search_expression When Expression1 THEN RESULT1 When Expression2 THEN result2 ... When Expressionn THEN Resultn ELSE Default_result

Search the case expression, using criteria to determine the return value.

Grammar:

The code is as follows	Copy Code
Case When Condition1 THEN RESULT1 When Condistion2 THEN result2 ... When Condistionn THEN Resultn ELSE Default_result End

1. The case-when expression has two forms

--Simple Case function

The code is as follows	Copy Code
Case Sex When ' 1 ' THEN ' male ' When ' 2 ' THEN ' woman ' Else ' other ' end --case search function Case When sex = ' 1 ' THEN ' male ' When sex = ' 2 ' THEN ' female ' Else ' other ' end

2. Case when in the statement in different positions in the use
2.1 SELECT Case when usage

Select and case combined to use the most benefits are two points, one is to display the results of the query can be flexible organization format, and the second is to effectively avoid multiple visits to the same table or several tables. Here is a simple example to illustrate. For example, table students (ID, name, birthday, sex, grade), which requires statistics of the number of boys and girls per grade, is the result of the table head, grade, number of boys, number of girls. If you do not need to select Case, in order to show the number of men and women side by side, statistics is very troublesome, first determine the grade information, and then the number of boys and girls according to grade, and very easy to make mistakes

The code is as follows	Copy Code
select    Grade, COUNT (case when sex = 1 THEN 1     /*sex 1 for boys, 2 girls */                         ELSE NULL                         end) Number of boys,                  COUNT (case when sex = 2 THEN 1                         ELSE NULL                          end) Number of girls     from students GROUP by grade;

2.3 WHERE Case Usage

The code is as follows	Copy Code
  SELECT t2.*, t1.*    from T1, T2   WHERE (case when T2. Compare_type = ' A ' and                     T1. Some_type like ' nothing% '                  THEN 1               when T2. Compare_type!= ' A ' and                     T1. Some_type not like ' nothing% '                  THEN 1               ELSE 0            end) = 1

2.4 GROUP by case usage

The code is as follows	Copy Code
SELECT Case when salary <= THEN ' 1 ' When salary > Salary <= THEN ' 2 ' When salary > Salary <= THEN ' 3 ' When salary > Salary <= 1000 THEN ' 4 ' ELSE NULL End Salary_class,--alias name COUNT (*) From Table_a GROUP by Case when salary <= THEN ' 1 ' When salary > Salary <= THEN ' 2 ' When salary > Salary <= THEN ' 3 ' When salary > Salary <= 1000 THEN ' 4 ' ELSE NULL end;