469,366 Members | 2,236 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

PL-SQL Tips (SINGLE ROW FUNCTIONS)

amitpatel66
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 11982

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Hmmm... | last post: by
reply views Thread by mucha3456 | last post: by
3 posts views Thread by D. Stimits | last post: by
3 posts views Thread by Klaus P. Pieper | last post: by
22 posts views Thread by Christopher Murtagh | last post: by
2 posts views Thread by Chris Ochs | last post: by
1 post views Thread by Eric D. Nielsen | last post: by
5 posts views Thread by eranshuman | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.