What is the difference between a function and a stored procedure in Oracle PL/SQL?

In Oracle PL/SQL, a function and a stored procedure are both stored PL/SQL blocks that can be called from within other PL/SQL blocks or SQL statements. However, there are key differences between the two:

1. Return Value:

A function must return a value to the calling program, whereas a stored procedure may or may not return a value.

2. Usage in SQL Statements:

A function can be used in SQL statements directly, while a stored procedure cannot be used directly in SQL statements.

3. Exception Handling:

Functions cannot have DML (Data Manipulation Language) statements, like INSERT, UPDATE, or DELETE, in exception handling, while stored procedures can have DML statements in exception handling.

4. Transaction Control:

Stored procedures can be used for transaction control using COMMIT and ROLLBACK statements, whereas functions cannot control transactions.

In conclusion, functions are used to perform calculations and return a single value, while stored procedures are used to perform a series of operations or tasks without necessarily returning a value.