### What is Function in Oracle?

A function is a subprogram that returns a single value. We must declare and define a function before invoking it. We can either declare or define it at the same time, or we can declare it first and then define it later in the same block.

Types of Functions in Oracle:

- Single Row Functions: Single row or Scalar functions return a value for every row that is processed in a query.
- Group Functions: These functions group the rows of data based on the values returned by the query. This is discussed in Oracle Group Functions. The group functions are used to find out aggregate values like sum or average, which return just one sum or one average value after processing a group of rows.

Types of Single Row Functions:

- Numeric Functions: These are functions that accept numeric input and return numeric values.
- Character or Text Functions: These are functions that accept character input and can return both character and number values.
- Date Functions: These Functions takes input in the form of DATE Datatype(in format of Date) and outputs values of DATE
- Datatype, except for the MONTHS_BETWEEN function, which returns a number.
- Conversion Functions: These are functions that help us to convert a value in one datatype to another datatype. For Example: a null/zero value into an actual value, or a value from one form to another form like TO_NUMBER, TO_DATE, NVL_TO_CHAR, etc.
- Aggregate Functions:Aggregate functions are used in Oracle to find aggregate of numbers or perform some simple operations like Sum (), Round (), MIN () etc.

**Note**: We can merge more than one function in an expression. This is called nesting of functions.

Concept of Dual Table in Oracle:

This is a single row and single column dummy table is stored in oracle (by default). This is used to perform mathematical calculations without using a valid table. This table is by default defined in Oracle Ex:

Select * from DUAL

Output:

DUMMY

-------

X

Ex:

Select 777 * 888 from Dual

Output:

777 * 888

---------

689976

### Numeric Functions

Numeric functions are used to perform operations on numbers. They use numeric data as input and return numeric data as result.

Some of the Numeric functions are given below:

Function Name | Return Value | Query | Output |

ABS (x) | Absolute value of the number 'x' | ABS(1)ABS(-1) | 1-1 |

CEIL (x) | Integer value that is Greater than or equal to the number 'x' | CEIL (2.83) CEIL (2.49) CEIL (-1.6) | 33-1 |

FLOOR (x) | Integer value that is Less than or equal to the number 'x' | FLOOR (2.83) FLOOR (2.49) FLOOR (-1.6) | 22-1 |

TRUNC (x, y) | Truncates value of number 'x' up to 'y' decimal places | ROUND (125.456, 1)ROUND (125.456, 0)ROUND (124.456, -1) | 125.4125120 |

ROUND (x, y) | Rounded off value of the number 'x' up to the number 'y' decimal places | TRUNC (140.234, 2) TRUNC (-54, 1) TRUNC (5.7) TRUNC (142, -1) | 140.23545140 |

These functions are used in database columns:

Ex: Let's consider the product table used in oracle joins. We can use ROUND function to round off the unit_pric to the nearest integer, if any product has prices in real fraction.

SELECT ROUND (unit_pric) FROM product11; |

### Character or Text Functions

Character or text functions are used to manipulate text strings. They takes strings or characters as raw data and can results both character and number values as processed data.

Some of the character or text functions are as given below:

Function Name | Return Value | Query | Output |

LOWER (string_value) | All the letters in 'string_value' is converted to lowercase. | LOWER('Good Morning') | good morning |

UPPER (string_value) | All the letters in 'string_value' is converted to uppercase. | UPPER('Good Morning') | GOOD MORNING |

INITCAP (string_value) | All the letters in 'string_value' is converted to mixed case. | INITCAP('GOOD MORNING') | Good Morning |

