· Meaning:
Decode(Condition, value 1, translation value 1, value 2, translation value 2,... value n, translation value n, default value)
The function has the following meanings:
If condition = value 1 then
Return (translation value 1)
Elsif condition = value 2 then
Return (translation value 2)
......
Elsif condition = value n then
Return (translation value n)
Else
Return (default)
End if
· Usage:
1. Compare the size
SelectDecode(Sign (variable 1-variable 2),-1, variable 1, variable 2) from dual; -- smaller value
The sign () function returns 0, 1, and-1 respectively based on a value of 0, positive, or negative.
For example:
Variable 1 = 10, variable 2 = 20
Then sign (variable 1-variable 2) returns-1,DecodeThe decoded result is "variable 1", so that a smaller value is obtained. Students whose student ID table is student must use the decode function to implement the following functions: score> 85, excellent;> 70: Good;> 60: pass; otherwise, fail. Suppose student is ID and score is score:
Select ID, decode (sign (score-85), 1, 'excellent ', 0, 'excellent',-1,
Decode (sign (score-70), 1, 'good', 0, 'good',-1,
Decode (sign (score-60), 1, 'pass', 0, 'pass',-1, 'failed ')))
From student;