Oracle常用的數學函數

Oracle常用的數學函數有：CEIL、FLOOR、ROUND、TRUNC、ABS、MOD、POWER
以下針對上述的數學函數做介紹：

• CEIL(n)：傳回數字的最大整數
  EX1:SQL> SELECT CEIL(5.52) FROM DUAL;
  CEIL(5.52)
  ----------
  6
  
  EX2:SQL> SELECT CEIL(-2.01) FROM DUAL;
  CEIL(-2.01)
  -----------
  -2
• FLOOR(n)：傳回數字的最小整數
  EX1:SQL> SELECT FLOOR(5.52) FROM DUAL;
  FLOOR(5.52)
  -----------
  5
  
  EX2:SQL> SELECT FLOOR(-5.52) FROM DUAL;
  FLOOR(-5.52)
  ------------
  -6
• ROUND(n [,m])：對n值做四捨五入，如有輸入m值，則表示由n值小數點前、後第m位做四捨五入，m需為整數，預設值為0
  EX1:SQL> SELECT ROUND(5.52) FROM DUAL;
  ROUND(5.52)
  -----------
  6
  
  EX2:SQL> SELECT ROUND(5.52, 1) FROM DUAL;
  ROUND(5.52,1)
  -------------
  5.5
  
  EX3:SQL> SELECT ROUND(25.52, -1) FROM DUAL;
  ROUND(25.52,-1)
  ---------------
  30
• TRUNC(n [,m])：對n值做無條件捨去，如有輸入m值，則表示由n值小數點前、後第m位做無條件捨去，m需為整數，預設值為0
  EX1:SQL> SELECT TRUNC(5.52) FROM DUAL;
  TRUNC(5.52)
  -----------
  5
  
  EX2:SQL> SELECT TRUNC(5.52, 1) FROM DUAL;
  TRUNC(5.52,1)
  -------------
  5.5
  
  EX3:SQL> SELECT TRUNC(25.52, -1) FROM DUAL;
  TRUNC(25.52,-1)
  ---------------
  20
  
• ABS(n)：對n值取絕對值
  EX1:SQL> SELECT ABS(5.52) FROM DUAL;
  ABS(5.52)
  ---------
  5.52
  
  EX2:SQL> SELECT ABS(-5.52) FROM DUAL;
  ABS(-5.52)
  ----------
  5.52
  
• MOD(n, m)：取得n值除以n值後的餘數
  EX1:SQL> SELECT MOD(18, 3) FROM DUAL;
  MOD(18,3)
  ---------
  0
  
  EX2:SQL> SELECT MOD(18, 5) FROM DUAL;
  MOD(18,5)
  --------
  3
  
• POWER(n, m)：取得n值的m次方根
  EX1:SQL> SELECT POWER(3, 8) FROM DUAL;
  POWER(3,8)
  ----------
  6561
  
  EX2:SQL> SELECT POWER(27, -3) FROM DUAL;
  POWER(27,-3)
  ------------
  .00005081