The Decode function is similar to a series of nested IF-THEN-ELSE statements
Select decode (sign (variable 1-variable 2), 1, variable 1, variable 2) from dual; --Take a smaller value
The sign () function returns 0, 1, 1, depending on whether a value is 0, positive, or negative.
For example:
Variable 1=10, variable 2=20
SIGN (variable 1-variable 2) returns the -1,decode decoding result as "Variable 1", which achieves the purpose of taking a smaller value.
Columns :
There are students score table student, now to use the Decode function to achieve the following several functions: Performance >85, show excellent, >70 show good, >60 pass;
Assuming the student number is ID and the score is score, then:
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, ' fail '))
from student;
The use of function decode in database Oracle