add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, December 13

Number Functions in Oracle

------- Round Function ------------Round(n,m)

SELECT 15.193 Num, ROUND (15.136, 1) Rounded FROM DUAL;

SELECT 15.193 Num, ROUND (13.193, -1) Rounded FROM DUAL;

SELECT 45.923 Num,
       ROUND (45.923, 2),
       ROUND (44.923, 0) Rounded,
       ROUND (44.923, -1) Rounded
  FROM DUAL;

------- Trunc Function ------------TRUNC(n,m)

SELECT 15.79 Num, TRUNC (15.74, 0) Truncated FROM DUAL;

SELECT 15.79 Num, TRUNC (158.78899, 4) Truncated FROM DUAL;

SELECT 45.923 Num,
       TRUNC (45.923, 2),
       TRUNC (45.923, 0) Truncated,
       TRUNC (45.923, -1) Truncated
  FROM DUAL;

------- CEIL Function ----------- Ciel(n)

SELECT 15.7 NUM, CEIL (15.7) Ceiled FROM DUAL;

SELECT 14.27 NUM,
       CEIL (14.7) Ceiled,
       CEIL (14.2) Ceiled,
       CEIL (14.1) Ceiled
  FROM DUAL;

------- FLOOR Function ----------- FLOOR(n)

SELECT 15.7 NUM, FLOOR (15.7) FLOOR FROM DUAL;

SELECT 14.27 NUM,
       FLOOR (14.7) FLOOR,
       FLOOR (14.2) FLOOR,
       FLOOR (14) FLOOR
  FROM DUAL;

------- MODULUS Function ----------- MOD(m,n)

SELECT MOD (11, 4) Modulus, MOD (10, 2) Modulus FROM DUAL;

------- POWER Function ------------- Power(m,n)

SELECT POWER (3, 2) POWER, POWER (-3, 2) POWER FROM DUAL;

SELECT POWER (3, -2) POWER, POWER (-3, -2) POWER FROM DUAL;

-------- SQUARE ROOT Function ------- SQRT(N)

SELECT SQRT (625) FROM DUAL;

--------Absolute Function ---------ABS(n)

SELECT ABS (-15) FROM DUAL;

SELECT ABS (15) FROM DUAL;

------- SIGN FUNCTION------------ SIGN(N)

SELECT SIGN (-360), SIGN (15), SIGN (0) FROM DUAL;

SELECT SIGN (-25), SIGN (25), SIGN (0) FROM DUAL;

SELECT ENAME,
       SAL,
       COMM,
       SAL - COMM,
       SIGN (SAL - COMM)
  FROM EMP
 WHERE SIGN (SAL - COMM) = -1;

No comments: