Difference between Scalar Function, Table-Valued function, and Aggregate Function in Oracle

Scalar Function:-  Scalar Function are the function that returns a single value, not the result set. we can not use aggregate functions in the scalar function because scalar functions execute row by row, not in the set of values.

E.g:- Upper(), Lower(),Length() etc.

Query:- Select Upper('database dynasty') from dual;

O/P:-      DATABASE DYNASTY

Table-Valued Function:- Table-Valued functions return result set and it can be used in  a From clause, Joins , Outer apply of any query but it can not target any DML operations like (Insert, Update, Delete).

E.g:- we have two tables one is EmployeesDetails and another one is SalaryDetails.

EmployeesDetails			SalaryDetails
Id	Name		Id	EmployeesDetailsId	Salary
1	Ravi		101	1	2000.50
2	Yash		102	2	3050.50

Create or replace Type Typ_EmployeesDetails as Object

(Id Number(10),

Name Varchar2(100));

O/P:- TYPE TYP_EMPLOYEESDETAILS compiled

Create or replace type Tbl_EmployeesDetails as table of Typ_EmployeesDetails;

O/P:- TYPE Tbl_EmployeesDetails compiled

Create or replace function FN_EmployeeDetails

return Tbl_EmployeesDetails

Is 

V_Emp Tbl_EmployeesDetails;

Begin

Select Typ_EmployeesDetails (Id,Name)

Bulk Collect into V_Emp

from EmployeesDetails;

Return V_Emp;

End;

/

O/P:- FUNCTION FN_EMPLOYEEDETAILS compiled

To Execute or use the function with join in select statement.

Select Emp.ID as EmployeeId, Emp.Name,s.Salary

from SalaryDetails s

Inner Join Table(FN_EmployeeDetails())Emp on Emp.Id=s.EmployeeDetailsId;

O/P:- 

EmployeeId	Name	Salary
1	Ravi	2000.50
2	Yash	3050.50

Aggregate Function:- Aggregate Function are user-defined aggregates like Sum(), Count(), Min(), Max() etc. and it typically requires the Group By clause. 

E.g:-  Suppose we have table called Emp 

Query:- Select * from Emp;

O/P:-   

Id	Name
101	Database
102	Dynasty

Condition:- Check the total number from records in Emp table
Query:- Select Count(*) from Emp;
O/P:-    2