Wednesday, October 23, 2013

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:

Post a Comment

Best Blogger TipsGet Flower Effect