472,351 Members | 1,586 Online

# PL-SQL Tips (SINGLE ROW FUNCTIONS)

2,367 Expert 2GB
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)
Expand|Select|Wrap|Line Numbers
1. SELECT ROUND(2.555,2) FROM dual -- returns 2.56
2.
Expand|Select|Wrap|Line Numbers
1. DECLARE
2. a NUMBER;
3. BEGIN
4. a:= ROUND(2.555,2);
5. END;
6.
TRUNC(m,[n]) - Truncate the number m to n decimal places (n is optional)
Expand|Select|Wrap|Line Numbers
1. SELECT TRUNC(2.555,2) FROM dual -- returns 2.55
2.
MOD(m,n) - Return the remainder of the number m divided by n
Expand|Select|Wrap|Line Numbers
1. SELECT MOD(3,1) FROM dual -- returns 0
2.
CEIL(m) - Returns smallest integer (Whole Number) without decimal place larger than the number m
Expand|Select|Wrap|Line Numbers
1. SELECT CEIL(1.5) FROM dual -- smallest integer larger than 1.5 is 2 so this query returns 2
2.
FLOOR(m) - Returns largest integer (Whole Number) without decimal place smaller than the number m (Opposite to CEIL)
Expand|Select|Wrap|Line Numbers
1. SELECT FLOOR(1.5) FROM dual -- largest integer smaller than 1.5 is 1 so this query returns 1
2.
SIGN(m) - Retuns +1 if the number m >0 else Returns -1 if the number m is < 0
Expand|Select|Wrap|Line Numbers
1. SELECT SIGN(-235) FROM dual -- returns -1
2. SELECT SIGN(235) FROM dual -- returns 1
3.
POWER(m,n) - Returns multiplication of m, n times
Expand|Select|Wrap|Line Numbers
1. SELECT POWER(2,2) FROM dual -- returns 4
2.
Sep 17 '07 #1
0 12188