LTRIM (string_value, trim_text) | All occurrences of 'trim_text' is removed from the left of 'string_value'. | LTRIM ('Good Morning', 'Good) | Morning |

RTRIM (string_value, trim_text) | All occurrences of 'trim_text' is removed from the right of'string_value' . | RTRIM ('Good Morning', ' Morning') | Good |

TRIM (trim_text FROM string_value) | All occurrences of 'trim_text' from the left and right of 'string_value' ,'trim_text' can also be of atmost one character long. | TRIM ('o' FROM 'Good Morning') | Gd Mrning |

SUBSTR (string_value, m, n) | Returns 'n' number of characters from'string_value' starting from the 'm'position. | SUBSTR ('Good Morning', 6, 7) | Morning |

LENGTH (string_value) | Number of characters in 'string_value'in returned. | LENGTH ('Good Morning') | 12 |

LPAD (string_value, n, pade_value) | Returns 'string_value' left-padded with'pade_value' . The size of the whole given string will be of 'n' characters. | LPAD ('Good', 6, '*') | **Good |

RPAD (string_value, n, pade_value) | Returns 'string_value' right-padded with 'pade_value' . The size of the whole given string will be of 'n' characters. | RPAD ('Good', 6, '*') | Good** |

For Example, we can use the above UPPER() text function with the column value as follows.

SELECT UPPER (product_name) FROM product; |

### Date Functions

These are the functions of Oracle that take values that are of datatype DATE as input and return values of data types DATE, except for the MONTHS_BETWEEN function, which returns a number as output.

Few date functions are as given below.

Function Name | Return Value | Query | Output |

ADD_MONTHS (date, n) | Returns a date value after adding 'n'months to the date 'x'. | ADD_MONTHS ('16-Sep-81', 3) | 16-Dec-81 |

MONTHS_BETWEEN (x1, x2) | Returns the number of months between dates x1 and x2. | MONTHS_BETWEEN ('16-Sep-81', '16-Dec-81') | 3 |

ROUND (x, date_format) | Returns the date 'x' rounded off to the closest century, year, month, date, hour, minute, or second as specified by the 'date_format'. | round(to_date ('22-AUG-03'),'YEAR') | '01-JAN-04' |

TRUNC (x, date_format) | Returns the date 'x' lesser than or equal to the closest century, year, month, date, hour, minute, or second as specified by the 'date_format'. | TRUNC (to_date ('22-AUG-03'),'YEAR') | 01-JAN-13 |

NEXT_DAY (x, week_day) | Returns the next date of the 'week_day'on or after the date 'x' occurs. | NEXT_DAY ('01-Jun-08', 'Wednesday') | 04-JUN-08 |

LAST_DAY (x) | It is used to determine the number of days remaining in a month from the date 'x' specified. | LAST_DAY ('01-Jun-08') | 30-Jun-08 |

SYSDATE | Returns the systems current date along with time. | SYSDATE() | 2006-04-12 13:47:44 |

NEW_TIME (x, zone1, zone2) | Returns the date and time in zone2 if date 'x' represents the time in zone1. | NEW_TIME ('01-Jun-08', 'IST', 'EST') | 31-May-08 |

### Conversion Functions

These are functions that help us to convert a value in one form to another form. For Ex: a null/zero value into an actual/formal value, or a value from one datatype to some other datatype like TO_NUMBER, NVL, TO_CHAR ,TO_DATE.

Some of the conversion functions used in Oracle are:

Function Name | Return Value | Query | Output |

TO_CHAR (x [,y]) | Converts Numeric and Date values to a character string value. It cannot be used for account since it is a string value. | TO_CHAR (SYSDATE, 'Day, Month YYYY') | Monday, June 2008 |

TO_DATE (x [, date_format]) | Converts a valid Numeric and Character values to a Date value. Date is patterned to the rule specified by 'date_format'. | TO_DATE ('01-Jun-08') | 01-Jun-08 |

NVL (x, y) | If 'x' is NULL, replace it with 'y'. 'x' and 'y' must be of the same datatype. | NVL (null, 1) | 1 |

DECODE (a, b, c, d, e, default_value) | Checks the value of 'a', if a = b, then returns 'c'. If a = d, then returns 'e'. Else, returns default_value. |

### Aggregate Functions

This Functions are used to perform some simple operations on numeric data or numbers.

Function Name | Return Value | Query | Output |

SUM() | compute sum | SUM(Salary) | 50000 |

COUNT() | counts a number of rows | Count(NO_of_Employee) | 20 |

AVG() | compute average | AVG(Age) | 500 |

MIN() | compute minimum | Min(Age) | 25 |

MAX() | compute maximum | Max(Age) | 45 |

### Conclusion:

In this article we studied how we can perform certain functions like conversion, date, numeric and text functions with efficiency and quickly. It introduces user-defined aggregate functions evident that how to create and use them, either in single or in parallel, and represents how to work with large aggregation contexts and materialized views.