Difference between function and stored procedure in Oracle with examples

Functions and stored procedures are set of SQL statements that can be called by name. They have many similarities like they take arguments, used to perform required tasks and have same programming style and structure. But we are more interested in differences to grab the knowledge about power they held against each other. So here are some difference between function and stored procedure in Oracle point of view with examples.


Purpose of function

Function is written for a specific task or computations

Return a value

Function return at least one value of any data type. More values can be returned using OUT parameters


Call from SQL statements (Insert, Update, Delete…)

Functions can be called from SQL statements

SELECT function_name() FROM table_name;

Click here to learn history of SQL

DML Statements

DML Statements (Insert, Update, Delete…) can be called inside functions but that function cannot be used in SQL statements

Execution of Functions

Functions are executed in following ways:

  • Inside an SQL query (E.g. Select function_name(parameters) from dual)
  • Using assignment variables (E.g. var := function_name(parameters); )

Stored Procedure

Purpose of procedure

Procedure is written to execute a business logic

Return a value

Procedure may or may not return a value. More values can be returned using OUT parameters (Maximum: 1024)


Call from SQL statements (Insert, Update, Delete…)

Procedures cannot be called from SQL statements

DML Statements

DML Statements can be called inside procedures. You can call that procedure inside other procedures

Execution of Procedures

Procedures are executed in following ways:

  • In Begin…End blocks
  • Using Execute command ( E.g. Execute procedure_name(parameters); )
  • Calling procedures (E.g. procedure_name(parameters);)

Subscribe here and stay updated on latest posts of OracleMine.com.

If you liked the above post put your comments below. Your suggestions and feedback are valuable to us.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.