Difference between procedure and function in oracle sql plsql interview question and answers for experienced and fresher

 "It is the most accepted and guaranteed question asked in plsql interview for fresher and experienced candidates."

INTRODUCTION:

Function is calculating the program result on the basis of the given input. It will be returning value or control to the code or calling program. Procedures are calculating or performing the tasks in particular way on the basis of the given inputs. It will be returning the control to the calling function or code, but it will not return any value. In SQL server and DBMS, procedure is termed as stored procedure. It saves their time in terms of reusability and maintainablity.Oracle procedure and function are called as subprograms which are stored in the database as database objects.

FUNCTION:

It can be defined as the set of various instructions received as input from the users for performing the certain tasks.It will return the value. It is used in calculation of the inputs and will provide outputs on the basis of given inputs. It can be pre-defined and user defined. It will perform the required tasks on the basis of the block of codes. It is also called as the named plsql blocks.

PROCEDURE:

It can be defined as the set of various instructions received as input from the users for performing the certain tasks. It will not return the value on the basis of given inputs specified. It is also called as sub-programs. It is the set of plsql commands which is stored in the database with the name as like function. It is used and executed in the anonymous block or with help of exec keyword. It is useful in performance management and optimization, security and database management. It is used in the repetitive tasks and help in automation work.

DIFFERENCE:

Function	Procedure
It will calculate the program result on the basis of the given input.	It will perform the certain tasks in the particular order on the basis of inputs given.
It does not provide the support to try catch blocks.	It provides support to the try catch blocks.
It can be called in the sql query.	It cannot be called in the sql query.
Select statements can have the function calls.	Select statements cannot have the procedure calls.
It can return the returning value or control to the code or calling function.	It can return the control but cannot  return value to the code or calling function.
DML statements are not used.	DML statements are used.
It must deal with expressions.	It must not deal with expressions.
It can be called using procedure.	It cannot be called using function.
Explict exception handling is used.	Explicit exception handling is not used.
Compilation occurs when it is called in the program.	Compilation occurs once and it can be repeatedly and don’t have to compile every single time.

It is not possible to insert, update and delete the data.	It is possible to insert, update and delete the data as operation work recommended to be changes on the database or server as per time.
It is used in an expression.	It is not used in the expression.
It can accept the output parameters. It uses OUT parameter.	It can accept the input parameters. It uses IN,OUT and INOUT parameter.
Temporary tables are not used in functions.	Temporary tables are used in the procedure.
Functions can be called from functions.	Procedures cannot be called from functions.
DML queries are not used.	DML queries are used.
Transaction are not managed in the function.	Transaction are managed in the procedure.
In the header part, return clause is not present.	In the header part, return clause is present.