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.