Use of case and decode in Oracle __oracle

Case when it is convenient to use when different conditions require different return values, you can use them when assigning values to variables, or you can use them in a SELECT query statement.
Case Search Statement format:

Case
When condition 1 then return value 1
When condition 2 then return value 2
...
else return value n

End

Example:
Declare
I integer;
STR VARCHAR2 (20);
Begin
I: = 3;
STR: = case
When i = 1 Then ' a '
When i = 2 Then ' B '
When i = 3 Then ' C '
End;
Dbms_output.put_line (str);
End

Case expression is a statement format:

Case conditions
When value 1 then return value 1
When value 2 then return value 2
...
else return value n
End

Example:
Declare
I integer;
STR VARCHAR2 (20);
Begin
I: = 3;
str: = case I
When 1 Then ' a '
When 2 Then ' B '
When 3 Then ' C '
End
Dbms_output.put_line (str);
End

Use the format of case when in SQL statements:
Select Case Condition
When value 1 then return value 1
When value 2 then return value 2
...
else return value n
End [case] from ...

Select Case
When condition 1 then return value 1
When condition 2 then return value 2
...
else return value n
End [case] from ...

Example:
Select Case Dtype when ' factory ' then ' refinery ' when ' storage ' then ' oil depot ' when ' pipeline ' then ' pipe ' else ' other ' end case from ACC_ domain;
Select Case when dtype= ' factory ' then ' refinery ' when dtype= ' storage ' then ' oil depot ' when dtype= ' pipeline ' then ' pipe ' else ' other ' end CA Se from Acc_domain;

Where the Select Case condition
When value 1 then return value 1
When value 2 then return value 2
...
else return value n
End [case] from ... Features are similar to decode.

Decode use Format: decode (variable or expression, value 1, return value 1, value 2, return value 2,..., default value)

Alternatively, when and decode can be used to make column conversion.
--Column Careers
CREATE TABLE S (
Name Nvarchar2 (10),
Course Nvarchar2 (10),
Grade int
);

Insert into S (Name, Course, Grade) VALUES (' John ', ' language ', 80);
Insert into S (Name, Course, Grade) VALUES (' John ', ' mathematics ', 86);
Insert into S (Name, Course, Grade) VALUES (' John ', ' English ', 75);
Insert into S (Name, Course, Grade) VALUES (' Dick ', ' language ', 78);
Insert into S (Name, Course, Grade) VALUES (' Dick ', ' mathematics ', 85);
Insert into S (Name, Course, Grade) VALUES (' Dick ', ' English ', 70);

SELECT * from S;

Select Name,
SUM (Decode (Course, ' language ', Grade, 0)) language,
SUM (Decode (Course, ' mathematics ', Grade, 0)) Mathematics,
SUM (Decode (Course, ' English ', Grade, 0)) English
From S
Group BY Name;

Use today to make a small mistake and toss it for half a day in this record. (Added April 14, 2012)

   Select Case 1 when 
         1 Then ' not accepted ' when
         2 Then ' has been accepted '
         else ' unconfirmed ' end case from dual;--correct

But want to give the field from the individual name Pl/sql but reported "ORA-00923: did not find the required from keyword" error, but from the keyword clearly ah, debugging a half-day found to be "end case" caused the curse, will "end case" replaced by "case" problem solved:

  Select Case 1 when 
         1 Then ' not accepted ' when
         2 Then ' accepted '
         else ' unconfirmed ' end status from dual;--alias

To further clarify the use of "end" and "End case" you have done two examples:

--Created on 2012/4/14 by Cuisea 
declare 
  i integer;
  s varchar2 (MB);
Begin
  i:=2;
  S:=case I when 
         1 then ' not accepted ' when
         2 then ' has accepted '
         else ' unconfirmed ' end;--must use ' end ' and cannot only be dbms_output with ' end Case '
  . Put_Line (s);
End

--Created on 2012/4/14 by Cuisea 
declare 
  i integer;
  s varchar2 (MB);
Begin
  i:=2;
  Case I when 
         1 then s:= ' not accepted ';
         When 2 then s:= ' has been accepted ';
         else s:= ' unacknowledged ';
         End case;--must use "End case", not only "end"
  dbms_output.put_line (s);
End

This means that you want to use "end" in an expression, and you need to use "End case" in pl/sql process judgments. But this is not absolute, the use of the time to pay attention to the point on the line.