Advantage of PL/SQL procedure and function - when to use ?

What is the difference between PL/SQL procedure and function ?

When is better to use procedure and function ?

Hi,

Both procedures and functions can be described as a group of SQL statements, that perform a specific task. They are usually used to perform a more complex operations. Procedures and functions might look very similar in some cases, but there are several key differences between them:

User Defined Function (UDF)

• Is used to get a result using given inputs. It cannot be used to modify the database (statements INSERT, UPDATE or DELETE are not allowed).
• Can have only Input parameter.
• Should return a single value.
• Procedure cannot be called from a function.
• Transactions are not possible.
• Can be used in the SQL statements in the SELECT, WHERE or HAVING section.

Stored Procedure (SP)

• Can be used to modify the database (statements INSERT, UPDATE or DELETE can be used).
• Can have Input and Output parameters.
• Can return zero, single or multiple values.
• Function can be called from a procedure.
• Transactions are possible.
• Cannot be used in the SQL statements in the SELECT, WHERE, or HAVING section.