S.no Stored Procedure Function
1. It supports all the parameters of PL/SQL, namely, IN, OUT, and IN-OUT. It supports only input parameters, not the output parameters.
2. They can call functions as and when required. A function can not call a stored procedure.
3. There is no possibility of calling stored procedures from select, having, and where statements. A function can be called using the select statement.
4. Here, transactions can be used. No transactions can be used here.
5. It can undergo exception handling by the insertion of try/catch blocks. There are no provisions for a function to explicitly undergo exception handling.
6. It may or may not return with a value. It has to return with either a result or a value to the caller.
7. All the database operations such as Insert, Delete, Update, etc. can be performed with stored procedures. Only the Select Database operation can be carried out with functions.
8. Stored procedures can fire triggers. Functions can not fire triggers.
9. It can use print commands. A function can not use print commands.
10. Stored Procedures can execute dynamic SQL commands. Functions can not execute dynamic SQL commands.
BY Best Interview Question ON 27 Jun 2020