SQL- Inbuilt Functions in Oracle

Functions play a vital role in data processing and reporting needs of an organization. Oracle provides many predefined functions to manipulate character, numeric and date data. There are many manipulation, conversion and other general functions to present data in required forms.

Types of Inbuilt Functions in Oracle

The inbuilt functions in Oracle are mainly categorized into two broad categories-

 Inbuilt Function in Oracle

 

Single Row Functions

The functions that act on individual rows of the data of table selected on the basis of the filter criteria are called Single Row Functions. In other word a single row function has a defined set of arguments using which the function operates on each single column value returned by the SELECT statement.

All the Single Row functions take one or more arguments. Usually you pass on the name of the column as argument on which want to apply the function. The output of the function will be displayed for value of the specified column of every qualifying row.

Single row functions are further categorized as

Character Functions– These functions are used to manipulate the character or string data like concatenation, finding length of a string etc..

Number Functions– These functions work on numeric values stored in table columns. These functions perform mathematical or arithmetic operations on the columns of the table to which these are applied.

Date Functions– If you need to manipulate the date values like finding days between two dates or extracting a part of a date value, you will need Date Functions.

Conversion Functions– Sometimes you may want to convert one data type into another. The Conversion Functions will come handy here.

Multiple Row or Group Functions or Aggregate Functions

The functions that act on a set of rows selected by the SELECT statement are called Multiple Row or Group Functions or Aggregate Functions. These rows are returned on the basis of the Filter condition applied in WHERE clause.

The Group functions take all the values returned by the query as arguments and return a single value as output. In other words the arguments count of an Aggregate Function is variable. It is not known beforehand. For example adding up salaries of all employees to get total salary.