Oracle
Functions
Functions in oracle serves
the purpose manipulating data items and returning a result. Oracle also allows
definition of User-defined functions.
Functions in oracle can be
classified as Group / Aggregate Functions, which operate on a collection
of rows or columns and returns a value, Single-Row / Scalar Functions
operates on single row of data.
The scalar functions can be
further classified based the type of data they operate on as follows.
String Functions
Numeric Functions
Conversion Functions
Date Functions
Some of the functions along
with the argument types and return values arelisted below.
Group Functions
Function Name & Arguments & type Return value
1. AVG([Distinct|All] n) average
value of n ignoring null values
2. Min([Distinct|All] expr) minimum
value of set of expr values
3. Count(Distinct|All]
expr) number
of rows where expr is not null
4. Count(*) number
of rows in a relation
5. Max(Distinct|All] n) minimum
value of column 'n'
6. Sum(Distinct|All] n) sum of
values of n
Numeric Functions
1. Abs(column | number) absolute
value of column | number
2. Power(base, exponent) value when
'base' raised to the power 'exponent'
3. Round(number, dec) returns the
number rounded to 'dec' places. Default value of
number of places is 0.
*dec
must be integer.
4. Sqrt(number) returns
the squre root of the 'number' as real. if number <0
then returns 'NULL'.
String Functions
1. Lower(char) returns in lower case
2. Upper(char) returns
in upper case
3. Initcap(char) capitalizes
the first character of each word
4. Substr(string, start,
length) return
the substring in 'string' from 'start' position to
'start'
+ 'length' position
5. Length(string) length
of the string
6. Ltrim(str,[set]) removes
characters from the left in 'str' with initial chars
removed upto the first character not in the set.
7. Rtrim(str,[set]) removes
characters from the right in 'str' with chars removed
after the last character not in the set.
8. Lpad(str,n,char) returns
string 'str' left padded to length 'n' with character
'char'.default value of 'char' is space.
9. Rpad(str,n,char) returns
string 'str' right padded to length 'n' with character
'char'.
Conversion Functions
1. To_number(char) converts
the number inthe character form to number data type.
2. To_char(num,[fmt]) converts
the number to the optional format.
ex:
[fmt] = 'Rs.09,99,99,999.99'
3. To_char(date,[fmt]) converts
the date from default format to the format specified.
Ex:
'day month year'
4. To_date(char,[fmt]) converts
the string to the date format specified.
Date Functions
1. Add_months(date1,num) date after
adding the 'num' number of months.
2. Last_day(date) last
date of the month specified in the given date
3.
Months_between(date1,date2) difference
between dates in number of months
4. Next_day(date1,char) * char must
be name of the week. returns the immidiate
next weekday as 'char' after the date date1.