By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,245 Members | 1,180 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

PL-SQL Tips (SINGLE ROW FUNCTIONS)

amitpatel66
Expert 100+
P: 2,367
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
Share this Article
Share on Google+