Difference between Function and Procedure in Oracle

Key difference: Functions and procedures are both subprograms of Oracle. The main point of difference which lies between them is that functions always return a value, but procedures may or may not.

Oracle, as we know, is a widely used coding language. A program is known as a set of codes and instructions in technical or coding terminology. These programs consist of small modules or mini programs in them, which are devised for performing specific tasks. These sub-programs are assigned various tasks, and are invoked through a calling program or by another subprogram in the event of accomplishing a certain specified task.

Functions and procedures are nothing but subprograms in Oracle. They are used for carrying out a set of particular instructions from the user. For instance, the function AVG () is used to calculate the average of all the values that are provided to a program as input. Similarly, procedures are also used for such specific tasks in a program. However, the striking distinction between the two is that a function always returns a value, but a procedure doesn’t always return one. A procedure accomplishes the task successfully, but it may or may not return a value.

Comparison between Function and Procedure:




Return of value

Always returns a value.

May or may not return a value.

Tasks performed

Function performs a specific task.

A procedure may perform multiple tasks.

Calling from SQL statements

Such calling is possible for functions.

Such calling isn’t feasible for procedures.


Functions are typically used to perform computations.

Procedures are used to execute business logic.

Independent execution

A function doesn’t execute independently. It has to be a part of the executable statement.

A procedure itself represents an executable statement, so it can run independently.

Image Courtesy: oracle.ehelp365.com, apex.oracle.com

Most Searched in Computers and Internets Most Searched in Education and References
Most Searched in Cars and Transportation Most Searched in Home and Garden
Corn vs Sweet Corn
Oxycontin vs Oxycodone
Internet vs Intranet vs Extranet
HTC Butterfly vs LG Optimus G

Add new comment

Plain text

This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.