Difference between Oracle function and Procedure

A procedure is a set of PL/SQL commands stored in the database with a name. A procedure is similar to a function because both are stored in the database. A procedure, however, is more versatile and can accomplish more. The following table outlines the similarities and differences between procedures and functions.

Oracle7 introduced stored procedures written in PL/SQL, the proprietary language of Oracle for writing application logic. These procedures are stored in the database and executed by clients issuing remote procedure calls (RPCs) as opposed to executing SQL statements. Instead of issuing multiple SQL calls, occasionally with intermediate logic to accomplish a task, the client issues one procedure call, passing in the required parameters. The database executes all the required SQL and logic using the parameters it receives. Stored procedures can also shield the client logic from internal changes to the data structures or program logic. As long as the parameters the client passed in and received back do not change, no changes are required in the client software. Stored procedures move a portion of the application logic from the client to the database server. By doing so, stored procedures can reduce the network traffic considerably. This capability increases the scalability of two-tier systems.

You usually create a function when you want to perform some complex calculations and then use the calculations in other sub-programs or in SQL commands. You usually create a procedure when you need to make changes to data within the sub-program. The function has limitations that the procedure does not, so there are more instances in which a procedure is appropriate.
In the next lesson, you will learn how to create a procedure.