Oracle database provides two flavours of functions.
Built-in Functions : Oracle built-in functions help you perform complex operations easily and quickly.
User Defined Functions : Oracle supports user defined functions creation that provides users to encapsulate a sequence of statements and actions. User defined functions are written in PL/SQL code.
Below diagram shows Oracle functions classification.
Built-in Functions in Oracle
Oracle database provides rich set of built-in functions which can be broadly classified into following types.
3.1.1 Single Row functions : The single row functions operate on single row of a queried table and return only one result per row.
3.1.2 Multiple Row Functions : Manipulate on groups of rows and return one result per group of rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups.
3.1.3 Hierarchical Functions : Hierarchical functions applies hierarchical path information to a result set.
3.1.4 Analytical Functions : Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.