PL-SQL Tips (SINGLE ROW FUNCTIONS)

[amitpatel66]

All,

Please find below POST about Oracle SINGLE ROW FUNCTIONS which might be useful.

SINGLE ROW FUNCTION are those which are executed once for each and every row of the Query.

The different types of SINGLE ROW FUNCTIONS are:

1. NUMBER Functions
2. DATE Functions
3. CHARACTER Functions
4. CONVERSION Functions
5. GENERAL Functions

Let us categorize some of the SINGLE ROW FUNCTIONS and its usage:

NUMBER Functions:

ROUND(m,[n]) - Round the number m to n decimal places (n is optional)

SELECT ROUND(2.555,2) FROM dual -- returns 2.56
 

DECLARE
a NUMBER;
BEGIN
a:= ROUND(2.555,2);
END;
 

TRUNC(m,[n]) - Truncate the number m to n decimal places (n is optional)

SELECT TRUNC(2.555,2) FROM dual -- returns 2.55
 

MOD(m,n) - Return the remainder of the number m divided by n

SELECT MOD(3,1) FROM dual -- returns 0
 

CEIL(m) - Returns smallest integer (Whole Number) without decimal place larger than the number m

SELECT CEIL(1.5) FROM dual -- smallest integer larger than 1.5 is 2 so this query returns 2
 

FLOOR(m) - Returns largest integer (Whole Number) without decimal place smaller than the number m (Opposite to CEIL)

SELECT FLOOR(1.5) FROM dual -- largest integer smaller than 1.5 is 1 so this query returns 1
 

SIGN(m) - Retuns +1 if the number m >0 else Returns -1 if the number m is < 0

SELECT SIGN(-235) FROM dual -- returns -1
SELECT SIGN(235) FROM dual -- returns 1
 

POWER(m,n) - Returns multiplication of m, n times

SELECT POWER(2,2) FROM dual -- returns 4