1. The SIGN (n) function is a symbolic function whose function is to return (1 or 0 or-1).
The following example:
Sql> SELECT SIGN ( -5), SIGN (0), SIGN (5) from dual;
SIGN ( -5) SIGN (0) SIGN (5)
------------------------------
-1 0 1
It can be seen from the results that:
When n < 0 returns-1
n = 0 return 0
n > 0 return 1
2, DECODE (VALUE, If1,then1, If2,then2, If3,then3, DEFAULT)
When value is compared to conditional IFN, values equal to IFN (IFN represents IF1, If2 ...). ), it returns Thenn, and if value is not equal to IFN, he returns default (where value and IFN comparisons are short circuit comparisons such as: value comparison to if2 and equivalence he would not be compared in if3)
The following example:
SELECT level, DECODE (level, 2, ' tow ',
5, ' Five ',
8, ' Eight ',
' default ') to
dual CONNECT by level<10 ;
Level DECODE (
-----------------
1 default
2 tow
3 default
4 default
5 five
6 default
7 default
8 eight
9 default
9 rows selected.
3, SIGN () and Decode () use the example:
When the level<5 output is low
When level<8 output mid
else output high
SELECT level, DECODE ( -1, SIGN (LEVEL-5), "Low",
SIGN (LEVEL-8), ' mid ',
' high ') from
dual CONNECT by level <;
Level DECO
--------------
1 down
2 low
3 low
4 low
5 mid
6 mid
7 mid
8 high
9 High
9 rows selected.